October 22, 2012 at 10:49 am
Hi all,
The SQL below *seems* to work as expected. However, it takes an awful long time to run. I would appreciate any advice how to improve this. Should I create temporary tables instead and join them?
SELECT r.serialno,
(CASE WHEN r.serialno in (SELECT rr.serialno
FROM readings rr
WHERE rr.uniqueid in (SELECT max(rrr.uniqueid)
FROM readings rrr
GROUP BY rrr.serialno) and
rr.vtype in ('F','G','S'))
THEN -1 ELSE 1 END) AS 'COUNT'
FROM readings r
INNER JOIN customer p
ON r.number = p.number
WHERE p.number <> 1 and
p.[private] = 'N' and
(r.vtype in ('A','I','J','R','F','G','S','B','E','V'))
GROUP BY r.serialno
ORDER BY r.serialno
October 22, 2012 at 12:06 pm
joseph.moffatt (10/22/2012)
Hi all,The SQL below *seems* to work as expected. However, it takes an awful long time to run. I would appreciate any advice how to improve this. Should I create temporary tables instead and join them?
SELECT r.serialno,(CASE WHEN r.serialno in (SELECT rr.serialno
FROM readings rr
WHERE rr.uniqueid in (SELECT max(rrr.uniqueid)
FROM readings rrr
GROUP BY rrr.serialno) and
rr.vtype in ('F','G','S'))
THEN -1 ELSE 1 END) AS 'COUNT'
FROM readings r
INNER JOIN customer p
ON r.number = p.number
WHERE p.number <> 1 and
p.[private] = 'N' and
(r.vtype in ('A','I','J','R','F','G','S','B','E','V'))
GROUP BY r.serialno
ORDER BY r.serialno
There just isn't enough information to offer much for a suggestion here. Please see the following article about how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D
One thing that jumps out right away is your where clause in the main query is NON-SARGable. Instead of p.number <> 1 you should instead use p.number < 1 or p.number > 1
I think your query can be great simplified but without any ddl and sample data it is pretty tough to offer much.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply