VIew Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

VIew Performance

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-31 : 11:20:20

Hi All,

I am finding trouble to optimize this view/query performance.
User reported us that below query is taking time and not returning even top 100 records.
I looked this query and ran it after down each part and found that this causing trouble at the end where NOT EXISTS clause is used.
As a beginner I am not sure how to change this query from NOT EXISTS to in different query so that result could come fast .Please advise what can be possible things I can use in place of NOT EXISTs to get the same records.

SELECT
top 100 S.ClaimID
,S1.SalesID AS OriginalShipmentSalesID
,C.AnshinPlusID
,AD.Date AS ApprovedDate
,SD.Date AS ShipDate
,O.created_by AS 'ReshipmentOperatedBy'
,O.created_dtm AS 'ReshipCreatedDateTime'
,LRD.Date AS LastReshipDate
,LRD.Date_Int As LastReshipDateID
,PT.PerilType
,MT.MatrixType AS OriginalMatrixType
,SH1.AsurionSKU AS OriginalShippedEquipment
,SL1.ASU_ESN AS OriginalShippedESN
,SH1.Model AS OriginalShippedModel
,SH1.ColorEnglish AS OriginalShippedModelColor
,SH1.Manufacturer AS OriginalShippedHandsetMake
,S1.LineAmount AS OriginalShipHandsetCost
-------,SL.SALESID AS ReshippedSalesOrderID
,S.SalesID AS ReshipmentSalesID
,SH.AsurionSKU AS ReshippedEquipment
,SL.ASU_ESN AS ReshippedESN
,SH.Model AS ReshippedModel
,SH.ColorEnglish AS ReshippedModelColor
,SH.Manufacturer AS ReshippedHandsetMake
,S.LineAmount AS ReshipHandsetCost
,(CASE WHEN
ISNULL(LRD.Date,SD.Date) < (SELECT Min(I1.PRICEDATE) FROM Master.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) ))
THEN
(SELECT TOP 1 I1.Price FROM Master.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) )
ORDER BY I1.PRICEDATE DESC)
ELSE
(SELECT I1.Price FROM MASTER.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) )
AND ISNULL(LRD.Date,SD.Date) BETWEEN I1.PRICEDATE AND ISNULL(I1.EndDate - 1, GETDATE()))
END) AS ReshipHandsetNewCost
,ReshipReasonCategory_Level1
,ReshipReasonCategory_Level2
,ReshipReasonCategory_Level3
,TS.Comment AS Comments
,(CASE WHEN TS.DS_Confirmation = 1 THEN N'DS??????'
WHEN TS.DS_Confirmation = 2 THEN N'DS??????'
WHEN TS.DS_Confirmation = 3 THEN N'DS????'
END) AS DSConfirmation
,IRM.ASU_LEVEL1 AS ReasonCategory
,IRM.ASU_LEVEL2 AS ReasonCode
,(CASE WHEN SRRMA.REASONMATCH = 0 THEN 'None'
WHEN SRRMA.REASONMATCH = 1 THEN 'No Match'
WHEN SRRMA.REASONMATCH = 2 THEN 'Match'
WHEN SRRMA.REASONMATCH = 3 THEN 'Not Tested'
ELSE 'Unknown'
END) AS ReproducibilityTest
,SRRMA.EMPLID AS 'ShipReturnOperatorID'
,DATEADD(HH,9,SRRMA.CREATEDDATETIME) AS 'MatchUnMatchDateTime'
,SRRMA.InspectionComments AS ReproducibilityTestResult
FROM Fact.Shipment S WITH (NOLOCK)
LEFT JOIN Fact.Shipment S1 WITH (NOLOCK)
ON S.ClaimID = S1.ClaimID
LEFT JOIN Fact.Claim C WITH (NOLOCK)
ON S.ClaimID = C.ClaimID
LEFT JOIN SourceAxapta.vSalesLine SL WITH (NOLOCK)
ON cast (S.SALESID as nvarchar(20)) = cast(SL.SALESID as nvarchar(20))
AND S.SalesLineNum = SL.LINENUM
LEFT JOIN SourceAxapta.vSalesLine SL1 WITH (NOLOCK)
ON cast(S1.SALESID as nvarchar(20)) = cast(SL1.SALESID as nvarchar(20)) AND
S1.SalesLineNum = SL1.LINENUM
INNER JOIN Dim.Handset H WITH (NOLOCK)
ON S.ShippedItemID = H.HandsetID
INNER JOIN Dim.Handset H1 WITH (NOLOCK)
ON S1.ShippedItemID = H1.HandsetID
LEFT JOIN Dim.MatrixType MT WITH (NOLOCK)
ON S1.MatrixTypeID = MT.MatrixTypeID
LEFT JOIN Dim.PerilType PT WITH (NOLOCK)
ON C.PerilTypeID = PT.ID
LEFT JOIN Dim.Handset SH WITH (NOLOCK)
ON S.ShippedItemID = SH.HandsetID
LEFT JOIN Dim.Handset SH1 WITH (NOLOCK)
ON S1.ShippedItemID = SH1.HandsetID
LEFT JOIN SourceAxapta.vSalesTable ST WITH (NOLOCK)
ON cast(S.SALESID as nvarchar(20)) = cast(ST.SALESID as nvarchar(20))
LEFT JOIN SourceIntelliset.vOrder O WITH (NOLOCK)
ON ST.ASU_ISORDERID = O.order_id
LEFT JOIN SourceIntelliset.vReshipReasonCategorySource RRC WITH (NOLOCK)
ON O.parent_order_id = RRC.order_id
LEFT JOIN SourceIntelliset.vTechSupport TS WITH (NOLOCK)
ON RRC.order_id = TS.order_id
LEFT JOIN SourceAxapta.vASU_SHIPRETURNRMA SRRMA WITH (NOLOCK)
ON S1.SALESID = SRRMA.SALESID AND SRRMA.SALESID NOT LIKE '%SO%'
LEFT JOIN SourceAxapta.vASU_INSPECTIONREASONMASTER IRM WITH (NOLOCK)
ON SRRMA.INSPECTIONREASONSERIALNUMBER = IRM.SERIALNUMBER
LEFT JOIN Dim.Date AD WITH (NOLOCK)
ON C.ApprovedDateID = AD.ID
LEFT JOIN Dim.Date SD WITH (NOLOCK)
ON C.ShipDateID = SD.ID
LEFT JOIN Dim.Date LRD WITH (NOLOCK)
ON S.ShipDateID = LRD.ID
WHERE S.SalesID > S1.SalesID
AND SL.SALESID NOT LIKE '%SO%'
AND SL1.SALESID NOT LIKE '%SO%'
AND ST.SALESID NOT LIKE '%SO%'
and (S1.ShippedItemID = S.PrevShipmentItemID)
AND H.ItemGroupID = 'Phone'
AND H1.ItemGroupID = 'Phone'
AND S.ShipEventID IN (1,2)
AND S1.ShipEventID IN (1,2)
AND S.ShippedQty = 1
AND S1.ShippedQty = 1
AND NOT EXISTS
(SELECT 1 FROM fact.shipment FS WITH (NOLOCK)
INNER JOIN Dim.Handset H2
ON FS.ShippedItemID = H2.HandsetID
WHERE H2.ItemGroupID = 'Phone' AND FS.SalesID < S.SalesId AND FS.SalesID > S1.SalesID) AND O.parent_order_id > 0

Thanks
VIjay Sahu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:25:31

try using LEFT JOIN instead and check for IS NULL condition for relating fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 11:35:26

Doesn't make sense
You have a left join on s1 and are using it in the inequality in the subquery. If there is no associated s1 row that will never return a row.
s1 is also used i the where clause - so this is an inner join.

I would populate a temp table and then use that to optimise the subquery.

in fact
FROM Fact.Shipment S WITH (NOLOCK)
LEFT JOIN Fact.Shipment S1 WITH (NOLOCK)
ON S.ClaimID = S1.ClaimID

How can there not be a row from S1 in that join and presumably you will be returning duplicates. The where clause will remove the equality rows.
I think you proibably want to group by claim_id having more than one sales ID. So you want to get a row for each sale or each claim - if for each claim then use the max and min for the subquery.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:45:33

best thing is to show with some sample data with required output so that we can try giving you optimised rewritten query instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-31 : 13:01:14

ClaimID ReshipmentSalesID ReshippedEquipment ReshippedESN ReshippedModel ReshippedModelColor ReshippedHandsetMake ReshipHandsetCost ReshipHandsetNewCost
6368412 636841205 S900-3744-SH01B 359421020520982 SH-01B Gold Sharp 29300.000000000000 52072.000000000000
6474300 647430003 N900-3771-SH06B 355604034814947 SH-06B White Sharp 31556.000000000000 31556.000000000000
6502608 650260802 N900-8820-F08B 353168035615945 F-08B Black Fujitsu 27400.000000000000 27400.000000000000
6500555 650055502 S900-2120-P905i 359932017422248 P905i Black Panasonic 24323.000000000000 51980.000000000000
6501416 650141602 S900-8871-F01B 356772024407680 F-01B White Fujitsu 26180.000000000000 53268.000000000000
6501416 650141602 S900-8871-F01B 356772024407680 F-01B White Fujitsu 26180.000000000000 53268.000000000000
6501416 650141602 S900-8871-F01B 356772024407680 F-01B White Fujitsu 26180.000000000000 53268.000000000000
6502305 650230502 S900-1820-N905iu 355298017521916 N905iu Black NEC 27586.000000000000 48852.000000000000
6462304 646230402 S900-1871-N04B 359940017366794 N-04B White NEC 20175.000000000000 52500.000000000000
6464821 646482102 S900-2171-P906i 353707024184361 P906i White Panasonic 25486.000000000000 51060.000000000000

above given data is from query which is written before NOT EXIST.

Please fine the top 10 records with some of the columns from fact.shipment table .

SalesLineNum ClaimID ShippedQty ShipEventID ShipEventDateID ShipDateID ShipReturnDateID IsValidShipment PrevShipmentItemID MatrixTypeID
1 50006 1 1 38899 38899 -1 1 -1 1
2 50006 1 1 38899 38899 -1 1 -1 1
1 50009 1 1 38899 38899 -1 1 -1 1
2 50009 1 1 38899 38899 -1 1 -1 1
1 50010 1 1 38899 38899 -1 1 -1 1
2 50010 1 1 38899 38899 -1 1 -1 1
1.5 50016 1 1 38900 38900 -1 1 -1 5
2 50016 1 1 38900 38900 -1 1 -1 5
3 50016 1 1 38900 38900 -1 1 -1 5
1 50033 1 1 38902 38902 -1 1 -1 1

Smaple data from Dim.Handset table :

HandsetID ManuFacturerCode Manufacturer InstallmentJapanese InstallmentEnglish InstallmentRomaji HandsetType HandsetPackage ItemGroupID
-1 NULL Unknown ?? Unknown Fumei Unknown Unknown Unknown
2 88 Fujitsu ????? Not Eligible Hitaisyou New Kit PHONE
3 16 Mitsubishi ????? Not Eligible Hitaisyou New Handset PHONE
4 18 NEC ???? Eligible Taisyou New Kit PHONE
5 37 Sharp ????? Not Eligible Hitaisyou New Handset PHONE
6 18 NEC ????? Not Eligible Hitaisyou New Handset PHONE
7 88 Fujitsu ????? Not Eligible Hitaisyou New Handset PHONE
8 16 Mitsubishi ????? Not Eligible Hitaisyou New Kit PHONE
9 37 Sharp ???? Eligible Taisyou New Kit PHONE
10 16 Mitsubishi ????? Not Eligible Hitaisyou New Kit PHONE

I hope this data will be enough make it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 13:32:17

and whats the output you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-31 : 14:37:03

user is trying to get the result of the to 100 records from the
report.vReshipment view.. report.vReshipment view is created by using my given query ..

select top 100 * from report.vReshipment nolock

Kristen
Test

22859 Posts

Posted - 2011-10-31 : 15:11:28

All those "WITH (NOLOCK)" worry me.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-31 : 23:45:56

Hi Kristen

Should I remove all NOLOCK ,If I do so it might create a blocking issue

Thanks
Vijay Sahu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:50:07

quote:
Originally posted by vijays3

Hi Kristen

Should I remove all NOLOCK ,If I do so it might create a blocking issue

Thanks
Vijay Sahu

see

http://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-11-01 : 13:39:02

It seems this is very difficult to get the exact answer from you Techies without giving you proper question becuase I am also finding tough how to put it so that I can get your ideas.As per your advise
inplace of NO EXISTS I have used LEFT join and filtering NULL..
I just want your advise will this join work fine in place of NOEXIST
where co-related query is written. Please advise

SELECT
S.ClaimID
,S1.SalesID AS OriginalShipmentSalesID
,C.AnshinPlusID
,AD.Date AS ApprovedDate
,SD.Date AS ShipDate
,O.created_by AS 'ReshipmentOperatedBy'
,O.created_dtm AS 'ReshipCreatedDateTime'
,LRD.Date AS LastReshipDate
,LRD.Date_Int As LastReshipDateID
,PT.PerilType
,MT.MatrixType AS OriginalMatrixType
,SH1.AsurionSKU AS OriginalShippedEquipment
,SL1.ASU_ESN AS OriginalShippedESN
,SH1.Model AS OriginalShippedModel
,SH1.ColorEnglish AS OriginalShippedModelColor
,SH1.Manufacturer AS OriginalShippedHandsetMake
,S1.LineAmount AS OriginalShipHandsetCost
---------,SL.SALESID AS ReshippedSalesOrderID
,S.SalesID AS ReshipmentSalesID
,SH.AsurionSKU AS ReshippedEquipment
,SL.ASU_ESN AS ReshippedESN
,SH.Model AS ReshippedModel
,SH.ColorEnglish AS ReshippedModelColor
,SH.Manufacturer AS ReshippedHandsetMake
,S.LineAmount AS ReshipHandsetCost
,(CASE WHEN
ISNULL(LRD.Date,SD.Date) < (SELECT Min(I1.PRICEDATE) FROM Master.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) ))
THEN
(SELECT TOP 1 I1.Price FROM Master.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) )
ORDER BY I1.PRICEDATE DESC)
ELSE
(SELECT I1.Price FROM MASTER.InventoryPrice I1 WITH (NOLOCK)
WHERE I1.ItemId = (SELECT STUFF(HD.AsurionSKU,1,1,'N') FROM Dim.Handset HD
WHERE HD.HandsetID = ISNULL(S.ShippedItemID,-1) )
AND ISNULL(LRD.Date,SD.Date) BETWEEN I1.PRICEDATE AND ISNULL(I1.EndDate - 1, GETDATE()))
END) AS ReshipHandsetNewCost
,ReshipReasonCategory_Level1
,ReshipReasonCategory_Level2
,ReshipReasonCategory_Level3
,TS.Comment AS Comments
,(CASE WHEN TS.DS_Confirmation = 1 THEN N'DS??????'
WHEN TS.DS_Confirmation = 2 THEN N'DS??????'
WHEN TS.DS_Confirmation = 3 THEN N'DS????'
END) AS DSConfirmation
,IRM.ASU_LEVEL1 AS ReasonCategory
,IRM.ASU_LEVEL2 AS ReasonCode
,(CASE WHEN SRRMA.REASONMATCH = 0 THEN 'None'
WHEN SRRMA.REASONMATCH = 1 THEN 'No Match'
WHEN SRRMA.REASONMATCH = 2 THEN 'Match'
WHEN SRRMA.REASONMATCH = 3 THEN 'Not Tested'
ELSE 'Unknown'
END) AS ReproducibilityTest
,SRRMA.EMPLID AS 'ShipReturnOperatorID'
,DATEADD(HH,9,SRRMA.CREATEDDATETIME) AS 'MatchUnMatchDateTime'
,SRRMA.InspectionComments AS ReproducibilityTestResult into #temp1
FROM Fact.Shipment S WITH (NOLOCK)
LEFT JOIN Fact.Shipment S1 WITH (NOLOCK)
ON S.ClaimID = S1.ClaimID
LEFT JOIN Fact.Claim C WITH (NOLOCK)
ON S.ClaimID = C.ClaimID
LEFT JOIN SourceAxapta.vSalesLine SL WITH (NOLOCK)
ON cast (S.SALESID as nvarchar(20)) = cast(SL.SALESID as nvarchar(20))
AND S.SalesLineNum = SL.LINENUM
LEFT JOIN SourceAxapta.vSalesLine SL1 WITH (NOLOCK)
ON cast(S1.SALESID as nvarchar(20)) = cast(SL1.SALESID as nvarchar(20)) AND
S1.SalesLineNum = SL1.LINENUM
INNER JOIN Dim.Handset H WITH (NOLOCK)
ON S.ShippedItemID = H.HandsetID
INNER JOIN Dim.Handset H1 WITH (NOLOCK)
ON S1.ShippedItemID = H1.HandsetID
LEFT JOIN Dim.MatrixType MT WITH (NOLOCK)
ON S1.MatrixTypeID = MT.MatrixTypeID
LEFT JOIN Dim.PerilType PT WITH (NOLOCK)
ON C.PerilTypeID = PT.ID
LEFT JOIN Dim.Handset SH WITH (NOLOCK)
ON S.ShippedItemID = SH.HandsetID
LEFT JOIN Dim.Handset SH1 WITH (NOLOCK)
ON S1.ShippedItemID = SH1.HandsetID
LEFT JOIN SourceAxapta.vSalesTable ST WITH (NOLOCK)
ON cast(S.SALESID as nvarchar(20)) = cast(ST.SALESID as nvarchar(20))
LEFT JOIN SourceIntelliset.vOrder O WITH (NOLOCK)
ON ST.ASU_ISORDERID = O.order_id
LEFT JOIN SourceIntelliset.vReshipReasonCategorySource RRC WITH (NOLOCK)
ON O.parent_order_id = RRC.order_id
LEFT JOIN SourceIntelliset.vTechSupport TS WITH (NOLOCK)
ON RRC.order_id = TS.order_id
LEFT JOIN SourceAxapta.vASU_SHIPRETURNRMA SRRMA WITH (NOLOCK)
ON S1.SALESID = SRRMA.SALESID AND SRRMA.SALESID NOT LIKE '%SO%'
LEFT JOIN SourceAxapta.vASU_INSPECTIONREASONMASTER IRM WITH (NOLOCK)
ON SRRMA.INSPECTIONREASONSERIALNUMBER = IRM.SERIALNUMBER
LEFT JOIN Dim.Date AD WITH (NOLOCK)
ON C.ApprovedDateID = AD.ID
LEFT JOIN Dim.Date SD WITH (NOLOCK)
ON C.ShipDateID = SD.ID
LEFT JOIN Dim.Date LRD WITH (NOLOCK)
ON S.ShipDateID = LRD.ID
--LEFT JOIN

left Join fact.shipment FS WITH (NOLOCK)
on FS.SalesID < S.SalesId AND FS.SalesID > S1.SalesID
LEFT JOIN Dim.Handset H2
ON FS.ShippedItemID = H2.HandsetID
AND H2.ItemGroupID = 'Phone'
------
WHERE S.SalesID > S1.SalesID
AND SL.SALESID NOT LIKE '%SO%'
AND SL1.SALESID NOT LIKE '%SO%'
AND ST.SALESID NOT LIKE '%SO%'
and (S1.ShippedItemID = S.PrevShipmentItemID)
AND H.ItemGroupID = 'Phone'
AND H1.ItemGroupID = 'Phone'
AND S.ShipEventID IN (1,2)
AND S1.ShipEventID IN (1,2)
AND S.ShippedQty = 1
AND S1.ShippedQty = 1
--AND NOT EXISTS
-- (SELECT 1 FROM fact.shipment FS WITH (NOLOCK)
-- INNER JOIN Dim.Handset H2
-- ON FS.ShippedItemID = H2.HandsetID
-- WHERE H2.ItemGroupID = 'Phone' AND FS.SalesID < S.SalesId AND FS.SalesID > S1.SalesID
--)
AND O.parent_order_id > 0
AND FS.ShippedItemID IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:34:21

the answer is simple
we cant tell unless we've some idea of how data exists in tables and how they're related. as told before please post some sample data and then explain what you're trying to achieve

see

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources