Performance Improvement of Stored Procedure

[Vikki McCormick]

I have a stored procedure which is being used on an online form.

In the form the user is able to ask for records based on an Age. So 5 days old, 2 days old, etc. The age is based on the date that the record was entered or received. So we have JobReceiveDate.


The stored procedure was written like this. Where @Ageto and @AgeFrom come from the online form.

JobReceivedDate Between DateAdd(dd, (isnull(@AgeTo,60000)* -1),JobReceivedDate) AND DateAdd(dd, (isnull(@AgeFrom,0)* -1),JobReceivedDate)

In doing this the query can not use the index on set up for JobReceiveDate.

Can anyone think of a better way to write this code? I can't think of how I could totally remove the function so that the index will be utilized.

[Rabbit]

I'm not even sure what that code is supposed to do. As far as I can tell, it will always return every record or no records.

If you're trying to filter by age, you have to take into account the current date, which you never do.

It really needs to be something like this

dateField BETWEEN (GETDATE() - 5) AND GETDATE()

[Vikki McCormick]

Hmmm... I didn't write it, but that code snippet is in the where clause which prohibits the index utilization.

I agree. What does this do?? I was assigned to re-write the stored procedure. It was over 13 pages when I printed it and included 10 seperate union queries. I was able to combine them all into 1 select atatement but it's still pretty complex, and I am trying now to re-write the predicates. It was running for 13 mintues, now it's running for 15 seconds, but I can do better, if I can resolve this last issue.

There were no requirements but the statement seems to be set up to indicating that there are multiple records based on a jobreceived date. And they want to see all records between like 0 and 5 days from jobreceivedate, but I think maybe you are right. Maybe they just want age based on getdate() which would actually make sense.

Okay. Thanks. I am going to have to go back and run that by someone.

I had pneumonia for the past 2 weeks. A co-worker thinks it was this sproc. Hehe. It's a possibility.

That was really a help.

[Rabbit]

As it was, the filter in there didn't do anything but return the wrong results. What was preventing the use of the index was that calculations were being done on the fields. If possible, that should be avoided and all calculations should be moved to the constant side of the equation.

For example

someField + 5 > 10
someField > 5

The first won't use an index but the second will.

If a calculation on a field is required, you can use either an index with the calculation or use a calculated field and check the option to store it.

[Vikki McCormick]

Ah right. Thanks. Got it. That was a big help. Can't wait to implement. Then finally I can get it off my task list. :)

Have a great week.

[Rabbit]

There have been a couple of posts recently about query optimization and that motivated me to write an article. Take a look at it if you need to further optimize your queries.
How To: Optimize SQL Queries