Hello I have this query
SELECT
ISNULL(sta5.AttributeValue, rdd5.Value) as 'Manufacturer Code',
(
SELECT TOP 1 ID2.Content FROM ItemMaster IM
join ItemMasterDetail ID1 on IM.Id = ID1.ItemMasterId and ID1.ItemMasterAttributeId = 14
join ItemMasterDetail ID2 on IM.Id = ID2.ItemMasterId and ID2.ItemMasterAttributeId = 15
WHERE ID1.content = ISNULL(sta5.AttributeValue, rdd5.Value) and ID1.Sequence = ID2.Sequence
order by ID2.[Sequence] desc
) as 'Manufacturer Description',
CONVERT(Varchar, (dbo.udfConvertTimeToCST(sk.CreationDate, default)), 109) as 'SKID Creation Date',
(
SELECT TOP 1 CONVERT(Varchar, (dbo.udfConvertTimeToCST(I.CreationTime, default)), 109) FROM ImportSignal I WHERE I.ObjectID = sk.ID order by I.CreationTime desc
) as 'Last Camera Scan Date',
(
SELECT TOP 1 CONVERT(Varchar, (dbo.udfConvertTimeToCST(es.ExportTime, default)), 109) FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4 or es.SignalStatusID = 5) order by es.CreationTime desc
) as 'Baan Signal Sent',
(
SELECT TOP 1 CONVERT(Varchar, (dbo.udfConvertTimeToCST(es.AcknowledgeTime, default)), 109) FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4 or es.SignalStatusID = 5) and es.AcknowledgeTime is not null order by es.CreationTime desc
) as 'Baan Resp Date',
(
SELECT TOP 1 es1.response.value('flxint[1]/app[1]/data[1]/rec[1]/inbound_data[1]/recno[1]', 'varchar(400)') FROM ExportSignal es1
WHERE es1.ObjectID = sk.ID and (es1.SignalStatusID = 4) and es1.SignalTypeId = 15 order by es1.CreationTime desc) as 'Baan Receipt',
(
SELECT TOP 1 es.response.value('flxint[1]/app[1]/data[1]/rec[1]/errors[1]/error[1]/msg[1]', 'varchar(400)') FROM ExportSignal es WHERE (es.SignalStatusID = 5 OR es.SignalStatusID = 6)
and es.objectid = sk.ID order by es.CreationTime desc
) as 'Error Message',
(
SELECT TOP 1
CASE es.response.value('flxint[1]/app[1]/data[1]/rec[1]/errors[1]/error[1]/add_data[1]/order[1]/wh_act[1]', 'varchar(400)')
WHEN 'E' then 'Error'
END
FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 5) and es.SignalTypeId = 15 order by es.CreationTime desc
) as 'Baan Response NACK',
(
SELECT TOP 1
CASE es.response.value('flxint[1]/app[1]/data[1]/rec[1]/inbound_data[1]/order[1]/wh_act[1]', 'varchar(400)')
WHEN 'I' THEN 'Inspection'
WHEN 'GR' then 'DocktoStock'
END
FROM ExportSignal es
WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4) and es.SignalTypeId = 15 order by es.CreationTime desc
) as 'Baan Response ACK',
lo.[Name] as 'Tote/Location',
CASE
WHEN sk.StockKeepingStatusId = 5 THEN 'Assigned'
ELSE 'Unassigned'
END as 'Location Assignment',
ISNULL(sk.ManufacturerPartNumber, STA8.AttributeValue) as 'MPN (SKID Attr)',
rdd8.Value as 'MPN (Doc Attr)',
rd.rmadocumentnumber as 'Packing List (Doc Attr)',
ISNULL(sk.CountryOfOrigin, STA7.AttributeValue) as 'COO (SKID Attr)',
rdd7.Value as 'COO (DOC Attr)',
re.ReceiptNumber as 'Packman Receipt Number'
FROM
StockKeeping sk
left join SKA STA on STA.StockKeepingId = sk.Id and AttributeNumber = 'Carrier'
left join SKA STA1 on sk.Id = STA1.StockKeepingId and STA1.AttributeNumber = 'Paca'
left join SKA STA2 on sk.Id = STA2.StockKeepingId and STA2.AttributeNumber = 'Wb'
left join SKA STA3 on sk.Id = STA3.StockKeepingId and STA3.AttributeNumber = 'DateCode'
left join SKA STA4 on sk.Id = STA4.StockKeepingId and STA4.AttributeNumber = 'LoteCode'
left join SKA STA5 on sk.Id = STA5.StockKeepingId and STA5.AttributeNumber = 'Manufacturer Code'
left join SKA STA6 on sk.Id = STA6.StockKeepingId and STA6.AttributeNumber = 'MSL'
left join SKA STA7 on sk.Id = STA7.StockKeepingId and STA7.AttributeNumber = 'Coo'
left join SKA STA8 on sk.Id = STA8.StockKeepingId and STA8.AttributeNumber = 'MPN'
left join SkidReceiptLine srl on sk.Id = srl.StockKeepingId
left join StockKeepingStatusHistory STH on STH.stockkeepingid=SK.id
left join [User] U on U.id=STH.UserId
left join ReceiptLine rl on srl.ReceiptLineId = rl.Id
left join Receipt re on rl.ReceiptId = re.Id
left join RMADocumentReceiptLine rdrl on rdrl.ReceiptLineId = rl.Id
left join RmaDocument rd on rdrl.RMADocumentId = rd.id
left join RMD rdd1 on rd.Id = rdd1.RmaDocumentId and rdd1.RmaDocumentAttributeId = '2'
left join RMD rdd2 on rd.Id = rdd2.RmaDocumentId and rdd2.RmaDocumentAttributeId = '11'
left join RMD rdd3 on rd.Id = rdd3.RmaDocumentId and rdd3.RmaDocumentAttributeId = '4'
left join RMD rdd4 on rd.Id = rdd4.RmaDocumentId and rdd4.RmaDocumentAttributeId = '5'
left join RMD rdd5 on rd.Id = rdd5.RmaDocumentId and rdd5.RmaDocumentAttributeId = '6'
left join RMD rdd6 on rd.Id = rdd6.RmaDocumentId and rdd6.RmaDocumentAttributeId = '8'
left join RMD rdd7 on rd.Id = rdd7.RmaDocumentId and rdd7.RmaDocumentAttributeId = '3'
left join RMD rdd8 on rd.Id = rdd8.RmaDocumentId and rdd8.RmaDocumentAttributeId = '7'
left join RML l on rl.RMALineId = l.Id
left join ItemMaster im on l.ItemMasterId = im.Id
left join RMA r on l.RmaId = r.Id
left join LocationLine ll on sk.Id = ll.ObjectId
left join [Location] lo on ll.LocationId = lo.Id
WHERE
(convert(varchar, dateadd(hour, -6, sk.CreationDate),0) between @StartDate and @EndDate) and sk.StockKeepingStatusId != 6
wich is joining the same table RMD and SKA so many times and is taking to much time when is showing a wide range of data. I read about using indexes on a join. How can i improve this query using indexes?
November 4, 2020 at 3:38 pm
Does SKA have an index on StockKeepingId and AttributeNumber ?
Does RMD have an index on RmaDocumentId and RmaDocumentAttributeId ?
Have you looked at an execution plan ?
November 4, 2020 at 4:58 pm
I read about using indexes on a join. How can i improve this query using indexes?
Indexes are probably the 3rd or 4th thing to change. The query needs to be a lot more efficient.
For starters, it appears you only need to join the tables "SKA" and "RMD" once.
The WHERE clause can be re-written from this:
(CONVERT(VARCHAR, DATEADD(hour, -6, sk.CreationDate), 0) BETWEEN @StartDate AND @EndDate)
To
sk.CreationDate BETWEEN @StartDate AND @EndDate
Do the manipulations on the variables, not the column.
Why all of the inline selects? Again, it appears that these can be changed to a subquery against the tables once, instead of many times.
Instead of using TOP 1 and ordering by the date, can you try MAX(datecolumn) and grouping?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 4, 2020 at 7:40 pm
I would look here too
(convert(varchar, dateadd(hour, -6, sk.CreationDate),0) between @StartDate and @EndDate)
why converting dates to varchar? This will not use an index. I see @StartDate and @EndDate, but don't know what data types they are. Looks like you are checking for 6 hours before the creation date to be between Start and End. If these parameters are datetime, then add the 6 hours to the parameters and not the column. See second instance below
declare @Cdate datetime = '11/4/2020 20:00:00.000',
@StartDate datetime = '11/4/2020 10:00:00.000',
@EndDate datetime = '11/4/2020 15:00:00.000'
select Case when dateadd(hour, -6, @Cdate) between @StartDate and @EndDate
then 'Select between'
else
'Not Between'
end
,
Case when @Cdate between dateadd(hour, 6, @StartDate) and dateadd(hour, 6,@EndDate)
then 'Select between'
else
'Not Between'
end
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 4, 2020 at 8:26 pm
Does SKA have an index on StockKeepingId and AttributeNumber ? yes Index for this table is StockKeepingId
Does RMD have an index on RmaDocumentId and RmaDocumentAttributeId ? also Index for this table is RmaDocumentId
Have you looked at an execution plan ? yes but maybe i don't know how to use this information.
Can i create an index plus de index already there in the table to quick find data?
November 4, 2020 at 9:28 pm
I don't have time to attempt to consolidate the ExportSignal lookups right now.
SELECT
ISNULL(STA.STA_Manufacturer_Code, RDD.rdd_5) as 'Manufacturer Code',
(
SELECT TOP (1) ID2.Content FROM ItemMaster IM
join ItemMasterDetail ID1 on IM.Id = ID1.ItemMasterId and ID1.ItemMasterAttributeId = 14
join ItemMasterDetail ID2 on IM.Id = ID2.ItemMasterId and ID2.ItemMasterAttributeId = 15
WHERE ID1.content = ISNULL(STA.STA_Manufacturer_Code, RDD.rdd_5) and ID1.Sequence = ID2.Sequence
order by ID2.[Sequence] desc
) as 'Manufacturer Description',
CONVERT(Varchar, (dbo.udfConvertTimeToCST(sk.CreationDate, default)), 109) as 'SKID Creation Date',
(
SELECT TOP (1) CONVERT(Varchar, (dbo.udfConvertTimeToCST(I.CreationTime, default)), 109) FROM ImportSignal I WHERE I.ObjectID = sk.ID order by I.CreationTime desc
) as 'Last Camera Scan Date',
(
SELECT TOP (1) CONVERT(Varchar, (dbo.udfConvertTimeToCST(es.ExportTime, default)), 109) FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4 or es.SignalStatusID = 5) order by es.CreationTime desc
) as 'Baan Signal Sent',
(
SELECT TOP (1) CONVERT(Varchar, (dbo.udfConvertTimeToCST(es.AcknowledgeTime, default)), 109) FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4 or es.SignalStatusID = 5) and es.AcknowledgeTime is not null order by es.CreationTime desc
) as 'Baan Resp Date',
(
SELECT TOP (1) es1.response.value('flxint[1]/app[1]/data[1]/rec[1]/inbound_data[1]/recno[1]', 'varchar(400)') FROM ExportSignal es1
WHERE es1.ObjectID = sk.ID and (es1.SignalStatusID = 4) and es1.SignalTypeId = 15 order by es1.CreationTime desc) as 'Baan Receipt',
(
SELECT TOP (1) es.response.value('flxint[1]/app[1]/data[1]/rec[1]/errors[1]/error[1]/msg[1]', 'varchar(400)') FROM ExportSignal es WHERE (es.SignalStatusID = 5 OR es.SignalStatusID = 6)
and es.objectid = sk.ID order by es.CreationTime desc
) as 'Error Message',
(
SELECT TOP (1)
CASE es.response.value('flxint[1]/app[1]/data[1]/rec[1]/errors[1]/error[1]/add_data[1]/order[1]/wh_act[1]', 'varchar(400)')
WHEN 'E' then 'Error'
END
FROM ExportSignal es WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 5) and es.SignalTypeId = 15 order by es.CreationTime desc
) as 'Baan Response NACK',
(
SELECT TOP (1)
CASE es.response.value('flxint[1]/app[1]/data[1]/rec[1]/inbound_data[1]/order[1]/wh_act[1]', 'varchar(400)')
WHEN 'I' THEN 'Inspection'
WHEN 'GR' then 'DocktoStock'
END
FROM ExportSignal es
WHERE es.ObjectID = sk.ID and (es.SignalStatusID = 4) and es.SignalTypeId = 15 order by es.CreationTime desc
) as 'Baan Response ACK',
lo.[Name] as 'Tote/Location',
CASE
WHEN sk.StockKeepingStatusId = 5 THEN 'Assigned'
ELSE 'Unassigned'
END as 'Location Assignment',
ISNULL(sk.ManufacturerPartNumber, STA.STA_MPN) as 'MPN (SKID Attr)',
rdd8.Value as 'MPN (Doc Attr)',
rd.rmadocumentnumber as 'Packing List (Doc Attr)',
ISNULL(sk.CountryOfOrigin, STA.STA_Coo) as 'COO (SKID Attr)',
rdd7.Value as 'COO (DOC Attr)',
re.ReceiptNumber as 'Packman Receipt Number'
FROM
StockKeeping sk
LEFT OUTER JOIN (
SELECT StockKeepingId,
MAX(CASE WHEN AttributeNumber = 'Carrier' THEN AttributeValue END) AS STA_Carrier,
MAX(CASE WHEN AttributeNumber = 'Coo' THEN AttributeValue END) AS STA_Coo,
MAX(CASE WHEN AttributeNumber = 'DateCode' THEN AttributeValue END) AS STA_DateCode,
MAX(CASE WHEN AttributeNumber = 'LoteCode' THEN AttributeValue END) AS STA_LoteCode,
MAX(CASE WHEN AttributeNumber = 'Manufacturer Code' THEN AttributeValue END) AS STA_Manufacturer_Code,
MAX(CASE WHEN AttributeNumber = 'MPN' THEN AttributeValue END) AS STA_MPN,
MAX(CASE WHEN AttributeNumber = 'MSL' THEN AttributeValue END) AS STA_MSL,
MAX(CASE WHEN AttributeNumber = 'Paca' THEN AttributeValue END) AS STA_Paca,
MAX(CASE WHEN AttributeNumber = 'Wb' THEN AttributeValue END) AS STA_Wb
FROM SKA
GROUP BY StockKeepingId
) AS STA ON STA.StockKeepingId = sk.Id
left join SkidReceiptLine srl on sk.Id = srl.StockKeepingId
left join StockKeepingStatusHistory STH on STH.stockkeepingid=SK.id
left join [User] U on U.id=STH.UserId
left join ReceiptLine rl on srl.ReceiptLineId = rl.Id
left join Receipt re on rl.ReceiptId = re.Id
left join RMADocumentReceiptLine rdrl on rdrl.ReceiptLineId = rl.Id
left join RmaDocument rd on rdrl.RMADocumentId = rd.id
LEFT OUTER JOIN (
SELECT RmaDocumentId,
MAX(CASE WHEN RmaDocumentAttributeId = '2' THEN Value END) AS rdd_2,
MAX(CASE WHEN RmaDocumentAttributeId = '3' THEN Value END) AS rdd_3,
MAX(CASE WHEN RmaDocumentAttributeId = '4' THEN Value END) AS rdd_4,
MAX(CASE WHEN RmaDocumentAttributeId = '5' THEN Value END) AS rdd_5,
MAX(CASE WHEN RmaDocumentAttributeId = '6' THEN Value END) AS rdd_6,
MAX(CASE WHEN RmaDocumentAttributeId = '7' THEN Value END) AS rdd_7,
MAX(CASE WHEN RmaDocumentAttributeId = '8' THEN Value END) AS rdd_8,
MAX(CASE WHEN RmaDocumentAttributeId = '11' THEN Value END) AS rdd_11
FROM RMD
GROUP BY RmaDocumentId
) AS rdd ON rdd.RmaDocumentId = rd.id
left join RML l on rl.RMALineId = l.Id
left join ItemMaster im on l.ItemMasterId = im.Id
left join RMA r on l.RmaId = r.Id
left join LocationLine ll on sk.Id = ll.ObjectId
left join [Location] lo on ll.LocationId = lo.Id
WHERE
sk.CreationDate between dateadd(hour, 6, @StartDate) and dateadd(hour, 6, @EndDate) AND
sk.StockKeepingStatusId != 6
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Hello Thank you so much for this. I changed already the code with the changes you gave me and query is much much faster now..thank you so much
November 4, 2020 at 10:58 pm
I also follow your advice changing the format of the date in the variable instead of on where. Query performance is better now! Thank you thank you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply