Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sp runs from 7 sec to 10 min (UnitPricingTrans) Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 7:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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
Post #1511115
Posted Monday, November 4, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 13,447, Visits: 12,309
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)
Post #1511116
Posted Monday, November 4, 2013 8:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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?
Post #1511121
Posted Monday, November 4, 2013 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 13,447, Visits: 12,309
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)
Post #1511122
Posted Monday, November 4, 2013 8:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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








Post #1511123
Posted Monday, November 4, 2013 8:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511130
Posted Monday, November 4, 2013 8:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.
Post #1511146
Posted Monday, November 4, 2013 9:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960
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?
Post #1511154
Posted Monday, November 4, 2013 9:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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?...
Post #1511156
Posted Monday, November 4, 2013 9:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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'"

Post #1511158
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse