SQL Clone
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62793 Visits: 17959
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 Modens 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62793 Visits: 17959
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 Modens 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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 Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215722 Visits: 41980
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RVO
RVO
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7434 Visits: 3696
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1488 Visits: 521
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