Any way of making this more efficient?
-
Not that he needs my defense, but I don't see him as being disrespectful to Paula, he's being disrespectful of bad design. There is plenty of room in this forum for dissing on bad code. Some people are just a little more colorful than others.
The only thing he asks Paula is for the DDL (and he even says "Please"!), which is a very reasonable request. Many beginning SQL programmers don't realize that you can't write "good SQL" without knowing the DDL.
Indeed, if Joe read the post he'd seen that Paula is not responsible for the incredibly dumb table naming conventions, they are "part of a package", most likely supplied by some 3rd party vendor.
I don't think you're going to get Joe to tone it down; that's just the way he writes, forcefully and bluntly. If you've read this:
http://www.intelligententerprise.com/001205/celko1_1.jhtml?_requestid=177142
You'll see that this is #10 on "Ten Things I Hate About You"; "10) Posting bizarre personal pseudo-code instead of real DDL and DML statements. "
cl
Signature is NULL
-
During the past 5 (+-) years or so, one might be inclined to consider this an advantage.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Joe,
As stated in my first post, I just wanted some advice on the actual query itself, and how to write it more efficiently. I did not ask people to advise me on indexes, constraints etc. Also, as pointed out by others, I have absolutely no control over this package. It is a third party system and yes, I agree with you, it is very poor. I am not as stupid as you may think. I have not written this query, it was supplied to me by the vendor. I however cannot get it to run on my database and am trying to correct it myself.
I am grateful to the people on here who have given me useful advice. Perhaps next time you can make your (valid) suggestions with a less patronising tone.
-
Here is a potential option (barring I didn't typo or misread the original)
SELECT
PERSON.PER_FNAME,
PERSON.PER_SNAME,
PERSON.PER_DOB,
NCYEAR.NCY_DESC,
PERSON.PER_ID,
ATTEND.PAT_TO_DATE
FROM
dbo.tblPERSON PERSON
INNER JOIN
dbo.tblPUPIL PUPIL
INNER JOIN
dbo.tblLU_PUP_NCYEAR NCYEAR
ON
PUPIL.tblLU_PUP_NCYEAR = NCYEAR.NCY_ID
INNER JOIN
(
SELECT
A.tblPUPIL,
NullIf(MAX(IsNull(A.PAT_TO_DATE,'99991231')),'99991231') PAT_TO_DATE
FROM
dbo.tblPUPIL_ATTEND A
GROUP BY
A.tblPUPIL
HAVING
NullIf(MAX(IsNull(A.PAT_TO_DATE,'99991231')),'99991231') IS NOT NULL
) ATTEND
ON
PUPIL.PER_ID = ATTEND.tblPUPIL
ON
PERSON.PER_ID = PUPIL.PER_ID
LEFT JOIN
(
SELECT
B.tblPUPIL
FROM
dbo.tblPUPIL_DISPLACED B
WHERE
B.DSP_TO_DATE IS NULL AND
B.tblLU_PUP_DISPLACED = 6
) DISPLACED
ON
PERSON.PER_ID = DISPLACED.tblPUPIL
WHERE
PERSON.PER_TO_DATE IS NULL AND
PUPIL.PUP_TO_DATE IS NULL AND
(ATTEND.PAT_TO_DATE > CAST('2003-09-01 00:00:00' AS DATETIME) AND
B.tblPUPIL IS NULL
-
Thanks, I'll give this a try.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply