temp table or nested select - 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

General SQL Server Forums

New to SQL Server Programming

temp table or nested select

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 03:05:18

hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.

SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate

SELECT *
FROM #ByItemID
ORDER BY CustomLotNo

DROP TABLE #ByItemID

----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-

select customlotno, itemid, locid(

select * from tblitemdetail
where customlotno='IN28606000'

) AS T
GROUP BY customlotno, itemid, locid

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 03:10:04

First case

SELECT		customlotno,
		itemid,
		ItemName,
		Ownership,
		TotalCTNInPlt,
		TotalCarton,
		sum(CartonPcs) AS CartonPcs,
		StorageID,
		StorageStatus,
		OriginUOM,
		PickQtyUOM,
		WhsID,
		WhsName,
		LocID,
		Zone,
		Expirydate,
		recvDate 
FROM		tblItemdetail 
GROUP BY	customlotno,
		itemid,
		ItemName,
		ownership,
		TotalCTNInPlt,
		TotalCarton,
		StorageID,
		StorageStatus,
		OriginUOM,
		PickQtyUOM,
		WhsID,
		WhsName,
		LocID,
		Zone,
		Expirydate,
		recvDate
ORDER BY	CustomLotNo

Second case

select distinct	customlotno,
		itemid,
		locid
where		customlotno = 'IN28606000'

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 03:37:57

huh?? no no noo.. and where is the temp table.. ?
i need to extract/digest records in tblitemdetail, and then perform some calsulation from the temp table.. actually the TotalCTNInPlt,
TotalCarton,
sum(CartonPcs) AS CartonPcs, are not originally from tblitemdetail

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 03:40:24

actually this is the real thing im working on.. it needs more than 3 mins to perform this SQl.. pliz help thanks...
-------------------------------------------------------------------------------------------------
BEGIN

SELECT DISTINCT customlotno, itemid, ItemName, Ownership, (SELECT clientName FROM tblClient WHERE clientID=ownership) AS ClientName, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate,

CASE WHEN (TotalCarton=0) THEN
sum(avaQty) * 1
ELSE sum(avaQty) * TotalCarton END AS TotalAVAQty

INTO #ByLocID

FROM (
SELECT d.cartonid, d.customlotno, d.itemid, d.whsID, d.LocID, substring(d.locID, 1, 1) AS Zone, d.OriginUOM, sum(d.PickQty) AS PickQty,-- d.PickQTyUOM,
sum(d.HoldQty) AS HoldQty, d.HoldUOM, sum(d.QCQty) AS QCQty, d.QCUOM, sum(ISNULL(d.OriginQty, 0))-sum(ISNULL(d.PickQty, 0))-sum(ISNULL(d.HoldQty, 0))-sum(ISNULL(d.QCQty, 0)) AS AVAQty,
d.PickQtyUOM, d.storageid, d.Recvdate, d.ExpiryDate, d.storageStatus, d.PackUOM, d.ownership, (Select ClientName from tblClient where ClientID=d.ownership) AS OwnerName,
d.ConsignorID, (Select ClientName from tblClient where ClientID=d.ConsignorID) AS ConsignorName, w.Whsname,

(SELECT ISNULL(count(cartonid), 0) FROM tblItemDetail WHERE storageid=d.storageid and itemid=d.itemid and customlotno=d.customlotno and ownership=d.ownership and consignorid=d.consignorid
GROUP BY customlotno, storageid, itemid, ownership, consignorid) As TotalCTNInPlt,

(SELECT ISNULL(count(cartonid), 0) FROM tblItemDetail WHERE originqty=d.originqty and storageid=d.storageid and itemid=d.itemid and customlotno=d.customlotno and ownership=d.ownership and consignorid=d.consignorid
GROUP BY customlotno, storageid, itemid, ownership, consignorid, originqty) As TotalCarton,

CASE WHEN (d.cartonid is null) THEN
sum(d.OriginQty)
ELSE d.OriginQty END AS CartonPcs,

sum(d.originQty) as sumOrigin, (SELECT i.ItemName FROM tblItem i WHERE i.ItemID=d.ItemID) AS ItemName

FROM tblItemDetail d

LEFT JOIN tblWarehouse w
ON w.WhsID=d.WhsID

WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom + '%' AND
d.LocID BETWEEN @LocFrom AND @LocTo AND
d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom

GROUP BY d.customlotno, d.storageid, d.itemid, d.whsID, d.LocID, d.Recvdate, d.ExpiryDate, d.storageStatus, d.OriginUOM,d.PickQtyUOM,
d.HoldUOM, d.QCUOM, d.locID, d.ownership, d.ConsignorID, w.Whsname, d.PackUOM, d.OriginQty, d.cartonid

)AS LocID
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate

SELECT *
FROM #ByLocID
ORDER BY CustomLotNo

DROP TABLE #ByLocID

END

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 04:52:24

No wonder. It is the subqueries that kill your performance.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 04:54:42

Also all these
LIKE '%' + @paramX + '%'

kills your performance since no existing indexes can be used.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 05:08:47

Also, what is this comparison?

AND SUBSTRING(d.LocID, 1, 1) LIKE '%' + @ZoneFrom

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 05:16:11

[code]SELECT DISTINCT CustomLotNo,
ItemID,
ItemName,
Ownership,
OwnerName AS ClientName,
TotalCTNInPlt,
TotalCarton,
SUM(CartonPcs) AS CartonPcs,
StorageID,
StorageStatus,
OriginUOM,
PickQtyUOM,
WhsID,
WhsName,
LocID,
Zone,
Expirydate,
RecvDate,
CASE
WHEN TotalCarton = 0 THEN SUM(avaQty)
ELSE SUM(avaQty) * TotalCarton
END AS TotalAVAQty
INTO #ByLocID
FROM (
SELECT d.CartonID,
d.CustomLotNo,
d.ItemID,
d.WhsID,
d.LocID,
SUBSTRING(d.LocID, 1, 1) AS Zone,
d.OriginUOM,
SUM(d.PickQty) AS PickQty,
SUM(d.HoldQty) AS HoldQty,
d.HoldUOM,
SUM(d.QCQty) AS QCQty,
d.QCUOM,
SUM(ISNULL(d.OriginQty, 0) - ISNULL(d.PickQty, 0) - ISNULL(d.HoldQty, 0) - ISNULL(d.QCQty, 0)) AS AVAQty,
d.PickQtyUOM,
d.StorageID,
d.RecvDate,
d.ExpiryDate,
d.StorageStatus,
d.PackUOM,
d.Ownership,
Owner.ClientName AS OwnerName,
d.ConsignorID,
Consignor.ClientName AS ConsignorName,
w.WhsName,
x1.TotalCTNInPlt,
x2.TotalCarton,
CASE
WHEN d.CartonID IS NULL THEN SUM(d.OriginQty)
ELSE d.OriginQty
END AS CartonPcs,
SUM(d.OriginQty) AS sumOrigin,
i.ItemName
FROM tblItemDetail AS d
LEFT JOIN tblItem AS i ON i.ItemID = d.ItemID
LEFT JOIN tblWarehouse AS w ON w.WhsID = d.WhsID
LEFT JOIN tblClient AS Owner ON Owner.ClientID = d.OwnerShip
LEFT JOIN tblClient AS Consignor ON Consignor.ClientID = d.ConsignorID
LEFT JOIN (

SELECT CustomLotNo,
StorageID,
ItemID,
Ownership,
ConsignorID,
COUNT(*) AS TotalCTNInPlt
FROM tblItemDetail
GROUP BY CustomLotNo,
StorageID,
ItemID,
Ownership,
ConsignorID
) AS x1 ON x1.StorageID = d.StorageID
AND x1.ItemID = d.ItemID
AND x1.CustomLotNo = d.CustomLotNo
AND x1.Ownership = d.Ownership
AND x1.ConsignorID = d.ConsignorID
LEFT JOIN (
SELECT CustomLotNo,
StorageID,
ItemID,
Ownership,
ConsignorID,
OriginQty,
COUNT(*) AS TotalCarton
FROM tblItemDetail
GROUP BY CustomLotNo,
StorageID,
ItemID,
Ownership,
ConsignorID,
OriginQty
) AS x2 ON x2.OriginQty = d.OriginQty
AND x2.StorageID = d.StorageID
AND x2.ItemID = d.ItemID
AND x2.CustomLotNo = d.CustomLotNo
AND x2.Ownership = d.Ownership
AND x2.ConsignorID = d.ConsignorID
WHERE d.Ownership LIKE '%' + @ClientID +'%'
AND d.WhsID LIKE '%' + @WhsFrom + '%'
AND d.CustomLotNo LIKE '%' + @CustomLotNoFrom + '%'
AND d.LocID BETWEEN @LocFrom AND @LocTo
AND d.ItemID LIKE '%'+ @ItemFrom + '%'
AND SUBSTRING(d.LocID, 1, 1) LIKE '%' + @ZoneFrom
GROUP BY d.CustomLotNo,
d.StorageID,
d.ItemID,
d.WhsID,
d.LocID,
d.RecvDate,
d.ExpiryDate,
d.StorageStatus,
d.OriginUOM,
d.PickQtyUOM,
d.HoldUOM,
d.QCUOM,
d.LocID,
d.Ownership,
d.ConsignorID,
w.WhsName,
d.PackUOM,
d.OriginQty,
d.CartonID
)AS LocID
GROUP BY CustomLotNo,
ItemID,
ItemName,
Ownership,
TotalCTNInPlt,
TotalCarton,
StorageID,
StorageStatus,
OriginUOM,
PickQtyUOM,
WhsID,
WhsName,
LocID,
Zone,
Expirydate,
RecvDate

SELECT *
FROM #ByLocID
ORDER BY CustomLotNo

DROP TABLE #ByLocID[/code]

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 19:44:41

ohh peso.. i havent try this solution yet.. but wanna thank u first,.. ohh it's my subquery.. ic...

1. AND SUBSTRING(d.LocID, 1, 1) LIKE '%' + @ZoneFrom
im trying to compare the first char of LocID wif param.. (the location id is like A1234, the zone is the 1st charactor.. any shorter/faster query?

2. Also all these
LIKE '%' + @paramX + '%'

kills your performance since no existing indexes can be used.

what is this means? does it means the LIKE statement is slow or what?

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 19:49:03

by reading the solution, are u trying to break the nested subqueries into 1-2 left join tblitemdetail.. ic.. let me try it.. can i just paste the thing :P/// thankss

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 20:10:20

peso,
im trying to fix this error found in the solution :-

Column ‘LocID.OwnerName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column ‘Owner.ClientName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column ‘Consignor.ClientName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column ‘x1.TotalCTNInPlt’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column ‘x2.TotalCarton’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column ‘i.ItemName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 20:26:18

hi peso,
ive run the solution.. it took 5:04mins for 4927 records.. i've no idea how to make it faster.. this seems the same as my old queries

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-30 : 21:50:32

can VIEW upgrade speed performance?? how to implement this using view?

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources