Updating a visual query takes a long time

Toad Data Point
1

Hi. I am on Windows XP and recently upgraded from 2.6 to 2.7. I’ve started to see very very long delays whenever I “visually change” a query. There is NO delay when I change the SQL code on the Query tab. But the delay is there whenever I change the query on the Diagram tab OR whenever I’m on the Query tab and right-click -> “Visualize Query”.
I have a bandwidth monitor, and I see very large amounts of data being downloaded every time I update the query and am wondering what the heck is going on. It didn’t do this prior to the upgrade, and the same thing happens when I use 2.6 OR 2.7 now …

2

NOTE - I have more info. This only happens when I have a query containing a subquery. The slow query I’m dealing with today has an outer join to a subquery. Hope that add’l info helps …

3

I will have to have the Query Builder developer look into this. Give me a couple of days to investigate.

Debbie

4

Thanks. That could be a great help. Actually, can you send me a sample query that you are seeing this behavior on? It is possible that it is more than subquery.

Debbie

5

Here is the query. You’ll see the subquery is joined via left outer join:

SELECT enc.enc_date,
doctor.doctor_name,
enc.visit_type,
enc.enc_start_time_reformat,
enc.enc_end_time_reformat,
enc.patient_id,
patient.patient_name,
Subquery.enc_date,
Subquery.outcome_instr_name,
Subquery.response_count
FROM ((atoregistry.dbo.enc enc
INNER JOIN atoregistry.dbo.patient patient
ON (enc.patient_id = patient.patient_id))
INNER JOIN atoregistry.dbo.doctor doctor
ON (enc.doctor_id = doctor.doctor_id))
LEFT OUTER JOIN (SELECT structured_items_and_answers.patient_id,
structured_items_and_answers.enc_date,
structured_items_and_answers.
outcome_instr_name,
COUNT (
structured_items_and_answers.
outcome_response)
AS response_count
FROM atoregistry.dbo.structured_items_and_answers structured_items_and_answers
GROUP BY structured_items_and_answers.patient_id,
structured_items_and_answers.enc_date,
structured_items_and_answers.
outcome_instr_name) Subquery
ON (enc.patient_id = Subquery.patient_id)
WHERE ( (enc.enc_date = ‘2010-11-02’ AND enc.visit_type IN (‘SU’, ‘HP’))
AND doctor.doctor_name = ‘Doe John’)
ORDER BY enc.enc_date ASC,
doctor.doctor_name ASC,
enc.visit_type ASC,
enc.patient_id ASC,
enc.enc_start_time_reformat ASC,
Subquery.enc_date ASC

6

I had the Query Builder developer take a look at your query. Actualy he built a similiar SQL and can not see any slows or changes in Visualizing the Query.

Let me go over what is going on under the hood. Anytime you request to Visualize the Query or change to the Query tab we re-generate the query. To do this we query the database to get the columns.

The TDA framework is desgined to cache the database data so you should only see a slow the first time those objects are accessed. Can you check your caching options to see if they are turned off? Options | Database | Cache. That would be one explaination for the change in behavior in both products.

Can you also turn on Trace SQL and look in the output window to see what queries are run everytime and perhaps see which are taking the longest. Try clearing the output and then enable and press Visualize so we only get the queries that are taking the long time. Save to file and send back to me.

Debbie

7

Hi - thank you for spending time on this! I have attached the log file.
ConnectionTrace.log (6.32 KB)