Any way of making this more efficient?
-
Hi all,
I have the following query which is taking an age to run. I am yet to look at the state of the indexes but as far as the query itself is concerned, is there a better way to write it? Should the parts of the where clause be in a different order? Any other tips?
Many thanks.....
SELECT
dbo.tblPERSON.PER_FNAME,
dbo.tblPERSON.PER_SNAME,
dbo.tblPERSON.PER_DOB,
dbo.tblLU_PUP_NCYEAR.NCY_DESC,
dbo.tblPERSON.PER_ID,
dbo.tblPUPIL_ATTEND.PAT_TO_DATE
FROM
dbo.tblPERSON INNER JOIN
dbo.tblPUPIL ON dbo.tblPERSON.PER_ID = dbo.tblPUPIL.PER_ID INNER JOIN
dbo.tblLU_PUP_NCYEAR ON dbo.tblPUPIL.tblLU_PUP_NCYEAR = dbo.tblLU_PUP_NCYEAR.NCY_ID INNER JOIN
dbo.tblPUPIL_ATTEND ON dbo.tblPUPIL.PER_ID = dbo.tblPUPIL_ATTEND.tblPUPIL
WHERE
(dbo.tblPERSON.PER_TO_DATE IS NULL) AND
(dbo.tblPUPIL.PUP_TO_DATE IS NULL) AND
(dbo.tblPUPIL_ATTEND.PAT_TO_DATE > CONVERT(DATETIME, '2003-09-01 00:00:00', 102)) AND
(NOT (dbo.tblPERSON.PER_ID IN
(SELECT tblPUPIL
FROM tblPUPIL_ATTEND
WHERE PAT_TO_DATE IS NULL)))
AND
(NOT (dbo.tblPERSON.PER_ID IN
(SELECT tblPUPIL
FROM tblPUPIL_DISPLACED
WHERE DSP_TO_DATE IS NULL AND tblLU_PUP_DISPLACED = 6)))
-
One thing that you can do is change your NOT IN into EXISTS as such:
NOT EXISTS (SELECT 'X'
FROM tblPUPIL_ATTEND
WHERE tblPERSON.PER_ID = tblPUPIL AND
PAT_TO_DATE IS NULL)
NOT EXISTS (SELECT 'X'
FROM tblPUPIL_DISPLACED
WHERE tblPERSON.PER_ID = tblPUPIL AND
DSP_TO_DATE IS NULL AND tblLU_PUP_DISPLACED = 6)
-
What are the advantages of using NOT EXISTS over NOT IN?
-thanks, ron
-
not in (Select 1000 rows from ...) then check if the id is in there
not exists (Select 1 row and exit task as soon as the match is found) returns boolean
-
Ok, so how do I use not exists? Where do I put the not and where do I put the exists? I can't seem to get it to work.
-
Remi,
The ink wasn't even dry on that post!
Thanks,
ron
-
Try this one:
SELECT
dbo.tblPERSON.PER_FNAME,
dbo.tblPERSON.PER_SNAME,
dbo.tblPERSON.PER_DOB,
dbo.tblLU_PUP_NCYEAR.NCY_DESC,
dbo.tblPERSON.PER_ID,
dbo.tblPUPIL_ATTEND.PAT_TO_DATE
FROM
dbo.tblPERSON INNER JOIN
dbo.tblPUPIL ON dbo.tblPERSON.PER_ID = dbo.tblPUPIL.PER_ID INNER JOIN
dbo.tblLU_PUP_NCYEAR ON dbo.tblPUPIL.tblLU_PUP_NCYEAR = dbo.tblLU_PUP_NCYEAR.NCY_ID INNER JOIN
dbo.tblPUPIL_ATTEND ON dbo.tblPUPIL.PER_ID = dbo.tblPUPIL_ATTEND.tblPUPIL
WHERE
dbo.tblPERSON.PER_TO_DATE IS NULL AND
dbo.tblPUPIL.PUP_TO_DATE IS NULL AND
dbo.tblPUPIL_ATTEND.PAT_TO_DATE > CONVERT(DATETIME, '2003-09-01 00:00:00', 102) AND
NOT EXISTS (SELECT 'X'
FROM tblPUPIL_ATTEND
WHERE tblPERSON.PER_ID = tblPUPIL AND
PAT_TO_DATE IS NULL) AND
NOT EXISTS (SELECT 'X'
FROM tblPUPIL_DISPLACED
WHERE tblPERSON.PER_ID = tblPUPIL AND
DSP_TO_DATE IS NULL AND tblLU_PUP_DISPLACED = 6)
-
For IN and EXISTS I think this is a ggod explanation by Itzik Ben-Gan:
http://groups.google.de/groups?hl=de&lr=&selm=uFffAFPaBHA.1900%40tkmsftngp04
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Very informative article.
Thanks,
Ron
-
"NOT EXISTS" can also be replaces by "LEFT JOIN" and looking for a null in the "WHERE" clause. The disinctness of your data determinines which one is more optimal. More than this, though, you should look at your indexes and Primary Keys. Optimally, all columns referenced in your join should have indexes or Primary Keys associated with them. Also make sure the following are indexed:
tblPUPIL_DISPLACED.tblPUPIL
tblPUPIL_ATTEND.tblPUPIL
Also, extremely optimal for this query would be if dbo.tblPUPIL_ATTEND.PAT_TO_DATE was clustered, but "tblPUPIL" is probably already clustered, and it should probably stay that way (but I can't comment more without seeing more code).
Signature is NULL
-
One of the best things you can do is run an explain plan on the query. It will tell your where you need to tweek.
-
Calvin,
I realise this is a lot to ask but would you mind re-writing one of the "not in" clauses using a left join? I have been trying to do it myself but cannot get it to work. As it happens I cannot change the indexes etc on this database as it is a package but I'd love to be able to write good sql against it. I'm still learning when it comes to these things and I'm obviously doing something wrong when it comes to left joins. I'd be very grateful......
Paula
-
Posted something similar yesterday. See if this helps:
USE pubs
GO
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
SELECT DISTINCT authors.city
FROM authors
LEFT JOIN publishers
ON authors.city = publishers.city
WHERE publishers.city IS NULL
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank's the man...MVP! Sheet, that's cool!
And he's not even American.
.
.
.
.
Just kidding about the American part; that's probably to your favor, considering what prigs we can be.
Signature is NULL
-
And what the heck kind of attribute is a "tblpupil" -- are you reaching furniture instead of kids?
Joe,
With all the respect you deserve I think you are crossing the decency line here. That is no way to treat others specially if they come here for help I believe you should turn your volume a little bit down. And please HELP more and criticize less.
No authority grant you the right to disrespect others!
* Noel
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply