Script

This script of mine is taking heck of a long time to execute, can you tell me if anything is wrong with this.

Thank you very much..

CREATE TABLE #Tab01
(
RequestId Numeric(10)
)

CREATE TABLE #Tab02
(
RequestId Numeric(10)
)

INSERT INTO #Tab01
SELECT SL.RequestId FROM SabreLog SL WITH (NOLOCK)
WHERE WebServiceId = 12 AND SL.Response NOT LIKE '%<Error ErrorCode=%'

INSERT INTO #Tab02
SELECT T1.RequestId FROM #Tab01 T1
INNER JOIN SabreLog SL WITH (NOLOCK)
ON T1.RequestId= SL.RequestId
AND SL.Response LIKE '%<Error ErrorCode=%'

SELECT * FROM #Tab02
DROP TABLE #Tab01
DROP TABLE #Tab02

January 15th, 2014 6:23am

Without some DDL I'm making some fairly outrageous guesses here. But my best guess is the culprit is your NOT LIKE operation. The LIKE operator scans a whole table row by row, regardless of indexes, and "NOT" is always a huge factor in bloating query performance. I'd consider trying to split the query down into batches to swerve using the NOT operator. Better yet, enabling Full Text Indexing would improve performance massively (potentially depending on your DDL).
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2014 6:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics