May 30, 2008 at 7:09 am
here is the stored proc....
CREATE
PROCEDURE [dbo].[Request.Sales]
@Integrator varchar(15) = NULL --Entity that sends Express requests
,@ExportStatusID tinyint = 1 --Process Flag/Status of entry
AS
SET
NOCOUNT ON;
----- Get Integrator ID
Declare
@OrgID UniqueIdentifier
Select
@OrgID
= OrgID
From
dbo
.Member
where
OrgKey
= @Integrator
--- Request Header ------------------
IF
@Integrator = '001'
BEGIN
Select
Case When Count(OrgID) is NULL
then 1
else Max(NextNumber) + 1 End as 'NextNumber'
From
dbo
.Express_ExportBatchID
Where
OrgID
= @OrgID
and ExtractDate = DATEADD(D, 0, DATEDIFF(D, 0, GETUTCDATE()))
END
ELSE
BEGIN
Select
Case When max(NextNumber) is NULL
then 1
else Max(NextNumber) + 1 End AS 'NextNumber'
From
dbo
.Express_ExportBatchID
where
OrgID
= @OrgID
END
---- Request Body --------------------
SELECT
re
.RequestEventID AS 'RequestEventID'
,r.ImportRequestID AS 'RequestID'
,re.ProcessStateID AS 'ExportStatusID'
,ec.ExportCode AS 'ExportCode'
,re.CustomFields AS 'CustomFields'
,re.OrgID AS 'OrgID'
,re.ShippingServiceID AS 'ShippingServiceID'
,re.TrackingNumber AS 'TrackingNumber'
,r.BuyerDealerCode AS 'BuyingDealerCode'
,r.PartNumber AS 'PartNumber'
,head.RequestType AS 'RequestType'
,r.QtyRequested AS 'QtyRequested'
,re.CreatedOn AS 'CreatedOn'
FROM
dbo
.Express_RequestEvent re
INNER JOIN dbo.Express_ExportCode ec ON re.ExportCodeID = ec.ExportCodeID
INNER JOIN dbo.MemberOrganizations mo ON ec.OrgID = mo.OrgID
INNER JOIN dbo.Express_ImportRequest r ON r.ASRID = re.RequestID AND r.ProcessFlagID = 2
INNER JOIN dbo.Express_ImportHeader head ON head.ImportHeaderID = r.ImportHeaderID
WHERE
ec
.OrgID = @OrgId
AND ((re.ProcessStateID & @ExportStatusID) > 0)
ORDER
BY
CreatedOn
ASC
SELECT
re
.RequestEventID AS 'RequestEventID'
,AutomotiveInventoryMaster.dbo.Inventory_LastSaleDate_Select(ip.InventoryPartID) AS 'LastSaleDate'
,c.ShipMethodID AS 'ShipMethodID'
,s.DealerCode AS 'SellingDealerCode'
FROM
dbo
.Express_RequestEvent re
INNER JOIN dbo.Express_ExportCode ec ON re.ExportCodeID = ec.ExportCodeID
AND re.CreatedOn > '2008-05-29'
INNER JOIN dbo.MemberOrganizations mo ON ec.OrgID = mo.OrgID
INNER JOIN dbo.Express_ImportRequest r ON r.ASRID = re.RequestID AND r.ProcessFlagID = 2
INNER JOIN dbo.Express_ImportHeader head ON head.ImportHeaderID = r.ImportHeaderID
INNER JOIN dbo.AutoSourcingCNL cnl ON re.CnlId = cnl.CnlId
INNER JOIN dbo.AutoSourcingWaves w ON w.CnlId = cnl.CnlId
INNER JOIN dbo.AutoSourcingCandidates c
ON c.WaveId = w.WaveId
AND c.OrgUnitId
IN (SELECT OrgId
FROM [dbo].[Security_Franchise_Select_ByOrgID](re.OrgId))
AND c.WaveId = w.WaveId
INNER JOIN dbo.Supplier s ON s.orgid = c.orgunitid
AND s.ManufacturerID = Mo.ManufacturerID
INNER JOIN AutomotiveInventoryMaster.dbo.Inventory_Inventory i ON i.orgUnitID = c.OrgUnitID
INNER JOIN AutomotiveInventoryMaster.dbo.Inventory_InventoryParts ip ON ip.SupplierID = i.SupplierID
INNER JOIN AutomotivePartsMaster.dbo.PartsMaster_PartsMaster pm 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
please help me out with this procedure ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
May 30, 2008 at 7:22 am
Help you with what? There is no where near enough information to give you any suggestions.
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)
The current execution time
The required execution time.
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.
Thank you
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:32 am
Now this is interesting. Same question from a different person. hmmm. Homework?
http://www.sqlservercentral.com/Forums/Topic509017-360-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:34 am
Gail, you're just too good.
"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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply