Weird-"almost" same query, quite different perf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weird-"almost" same query, quite different perf

Author

Topic

LogickleDoug
Starting Member

1 Post

Posted - 2008-11-26 : 15:47:41

Hi, all. I wrote a query against a single table to group records by date and to quintile them, per date, by two different columns, say F69 and F80. So for each date, each record would end up in some F69-quintile, F80-quintile "cell", say (1,5).

Even after adding indices for all columns sorted and joined on, I was dismayed at the performance of the query:

SELECT TA.Date, TA.F1, TA.F2, NtileA, NtileB
INTO TMP_F69_F80
FROM
(select Date, DID, F1, F2, NTILE(5) OVER (PARTITION BY Date ORDER BY F69 ASC) AS [NtileA]
FROM MyTable WHERE F69 IS NOT NULL AND F80 IS NOT NULL) TA
INNER JOIN
(select Date, DID, F1, F2, NTILE(5) OVER (PARTITION BY Date ORDER BY F80 ASC) AS [NtileB]
FROM MyTable WHERE F69 IS NOT NULL AND F80 IS NOT NULL) TB
on TA.DID = TB.DID AND TA.Date = TB.Date

It takes about 35 seconds to process a table of ~240K records, much longer than I expected.

Out of desperation I broke the query into three parts and ran them together:

select Date, DID, F1, F2, NTILE(5) OVER (PARTITION BY Date ORDER BY F69 ASC) AS [NtileA]
INTO TMP_F69 FROM MyTable WHERE F69 IS NOT NULL AND F80 IS NOT NULL

select Date, DID, F1, F2, NTILE(5) OVER (PARTITION BY Date ORDER BY F80 ASC) AS [NtileB]
INTO TMP_F80 FROM MyTable WHERE F69 IS NOT NULL AND F80 IS NOT NULL

SELECT TA.Date, TA.F1, TA.F2, NtileA, NtileB
INTO TMP_F69_F80
FROM
TMP_F69 TA
INNER JOIN
TMP_F80 TB
on TA.DID= TB.DID AND TA.Date = TB.Date

This takes only 6 seconds!

So I have a workaround, I can just put the three queries into a stored procedure.

But why would the performance differ so dramatically (CPU and memory utilization are comparable either way)? Since the subqueries obviously aren't correlated, I'm just doing with the temp tables what the DB must be doing itself anyway.

Any ideas?

Doug

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 17:42:35

Windowed functions need to dataset sorted. Check the execution plan.
If the partition and order by follows existing indexes, there are no much performance decrease.
But... When you partition and order by in several different ways in same query, SQL Server has to store intermediate data in worktables in disk.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources