Please help this slow and cumbersome query

Question

Please help this slow and cumbersome query

eyric101

Newbie

Points: 3
More actions
May 29, 2014 at 11:18 am

#307775

It takes more then 30 seconds to run
DECLARE @intTmpTblCnt INT,
@strTmpSql VARCHAR(max),
@comma BIT,
@colName VARCHAR(100),
@colAlias VARCHAR(100)
SET nocount ON
SELECT DISTINCT *
INTO #tmpresultstable
FROM (SELECT *
FROM preadmits WITH (nolock)
WHERE 1 = 1
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( ( CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014' )
OR ( CONVERT(DATE, admitdatexferdate) = Dateadd(day, 1,
CONVERT(DATE, Getdate
()))
AND Isnull(prioradmitdatexferdate, '') <> ''
AND CONVERT(DATE, prioradmitdatexferdate) =
CONVERT(DATE, Getdate())
) )
AND ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(assignedunit, '') = ''
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND recordtype = 'OR'
AND Isnull(removefromactiveworkqueue, '') = ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( recordtype = 'HAL'
OR recordtype = 'MEP' )
AND Isnull(removefromactiveworkqueue, '') = '') AS
SingleResultSet
SELECT DISTINCT id
INTO #tmppreservetable
FROM preadmits
WHERE id IN(SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON pa1.pa_pt_id = PA2.id
JOIN #tmpresultstable PA3
ON PA1.or_id = PA3.id
WHERE PA2.[status] LIKE '%Cancel%'
AND PA3.[status] LIKE '%Cancel%')
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT pa_pt_id
FROM #tmpresultstable
WHERE Isnull(pa_pt_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON PA1.or_id = PA2.id
WHERE ( PA1.recordtype = 'PA'
OR PA1.recordtype = 'PT' )
AND PA2.recordtype = 'OR'
AND PA1.[status] <> 'Admitted'
AND PA1.[status] <> 'Pending'
AND PA2.[status] = 'Pending')
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT or_id
FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE recordtype = 'OR'
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE removefromactiveworkqueue = 1
DELETE FROM #tmpresultstable
WHERE [service] = 'OBS'
SELECT @intTmpTblCnt = Count(*)
FROM #tmpresultstable
IF @intTmpTblCnt > 0
BEGIN
DECLARE colalias CURSOR FOR
SELECT Cast(exprop.objname AS VARCHAR),
Cast(exprop.value AS VARCHAR)
FROM Fn_listextendedproperty('Heading', 'user', 'dbo', 'table',
'Preadmits',
'column'
, DEFAULT) AS exprop
INNER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo',
'table',
'Preadmits',
'column',
DEFAULT) AS old
ON exprop.objname = old.objname
WHERE Cast(exprop.name AS VARCHAR) = 'Heading'
AND Cast(old.name AS VARCHAR) = 'Heading'
AND Isnull(Cast(exprop.value AS VARCHAR), '') <> ''
SET @strTmpSql = 'select '
SET @comma = 0
OPEN colalias
FETCH FROM colalias INTO @colName, @colAlias
WHILE @@fetch_status = 0
BEGIN
IF @comma = 1
SET @strTmpSql = @strTmpSql + ', '
SET @strTmpSql = @strTmpSql + Quotename(@colName) + ' as '
+ Quotename(@colAlias)
SET @comma = 1
FETCH next FROM colalias INTO @colName, @colAlias
END
CLOSE colalias
DEALLOCATE colalias
SET @strTmpSql = @strTmpSql + ' from #tmpResultsTable'
EXEC (@strTmpSql)
END

Viewing 5 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

eyric101 (5/29/2014)
It takes more then 30 seconds to run
DECLARE @intTmpTblCnt INT,
@strTmpSql VARCHAR(max),
@comma BIT,
@colName VARCHAR(100),
@colAlias VARCHAR(100)
SET nocount ON
SELECT DISTINCT *
INTO #tmpresultstable
FROM (SELECT *
FROM preadmits WITH (nolock)
WHERE 1 = 1
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( ( CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014' )
OR ( CONVERT(DATE, admitdatexferdate) = Dateadd(day, 1,
CONVERT(DATE, Getdate
()))
AND Isnull(prioradmitdatexferdate, '') <> ''
AND CONVERT(DATE, prioradmitdatexferdate) =
CONVERT(DATE, Getdate())
) )
AND ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(assignedunit, '') = ''
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND recordtype = 'OR'
AND Isnull(removefromactiveworkqueue, '') = ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( recordtype = 'HAL'
OR recordtype = 'MEP' )
AND Isnull(removefromactiveworkqueue, '') = '') AS
SingleResultSet
SELECT DISTINCT id
INTO #tmppreservetable
FROM preadmits
WHERE id IN(SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON pa1.pa_pt_id = PA2.id
JOIN #tmpresultstable PA3
ON PA1.or_id = PA3.id
WHERE PA2.[status] LIKE '%Cancel%'
AND PA3.[status] LIKE '%Cancel%')
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT pa_pt_id
FROM #tmpresultstable
WHERE Isnull(pa_pt_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON PA1.or_id = PA2.id
WHERE ( PA1.recordtype = 'PA'
OR PA1.recordtype = 'PT' )
AND PA2.recordtype = 'OR'
AND PA1.[status] <> 'Admitted'
AND PA1.[status] <> 'Pending'
AND PA2.[status] = 'Pending')
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT or_id
FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE recordtype = 'OR'
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE removefromactiveworkqueue = 1
DELETE FROM #tmpresultstable
WHERE [service] = 'OBS'
SELECT @intTmpTblCnt = Count(*)
FROM #tmpresultstable
IF @intTmpTblCnt > 0
BEGIN
DECLARE colalias CURSOR FOR
SELECT Cast(exprop.objname AS VARCHAR),
Cast(exprop.value AS VARCHAR)
FROM Fn_listextendedproperty('Heading', 'user', 'dbo', 'table',
'Preadmits',
'column'
, DEFAULT) AS exprop
INNER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo',
'table',
'Preadmits',
'column',
DEFAULT) AS old
ON exprop.objname = old.objname
WHERE Cast(exprop.name AS VARCHAR) = 'Heading'
AND Cast(old.name AS VARCHAR) = 'Heading'
AND Isnull(Cast(exprop.value AS VARCHAR), '') <> ''
SET @strTmpSql = 'select '
SET @comma = 0
OPEN colalias
FETCH FROM colalias INTO @colName, @colAlias
WHILE @@fetch_status = 0
BEGIN
IF @comma = 1
SET @strTmpSql = @strTmpSql + ', '
SET @strTmpSql = @strTmpSql + Quotename(@colName) + ' as '
+ Quotename(@colAlias)
SET @comma = 1
FETCH next FROM colalias INTO @colName, @colAlias
END
CLOSE colalias
DEALLOCATE colalias
SET @strTmpSql = @strTmpSql + ' from #tmpResultsTable'
EXEC (@strTmpSql)
END

I am not at all surprised this isn't fast. There are lots of performance bottlenecks in this.

First is the initial insert into the temp table. It has dozens of nonSARGable predicates. You continuously cast a column to a date. Why not change the column? Then you have leading wildcards in like. There are also several <> predicates. Each of these will produce a scan. We shouldn't forget about NOLOCK. Do you know what that hint does? Are you ok with missing and/or duplicate data?

Then we have a cursor...

If you really want some help with this we need to see some more details. Take a look at this article which explains what to post for performance problems.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 2

Not to mention that each delete will probably cause a full scan of the Temp Table because there are no indexes. It's far better to isolate what you want to put into the Temp Table instead of deleting what you don't want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

You need to do two things. Look into all the common code smells of T-SQL, like this:

Isnull(functionalunit, '') <> ''

That's a function on a column which will lead to scans and hence slow performance.

You also need to look at the execution plan of your queries. That will show you how the optimizer is resolving what you fed it. That can also lead to knowledge about how to make things run faster.

"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

eyric101 (5/29/2014)
It takes more then 30 seconds to run
DECLARE @intTmpTblCnt INT,
@strTmpSql VARCHAR(max),
@comma BIT,
@colName VARCHAR(100),
@colAlias VARCHAR(100)
SET nocount ON
SELECT DISTINCT *
INTO #tmpresultstable
FROM (SELECT *
FROM preadmits WITH (nolock)
WHERE 1 = 1
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( ( CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014' )
OR ( CONVERT(DATE, admitdatexferdate) = Dateadd(day, 1,
CONVERT(DATE, Getdate
()))
AND Isnull(prioradmitdatexferdate, '') <> ''
AND CONVERT(DATE, prioradmitdatexferdate) =
CONVERT(DATE, Getdate())
) )
AND ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(assignedunit, '') = ''
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND recordtype = 'OR'
AND Isnull(removefromactiveworkqueue, '') = ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
UNION ALL
SELECT *
FROM preadmits (nolock)
WHERE 1 = 1
AND CONVERT(DATE, admitdatexferdate) BETWEEN
'1/1/2011' AND '5/29/2014'
AND Isnull(functionalunit, '') <> ''
AND functionalunit IN ( 1, 10, 11, 12,
2, 3, 4, 5,
6, 7, 8, 9 )
AND ( recordtype = 'HAL'
OR recordtype = 'MEP' )
AND Isnull(removefromactiveworkqueue, '') = '') AS
SingleResultSet
SELECT DISTINCT id
INTO #tmppreservetable
FROM preadmits
WHERE id IN(SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON pa1.pa_pt_id = PA2.id
JOIN #tmpresultstable PA3
ON PA1.or_id = PA3.id
WHERE PA2.[status] LIKE '%Cancel%'
AND PA3.[status] LIKE '%Cancel%')
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT pa_pt_id
FROM #tmpresultstable
WHERE Isnull(pa_pt_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT pa1.id
FROM #tmpresultstable PA1
JOIN #tmpresultstable PA2
ON PA1.or_id = PA2.id
WHERE ( PA1.recordtype = 'PA'
OR PA1.recordtype = 'PT' )
AND PA2.recordtype = 'OR'
AND PA1.[status] <> 'Admitted'
AND PA1.[status] <> 'Pending'
AND PA2.[status] = 'Pending')
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id IN (SELECT or_id
FROM #tmpresultstable
WHERE ( recordtype = 'PA'
OR recordtype = 'PT' )
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE recordtype = 'OR'
AND id NOT IN(SELECT id
FROM #tmppreservetable)
AND id NOT IN (SELECT or_id
FROM #tmpresultstable
WHERE recordtype = 'OR'
AND Isnull(or_id, 0) <> 0)
DELETE FROM #tmpresultstable
WHERE removefromactiveworkqueue = 1
DELETE FROM #tmpresultstable
WHERE [service] = 'OBS'
SELECT @intTmpTblCnt = Count(*)
FROM #tmpresultstable
IF @intTmpTblCnt > 0
BEGIN
DECLARE colalias CURSOR FOR
SELECT Cast(exprop.objname AS VARCHAR),
Cast(exprop.value AS VARCHAR)
FROM Fn_listextendedproperty('Heading', 'user', 'dbo', 'table',
'Preadmits',
'column'
, DEFAULT) AS exprop
INNER JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo',
'table',
'Preadmits',
'column',
DEFAULT) AS old
ON exprop.objname = old.objname
WHERE Cast(exprop.name AS VARCHAR) = 'Heading'
AND Cast(old.name AS VARCHAR) = 'Heading'
AND Isnull(Cast(exprop.value AS VARCHAR), '') <> ''
SET @strTmpSql = 'select '
SET @comma = 0
OPEN colalias
FETCH FROM colalias INTO @colName, @colAlias
WHILE @@fetch_status = 0
BEGIN
IF @comma = 1
SET @strTmpSql = @strTmpSql + ', '
SET @strTmpSql = @strTmpSql + Quotename(@colName) + ' as '
+ Quotename(@colAlias)
SET @comma = 1
FETCH next FROM colalias INTO @colName, @colAlias
END
CLOSE colalias
DEALLOCATE colalias
SET @strTmpSql = @strTmpSql + ' from #tmpResultsTable'
EXEC (@strTmpSql)
END

Three extracts from table [preadmits], when one would do;

Complete ignorance of SARGability;

Lack of awareness of datatypes - Isnull(prioradmitdatexferdate, '') <> '';

Running the results of a query into a temp table for further processing - to cover inadequacies in the first query;

These are all "Absolute Beginner" errors and to correct them all with good explanations would take an entire article or better still a beginners/intermediate course in TSQL, because it's the developer of this code batch which requires some attention more than the code - or there will be much more like it.

Having said that, you're still looking for improvements to this query. Have a read of the article linked in my sig "please read this", it describes how to concoct and post up sample data. If you can provide a decent sample set of table [preadmits], it shouldn't take long for folks here to provide you with a significant improvement over your existing code.

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