Limit large query to check existance

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 2000 Forums

SQL Server Development (2000)

Limit large query to check existance

Author

Topic

atomz4peace
Starting Member

7 Posts

Posted - 2006-05-29 : 22:26:16

Hi,

I have a query that currently joins 2 tables of 1000s of rows. But I am only looking to see if at least 1 match is found. I don't care how many, nor do I care what the data is. I only am looking for a match. I would think there is an easy way to do this but does anyone know it?

Here is my query now:

select count(*) from t2 a left join t1 on a.id = t1.id where t1.row2 = 4221

So this will do a nasty full table scan on t2. Is there a way to do something like a "limit" in mysql and just return either the first hit or "true" if I have at least 1 value? I just don't want to do a full scan if I find a hit right away.

Thanks!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-29 : 22:33:21

if exists(select * from t2 a left join t1 on a.id = t1.id where t1.row2)

--------------------
keeping it simple...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-29 : 22:41:04

This might work better if you are just checking for the existence of a match:


if exists (
	select
		top 1
		*
	from
		t2 a
		join
		t1 b
		on a.id = b.id
	where
		b.row2 = 4221
	)
	begin
	print 'match found'
	end

CODO ERGO SUM

atomz4peace
Starting Member

7 Posts

Posted - 2006-05-30 : 10:05:51

Let's hope the sql optimizer is smart enough to stop scanning after the first hit on a top 1.

Thanks a million!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-30 : 10:21:13

not sure but doesn't top retrieves the records specified after sorting?

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-30 : 10:23:52

I think top 1 * wont make any different when used with EXISTS
Set the execution plan and see

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-30 : 10:29:12

quote:
Originally posted by jen

not sure but doesn't top retrieves the records specified after sorting?

--------------------
keeping it simple...

That is true, but I don't have an order by.

Actually, the big change I made in my version was that I changed it to an inner join, instead of a left join. The left join is most likely what is causing the table scan.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources