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 15 posts - 16 through 30 (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

Thanks very much for your help and advice, i appreciate it.

drew

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

Thanks very much...

i knew the original post was gnarly but thought i could get away with it, but it was too ugly.

My first boss, Lior Hod, also refused to read any code until it was presented to him type set.

that was right after night flight in Brief, but before SQL Formatter <g>.

thanks very much for your insight and guidance.

drew

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

If you need help there I can refformat that whole proc in 10 seconds for you... just let me know.

Do you still need help with this problem (tuning wise)?

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

Hello Gail;

@start and @enddates are collected by ccr0_BuildApproach and passed to each called sproc

exec sp_ccr2_encounter_table_ageAndsex @start_dt = @start_dt_master, @end_dt = @end_dt_master, @anchor_dt = @anchor_dt_master

then they are used in the called sproc to limit results in the where clause

wherecast(left(ph.CAPRIMDATE,6) as int) between @start_dt and @end_dt

For this where clause, or for the purpose of limiting rows returned, would it be a better choice to use dates as datetimes instead of the convention 200906 and either

1) convert them to first day of the month, or

2) use the first day of the month from a date table?

Would that let me lose the cast and use an index instead of a table scan?

thanks very much

drew

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

drew.georgopulos (8/11/2011)
Hello Gail;
@start and @enddates are collected by ccr0_BuildApproach and passed to each called sproc
exec sp_ccr2_encounter_table_ageAndsex @start_dt = @start_dt_master, @end_dt = @end_dt_master, @anchor_dt = @anchor_dt_master
then they are used in the called sproc to limit results in the where clause
wherecast(left(ph.CAPRIMDATE,6) as int) between @start_dt and @end_dt

Yeah, that much I got...

Are the columns datetime or something else?

Are the parameters datetime or something else?

What are those start and end dates? Date with no time? Date with some random time?

Are the columns dates with no time? Dates with time associated?

(sorry, I don't have time to read through 1.6MB of script to see if the answers are in there)

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 6

<<If you need help there I can refformat that whole proc in 10 seconds for you... just let me know.

Do you still need help with this problem (tuning wise)? >>

I have gotten so much juice from this forum that it made me dizzy!

I want to apply *what i think i learned* and see how i do on my own...if it aint woikin' ill be back.

My short list of todos is;

Change the parameters from ints to datetimes so i dont have to cast

Lose the cast in the where clause

Create a clustered index including effective date, termination date (the parameters) & dx1 through dx8

someone remarked that there was no CI in the plan, and that's true, but i was sufferning under the misapprehension that if there were discrete indexes against separate columns that the optimizer would be able to use them in combination, and i thought that would be enough, but it is not, so from what i know about the reports, effective date, term date and diagnoses are the groups by which the conformed table is queried, so that's how im going to build the ci.

So thank you very much everybody for your time and help.

I really appreciate it and am eager to try out these ideas.

best

drew

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

<<Yeah, that much I got...

Are the columns datetime or something else?

Are the parameters datetime or something else?

What are those start and end dates? Date with no time? Date with some random time?

Are the columns dates with no time? Dates with time associated?

(sorry, I don't have time to read through 1.6MB of script to see if the answers are in there) >>

The columns are datetime

The parameters are int

The start and end dates are for the dates of service that a member incurred a claim

The start and end dates have no time associated with them and the intent was to group the data by month

There is no need for an apology, indeed, i am gratified you took the time to respond.

thanks again

drew

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

drew.georgopulos (8/11/2011)
Create a clustered index including effective date, termination date (the parameters) & dx1 through dx8

That sounds like a rather poor choice for a clustered index. Clustered indexes should be narrow

but i was sufferning under the misapprehension that if there were discrete indexes against separate columns that the optimizer would be able to use them in combination, and i thought that would be enough,

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

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

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

drew.georgopulos (8/11/2011)
The start and end dates have no time associated with them and the intent was to group the data by month

So what would example values of those be?

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 10

I will read the cite.

My speculation was founded (again on the misapprehension?) that including the date and diagnoses ranges would make the index the most selective it could be.

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

The example values would be 200906 201006 (year to year June 2009 through June 2010)

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

Right, so if you change those to datetimes ('2009-06-01 00:00:00' and '2010-06-01 00:00:00') then your where clause becomes as simple as

WHERE <column name> >= @Start_dt AND <column name> < @End_dt AND <any other conditions>

It's not a between, because between is inclusive on both bounds, meaning you'd also catch rows where the column = '2010-06-01 00:00:00', but not ones where the column = '2010-06-01 00:01:00'

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 13

Great article, thanks for it.

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

i will create the ci with effective date, term date and include dx1.

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

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/

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

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

A couple of suggestions.

/* PQI 09 Measure */

-- second query of two in this section.

-- Existing code

update member_aggregate set PQI09N = discharges

from member_aggregate i

inner join (

select member_id, count(distinct fdos) as discharges

from newborn1 where (

dx1 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx2 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx3 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx4 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx5 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx6 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx7 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx8 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

or dx9 in (select code from qualindicator where measure = 'PQI #09' and NumeratorDenominatorStatification = 'N' and IncludeOrExclude = 'I' and CodeType = 'ICD9CmDiagCode')

)

group by member_id ) c on i.member_id = c.member_id

where PQI09D = 1

-- suggestion 1: IF subselect may return more than one row

SELECT code

INTO #PQI09N

from qualindicator

where measure = 'PQI #09'

and NumeratorDenominatorStatification = 'N'

and IncludeOrExclude = 'I'

and CodeType = 'ICD9CmDiagCode'

UPDATE member_aggregate SET PQI09N = discharges

FROM member_aggregate i

INNER JOIN (

SELECT member_id, count(distinct fdos) AS discharges -- is the distinct necessary?

FROM newborn1

WHERE dx1 in (SELECT code FROM #PQI09N)

or dx2 in (SELECT code FROM #PQI09N)

or dx3 in (SELECT code FROM #PQI09N)

or dx4 in (SELECT code FROM #PQI09N)

or dx5 in (SELECT code FROM #PQI09N)

or dx6 in (SELECT code FROM #PQI09N)

or dx7 in (SELECT code FROM #PQI09N)

or dx8 in (SELECT code FROM #PQI09N)

or dx9 in (SELECT code FROM #PQI09N)

GROUP BY member_id

) c ON i.member_id = c.member_id

WHERE PQI09D = 1

-- suggestion 2: IF subselect may return more than one row

SELECT code

INTO #PQI09N

from qualindicator

where measure = 'PQI #09'

and NumeratorDenominatorStatification = 'N'

and IncludeOrExclude = 'I'

and CodeType = 'ICD9CmDiagCode'

UPDATE member_aggregate SET PQI09N = discharges

FROM member_aggregate i

INNER JOIN (

SELECT n.member_id, count(distinct n.fdos) AS discharges -- is the distinct necessary?

FROM newborn1 n

INNER JOIN #PQI09N q ON q.code IN (dx1, dx2, dx3, dx4, dx5, dx6, dx7, dx8, dx9)

GROUP BY n.member_id

) c ON i.member_id = c.member_id

WHERE PQI09D = 1

-- suggestion 3: IF subselect ALWAYS returns one value (row)

DECLARE @PQI09N [Datatype]

SELECT @PQI09N = code

FROM qualindicator

WHERE measure = 'PQI #09'

and NumeratorDenominatorStatification = 'N'

and IncludeOrExclude = 'I'

and CodeType = 'ICD9CmDiagCode'

UPDATE member_aggregate SET PQI09N = discharges

FROM member_aggregate i

INNER JOIN (

SELECT member_id, count(distinct fdos) AS discharges -- is the distinct necessary?

FROM newborn1

WHERE @PQI09N IN (dx1, dx2, dx3, dx4, dx5, dx6, dx7, dx8, dx9)

GROUP BY member_id

) c ON i.member_id = c.member_id

WHERE PQI09D = 1

^{“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