Version 11: SQL window, using column totals severely slows down query speed

[Claus Pedersen]

Using column totals in SQL window severely slows down performance.

I have a table with a couple of thousand records. I apply column totals to a number column (average) and a varchar2 column (count).

This can result in a query that fetches approx. 13 rows a second (the speed goes down to fetching approx. 3-4 rows a second when the last rows are fetched) compared to 400 rows a second when column totals are off.

Sometimes the Break button is never enabled during the query, so the only way to kill the query is to kill PLD using the task manager.

 

[Marco Kalter]

We'll check it out.

 

[James B]

WOW!!! We've been banging our heads against this for weeks - checking our indexing, pulling in the DBAs - because the queries we were using suddenly tanked for no explainable reason. I would never have suspected PL/SQL Developer would be behind it, but that coincided with our upgrade. Wow.

I'm not sure I understand _how_ PL/SQL Developer could be the cause... can someone explain?

 

[James B]

I guess not...

 

[IvanZ]

I guess PLD recalculates totals for every row fetch.
This is common row by row (slow by slow) approach...