Release Date: 11/29/2023
Version: 7.x & Up
Q - With our custom web application, we attempt to find an existing customer and associated contact when creating
new customers via the Elliott web services. We are finding the customer just fine, but
when I attempt to retrieve the contacts with QueryTurnAround web services, we are getting a timeout error: Pervasive.Data.SqlClient.PsqlException
(0x80004005): Pervasive.Data.SqlClient.Lna.k: [LNA][Pervasive][ODBC Engine
Interface]S1T00Timeout expired.
QUERY
SYCONTCT_ID, SYCONTCT_EMAIL_ADDR, SYCONTCT_NAME, SYCONTCT_LNK_ADR_FLG,
SYCONTCT_ADDR_1, SYCONTCT_ADDR_2, SYCONTCT_CITY, SYCONTCT_STATE, SYCONTCT_ZIP,
SYCONTCT_COUNTRY, SYCONTCT_LNK_TEL_FLG, SYCONTCT_PHONE, SYCONTCT_PHONE_EXT,
SYCONTCT_SRCH_NAME, SYCONTCT_HOME_PHONE, SYCONTCT_MOBIL_PHONE,
SYCONTCT_BIRTH_YEAR, SYCONTCT_BIRTH_MON, SYCONTCT_BIRTH_DAY FROM SYCONTCT INNER
JOIN SYCONREL ON SYCONTCT.SYCONTCT_ID = SYCONREL.SYCONREL_CONTACT_ID WHERE
SYCONREL_DETAIL_FILE='ARCUSFIL' AND SYCONREL_REF_ID='476806'
Since this is QueryTurnAround, you can just add "SELECT" to the above query to test it in the PSQL Control Center. Can you advise how to get around the time out issue?
A - First, I logged in to your server and I saw that your database engine is PSQL 11. Upgrading to the latest PSQL 15 may help.
Second, I tried your query myself and it does take a little bit of time (like 18”), but not to the point of timeout. I think it may take more time if the corresponding SYCONTCT and SYCONREL tables are not cached in the database memory. This could happen if the server was rebooted.
I spent a little bit of time optimizing your query as follows, which you can try:
SYCONTCT_ID, SYCONTCT_EMAIL_ADDR, SYCONTCT_NAME,
SYCONTCT_LNK_ADR_FLG, SYCONTCT_ADDR_1, SYCONTCT_ADDR_2, SYCONTCT_CITY, SYCONTCT_STATE, SYCONTCT_ZIP,
SYCONTCT_COUNTRY, SYCONTCT_LNK_TEL_FLG, SYCONTCT_PHONE, SYCONTCT_PHONE_EXT, SYCONTCT_SRCH_NAME, SYCONTCT_HOME_PHONE,
SYCONTCT_MOBIL_PHONE, SYCONTCT_BIRTH_YEAR, SYCONTCT_BIRTH_MON,
SYCONTCT_BIRTH_DAY FROM SYCONTCT WHERE SYCONTCT_ID = (SELECT SYCONREL_CONTACT_ID FROM SYCONREL WHERE SYCONREL_DETAIL_FILE='ARCUSFIL' AND SYCONREL_REF_ID='476806')
It took about 2-3" to execute. It is faster, but still not as fast as I would like it to be. I like to see the result return in sub second. You could try breaking up the query into two steps by first executing this query:
SYCONREL_CONTACT_ID FROM SYCONREL WHERE SYCONREL_DETAIL_FILE='ARCUSFIL' AND SYCONREL_REF_ID='476806'
Then use the returned Contact ID to construct the next query:
SYCONTCT_ID, SYCONTCT_EMAIL_ADDR, SYCONTCT_NAME,
SYCONTCT_LNK_ADR_FLG, SYCONTCT_ADDR_1, SYCONTCT_ADDR_2, SYCONTCT_CITY, SYCONTCT_STATE, SYCONTCT_ZIP,
SYCONTCT_COUNTRY, SYCONTCT_LNK_TEL_FLG, SYCONTCT_PHONE, SYCONTCT_PHONE_EXT, SYCONTCT_SRCH_NAME, SYCONTCT_HOME_PHONE,
SYCONTCT_MOBIL_PHONE, SYCONTCT_BIRTH_YEAR, SYCONTCT_BIRTH_MON,
SYCONTCT_BIRTH_DAY FROM SYCONTCT WHERE SYCONTCT_ID = %ReturnedContactID%
Both queries were executed instantly and hence they will still be faster when combining them together. I cannot give good explanation on why the query is slower with your INNER JOIN case. It is possible PSQL is having problems optimizing the INNER JOIN in this case. Therefore, the alternative is that we can take this matter into our own hands to help PSQL optimize the performance. Generally speaking, we can assume PSQL will perform well with a query from a single table with the WHERE condition utilizing the index columns.
EMK