Can I move sorting and avoid Order By with Clustered Index on an empty table?

Question

Post reply

Can I move sorting and avoid Order By with Clustered Index on an empty table?

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 1

thank you for the additional reading, i will do it over the weekend.

drew

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 2

Holy Good Night...that sure is a lot more readable!

I am not sure if the distinct is necessary or not.

Thank you for your work

drew

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

drew.georgopulos (8/12/2011)
Holy Good Night...that sure is a lot more readable!
I am not sure if the distinct is necessary or not.
Thank you for your work
drew

Should be quite a bit faster too, because there are fewer reads of table qualindicator. Check if the distict is required because it has a cost. There are several areas where the same technique can be applied.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 4

GilaMonster (8/12/2011)
You can't include columns in a clustered index. The cluster by definition includes all columns in a table.
Some more reading for you, if you want:
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

not only were these absolutely the most superior treatment of the topic i have ever read, they were exactly what i needed to know. thank you for taking the time to point me to them as well as narrow or wide.

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 5

ChrisM@Work (8/15/2011)
drew.georgopulos (8/12/2011)
Holy Good Night...that sure is a lot more readable!
I am not sure if the distinct is necessary or not.
Thank you for your work
drew
Should be quite a bit faster too, because there are fewer reads of table qualindicator. Check if the distict is required because it has a cost. There are several areas where the same technique can be applied.

i think i still have a couple of old xml plans to doc the performance of the reporting query.

i will post either the changes or before and after comps tomorrow.

thank you for helping me move the rock

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 6

i think i found the elephant in the livingroom...

i am embarrassed to say that in looking at the following i feel like a dunce...there's no excuse for these except that things are they way they are because thats the way things are;

following on Gail's suggestion, i have found treasures like these

Scalar Operator(CONVERT_IMPLICIT(varchar(14),CONVERT(varchar(20),[ApproachDB].[dbo].[JINSTHM0_DAT].[CASUBNO] as [ih].[CASUBNO]

+[ApproachDB].[dbo].[JINSTHM0_DAT].[CAPERSNO] as [ih].[CAPERSNO],0),0)),

and

cast(replace(ph.CADX1,'.','') as varchar(5)) as dx1

what is so dizzyingly nauseating is that the firlst expression is creating a single column id out of a business key when an integer surrogate key is available for that one right on the row, so i can save myself some freight there.

the second one is an instance of formatting in the database...it is a diagnosis code that has a period stuck in it, and now, the author wants to remove it.

what is crazier yet is that the second one is just a fragment of this;

[Expr1033] = Scalar Operator(CONVERT_IMPLICIT(varchar(7),CONVERT(varchar(5),replace([ApproachDB].[dbo].[JINSTHM0_DAT].[CADX1] as [ih].[CADX1],'.',''),0),0)),

but the DOUBLE CONVERT for crying out loud is necessitated because the create table made the field varchar(7), and nothing is that big, so everything gets downshifted twice.

still in the deep weeds, but what i want to do is fix the freakin ETL so that this buffoonery gets nipped in the bud instead of on every execution. so, im going to replace the made up id with the integer surrogate key, get money fields displayed like money insetead of sum(cast(cbnet/100 as money)) as paid_amt and store diagnoses without the periods.

now, im going to try to do what i started to do <g>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

drew.georgopulos (8/15/2011)
not only were these absolutely the most superior treatment of the topic i have ever read, they were exactly what i needed to know. thank you for taking the time to point me to them as well as narrow or wide.

High praise. Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

drew.georgopulos SSCarpal Tunnel Points: 4931 More actions · Answer 8

just because i said i would, here are the stats for the alternative formulations of the query you wrote for me. Option 3 wins (also had the unintended benefit of rooting out several duplicated codes in the lookup table...i think their presence was causing an overcount, so it was a happy day.

thanks again

drew

--As Written

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 390 ms, elapsed time = 424 ms.

Table 'member_aggregate'. Scan count 1, logical reads 6166 phys0

Table 'Worktable'. Scan count 0, logical reads 0phys0

Table 'qualindicator'. Scan count 29626, logical reads 59597phys0

Table 'newborn1'. Scan count 1, logical reads 140phys0

(113 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 157 ms, elapsed time = 817 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--Option 1

Table 'qualindicator'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(54 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 129 ms.

Table 'member_aggregate'. Scan count 1, logical reads 6166 phys 0

Table 'Worktable'. Scan count 0,logical reads 0 phys 0

Table '#PQI09N'Scan count 26249,logical reads 59252 phys 0

Table 'newborn1'. Scan count 1, logical reads 140phys 0

(113 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 359 ms, elapsed time = 2428 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--Option 2

Table 'qualindicator'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(54 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 8 ms.

Table 'member_aggregate'. Scan count 118, logical reads 365

Table 'Worktable'. Scan count 1, logical reads 9298 phys 0

Table 'newborn1'. Scan count 1, logical reads 140 phys 0

Table '#PQI09N Scan count 1, logical reads 2 phys 0

(113 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 219 ms, elapsed time = 215 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

--Option 3

Table 'qualindicator'. Scan count 1, logical reads 23, physical reads 0

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table 'member_aggregate'. Scan count 54, logical reads 203, physical reads 0

Table 'newborn1'. Scan count 1, logical reads 140, physical reads 0

(50 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 30 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.