CASE statement slows down query drastically

Question

CASE statement slows down query drastically

Viewing 15 posts - 16 through 30 (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

ChrisM@Work - Your query works great now. Pretty sure i found the issue. I did have to change 'COUNT(TP_PartID)' to 'COUNT(p.TP_PartID)' to fix an ambiguous name error.
The main issue though, is the 'AND p.DGID = std.DGID' part b/c the DGID field in the std table is not indexed.
If i remove that part, the original query takes about 98 seconds. If I remove that part from your query, results are returned in 8 sec, so a massive improvement. Here is the final query:

SELECT DISTINCT m.Sent_ID, m.TP_PartID, DocType, ISNULL(DocRef,'') AS 'DocRef', DateSent, DocStatus, m.ICN, m.GCN, ISNULL(err.DataKey, '') ErrorID, [TP_Name] = ISNULL(x.[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

OUTER APPLY ( SELECT [TP_Name] FROM ( SELECT [TP_Name] = CASE WHEN t.TP_Name > '' THEN t.TP_Name ELSE t2.TP_Name END, q = COUNT(p.TP_PartID) OVER(PARTITION BY p.PartnerID, p.DGID) FROM [Partner] p LEFT JOIN Trade t ON t.TP_PartID = p.PartnerID LEFT JOIN Trade t2 ON t2.TP_PartID = p.TP_PartID WHERE p.PartnerID = m.TP_PartID --AND p.DGID = std.DGID ) d WHERE d.q = 1 ) x

WHERE DateSent >= '12/1/2017' AND DateSent <= '12/31/2017 23:59:59'

ORDER BY m.Sent_ID DESC

Thanks for all the help guys.

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 2

I guess i should ask what i can do to be able to add the 'AND p.DGID = std.DGID' back to the query. Do i need to add an index to the DGID column? Does it need to be a certain type?

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

Jackie Lowery - Tuesday, January 9, 2018 2:52 PM
I guess i should ask what i can do to be able to add the 'AND p.DGID = std.DGID' back to the query. Do i need to add an index to the DGID column? Does it need to be a certain type?

-- An index like this will support both the join and the aggregate (PARTITION BY).

-- Modify an existing index if possible

CREATE INDEX ix_Stuff ON [Partner] (PartnerID, DGID) INCLUDE (TP_PartID)

^{“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 4

I wouldn't need an index on the EDIStdDocs DGID column?

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

Jackie Lowery - Wednesday, January 10, 2018 6:49 AM
I wouldn't need an index on the EDIStdDocs DGID column?

Not necessarily. This new index I've suggested will radically change the execution plan. Try it, post up an actual plan and folks can assess for you whether or not further changes will bring more benefit.

^{“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 6

This is the only index/key on the table now. It seems to already contain the DGID column.

ALTER TABLE [dbo].[Partner] ADD CONSTRAINT [PK_Partner] PRIMARY KEY CLUSTERED ( [TP_PartID] ASC, [DGID] ASC, [PartnerQual] ASC, [PartnerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

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

Jackie Lowery - Wednesday, January 10, 2018 7:06 AM
This is the only index/key on the table now. It seems to already contain the DGID column.
ALTER TABLE [dbo].[Partner] ADD CONSTRAINT [PK_Partner] PRIMARY KEY CLUSTERED ( [TP_PartID] ASC, [DGID] ASC, [PartnerQual] ASC, [PartnerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Yes that's correct, but they're not in the right order for it to be useful for the query in the way that I anticipate.

CREATE INDEX ix_Stuff ON [Partner] (PartnerID, DGID) INCLUDE (TP_PartID)
I want only PartnerID, DGID as keys and preferably (but not necessarily) in that order. TP_PartID isn't necessary as a key. As the first key in the PK, it renders the PK unusable for the index seeks which this query needs.

^{“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 8

That index gives me a marked improvement. Query time goes from 1:07 to 0:29. But something still doesn't seem right b/c if i remove the 'AND p.DGID = std.DGID' part, the query only takes 3 sec.

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

Jackie Lowery - Wednesday, January 10, 2018 7:34 AM
That index gives me a marked improvement. Query time goes from 1:07 to 0:29. But something still doesn't seem right b/c if i remove the 'AND p.DGID = std.DGID' part, the query only takes 3 sec.

Can you attach an "actual" plan please Jackie?

^{“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 10

Took me a sec to figure out how to get that. lol. I'm definitely a SQL server novice. Thanks for all the help.

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

Jackie Lowery - Wednesday, January 10, 2018 7:41 AM
Took me a sec to figure out how to get that. lol. I'm definitely a SQL server novice. Thanks for all the help.

Thanks...

-- Create this index:

CREATE INDEX ix_doc_id ON EDIStdDocs (doc_id) INCLUDE (DGID)

-- Try this variant of your query

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(t2.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 PartnerID, DGID, TP_PartID,

q = COUNT(*) OVER(PARTITION BY PartnerID, DGID)

FROM [Partner]

) p

ON m.TP_PartID = p.PartnerID

AND std.DGID = p.DGID

AND q = 1

LEFT JOIN Trade t

ON t.TP_PartID = m.TP_PartID

LEFT JOIN Trade t2

ON t2.TP_PartID = p.TP_PartID

WHERE DateSent >= '12/1/2017' AND DateSent < '12/1/2017 23:59:59' -- is this Jan 12 or Dec 01?

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 12

Created the index and ran the query. Took 42 sec. The date is Dec 01.

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 13

Is that index dependent on the previous index you gave me? I've been removing them between tests.

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

Jackie Lowery - Wednesday, January 10, 2018 9:11 AM
Is that index dependent on the previous index you gave me? I've been removing them between tests.

Best to leave all new indexes in place whilst testing, but ensure they have memorable names. The query would work better with new indexes on all of the tables but you have to weigh the performance improvement against their maintenance cost.

^{“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 15

The query completes in 46 sec. with both indexes, which is weird b/c it completed in 42 sec with only the second index you gave me. Hmmm. Ive attached the actual execution plan with both indexes.