Solved Mac client - VERY slow number dialling when a lot of contacts

Discussion in '3CX Clients' started by kwenix, May 2, 2017.

Thread Status:
Not open for further replies.
  1. kwenix

    Joined:
    May 2, 2017
    Messages:
    5
    Likes Received:
    0
    We have 4000+ contacts in the 3CX database.

    When dialing a number on the 3CX (Mac) client version 15.1.0.76 we have following behavior: after entering the first digit of a phone number the client ‘stalls’ for 10 seconds, trying to match that first digit to 1 of our 4000+ contacts. As a user you start to doubt if you entered the first digit correctly, so you enter it again. After 10s the rest of the numbers that you dialled appear, making the manual dialling a BIG pain.

    When contacting support that this feature 'works as designed' and that I should submit my 'request' to this forum...

    The MAC client should not IMMEDIATELY start searching on the first digit, but wait for maybe first x digits (parameter to configure in preferences) before trying to match a (partial) number to a contact.

    Furthermore client could limit matching process to first xx records (again parameter to set in preferences (this translates into a select TOP xx ...)) so that the client does NOT try to return a dropdownlist of potentially 4000 contacts.
     
  2. kwenix

    Joined:
    May 2, 2017
    Messages:
    5
    Likes Received:
    0
    I also noticed there's NO index on the phonebook table.
     
  3. kwenix

    Joined:
    May 2, 2017
    Messages:
    5
    Likes Received:
    0
    in the sql log file I get following select statements

    SELECT "Tbl1003"."firstname" "Col1048","Tbl1003"."lastname" "Col1049","Tbl1003"."phonenumber" "Col1050","Tbl1003"."company" "Col1051" FROM "database_single"."public"."phonebook" "Tbl1003"
    LOG: statement: SELECT "Tbl1008"."firstname" "Col1054","Tbl1008"."lastname" "Col1055","Tbl1008"."company" "Col1056","Tbl1008"."pv_an1" "Col1057" FROM "database_single"."public"."phonebook" "Tbl1008"
    LOG: statement: SELECT "Tbl1013"."firstname" "Col1060","Tbl1013"."lastname" "Col1061","Tbl1013"."company" "Col1062","Tbl1013"."pv_an2" "Col1063" FROM "database_single"."public"."phonebook" "Tbl1013"
    LOG: statement: SELECT "Tbl1018"."firstname" "Col1066","Tbl1018"."lastname" "Col1067","Tbl1018"."company" "Col1068","Tbl1018"."pv_an3" "Col1069" FROM "database_single"."public"."phonebook" "Tbl1018"
    LOG: statement: SELECT "Tbl1023"."firstname" "Col1072","Tbl1023"."lastname" "Col1073","Tbl1023"."company" "Col1074","Tbl1023"."pv_an4" "Col1075" FROM "database_single"."public"."phonebook" "Tbl1023"
    LOG: statement: SELECT "Tbl1028"."firstname" "Col1078","Tbl1028"."lastname" "Col1079","Tbl1028"."company" "Col1080","Tbl1028"."pv_an5" "Col1081" FROM "database_single"."public"."phonebook" "Tbl1028"
    LOG: statement: SELECT "Tbl1033"."firstname" "Col1084","Tbl1033"."lastname" "Col1085","Tbl1033"."company" "Col1086","Tbl1033"."pv_an6" "Col1087" FROM "database_single"."public"."phonebook" "Tbl1033"

    which are 7 separate select statements, which equals 7 roundtrips to sql server.
    This can be optimised into 1 select statement, where the sql server will eliminate duplicates (union statement) for you and no need to return empty fields...

    SELECT "Tbl1003"."firstname" "Col1048","Tbl1003"."lastname" "Col1049","Tbl1003"."phonenumber" "Col1050","Tbl1003"."company" "Col1051" FROM "database_single"."public"."phonebook" "Tbl1003" where length(COALESCE("Tbl1003"."phonenumber", '')) > 0
    union
    SELECT "Tbl1008"."firstname" "Col1054","Tbl1008"."lastname" "Col1055","Tbl1008"."company" "Col1056","Tbl1008"."pv_an1" "Col1057" FROM "database_single"."public"."phonebook" "Tbl1008" where length(COALESCE("Tbl1008"."pv_an1", '')) > 0
    union
    SELECT "Tbl1013"."firstname" "Col1060","Tbl1013"."lastname" "Col1061","Tbl1013"."company" "Col1062","Tbl1013"."pv_an2" "Col1063" FROM "database_single"."public"."phonebook" "Tbl1013" where length(COALESCE("Tbl1013"."pv_an2", '')) > 0
    union
    SELECT "Tbl1018"."firstname" "Col1066","Tbl1018"."lastname" "Col1067","Tbl1018"."company" "Col1068","Tbl1018"."pv_an3" "Col1069" FROM "database_single"."public"."phonebook" "Tbl1018" where length(COALESCE("Tbl1018"."pv_an3", '')) > 0
    union
    SELECT "Tbl1023"."firstname" "Col1072","Tbl1023"."lastname" "Col1073","Tbl1023"."company" "Col1074","Tbl1023"."pv_an4" "Col1075" FROM "database_single"."public"."phonebook" "Tbl1023" where length(COALESCE("Tbl1023"."pv_an4", '')) > 0
    union
    SELECT "Tbl1028"."firstname" "Col1078","Tbl1028"."lastname" "Col1079","Tbl1028"."company" "Col1080","Tbl1028"."pv_an5" "Col1081" FROM "database_single"."public"."phonebook" "Tbl1028" where length(COALESCE("Tbl1028"."pv_an5", '')) > 0
    union
    SELECT "Tbl1033"."firstname" "Col1084","Tbl1033"."lastname" "Col1085","Tbl1033"."company" "Col1086","Tbl1033"."pv_an6" "Col1087" FROM "database_single"."public"."phonebook" "Tbl1033" where length(COALESCE("Tbl1033"."pv_an6", '')) > 0

    This change alone reduced returned number of records from 32K to 16K (50%...) and query took 1.1s.
    I did not find any number lookup in the sql database, which means clients loads full contact database (into memory ?) and all searches are done locally on the client. Using an index mechanism (if not already in place) will also speed up local searches.
     
  4. kwenix

    Joined:
    May 2, 2017
    Messages:
    5
    Likes Received:
    0
    Edit:

    The first SQL statement has other field ordering in select statement (I just copied this from the logs...)
    This is correct statement, having all the fields in the select statement in the SAME place/sequence.

    SELECT "Tbl1003"."firstname" "Col1048","Tbl1003"."lastname" "Col1049","Tbl1003"."company" "Col1051", "Tbl1003"."phonenumber" "Col1050" FROM "database_single"."public"."phonebook" "Tbl1003" where length(COALESCE("Tbl1003"."phonenumber", '')) > 0
    union
    SELECT "Tbl1008"."firstname" "Col1054","Tbl1008"."lastname" "Col1055","Tbl1008"."company" "Col1056","Tbl1008"."pv_an1" "Col1057" FROM "database_single"."public"."phonebook" "Tbl1008" where length(COALESCE("Tbl1008"."pv_an1", '')) > 0
    union
    SELECT "Tbl1013"."firstname" "Col1060","Tbl1013"."lastname" "Col1061","Tbl1013"."company" "Col1062","Tbl1013"."pv_an2" "Col1063" FROM "database_single"."public"."phonebook" "Tbl1013" where length(COALESCE("Tbl1013"."pv_an2", '')) > 0
    union
    SELECT "Tbl1018"."firstname" "Col1066","Tbl1018"."lastname" "Col1067","Tbl1018"."company" "Col1068","Tbl1018"."pv_an3" "Col1069" FROM "database_single"."public"."phonebook" "Tbl1018" where length(COALESCE("Tbl1018"."pv_an3", '')) > 0
    union
    SELECT "Tbl1023"."firstname" "Col1072","Tbl1023"."lastname" "Col1073","Tbl1023"."company" "Col1074","Tbl1023"."pv_an4" "Col1075" FROM "database_single"."public"."phonebook" "Tbl1023" where length(COALESCE("Tbl1023"."pv_an4", '')) > 0
    union
    SELECT "Tbl1028"."firstname" "Col1078","Tbl1028"."lastname" "Col1079","Tbl1028"."company" "Col1080","Tbl1028"."pv_an5" "Col1081" FROM "database_single"."public"."phonebook" "Tbl1028" where length(COALESCE("Tbl1028"."pv_an5", '')) > 0
    union
    SELECT "Tbl1033"."firstname" "Col1084","Tbl1033"."lastname" "Col1085","Tbl1033"."company" "Col1086","Tbl1033"."pv_an6" "Col1087" FROM "database_single"."public"."phonebook" "Tbl1033" where length(COALESCE("Tbl1033"."pv_an6", '')) > 0
     
  5. kwenix

    Joined:
    May 2, 2017
    Messages:
    5
    Likes Received:
    0
    issue was/is solved in client v15.1.0.79 which appeared in the updates today.
    Case closed.
     
Thread Status:
Not open for further replies.