Limiting SQL results

I have a query that simply needs to determine if data exists in a table for
a given key.
Initially I used COUNT:
Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
The table has about 500,000 of rows so this takes too long.
Then I used TOP to speed things up. But TOP causes a timeout if key X isn't
in the table.
Does anyone have any suggestions on how to simply determine if key X has
records without counting every record?
Does anyone have a clue why TOP goes into never-never land if the table has
a lot of rows?
ThanksTry
if exists (Select 1 from TABLEWITHTONSODATA Where Key = 'X')
begin
...
end
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||Use EXISTS:
...
EXISTS
(SELECT *
FROM tablewithtonsodata
WHERE key = 'X')
If it's still too slow then consider indexing the column "key".
--
David Portas
--
Please reply only to the newsgroup
--|||You could do...
IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
SELECT 1
Or I believe this would perform equally well:
SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
COUNT(*) has to actually go and count all of the rows. TOP has to return
based on ordering of some sort. EXISTS just needs to find a single row that
matches.
I assume that Key is indexed?
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||All great suggestions...will try. I am still wondering if anyone else has
had problems with TOP in large tables.
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:eF6nK$sqDHA.2592@.TK2MSFTNGP10.phx.gbl...
> I have a query that simply needs to determine if data exists in a table
for
> a given key.
> Initially I used COUNT:
> Select COUNT(*) from TABLEWITHTONSODATA Where Key = 'X'
> The table has about 500,000 of rows so this takes too long.
> Then I used TOP to speed things up. But TOP causes a timeout if key X
isn't
> in the table.
> Does anyone have any suggestions on how to simply determine if key X has
> records without counting every record?
> Does anyone have a clue why TOP goes into never-never land if the table
has
> a lot of rows?
> Thanks
>|||In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> You could do...
> IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> SELECT 1
> Or I believe this would perform equally well:
> SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> COUNT(*) has to actually go and count all of the rows. TOP has to return
> based on ordering of some sort. EXISTS just needs to find a single row that
> matches.
Top doesn't have to have an order. I would think you would want to go
with:
IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
Key = 'X')
-- Do your thing
The NoLock should help depending on usage.|||"Brad" <me@.privacy.net> wrote in message
news:MPG.1a1f0ba92c81d36f98b995@.news...
> Top doesn't have to have an order. I would think you would want to go
> with:
An unordered TOP is meaningless. TOP 1 what?|||In article <OuROemvqDHA.3732@.tk2msftngp13.phx.gbl>, amachanic@.air-
worldwide.nospamallowed.com said...
> "Brad" <me@.privacy.net> wrote in message
> news:MPG.1a1f0ba92c81d36f98b995@.news...
> > Top doesn't have to have an order. I would think you would want to go
> > with:
> An unordered TOP is meaningless. TOP 1 what?
It doesn't matter in his case because he is just checking existence. Top
1 is perfect for that because it tells the server to find one record in
whatever order is easiest and then stop.
I use top 1 * or top 10 * all of the time to just get a sample record(s).|||Brad wrote:
> In article <ueHi#GtqDHA.1632@.TK2MSFTNGP10.phx.gbl>, amachanic@.air-
> worldwide.nospamallowed.com said...
> > You could do...
> >
> > IF EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> > SELECT 1
> >
> > Or I believe this would perform equally well:
> >
> > SELECT 1 WHERE EXISTS (SELECT * FROM TABLEWITHTONSODATA Where Key = 'X')
> >
> > COUNT(*) has to actually go and count all of the rows. TOP has to return
> > based on ordering of some sort. EXISTS just needs to find a single row that
> > matches.
> Top doesn't have to have an order. I would think you would want to go
> with:
> IF EXISTS (SELECT TOP 1 'X' FROM TableWithTonsOfData With (NoLock) Where
> Key = 'X')
> -- Do your thing
> The NoLock should help depending on usage.
Using EXISTS(SELECT TOP 1) will not be faster than EXIST(SELECT *), but
equally fast. This is because both can be satisfied with exactly the
same algorithm.
For that reason I would suggest you use the ANSI-SQL standard, and avoid
the SQL-Server proprietary TOP keyword.
Gert-Jan