Parameter slow sp
-
I was writing/testing a sproc today. My first SQL statements hard coded a date just for testing purposes. Once everything was working fine I then added a parameter so that the date can be passed in by a user.
The results were interesting. The sproc with the hard coded date took 2 seconds to execute. The sproc with the parameter took 11 seconds. Each method passed in the same date. Table has about 75,000 rows, field being searched is not indexed. The only difference is the use of a parameter versus a hardcoded date.
Interested in your comments. I was not aware of the overhead in execution time that parameters add.
Bill
Chattanooga, TN
-
As you have discovered, using elapsed time as a comparison criteria is un-relialable. This can be due to network workload, other SQL running on the server, virus scanning, someone logging on the the server, data being flushed from cache and many other factors.
I have found that the number of logical reads is the most reliable comparison factor in most cases.
Try re-running your comparison with "set statistics io" on.
SQL = Scarcely Qualifies as a Language
-
There must be something wrong with datatype conversions.
Hardcoded date is harcoded as varchar.
Supplied parameter must be datetime.
What is the datatype of column you scan for matching?
_____________
Code for TallyGenerator -
I realize that realtime comparisions can vary but these were consistent times in each of about 5 compares.
Sergiv, you are correct about the two datatypes. The column is datetime which makes less sense given the faster of the two was the varchar hardcoded date. I'll have to get back to the office to try this again but I think I'll try passing the parameter as varchar instead of datetime.
I can live with 11 seconds as this runs a report that is produced only once per day. I just thought it was curious.
Bill
-
If you're passing the parameter as varchar just convert it to datetime in SP:
where DateCol = convert(datetime, @Param, @FormatParameter)
_____________
Code for TallyGenerator -
What I'm saying is that searching the datetime column with a hardcoded varchar value is faster than searching with a parameter being passed in as datetime. It doesn't make much sense but the results are faster. I am going to try passing it as varchar just to see if that speeds up the query while using a parameter rather than a hardcoded value. That will possibly give a clue as to whether the speed loss is due to pasing in a parameter vs. hardcoded values.
Bill
-
Can you pass the part of WHERE clause where you use this parameter?
There is a feeling I know whats a problem.
_____________
Code for TallyGenerator -
Okay. I'll have to do that once I get to the office tomorrow morning.
-
Look up 'parameter sniffing'.
Create a local variable and set this to the value of the parameter. Then use the local variable in your queries rather than the parameter directly. That way, the query engine is able to build its execution plan in advance.
-
We have a very similar situation and I am very interested in your outcome. We have a table with numerous indexes, one of them on datatime column. If I do a simple select with a hard-coded date the execution plan picks up the correct index and the query runs in a few milli-seconds. If I declare a variable then set that variable to the same date and execute the query with the variable in the where clause a completely different execution plan is created and the query takes 10+ seconds to run. If i hint the correct index in the query with the variable it runs in milli-seconds (I can reproduce all the above in Query Analyser).
I know you can say the solution is to hint the correct index, and in this specific case that would solve the issue. But I need to understand exactly why the correct index is not being picked up because I believe that a similar think could be happening (but to a much lesser extent) in many other cases in our production system. From my understanding of parameter sniffing the optimiser would have determined that in this example the value of the variable (the same as the hard-coded date) and therefore parameter sniffing should have resulted in the same query plan as the query with the hard-coded date.
-
Would you post the query, please
-
And "CREATE TABLE ..." would be useful.
_____________
Code for TallyGenerator -
Sorry folks. I prepared a response earlier today with several examples and somehow it didn't post. Probably because I worked on it off and on over several hours (between tasks at work).
Lost one of our IT guys yesterday and I'm trying to find a replacement. Perhaps I'll post as soon as time permits.
I did find that it isn't parameters causing the problem but passing a date in a parameter, even when the parameter is in datetime and the column searched is datetime or one is varchar and the other datetime - doesn't matter - very slow. Using DateAdd(d, -1, getDate()), even when passing in a parameter for the deviation (such as DateAdd(d, @Deviation, getDate()) executes in 2 seconds. Pass in a date as a parameter (varchar or datetime) and it slows way down to 11 to 16 seconds.
Bill
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply