Help on a simply Query

Question

Post reply

Help on a simply Query

James Tech

Right there with Babe

Points: 762
More actions
August 18, 2010 at 1:39 pm

#228087

Hi Guys,
I have a table with 25M+ records, i tried doing query like as simple as this:
SELECT COUNT(id)
FROM MyTable
WHERE clientId = @clientId
If the @clientId supplied exists in MyTable table, the query will return the row count quickly like less than 2 seconds at least BUT if it doesn't it just keeps on reader until like 15minutes+ and have to stop it.
I just want to check if the client id exist on MyTable or not.
Any thought?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If you only need to know if a client id exists use EXISTS instead of COUNT(). See Gail's related blog[/url].

Side note: Is there any index on clientId to support the query?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

James Tech Right there with Babe Points: 762 More actions · Answer 2

LutzM (8/18/2010)
If you only need to know if a client id exists use EXISTS instead of COUNT(). See Gail's related blog[/url].
Side note: Is there any index on clientId to support the query?

Thank you for the response LutzM.

I tried EXISTS but it was the same. ClientId is a reference to Client table of some sort from MyTable.

OTF SSCertifiable Points: 6104 More actions · Answer 3

I think you might want to respond to Lutz's side note? Is the ClientId Column Indexed appropriately?

James Tech Right there with Babe Points: 762 More actions · Answer 4

I don't think the ClientId column is indexed, the only indexed column that I see is the ID of MyTable and this is already an existing design when I came in.

LutzM SSC Guru Points: 107049 More actions · Answer 5

If the ClientId column isn't indexed you will end up with a table scan causing bad performance. If you're allowed to, add the ClientId (nonclustered) index and see if it helps.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]