May 30, 2008 at 6:31 am
hi,
could you plz help me in tuning this procedure which is talking 12 min, before it is used to take 20 sec?
CREATE PROCEDURE [dbo].[Express_pr_RequestEvent_Select]
@Integratorvarchar(15) = NULL--Entity that sends Express requests
,@ExportStatusIDtinyint= 1--Process Flag/Status of entry
AS
SET NOCOUNT ON;
----- Get Integrator ID
Declare @OrgID UniqueIdentifier
Select
@OrgID = OrgID
From
dbo.MemberOrganizations
where
OrgKey = @Integrator
--- Request Header ------------------
IF @Integrator = '001-W3Q-LC2-000'
BEGIN
Select
Case When Count(OrgID) is NULL
then 1
else Max(NextNumber) + 1 End as 'NextNumber'
From
dbo.Express_gen_ExportBatchID
Where
OrgID = @OrgID
andExtractDate = DATEADD(D, 0, DATEDIFF(D, 0, GETUTCDATE()))
END
ELSE
BEGIN
Select
Case When max(NextNumber) is NULL
then 1
else Max(NextNumber) + 1 EndAS 'NextNumber'
From
dbo.Express_gen_ExportBatchID
where
OrgID = @OrgID
END
---- Request Body --------------------
SELECT
re.RequestEventIDAS'RequestEventID'
,r.ImportRequestIDAS'RequestID'
,re.ProcessStateIDAS'ExportStatusID'
,ec.ExportCodeAS'ExportCode'
,re.CustomFieldsAS'CustomFields'
,re.OrgIDAS'OrgID'
,re.ShippingServiceIDAS'ShippingServiceID'
,re.TrackingNumberAS'TrackingNumber'
,r.BuyerDealerCodeAS'BuyingDealerCode'
,r.PartNumberAS'PartNumber'
,head.RequestTypeAS'RequestType'
,r.QtyRequestedAS'QtyRequested'
,re.CreatedOnAS'CreatedOn'
FROM
dbo.Express_log_RequestEventre
INNER JOIN dbo.Express_cfg_ExportCodeecON re.ExportCodeID = ec.ExportCodeID
INNER JOIN dbo.MemberOrganizationsmo ON ec.OrgID = mo.OrgID
INNER JOIN dbo.Express_etl_ImportRequestrON r.ASRID = re.RequestID AND r.ProcessFlagID = 2
INNER JOIN dbo.Express_etl_ImportHeaderheadON head.ImportHeaderID = r.ImportHeaderID
WHERE
ec.OrgID = @OrgId
AND ((re.ProcessStateID & @ExportStatusID) > 0)
ORDER BY
CreatedOn ASC
SELECT
re.RequestEventIDAS'RequestEventID'
,AutomotiveInventoryMaster.dbo.Inventory_fn_LastSaleDate_Select(ip.InventoryPartID)AS'LastSaleDate'
,c.ShipMethodIDAS'ShipMethodID'
,s.DealerCodeAS 'SellingDealerCode'
FROM
dbo.Express_log_RequestEventre
INNER JOIN dbo.Express_cfg_ExportCodeecON re.ExportCodeID = ec.ExportCodeID
AND re.CreatedOn > '2008-05-29'
INNER JOIN dbo.MemberOrganizationsmo ON ec.OrgID = mo.OrgID
INNER JOIN dbo.Express_etl_ImportRequestrON r.ASRID = re.RequestID AND r.ProcessFlagID = 2
INNER JOIN dbo.Express_etl_ImportHeaderheadON head.ImportHeaderID = r.ImportHeaderID
INNER JOIN dbo.AutoSourcingCNLcnlON re.CnlId = cnl.CnlId
INNER JOIN dbo.AutoSourcingWaveswON w.CnlId = cnl.CnlId
INNER JOIN dbo.AutoSourcingCandidatesc
ON c.WaveId = w.WaveId
AND c.OrgUnitId
IN (SELECT OrgId
FROM [dbo].[Security_fx_Franchise_Select_ByOrgID](re.OrgId))
AND c.WaveId = w.WaveId
INNER JOIN dbo.Supplier sON s.orgid = c.orgunitid
AND s.ManufacturerID = Mo.ManufacturerID
INNER JOIN AutomotiveInventoryMaster.dbo.Inventory_vw_Inventoryi ON i.orgUnitID = c.OrgUnitID
INNER JOIN AutomotiveInventoryMaster.dbo.Inventory_vw_InventoryPartsip ON ip.SupplierID = i.SupplierID
INNER JOIN AutomotivePartsMaster.dbo.PartsMaster_vw_PartsMasterpm ON ip.PartID = pm.PartID AND pm.PartNumber = r.PartNumber AND pm.ManufacturerID = mo.ManufacturerID
WHERE
ec.OrgID = @OrgId
AND ((re.ProcessStateID & @ExportStatusID) > 0)
ORDER BY
re.RequestEventID asc
May 30, 2008 at 7:26 am
The first thing I would recommend is checking to see if your statistics are being updated. Check the DB to see if autoupdate stats is on.
Second in your last query you have this:
dbo.AutoSourcingCandidates c ON
c.WaveId = w.WaveId AND
c.OrgUnitId IN (SELECT OrgId FROM [dbo].[Security_fx_Franchise_Select_ByOrgID](re.OrgId)) AND
c.WaveId = w.WaveId INNER JOIN
Is dbo.Security_Fx_Franchise_Select_ByOrgID a table-valued function? If it is you might be better off duplicating it with a join on the actual tables as this function is running for every record returned.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2008 at 7:27 am
There is no where near enough information...
Please post the following:
The table structures
The index structures
Some idea of the volumne of data in these tables.
The current execution plan (saves as a .sqlplan file, zipped and attached to your post)
Please remember that we are all volnteers on this forum. If you help us by providing all the necessary info, then you are far more likely to get a useful suggestion in a reasonable amount of time.
Please read this article[/url] as well.
As for the increase in time, has anything changed? Are your indexes fragmented? Are your statistics up to date?
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
May 30, 2008 at 7:30 am
Interesting. How come another member has posted the exact same procedure (but different name) with virtually the same question?
See here: http://www.sqlservercentral.com/Forums/Topic509053-146-1.aspx
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
May 30, 2008 at 7:33 am
Everything Jack said plus...
Have you defragmented or rebuilt your indexes in recent memory? If not, I'd do that.
Have you examined the execution plan to see where the bottlenecks in the performance are located? Identifying the table/index scans or id lookups is one of the biggest steps you can do in tuning.
In order to have the cleanest possible execution plan, although I don't think you'll see huge performance improvements from this, you should take those SELECT statements that are inside the IF clause and put them into seperate stored procedures. The problem is, you'll get recompiles during execution and/or bad plans when the IF goes down the different paths as long as the SELECTS are within this procedure. Seperate them out and they get their own execution plan, independent of this one. Again, this is unlikely to be a big performance win, but it is a best practice to follow.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply