July 27, 2009 at 7:08 am
I have a stored proc that if I run it for the first time in the Query Analyser or from the app.; it takes about 30 sec, but after that all runs takes less than 6 sec?! Is there a way to troubleshoot the cause of the delay in the first run? or to oevrcome this delay?
July 27, 2009 at 7:16 am
that is normal.
the first time a procedure gets run, an execution plan is created and saved. that takes longer.
after that plan is created, subsequent calls are much faster.
if the procedure gets recompiled, you'll see that same slow down as a new execution plan gets created.
30 seconds build time and 6 second executiions is a long time; unless your procedure is manipulating or returning a LOT of rows, you might want to paste it here for some suggestions on improving it's performance.
Lowell
July 27, 2009 at 7:23 am
Understood, but is there a way to control the existance of the execution plan in memory? or have SQL server create it automatically when it starts, provided that the SP has inputs and outputs?
July 27, 2009 at 7:41 am
not really...you could call the procedure prior to your users calling it to get it compiled, but it depends on what it is doing. does it just SELECT data or is it an UPDATE or something?
if you call it with NULL parameters, you could cache a bad execution plan(parameter sniffing) which would be even worse.
give us more details on your proc. like i said, it seems to be slow, maybe it can be improved. does it have a cursor in it?
Lowell
July 27, 2009 at 7:45 am
It could be compiling & storing the execution plan, or it could be caching the data, or it could be both.
We ran into a similar situation once. What we did was set up SQL Agent job that would run at 3AM when we had very few users on the system. It would clear the procedure out of cache and re-run it using a default set of parameters. The stupid query took several minutes to compile, so we got it loaded into memory early in the day.
Ultimately though, we replaced the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 27, 2009 at 7:54 am
I've done almost the same thing that Grant is talking about;
for a .net web page suite, we slapped together a couple of utilities that spidered all the web pages so they would be in the SQL cache(because the web page called the SELECT type procs) and the web server's cache in order to improve perceived performance.
Lowell
July 27, 2009 at 8:04 am
The stored proc is a bit large (has over 1300 line) it generates a report (No updates or inserts to actual tables) but it has many variable tables and functions (Table return type) with update and insert statements for the variable tables. No cursors but I have a couple of while statements!
I thought about running the SP as soon as the user login to the app. but I guess that would not be good practice?!!
July 27, 2009 at 8:12 am
If I may suggest...
Look at optimising that, at minimum removing the while loops and breaking it up into smaller procs. 1300 lines is waaaay huge for a single stored proc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2009 at 8:16 am
I'd like to second Gail's suggestion. It sounds like a great candidate for some re-work, re-design & tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 27, 2009 at 8:34 am
I've been working on the tuning of the SP for more than a week, and was able to get it down to 5 sec, but this 5 sec can't be acheived before the first run. the segment that takes half the time running in the first run is:
INSERT INTO @tblTemp_TransDetail(TRANSDETAILID ,TRANSACTIONID ,VOUCHERNO ,MainDescription ,VOUCHERTYPEID ,SUBDESC ,CURRENCYID ,CURRENCYNAMEAR,ACCID, ACCNO, ACCTSEQID ,ACCNAME,ACCNAMEEN, CUSTOMERID ,CUSTOMERNUMBER ,CUSTOMERNAME, CUSTOMERNAMEEN, VENDORID ,VENDORNUMBER,VENDORNAME, VENDORNAMEEN, ORGANIZATIONSTRUCTUREID ,ORGANIZATIONSTRUCTURENUMBER ,ORGANIZATIONSTRUCTURENAME ,PROJECTSEQID ,PROJECTID ,ProjectID2 ,PROJECTNUMBER ,PROJECTNAME ,DEBITAMOUNT ,DEBITAMOUNTFC ,CREDITAMOUNT ,CREDITAMOUNTFC, ISLOCAL,TRANSFERDATE,TransactionLevel,AccOrderID,CustomerOrderID,VendorOrderID,ACTIVE ,CREATEDBY ,CREATEDDATE ,MODIFIEDBY ,MODIFIEDDATE)
SELECT
TRD.TransDetailID
,TRD.TransactionID
,TRD.VoucherNo
,VB.MainDescription
,TRD.VoucherTypeID
,TRD.SubDesc
,TRD.CurrencyID
,C.CurrencyNameAR
,ACC.AccID
,ACC.AccNo
,TRD.ACCTSEQID
,ACC.AccName
,ACC.AccNameEN
,CU.CustomerID
,CU.CustomerNumber
,CU.CustomerName
,CU.CustomerNameEN
,V.VendorID
,V.VendorNumber
,V.VendorName
,V.VendorNameEN
,TRD.OrganizationStructureID
,ORG.OrganizationStructureNumber
,ORG.OrganizationStructureName
,TRD.ProjectSeqID
,PR.ProjectID
,PR2.ProjectID
,PR.ProjectNumber
,PR.ProjectName
,TRD.DebitAmount
,TRD.DebitAmountFC
,TRD.CreditAmount
,TRD.CreditAmountFC
,C.CurrencyID
,TRD.TransDate
,TRD.TransactionLevel
,CACC.OrderID
,CU.OrderID
,V.OrderID
,TRD.Active
,TRD.CreatedBy
,TRD.CreatedDate
,TRD.ModifiedBy
,TRD.ModifiedDate
FROM
@tblTMS_Accounts ACC
INNER JOIN #tblAc_Transactions TRD
ON TRD.AcctSeqID = ACC.AcctSeqID
INNER JOIN @tblChildAccount CACC
ON ACC.AccNo = CACC.AccNo
INNER JOIN @tblTransactionType TT
ON TRD.TransactionLevel = TT.TRANSACTIONTYPE
INNER JOIN @tblCurrancy C
ON TRD.CurrencyID = C.CurrencyID
INNER JOIN @tblOrgStructureID ORG
ON TRD.OrganizationStructureID = ORG.OrganizationStructureID
LEFT JOIN TMS_Ac_VoucherBook VB
ON VB.VoucherBookID = TRD.VoucherBookID
LEFT JOIN @tblCustomer CU
ON TRD.CustomerSeqID = CU.CustomerSeqID
LEFT JOIN @tblVendor V
ON TRD.VendorSeqID = V.VendorSeqID
LEFT JOIN TMS_Ac_Projects PR
ON TRD.ProjectSeqID = PR.ProjectSeqID
AND PR.ProjectID = @bintProjectID
LEFT JOIN TMS_Ac_Projects PR2
ON TRD.ProjectSeqID2 = PR2.ProjectSeqID
AND PR2.ProjectID = @bintProjectID
July 27, 2009 at 8:41 am
you might want to attach your proc, instead of copying and pasting it.
just from what you pasted, i see one way to improve it:
you mentioned you are using some functions,a nd hopefully those can be replaced with joins to ge tthe desired data instead.
also as I understand it any @table variable should only be used for small data sets. If you are inserting large sets into a variable and then manipulating them further, that's a performance hit, right? no statistics and all that.
much better to replace any variables with a CTE instead, where possible, or a real #temp table if you have to.
I'm thinking you are updating a table variable a few times until you get the desired set, and it might be improved with some joins.
Lowell
July 27, 2009 at 8:54 am
If you want specific optimisation help, please read this first http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2009 at 9:00 am
Thank you all for your feedback,
I was able to pin point the bottelneck in the stroed proc when it is first run after a SQL server start or when a new session is opened through the app.
In short the stored procedure does alot of processing and when it tries to retrieve the row data (~11,000 records)from the DB tables and insert them into a temporary table or variable table it takes a while.
I tried to run the select statement that feeds the Tempoarary or Variable table (tried both) without the insert statement;
--INSERT INTO #tblTemp_TransDetail(TRANSDETAILID ,TRANSACTIONID ,VOUCHERNO ,MainDescription ,VOUCHERTYPEID ,SUBDESC ,CURRENCYID ,CURRENCYNAMEAR,ACCID, ACCNO, ACCTSEQID ,ACCNAME,ACCNAMEEN, CUSTOMERID ,CUSTOMERNUMBER ,CUSTOMERNAME, CUSTOMERNAMEEN, VENDORID ,VENDORNUMBER,VENDORNAME, VENDORNAMEEN, ORGANIZATIONSTRUCTUREID ,ORGANIZATIONSTRUCTURENUMBER ,ORGANIZATIONSTRUCTURENAME ,PROJECTSEQID ,PROJECTID ,ProjectID2 ,PROJECTNUMBER ,PROJECTNAME ,DEBITAMOUNT ,DEBITAMOUNTFC ,CREDITAMOUNT ,CREDITAMOUNTFC, ISLOCAL,TRANSFERDATE,TransactionLevel,AccOrderID,CustomerOrderID,VendorOrderID,ACTIVE ,CREATEDBY ,CREATEDDATE ,MODIFIEDBY ,MODIFIEDDATE)
SELECT
TRD.TransDetailID
,TRD.TransactionID
,TRD.VoucherNo
,VB.MainDescription
,TRD.VoucherTypeID
,TRD.SubDesc
,TRD.CurrencyID
,C.CurrencyNameAR
,TRD.AccID
,TRD.AccNo
,TRD.ACCTSEQID
,TRD.AccName
,TRD.AccNameEN
,CU.CustomerID
,CU.CustomerNumber
,CU.CustomerName
,CU.CustomerNameEN
,V.VendorID
,V.VendorNumber
,V.VendorName
,V.VendorNameEN
,TRD.OrganizationStructureID
,ORG.OrganizationStructureNumber
,ORG.OrganizationStructureName
,TRD.ProjectSeqID
,PR.ProjectID
,PR2.ProjectID AS ProjectID2
,PR.ProjectNumber
,PR.ProjectName
,TRD.DebitAmount
,TRD.DebitAmountFC
,TRD.CreditAmount
,TRD.CreditAmountFC
,C.CurrencyID AS ISLOCAL
,TRD.TransDate AS TRANSFERDATE
,TRD.TransactionLevel
,CACC.OrderID AS AccOrderID
,CU.OrderID AS CustomerOrderID
,V.OrderID AS VendorOrderID
,TRD.Active
,TRD.CreatedBy
,TRD.CreatedDate
,TRD.ModifiedBy
,TRD.ModifiedDate
--INTO #tblTemp_TransDetail
FROM
#tblAc_Transactions TRD
INNER JOIN @tblChildAccount CACC
ON TRD.AccNo = CACC.AccNo
INNER JOIN @tblTransactionType TT
ON TRD.TransactionLevel = TT.TRANSACTIONTYPE
INNER JOIN @tblCurrancy C
ON TRD.CurrencyID = C.CurrencyID
INNER JOIN @tblOrgStructureID ORG
ON TRD.OrganizationStructureID = ORG.OrganizationStructureID
LEFT JOIN TMS_Ac_VoucherBook VB
ON VB.VoucherBookID = TRD.VoucherBookID
LEFT JOIN #tblCustomer CU
ON TRD.CustomerSeqID = CU.CustomerSeqID
LEFT JOIN #tblVendor V
ON TRD.VendorSeqID = V.VendorSeqID
LEFT JOIN TMS_Ac_Projects PR
ON TRD.ProjectSeqID = PR.ProjectSeqID
AND PR.ProjectID = @bintProjectID
LEFT JOIN TMS_Ac_Projects PR2
ON TRD.ProjectSeqID2 = PR2.ProjectSeqID
AND PR2.ProjectID = @bintProjectID
and it ran fast. When I put back the insert statement at the top it takes around 20 sec?!!
I tried to use insert into as well but it gives me a lot of troubles down the sp because it inherits the columns data types of the selected tables.
The same statement takes less than 2 sec after the first run?!
Any ideas on how to store the data coming back from the DB in the sp for processing?
July 28, 2009 at 9:10 am
after you stop and restart the server, the tempdb gets recreated....if it is small by default,(say 10 meg) the first SQL that inserts those 11,000 rows into the temp table has to wait for tempdb to grow, i bet....
after tempdb is bigger, subsequent passes are faster.
change the minimum size of tempdb to something a bit bigger, and see how much that helps .
Lowell
July 28, 2009 at 9:15 am
the other thing you can do is create a stored procedure to run this proc with some likely parameters. Then mark that as a startup procedure and have it run when the server starts.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply