Index problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Index problem

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-05-12 : 11:36:28

In table PolicyRetrievals:
Select policytype, count (policytype) Total from PolicyRetrievals group by policytype
/*
policytype Total
----------- -----------
TT 25427
AA 7608525
AR 8
CA 349205
SL 406371
SS 34274
*/
When I ran this:

Select top 50 * from PolicyRetrievals
where policytype = 'AR'
order by operreckey desc
There is non clustered index on operreckey and when I put policytype
other than AR I got result immediately and it uses the non clustered index on operreckey but when I put AR it will take long time and it doesn't use that index. Even I try to put index on policytype it doesn’t help. There are 83,00,000 rows on the column. Help me to find solution:

Thanks
Sanjeev OH

Sanjeev Shrestha
12/17/1963

Kristen
Test

22859 Posts

Posted - 2006-05-12 : 12:15:22

Presumably there is a statistics that says that AR is a very rare value for policytype, therefore using the operreckey index is going to mean a VERY large number of index reads, followed by data reads, whereas just reading the Data directly will be faster (in terms of finding the 8 records) and then sorting them will be trivial.

For other records with lots of hits, and given that you have specified only the TOP 50, it makes more sense for SQL to access via the operreckey index - otherwise ALL the matching records must be found, and THEN sorted, and THEN just the TOP 50 selected.

An index on policytype would help for this query, but would be useless for all the other values (given that it would not be selective for them - UNLESS you could cover the query from that index - but if you genuinely need to do SELECT * that won't be the case)

So ... if its a regular requirement you'll have to find another route - such as keeping a lookup-list of the AR records in another table.

Worth making sure that the statistics are up to date (or set to update automatically)

Kristen

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-05-12 : 12:54:21

Hi Kristen,
Thanks for reply.
Actually I need:

SELECT TOP 50 Insdstcd,PaymentMessageKey,policytype,operreckey FROM PolicyRetrievals
ORDER BY operreckey DESC

I need to do various combination of where clause like:
where PaymentMessageKey = 'CancelPaymentInformation' AND Insdstcd = 'CA' AND policytype = 'AA'
or PaymentMessageKey = 'CancelPaymentInformation' AND policytype = 'AA'
etc.

statistics for PaymentMessageKey:
PaymentMessageKey TotalCount
CC 7852537
CancelPaymentInformation 98914
NCRenewalText 5775
QuoteLapsePaymentInformation 127153
QuotePaymentInformation 285568
UpfrontCancelPaymentInformation 1628

statistics for Insdstcd:
Insdstcd TotalCount
NA 25959
AA 66
AE 217
AK 20008
AL 125444
AP 233
AR 44397
AZ 181746
BC 1
CA 537265
CO 120169
CT 138135
DC 11269
DE 36266
FL 905815
GA 541119
HI 36567
IA 84044
ID 30906
IL 120803
IN 188493
KS 57748
KY 114654
LA 166482
MA 1869
MD 157143
ME 50964
MI 245138
MN 160728
MO 123835
MS 93481
MT 15409
NC 173274
ND 14530
NE 39870
NH 35273
NJ 15461
NM 53529
NV 87047
NY 544406
OH 1122274
OK 84289
ON 8
OR 121252
PA 420482
PQ 17
PR 145
RI 62212
SC 96001
SD 24227
TN 87127
TX 570811
UT 43370
VA 255466
VI 73
VT 26644
WA 133207
WI 89342
WV 14199
WY 18458

-------
How to create proper index? I tried various combination but couldn't success:
Thanks

Sanjeev Shrestha
12/17/1963

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources