January 19, 2012 at 11:20 pm
I have a table which has a column (ISSUE_DATE) which is a date time Data type.
But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.
So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.
Please need help.
January 20, 2012 at 12:16 am
sudhirnune (1/19/2012)
I have a table which has a column (ISSUE_DATE) which is a date time Data type.But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.
So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.
Please need help.
your second guessing whether the index will help without any proof or testing.
first, you of course need to test it on a development environment prior to deploying it.
Remember a datetime is actually stored behind the scenes as two integers. In my experience, indexes on datetime columns can help queries a LOT.
the index will still allow SQL to do a better job even if the dates are not an exact match (milliseconds)
i'd say test the potential index first, regardless of it's size, by comparing the execution plans before and after you add the index.
think of it this way: if you had an integer column that you KNEW only help numbers in increments of 5, an index help would help any queries that are searching the column regardless of the searched parameter...whether it was @param=42 or not, SQL would know the order of the data and be able to seek for it; if you needed valuesgreater than @param, then a seem would get a lot of help fromt he index too.
Lowell
January 20, 2012 at 1:39 am
sudhirnune (1/19/2012)
I have a table which has a column (ISSUE_DATE) which is a date time Data type.But for improving the Query Performance i need to Index the Table based on the Date Column (ISSUE_DATE).
If i create a Index directly on the Column the index will be huge and it merely waste.
5 extra bytes per row, datetime vs date. Not that huge. Unless you have millions upon millions or rows (or a overly-large clustering key), your nonclustered index shouldn't be that large
So I want to create an Index on the same column but which generates the Index on Date, instead of date time.
Bottleneck is cannot create an extra column on the table.
The only way to create an index on a different datatype version of a column is to create a computed column and index that, then make sure that all your queries use the new column and that all the data types in those queries match the computed column or the index won't be used
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 23, 2012 at 8:47 am
Hi Friend thanks,
But my issue is not with the Size of Index, but with the retrival Time.
Table wil be loaded with around 5,00,00,000 Records and will be Queried by one more job for providing extracts.
Please need help and suggessions.
January 23, 2012 at 8:52 am
So with an index on the datetime column the query is still slow? If so, post the query at the very least.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply