Issue with Index?

Question

Issue with Index?

myself

Grasshopper

Points: 10
More actions
July 22, 2004 at 12:39 am

#152236

Hi there,
I need some advice on an issue which I am faced with right now.
We have a huge table which stores the gate entry and exit times of employees and it has two indexes one on the time field and the other on the emp id field. There are daily dumps from the central server to another server which happens on an hourly basis. Now the issue is that during this load, we have a delete step which should delete all the records that are older than 14 months, using the below procedure.
declare @dtDelete datetime
select @dtDelete = dateadd(mm, -14, getdate())
DELETE FROM GateTimes Where Time < @dtDelete
This seems to take a lifetime to complete and generally ends in a timeout exceeded error. But when I'm using a literal in place of the variable @dtDelete like say "where Time < 22 May 2003" it works fine and the records get deleted in no time. I feel that the index on the time field is not used in the first instance. Could someone justify and pour some thoughts on this issue.
Cheers!
Ash

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

I you use a literal, SQL Server can use the index statistics to work out reasonably accurately how many rows will be affected by the delete clause. If this is under a certain percentage of the rows, then it will use the index, otherwise it will choose a table scan.

When you use a parameter, SQL Server can't use the statistics in the same way, and has to make more of a guess on the percentage, depending on the operator (e.g. if you use =, it will estimate a much smaller percentage than if you use > or &lt. I've seen the actual percentages in a document somewhere, but can't remember where it was!!!

Look at the estimated query plan in Query Analyzer, and you will probably find that it has chosen a table scan instead of using the index.

This may be one of those instances where using in index hint to force use ofthe index on date will bring big rewards.

myself Grasshopper Points: 10 More actions · Answer 2

Thank you Ian.

Further, now since this is supposed to go into a stored procedure, I will be forced to send in another parameter from the front end which will have the @dDelete value as a 14 months prior date. This again is a variable which will be used in the delete statement. Will this have any adverse effect on the performance in terms of speed or should I opt for an execution of a pre-formed SQL Statement like "Delete ... where Time < '22 May 2003'" from the front end itself instead of calling this SP?

Kindly advise.

Cheers! - Ash

kokyan SSCommitted Points: 1694 More actions · Answer 3

myself,

If you really can't avoid in using variable to query, i suggest to use sp_executesql instead of direct query. Ian is correct. When you are using variable in the select statement (including update and delete), the indexes maybe not in used by the SQL Server because of the dynamic rebuilt of the query (cannot use the execution plan efficiently).

If you use sp_executesql, SQL Server will have the higher percentage to use the indexes to delete. For example,

DECLARE @sql AS NVARCHAR(250)

DECLARE @PARAMETER AS NVARCHAR(100)

DECLARE @DATE AS VARCHAR(8)

SET @DATE = CONVERT(NVARCHAR(8), DATEADD(DD, -14, @INPUTDATE), 112)

SET @sql = N'DELETE FROM TBL1 WHERE DATE < @PARAMETER'

EXEC sp_executesql @sql, '@PARAMETER NVARCHAR(100)', @DATE

Another method is use table hints to force the using of index. Careful when using this method because SQL Server will use this indexes even it will endangered the server resources or the performance is bad.

Regards,
kokyan