November 11, 2011 at 4:01 am
Hello I need help on this StoredProc
It has been running for like more than a minute
I want to performance tune it to run for seconds any help will be appreciated
USE [prdlcon]
GO
/****** Object: StoredProcedure [dbo].[upr_MaintenanceSpendByManuByDate] Script Date: 11/11/2011 09:43:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/**********************************************************************************************
-- USE LCON
-- Swan AUR228 for Joe Bhamra 26/03/2008
-- Requires new some new fields added to the following report - Reporter > ServiceTrak > Maintenance Spend by Manufacturer by date
-- add model(of vehicle). Currently column (F) - Maintspend - can this be split into Parts and Labour for the invoice.
**********************************************************************************************/
--ALTER Procedure [dbo].[upr_MaintenanceSpendByManuByDate]
declare @dbPrefix varchar(3) = 'PRD', @StartDate varchar(10) = '2011/10/01', @EndDate varchar(10)= '2011/10/10', @Mfr varchar(8) = 'FO', @LiveVehicles varchar(1) = 'y' -- AS
DECLARE @sqlstring NVARCHAR(4000)
SELECT SUBSTRING(v.description,1,2) AS Manufacturer,
mo.model_name AS Model,
ih.supp_acc_no AS Supp_Acc_No, tp.[name] AS Supplier_Name,
prdtprt.dbo.GetCity(addr1,addr2,addr3,addr4,addr5),
CONVERT(VARCHAR,ih.created_on,111) AS Created_On,
SUM(prdlcon.dbo.GetMaintSpendByDate(ch.vehicle_id, @StartDate, @EndDate, ih.invoice_header_id)) AS Maintenance_Spend,
SUM(prdlcon.dbo.GetPartsCost(ch.vehicle_id, @StartDate, @EndDate, ih.invoice_header_id)) AS Parts,
SUM(prdlcon.dbo.GetLabourCost(ch.vehicle_id, @StartDate, @EndDate, ih.invoice_header_id)) AS Labour,
prdtprt.dbo.GetGarageFranchises(ih.supp_acc_no) AS Franchises, v.vehicle_reg, oh.supp_labour_rate, ch.fleet,
prdtprt.dbo.GetFleetName (ch.fleet) AS Fleet_Name,
(CASE ga.service_trak_flag when 1 then 'YES' else 'NO' END) as Service_trak,
(CASE ga.preferred when 1 then 'YES' else 'NO' end) as Preferred,
a.bacs_discount *100,
Prdtprt.dbo.GetFranchisePartsDiscount(tp.trading_partner_id,getdate()) *100,
op.description,
op.quantity,
op.unit_price,
op.disc_unit_price,
op.net_parts_price
FROM
contract_history ch
INNER JOIN vehicles v ON v.contract_id = ch.contract_id and v.vehicle_id = ch.vehicle_id
INNER JOIN invoice_headers ih ON ih.vehicle_id = ch.vehicle_id
INNER JOIN order_headers oh ON oh.order_header_id = ih.order_header_id and oh.vehicle_id = ih.vehicle_id
INNER JOIN prdtprt.dbo.accnt_details a ON account_no = oh.supp_account
INNER JOIN prdtprt.dbo.tp_contact tpc ON tpc.trading_partner_id = a.trading_partner_id and tpc.contact_role_id =1
INNER JOIN prdtprt.dbo.address addr ON addr.address_id = tpc.address_id
INNER JOIN prdtprt.dbo.trading_partner tp ON tp.trading_partner_id = tpc.trading_partner_id
LEFT OUTER JOIN prdvmnt.dbo.variants [var] ON v.david_henley_id = [var].dhs_id COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN prdvmnt.dbo.models mo ON [var].manufacturer_id = mo.manufacturer_id and VAR.model_id = mo.model_id
LEFT OUTER JOIN prdtprt.dbo.garages ga ON tp.trading_partner_id = ga.trading_partner_id
and ga.GARAGE_ID = (select MIN(garage_id) from prdtprt.dbo.garages ga2 where ga2.TRADING_PARTNER_ID = ga.TRADING_PARTNER_ID)
LEFT OUTER JOIN order_parts op on op.vehicle_id = oh.vehicle_id -- and op.order_header_id = oh.order_header_id and (op.description like '%synth%' or op.description like '%min%')
WHERE ih.created_on BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME)
AND (@Mfr ='' OR (SUBSTRING(@Mfr,1,2) =SUBSTRING(v.[description],1,2)))
AND ((ch.[to] IS NULL AND @LiveVehicles = 'Y') OR @LiveVehicles = 'A')
GROUP BY
SUBSTRING(v.[description],1,2),
ih.supp_acc_no, tp.[name], prdtprt.dbo.GetCity(addr1,addr2,addr3,addr4,addr5),
CONVERT(VARCHAR,ih.created_on,111),
prdtprt.dbo.GetGarageFranchises(ih.supp_acc_no), v.vehicle_reg, oh.supp_labour_rate, ch.fleet,
prdtprt.dbo.GetFleetName (ch.fleet),
mo.model_name, ch.vehicle_id, ih.created_on, ih.order_header_id, ga.service_trak_flag, ga.preferred,a.bacs_discount *100,
Prdtprt.dbo.GetFranchisePartsDiscount(tp.trading_partner_id,getdate()) *100,
op.description,
op.quantity,
op.unit_price,
op.disc_unit_price,
op.net_parts_price
HAVING
sum(prdlcon.dbo.GetMaintSpendByDate(ch.vehicle_id, @StartDate , @EndDate , ih.invoice_header_id)) <> 0
ORDER BY 1,2,3,11
--execute sp_executesql @sqlstring
--print @sqlstring
Regards
November 11, 2011 at 4:14 am
Two things you might consider are converting your scalar-value "Get" functions to table-value functions, and omitting the ORDER BY and having your presentation layer do the sorting instead.
John
November 11, 2011 at 4:16 am
Please share the execution plan of stored procedure.
November 11, 2011 at 4:40 am
There are seven functions in your output list. Guessing from the function names and the passed parameters, a couple of them perform quite complex calculations touching several tables. Your first step should be to evaluate how much each one costs to run, then determine if it's worth rewriting the logic into your code instead of using the function. Since you're using SS2K8 you can probably incorporate those functions with minimal change into your FROM list as CROSS/OUTER APPLY which would make this rewriting much easier.
So, comment out all of the functions in the SELECT list. Run the query a couple of times and time how long it takes. Better still, SET STATISTICS TIME ON. Then uncomment the first function and repeat, then the second and so on - uncommenting one function at a time.
There's some scope for performance tweaking in the rest of the query too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 11, 2011 at 5:43 am
Please post table definitions, index definitions and execution plan, as per http://qa.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
November 11, 2011 at 6:37 am
PLEASE DONT MIND THE FAINT NATURE
THE EXECUTION PLAN IS ATTACHED
THANKS FOR UR HELP
November 11, 2011 at 6:42 am
pespes009 (11/11/2011)
PLEASE DONT MIND THE FAINT NATURETHE EXECUTION PLAN IS ATTACHED
THANKS FOR UR HELP
We need the .sqlplan file. We can't do anything with that jpeg.
P.S. posting in all caps = shouting and is usually not welcomed on forums ;-).
November 11, 2011 at 6:49 am
HERE IT IS
November 11, 2011 at 6:57 am
pespes009 (11/11/2011)
HERE IT IS
All capitals letters = shouting. Please stop it.
Thanks in advance.
November 11, 2011 at 7:00 am
o sorry
November 11, 2011 at 7:03 am
Great, one of three.
Table definitions please
Index definitions please (both as CREATE statements, not pictures)
Edit: Also the definition of the user defined functions please. It's very likely that they are a large part of the problem.
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
November 11, 2011 at 7:04 am
pespes009 (11/11/2011)
o sorry
No problem.
You have no less than 12 objects scans. You also have functions that possibly also do table scans.
There's no 2 minute solution I can give you for this. I'd need full access to the system to start working on it.
What I'd try here is to remove the functions to see how much gains that gets me. If it's massive I'd consider divide and conquer. That means intermediate temp table to save the results.
I'd probably do the same on the base tables that will have the greatest filter impact.
Then finalize with all the joins. That's the best chance you have to get seeks somewhere.
The plan is not noting any missing indexes and there's no compile timeout... so again, nothing obvious.
This is easily a 1-2 days job depending on how fast you need this to run.
November 11, 2011 at 7:05 am
One thing straight off...
Why are you declaring your dates as strings
@StartDate varchar(10) = '2011/10/01', @EndDate varchar(10)= '2011/10/10'
and then converting them to datetime
CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME)
That's just a waste of processing time
Also:
ORDER BY 1,2,3,11
Column names, rather than ordinal positions are easier to read and less likely to be broken by a minor change to the select.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply