(RVO) script runs 5 sec with hard-coded value - 45 sec using parameter!
-
This script below runs 5 sec with hard-coded value
and 45 sec using parameter!
guys, any ideas why?
Hard-coded version:
select gl_acct_detail.*
from
gl_acct_detail
inner join
gl_acct_code
on gl_acct_detail.acct_code = gl_acct_code.acct_code
and gl_acct_detail.co_code = gl_acct_code.co_code
where
gl_acct_code.co_code = '5450'
Parameter version:
declare@P_CO_CODE varchar(10)
set @P_CO_CODE = '5450'
select gl_acct_detail.*
from
gl_acct_detail
inner join
gl_acct_code
on gl_acct_detail.acct_code = gl_acct_code.acct_code
and gl_acct_detail.co_code = gl_acct_code.co_code
where
gl_acct_code.co_code = @P_CO_CODE
Please see attached Execution plans.
-
The optimizer doesn't know the value of the variable in the second query it is changing the query plan to use a clustered index scan rather than a seek as shown by your plans.
Here is an article with a good explanation and suggestions:
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
-
First, here's the problem (looking at the 45 sec plan):
This looks like parameter sniffing; your cardinality estimates are messed. Your query is scanning an index that has 5M+ rows to only returning 18K+ rows. The 5sec version is splitting the work up evenly between a non-clustered index seek and a non-clustered index scan.
I am going to make some suggestions but hope someone else chimes in too; I am no query plan guru and hope to learn something from this thread too. That said, here's some things I would do to tackle this:
The quick and dirty fix might be to create the missing non-clustered index that your 45 second plan is complaining about:
USE [proddb6]GO
CREATE NONCLUSTERED INDEX lets_let_you_name_this_index
ON [dbo].[gl_acct_detail] ([co_code])
INCLUDE ([src_co_code],[accounting_period],[orig_acct_period],[fiscal_year],[fiscal_per_nbr],[batch_nbr],[seq_nbr],[line_nbr],[org_code],[acct_code],[currency_code],[sys_doc_type_code],[usr_doc_type_suf],[doc_nbr],[doc_date],[usr_batch_id],[input_date],[per_end_date],[update_time],[evc_type_code],[evc_code],[fcs_desc_skey],[ref_nbr],[ref_date],[db_amt_ac],[db_amt_cc],[cr_amt_ac],[cr_amt_cc],[prj_code],[phase_code],[task_code],[inv_grouping_code],[intern_ref_nbr],[mod_date])
GO
A new plan should be complied when you run that query again and It should use the new non-clustered index with a more accurate cardinality estimate and much faster results. I say "quick and dirty" because, in this case you are basically re-creating the clustered index and not solving the underlying cause of the parameter sniffing.
Other options (and I can't say which is best for your situation) could include:
1) You could optimize the slow query on every execution by running the using OPTION (RECOMPLIE)
2) You could optimize for a typical parameter; e.g. you could include OPTION (OPTIMIZE FOR (@P_CO_CODE=5450))
3) You could optimize for UNKNOWN by including (OPTIMIZE FOR UNKNOWN)
Query hints should be a last resort and have their risks but, for this situation, they may be something to explore. Again, I hope someone else chimes in. Let me know if this helps.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
ahperez (11/14/2013)
The optimizer doesn't know the value of the variable in the second query it is changing the query plan to use a clustered index scan rather than a seek as shown by your plans.Here is an article with a good explanation and suggestions:
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx%5B/quote%5D
+1! When I posted my comment I did not see any other comments. Erland Sommarskog has a great article which covers this topic in detail:
Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Thanks guys for your response !
ahperez is referring to a good article about parameter sniffing
but it mostly makes recommendations for the case of stored procedure.
In my case it is a simple script with a parameter.
I am willing to try number # 3 from this article:
1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:create procedure dbo.SearchProducts
@Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy
2. To prevent this and other similar situations, you can use the following query option:
OPTIMIZE FOR
RECOMPILE
3. Disable auto-update statistics during the batch
But not sure how to do it.
Anybody knows?
-
Alan B.
Unfortunately it is Third Party Vendor Application database.
We are not allowed to change schema.
We store all our code in external database where we pull data from views that
are just a mirror of tables in proddb6.
( create view gl_acct_detail as select * from proddb6.dbo.gl_acct_detail ) -
Well, since this is a third-party app this might not be a practical option. Auto update stats is a database level command, impacting the whole db. Below is the command to disable auto update and you will also want to add the command to re-enable them when the query finishes.
ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS OFF
Please test your results and let me know if this speeds things up without impacting other operations.
-
ahperez (11/18/2013)
Well, since this is a third-party app this might not be a practical option. Auto update stats is a database level command, impacting the whole db. Below is the command to disable auto update and you will also want to add the command to re-enable them when the query finishes.ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS OFF
Please test your results and let me know if this speeds things up without impacting other operations.
I would NEVER, EVER recommend turning off auto update stats. that is something you might do only in a high transaction environment where you have put something in place to explicitly handle statistics being updated.
much better to leave that on, and add a job that updates statistics on onyl specific tables that are heavily inserted/updated/deleted.
Auto update stats maybe be a database level command, but it actually occurs on an individual table basis, when the # of rows changed in a table reaches 20% + 500 rows.
so a table with a billion rows needs to see a HUGE number of rows changed before auto updates executes for that table, and it takes a tiny fraction of that number of rows for out of date statistics to change to severely impact performance.
so if you are seeing a performance hit on table gl_acct_detail due to statistics, for example run this command:
UPDATE STATISTICS dbo.gl_acct_detail WITH FULLSCAN ;
schedule that to run more often, say once or twice a day, depending on the frequency of the tables that get changed.
I've heard of some environments updating stats on specific tables once an hour on hight transaction tables, for example.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell, yours is a better answer for most environments. Mine is fairly unique with very large weekly/monthly loads. To speed the load we update stats later in a stored proc.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply