CASE statement slows down query drastically

Question

CASE statement slows down query drastically

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 1

You ever use DTA much? Here is what it output as index recommendations.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jackie Lowery - Wednesday, January 10, 2018 11:33 AM
You ever use DTA much? Here is what it output as index recommendations.

Nope. Its recommendations aren't always good,

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Lynn Pettis - Wednesday, January 10, 2018 12:13 PM
Jackie Lowery - Wednesday, January 10, 2018 11:33 AM
You ever use DTA much? Here is what it output as index recommendations.
Nope. Its recommendations aren't always good,

And are occasionally quite bad.

"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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4


-- Add column Docref to index ix_sent_01
-- in addition to eliminating the key lookup, this will refresh statistics, which look stale
-- Another new index for you
CREATE INDEX ix_Stuff ON Trade (TP_PartID) INCLUDE (TP_Name)
-- And a couple of query changes

SELECT DISTINCT
m.Sent_ID,
m.TP_PartID,
DocType,
ISNULL(DocRef,'')     AS 'DocRef',
DateSent,
DocStatus,
m.ICN,
m.GCN,
--ISNULL(err.DataKey, '') ErrorID,
CASE
WHEN ISNULL(t.TP_Name,'') = '' THEN ISNULL(p.TP_Name,'')
ELSE ISNULL(t.TP_Name,'')
END                  AS 'TP_Name',
m.TCN

FROM [sent] m

--LEFT JOIN ErrorLog err
-- ON CAST(m.Sent_ID AS nvarchar(50)) = err.DataKey
-- AND ISNULL(err.DataType, '') IN ('', 'sent')

LEFT JOIN EDIStdDocs std
ON m.DocType = std.doc_id

LEFT JOIN (
SELECT p.PartnerID, p.DGID, p.TP_PartID, t2.TP_Name,
  q = COUNT(*) OVER(PARTITION BY p.PartnerID, p.DGID)
FROM [Partner] p
LEFT JOIN Trade t2
  ON t2.TP_PartID = p.TP_PartID
) p
ON p.PartnerID = m.TP_PartID
AND p.DGID = std.DGID
AND p.q = 1

LEFT JOIN Trade t
ON t.TP_PartID = m.TP_PartID

WHERE m.DateSent >= '20171201' AND m.DateSent < '20171202'

ORDER BY m.Sent_ID DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 5

ChrisM,

We're going to move to the new version of our EDI software. The engineers I've been working with are telling me the indexes we've been looking at are already implemented in the newest version, so you were definitely taking me down the right path. Also, they've been testing your version of the query and i think they're going to implement it in the next update.

Thanks for all the help .