Strange issue with query hanging after first run. Narrowed down to ORDER BY?

[former_SQLA_member1694956]

I have the below query which is actually in a stored procedure. This is on SQLAnywhere v17.0.9.4793.

When the server first start the server and run the query with the ORDER by P.last_name, P.first_name the results are instant. Returning the correct number of rows in < 1 second. However, on the subsequent run of the query it hangs. However, if I modify the ORDER BY clause I have found some values which cause the query to consistently return results even after the first time. It's strange some of the combinations which cause it to work (ex: adding DESC to the first_name?) or even ordering by another column on the table which is not indexed (p.address_1).

I have no idea where to go in order to track this down. I've unloaded and reloaded the entire database in case it was some strange error related to the indexes (thinking that rebuilding them might help)

SELECT
"RESP"."RespPartyId",
"P"."last_name" + ', ' + "P"."first_name" + ' ' + "P"."middle_initial" AS "Resp Party Name",
"RESP"."CURRENT",
"RESP"."ThirtyDay" AS "30 DAYS",
"RESP"."SixtyDay" AS "60 DAYS",
"RESP"."NinetyDay" AS "90 DAYS",
"RESP"."CONTRACT",
("RESP"."CURRENT" + "RESP"."ThirtyDay" + "RESP"."SixtyDay" + "RESP"."NinetyDay" + "RESP"."CONTRACT") AS "TOTAL A/R",
"RESP"."EstIns" AS "- EST. INS.",
("RESP"."CURRENT" + "RESP"."ThirtyDay" + "RESP"."SixtyDay" + "RESP"."NinetyDay" + "RESP"."CONTRACT" - "RESP"."EstIns") AS "= DUE NOW"
FROM (SELECT
P1.responsible_party AS "RespPartyId",
SUM(P1.current_bal) + sum(P1.estimated_insurance) AS "CURRENT",
SUM(P1.thirty_day) AS "ThirtyDay",
SUM(P1.sixty_day) AS "SixtyDay",
SUM(P1.ninety_day) AS "NinetyDay",
SUM(P1.contract_balance) AS "CONTRACT",
SUM(P1.estimated_insurance) AS "EstIns"
FROM patient P1
WHERE (P1.current_bal <> 0.00 OR P1.thirty_day <> 0.00 OR P1.sixty_day <> 0.00 OR P1.ninety_day <> 0.00 OR P1.contract_balance <> 0.00 OR         
P1.estimated_insurance <> 0.00)
GROUP BY P1.responsible_party) AS "RESP"
JOIN patient P ON "P"."patient_id" = "RESP"."RespPartyId"
JOIN account A ON P.patient_id = A.patient_id
WHERE (P.practice_id = 1 OR null IS NULL)

Here is a list of the various ORDER BY columns and their results. The original query has:

ORDER BY P.last_name, P.first_name

ORDER BY P.last_name, P.first_name; << does not work
ORDER BY P.last_name; << does not work 
ORDER BY "Resp Party Name"; << works using the computed column from the same fields 
ORDER BY P.last_name, P.first_name desc; << This works (adding DESC to first_name, strange?!?)
ORDER BY P.last_name, P.home_phone << works fine
ORDER BY P.notes << random text column, works fine
ORDER BY P.address_1 << unindexed text column, works fine

[chris_keating]

When you say the query hangs, do you mean that it runs for a long time but server executable is using CPU? is the usage high i.e., a pegged CPU? Or is the executable using very little CPU?

Do you have indexes on last_name and/or first_name? Can you experiment with adding one or both indexes.

It would be ideal to get a graphical plan with statistics for the good (first run) and bad query (subsequent runs). But the slow query needs to complete for that to work. If a graphical plan is not possible, enable remember last statement (-zl or via sa_server_option) and remember last plan (-zp or via sa_server_option) Run the good performing case and collect the results from sa_performance_diagnostics() and then repeat with the slow case. Extract the text plans from each of the collected outputs and see if there is anything interesting in comparing the plans. Perhaps there is now a table scan in the slow plan. There is also other useful diagnostic values in the sa_performance_diagnostics that may provide evidence of an issue.

If you execute the system procedure sa_flush_cache() before running the slow case, does the query still perform poorly on the subsequent runs?

[former_SQLA_member1694956]

There is some CPU consumed, but not 100% consumed. If I stop the query after a few minutes some number of rows is obtained. I do have an index on patient first_name asc, last_name asc

YES! running sa_flush_cache() does cause it to work every time.

I was able to get a plan after running the flush, but the plan hangs just like the query without first running sa_flush_cache.

Comparing the two simple optimizer estimates only I get similar results on both.

[former_SQLA_member1694956]

running the slow query (no flush) and looking at sa_performance_diagnostic() doesn't reveal anything interesting. non of the columns are increasing (like RequTimeBlockIO, etc). The state is just "Executing" and ReqType is Fetch.

[VolkerBarth]

FWIW, you might check whether intra-query parallelism does have an (negative) effect here... by trying to set option max_query_tasks to 1 to disable parallelism... I have stumbled over several queries that should run efficiently but intra-query parallelism made them worse.

[VolkerBarth]

Is there a need to JOIN with table "account" (as no of its fields seem to be used in other parts of the sample query)? And is the predicate "OR null IS NULL" (which makes the whole WHERE clause always TRUE) just an test artifact?

[former_SQLA_member1694956]

The OR null is null portion is part of the stored proc to allow an optional parameter. Ex: where (P.practice_id = @param or @param is null)

The stored procedure isn't mine, it's from a dental practice mgmt software called Eaglesoft. I have no control over the query, so I don't know if account is really needed. I would need to study the data mode.

It's worth noting that the data we are sorting by is already in the select as a calculated column ("Resp Party Name"). Sorting by that works just fine.

I'll try max_query_tasks.

[former_SQLA_member1694956]

I've copied the database to my machine (Mac OSX) using SQL Anywhere Network Server Version 17.0.11.6933 and it runs just fine. This is with the same DB file/encryption key.

[former_SQLA_member1694956]

setting max_query_tasks to 1 does not change the result.