Help improve this 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help improve this query

Author

Topic

fchachar
Starting Member

15 Posts

Posted - 2012-11-02 : 10:21:15

select *
from Table1
LEFT JOIN Table2 ON Table1.visit_id=Table2._fk_visit
WHERE Table2.date >= '10/01/2012'
AND Table2._pk IN (SELECT MAX(Table2._pk) FROM Table2 GROUP BY Table2._fk_visit)

I am currently using the above query but it takes forever to show me the results, am I doing anything wrong?. I just want to get the last result from Table2 and group by Table2._fk_visit.

Is there any other better way of doing this please suggest.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 10:27:37

Two things:

First, you may want to look at the query plan (press control-m before you run the query) and see what takes a long time. Do you have any indexes on visit_id or _fk_visit columns?

Second, try this query and see if it performs any better and whether it gives you the columns you want.

SELECT
	t1.*, t2.t2pk
FROM
	Table1 t1
	OUTER APPLY
	(
		SELECT MAX(t2._pk) AS t2pk
		FROM Table2 t2
		WHERE t1.visit_id = t2._fk_visit
	) t2;

fchachar
Starting Member

15 Posts

Posted - 2012-11-02 : 10:35:59

Thanks Sunita that helps.

mfemenel
Professor Frink

1421 Posts

Posted - 2012-11-02 : 11:26:41

also ask yourself if you truly need to select t1.*. that's not a good practice.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources