SQL Query Optimisation on a Date Column

Question

I have a table which is never updated and rarely inserted into. I need to optimise this table for rapid querying on a date column and an nvachar(32).

I'm adding Index's to the table, and I'm unsure if it would be best for performance to split the date column up into separate columns e.g. a separate column for year, month and day or just leave the date column as is.

Queries will basically be on the nvarchar column and year column, or may include year, and month, and day.

What I will actually be doing is running a SOUNDEX clause over a surname column and a date column

e.g. something like:

SELECT DISTINCT [Surname]
FROM [dbo].[sometable]
WHERE YEAR(Datecolumn) = 2011 AND SOUNDEX(Surname) = SOUNDEX('smith')

What would be best for performance?

Lamak · Accepted Answer · 2013-03-01 12:45:44Z

2

If that is the way you are gonna do your filters, then an index is useless since you are applying functions to those columns. For the DateColumn you could do

DateColumn >= '20110101' AND DateColumn < 20120101

You can also create a computed column with the year and month if its really necessary and create an index on those columns. For the Surname column I don't think there is much to do, the SOUNDEX search will be slow, and won't use an index the way you are using it. You can still think in creating a persisted computed column with the result of the SOUNDEX function applied to Surname, create an index on that column and do your filter using the computed column. This should speed up your query.

edited Mar 1, 2013 at 12:45

answered Mar 1, 2013 at 12:24

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Sobolewski Over a year ago

I was typing the exact same answer. You could use "between" as I think it reads better. You should check the execution plan to make sure you have the proper index and that it will actually use it.

Leigh Ciechanowski Over a year ago

Thanks for the advice, I'm testing now. Are you sure that applying a function e.g. YEAR(DateColumn) = 2011 means that the index is useless. Is this dependent on database technology as I'm using SQL Server 2008 and in the execution plan it seems to using the index on the date column even though I'm applying Year function to it?

Sergio · Accepted Answer · 2013-03-01 12:25:57Z

0

For datetime column you can use where clause as:

WHERE DateColumn BETWEEN '01/01/2011'  AND '01/01/2012'

But if your're going to do any operation on a column in where clause index is useless. What can i offer is storing precalculated SOUNDEX value of Surname in a separate column

answered Mar 1, 2013 at 12:25

Sergio

6,9485 gold badges33 silver badges55 bronze badges

1 Comment

Dan Bracuk Over a year ago

If the date column has a time component, using between could cause you to miss data.

Collectives™ on Stack Overflow

SQL Query Optimisation on a Date Column

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related