Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sp runs from 7 sec to 10 min (UnitPricingTrans)


Sp runs from 7 sec to 10 min (UnitPricingTrans)

Author
Message
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
I compiled a stored procedure (UnitPricingTrans) 3 days ago.
The first execution took 10 min.
When I ran it second time it took 7 sec.
All other runs on that day took from 7 to 11 seconds.

Today I ran exactly the same procedure.
It returns exactly the same number of rows (75).
But it took 4 min.
SQL Server was last restarted on October 16th.
Today is November 4th.

This is development server. Not too busy.
I wonder why I have such huge inconsistency in terms of execution times.

Thanks,
Robert
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
RVO (11/4/2013)
I compiled a stored procedure (UnitPricingTrans) 3 days ago.
The first execution took 10 min.
When I ran it second time it took 7 sec.
All other runs on that day took from 7 to 11 seconds.

Today I ran exactly the same procedure.
It returns exactly the same number of rows (75).
But it took 4 min.
SQL Server was last restarted on October 16th.
Today is November 4th.

This is development server. Not too busy.
I wonder why I have such huge inconsistency in terms of execution times.

Thanks,
Robert


Not much in the way of details here. Are the stats current? Are you passing parameters to your proc, if so it might be parameter sniffing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
Thank you for a quick response.
This is how I call procedure:

EXEC UnitPricingTrans
@P_PER_START = 201308,
@P_PER_END = 201309,
@P_CO_CODE = '7001',
@P_UNIT_TYPE = 'VEH',
@P_UNIT = 'AUSMV'



And I always run it with the same parameters.
What is parameter sniffing?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
RVO (11/4/2013)
Thank you for a quick response.
This is how I call procedure:

EXEC UnitPricingTrans
@P_PER_START = 201308,
@P_PER_END = 201309,
@P_CO_CODE = '7001',
@P_UNIT_TYPE = 'VEH',
@P_UNIT = 'AUSMV'



And I always run it with the same parameters.
What is parameter sniffing?


Gail has an excellent article about parameter sniffing here. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
This is procedure code.
The biggest table is prj_detail ( 53,000,000 records)

ALTER PROCEDURE UnitPricingTrans
(
@P_PER_START int,
@P_PER_END int,
@P_CO_CODE varchar(1000),
@P_UNIT_TYPE varchar(1000),
@P_UNIT varchar(1000)
)
AS

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#UnitType')) DROP TABLE #UnitType;

DECLARE @XML XML
SET @XML = '<list><i>' + Replace(@P_UNIT_TYPE, ',', '</i><i>') + '</i></list>'
SELECT x.y.value('.', 'varchar(1000)') AS Value INTO #UnitType FROM @XML.nodes('list/i/text()') AS x(y)


--select Value from #UnitType
--return


select
prj_detail.accounting_period,
prj_detail.co_code,
prj_detail.acct_currency_code,
equip_type.equip_type_code + ' - ' + equip_type.equip_type_name as UnitType,
equip_code.equip_code + ' - ' + equip_code.equip_name as Unit,
prj_info.prj_code + ' - ' + prj_info.prj_name as Project,
equip_code.equip_code + ' - ' + equip_code.equip_name as Equipment,
prj_detail.qty_amt as Quantity,
measure_code.measure_name as Measure,
prj_detail.goal_multiplier as Multiplier,
up_sched.up_sched_name as UPSchedule,
prj_detail.cost_cc AS Cost,
prj_detail.eff_cc AS Effort,
prj_detail.prj_code,
per_end_date

from
equip_type
inner join
equip_code
on equip_type.equip_type_code = equip_code.equip_type_code
inner join
prj_detail
on equip_code.equip_code = prj_detail.equip_code
inner join
doc_type
on prj_detail.sys_doc_type_code = doc_type.sys_doc_type_code
inner join
prj_info
on prj_detail.prj_code = prj_info.prj_code
inner join
unit_code
on prj_detail.prj_detail_code = unit_code.unit_code
inner join
measure_code
on unit_code.measure_code = measure_code.measure_code
inner join
up_sched_units
on unit_code.unit_code = up_sched_units.unit_code
inner join
up_sched
on up_sched_units.up_sched_code = up_sched.up_sched_code

where
doc_type.sys_doc_type_code = 'UP'
and
prj_detail.co_code IN (@P_CO_CODE)
and
equip_type.equip_type_code IN (select Value from #UnitType)
and
equip_code.equip_code IN (@P_UNIT)
and
accounting_period between @P_PER_START and @P_PER_END









Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39921
While not huge, 53 million rows isn't exactly a small table. While it certainly could be parameter sniffing, it could also be that the data was driven out of memory by other requests and needed to be reloaded. That's probably why it took 10 minutes to run the first time and just seconds to run the second (for example).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
Guys, I am so thankful for your help.
I think parameter sniffing is definitely might be the root cause.
I am rewriting procedure now to use local variables.
Will let you know on the outcome results soon.
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
RVO (11/4/2013)
Guys, I am so thankful for your help.
I think parameter sniffing is definitely might be the root cause.
I am rewriting procedure now to use local variables.
Will let you know on the outcome results soon.


Please be aware that symptoms might look like parameter sniffing but most likely it isn't. Parameter sniffing would be my last option to look at.

Your sproc takes 7 secs to load for every consecutive run since the first one because most likely data and plan is cached. Have you looked at indices and stats before changing the code?
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
SSChampion,

I re-compiled procedure using local variables.
But... first run with ( @P_UNIT_TYPE = 'VEH' )
took 11 seconds.
Next run ( @P_UNIT_TYPE = 'COM' )
is taking more than 2 min... and still running.

Still parameter sniffing occurring?...
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
SSChampion,
How do I get execution plan in XML?
I would like to see what Gail is talking about in her article about parameter sniffing.
Especially this part -
ColumnReference Column="@StringVar" ParameterCompiledValue="'zzz'"


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search