Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to set index for these select queries Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:46 AM
Points: 7, Visits: 19
In my table (large one) there is mainly three select queries:
1) select * from Product where productid=101
Here productid is primary key so it is clustered index, so no problem here.

2) select * from Product where IsDeleted=0 order by SubmitDate Desc
3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

for 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.

How to set index for 2) and 3)
Post #1408195
Posted Thursday, January 17, 2013 1:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
Index on IsDeleted, SubmitDate DESC

Probably won't be used unless there's a small number of rows that qualify or you can get rid of that SELECT * , retrieve only the columns required and make the index covering.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1408203
Posted Thursday, January 17, 2013 1:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
prakash.kumar3669 (1/17/2013)
In my table (large one) there is mainly three select queries:
1) select * from Product where productid=101
Here productid is primary key so it is clustered index, so no problem here.

2) select * from Product where IsDeleted=0 order by SubmitDate Desc
3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

for 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.

How to set index for 2) and 3)
Do you always require "SELECT *" in your every query ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1408209
Posted Thursday, January 17, 2013 1:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:46 AM
Points: 7, Visits: 19
Do you always require "SELECT *" in your every query ?

the number of columns i need to display is 6-7 so i have put on * for the reference, in other words covering queries in not an option i guess.
Post #1408217
Posted Thursday, January 17, 2013 3:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
prakash.kumar3669 (1/17/2013)
in other words covering queries in not an option i guess.


Why not?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1408272
Posted Thursday, January 17, 2013 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:46 AM
Points: 7, Visits: 19
Why not?

I don't know, i have no experience of implementing indexing till now. What i have read from online articles, its important to index the columns which comes in where condition.
if i have 5 columns to retrieve in my queries then what's the best way of indexing ?
Post #1408322
Posted Thursday, January 17, 2013 4:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
prakash.kumar3669 (1/17/2013)
What i have read from online articles, its important to index the columns which comes in where condition.
Not only Where BUT group by, order by , JOIN's ON clause , Partititon by , and also the SELECT's columns.
See this http://www.sqlservercentral.com/articles/Indexing/68439/

prakash.kumar3669 (1/17/2013)
if i have 5 columns to retrieve in my queries then what's the best way of indexing ?
then those columns should be part of Covering index INCLUDE's part.
For better solution.post that Query


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1408324
Posted Thursday, January 17, 2013 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:46 AM
Points: 7, Visits: 19
For better solution.post that Query

select ProdName,Category,TotalStock,Price from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc
Post #1408351
Posted Thursday, January 17, 2013 6:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

btw, that's a rather odd predicate on the date column...



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1408396
Posted Thursday, January 17, 2013 9:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:46 AM
Points: 7, Visits: 19
GilaMonster (1/17/2013)
Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

What would be the exact sql query to create the index.
btw, that's a rather odd predicate on the date column...

how can you ellaborate ?
Post #1408705
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse