January 17, 2013 at 1:06 am
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)
January 17, 2013 at 1:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2013 at 1:26 am
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;-)
January 17, 2013 at 1:55 am
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.
January 17, 2013 at 3:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2013 at 4:23 am
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 ?
January 17, 2013 at 4:30 am
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;-)
January 17, 2013 at 5:12 am
For better solution.post that Query
select ProdName,Category,TotalStock,Price from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc
January 17, 2013 at 6:39 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2013 at 9:28 pm
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 ?
January 18, 2013 at 3:00 am
prakash.kumar3669 (1/17/2013)
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.
Why don't you open up books online and read over Create Index.
btw, that's a rather odd predicate on the date column...
how can you ellaborate ?
SubmitDate>=' ' and SubmitDate<' '
There is no possible way that any date can be both greater than or equal to '' and less than ''. Let me rephrase that into numbers...
WHERE SomeNumber >=0 AND SomeNumber < 0
That's completely, logically impossible as it is only true for a number that's both positive and negative at the same time, which no number is.
Either you mis-typed when you copied the query over, or there's some serious flawed logic at work.
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
January 18, 2013 at 4:06 am
WHERE SomeNumber >=0 AND SomeNumber < 0
this is the actual condition: SubmitDate>=DATEADD(day,DATEDIFF(day,0,GETDATE()),@duration) AND SubmitDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
January 18, 2013 at 4:08 am
@duration is negative number....like -1,-2,-3
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply