Question about Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question about Query

Author

Topic

so3arbelnox
Starting Member

2 Posts

Posted - 2009-05-22 : 08:51:07

So I'm a bit new to SQL in general so I apologize in advance. Basically I'm writing a statement that is going to return records where the LocName and ParName are the same (just two columns in my table) but only the most recent ones (has the most recent "EffectiveDate"), so basically there would be no duplicates of these records. My statement works but it is incredibly slow when doing records over 1000. Here is my sql.

SELECT EffectiveDate, '{1}' AS 'FacName', LocName, t1.ParName, Units, Detail FROM ""{0}PreLimit"" AS t1, ""{0}Parameter"" AS t2 WHERE t1.ParName = t2.ParName AND LimitType = 1 AND NOT EXISTS(SELECT * FROM ""{0}PreLimit"" AS t WHERE t.LocName = t1.LocName AND t.ParName = t1.ParName AND t.EffectiveDate > t1.EffectiveDate

Why would this sql be so slow? Is it bad to use AND NOT EXISTS. How can I rewrite this to have the same behavior and be more efficient.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 09:33:06

I don't know if this will have much of an impact on performance, but you can try this:

SELECT EffectiveDate, '{1}' AS 'FacName', LocName, t1.ParName, Units, Detail 
FROM ""{0}PreLimit"" t1
JOIN ""{0}Parameter"" t2 ON t1.ParName = t2.ParName AND LimitType = 1
JOIN ""{0}PreLimit"" t ON t1.LocName = t.LocName AND t1.ParName = t1.ParName
WHERE NOT EXISTS (SELECT * FROM t WHERE t.EffectiveDate > t1.EffectiveDate)

Also make sure that you have the proper indexes on your tables.

Some days you're the dog, and some days you're the fire hydrant.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-22 : 09:58:06

As already mentioned, indexes are important.
Also, a derived table may be more efficient than what in effect is a triangular anti-semi-join.

SELECT EffectiveDate, '{1}' AS 'FacName', LocName, t1.ParName, Units, Detail
FROM ""{0}PreLimit"" AS t1
    JOIN ""{0}Parameter"" AS t2
        ON t1.ParName = t2.ParName
    JOIN
    (
        SELECT t.LocName, t.ParName, MAX(t.EffectiveDate)
        FROM ""{0}PreLimit"" AS t
        -- Should the following be included?
        -- WHERE LimitType = 1
        GROUP BY t.LocName, t.ParName
    ) D
        ON t1.LocName = D.LocName
            AND t1.ParName = D.ParName
            AND t1.EffectiveDate = D.EffectiveDate
WHERE LimitType = 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-22 : 12:35:26

A LEFT OUTER JOIN might be faster than a NOT EXISTS:

SELECT 
	EffectiveDate, '{1}' AS 'FacName', 
	LocName, 
	t1.ParName, 
	Units, 
	Detail 
FROM 
	""{0}PreLimit"" AS t1
JOIN 
	""{0}Parameter"" AS t2 
	ON t1.ParName = t2.ParName 
	AND LimitType = 1
LEFT JOIN 
	""{0}PreLimit"" AS t 
	ON t1.LocName = t.LocName 
	AND t1.ParName = t1.ParName
	AND t.EffectiveDate > t1.EffectiveDate
WHERE
	t.LocName IS NULL

so3arbelnox
Starting Member

2 Posts

Posted - 2009-05-24 : 19:49:29

"Also make sure that you have the proper indexes on your tables."

I'm not sure I understand what that means. Is there a link you could point me too to learn about them. I'm using Pervasive V9.
This is my first try at SQL. I literally have 0 experience with it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-25 : 10:15:47

you must be posting in some Pervasive SQL forums in future. this forum is specifically for MS SQL SErver.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources