Query check

Hi, experts -
For performance, I want to find if any row exists (do not need to count all rows) then I go to another routine else I exit.

Is below select query still reading big table after finding 1st row ?

if exists (select 1 from bigTable with (nolock)
where aDate = @aDate and type = @type)
-- another routine

I think query stops after finding 1st row having if exists clause, but wanted to confirm.
If anyone thinks select is counting all rows - can you explain how and give me a tip, thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yes, the query is only finding the first row that matches your conditions.  After finding 1 row it stops looking through bigTable.  
ivan_belalAuthor Commented:
thanks...when lots of inserts happen this query runs hours to find 1 row where off peak hours on the same minute; how can perf be improved in peak hours ?
your assumption is correct. as soon as the exists statement finds 1 match it jumps out of execution.
this can be verified by running "SET STATISTICS IO ON" and looking at the reads while you execute your query
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

adding a with nolock to the select should improve your performance during peak hours. it sounds like a blocking issue
ivan_belalAuthor Commented:
thanks...when lots of inserts happen this query runs hours to find 1 row where off peak hours on the same minute; how can perf be improved in peak hours ?
ivan_belalAuthor Commented:
I already have nolock hint in the query; anything else pls
i would say the next thing would be to start diving into the routine inside of the if statement. unless if you have a trace that proves specifically that it is the if statement. also make sure you have an index helping you on this type of query because a table scan on a large table could perform badly because of any number of reasons.: filling tempdb, IO locks, page life expectancy, etc.
ivan_belalAuthor Commented:
Both columns used in where indexed; I know sql evaluate where clause first. will where cluase gather all rows then selelct only reads 1st one or anyhow even where can stop after finding 1st row ?
Scott PletcherSenior DBACommented:
Verify that the index(es) can be used to satisfy the query.  Check the query plan and see if the index(es) are being used.

Basically the columns in the WHERE need to be the first column in the index, or both columns in the same index with the first column one of the two.  Other indexes might be used, but it's much more uncertain if SQL could use them.

That is, the index(es) that best match this query are:

aDate, type [, ...otherColumn(s)...] --by itself
type, aDate [, ...otherColumn(s)...] --by itself

If not that, then:

--BOTH of these must be present
aDate [, ...otherColumn(s)...] AND
type,  [, ...otherColumn(s)...]

Something like these:

otherCol1, aDate, ...
otherCol2, type, ...

are much more "iffy".  SQL in theory might be able to use those indexes to satisfy the query, but the only way to be sure is to see the query plan and verify it!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ivan_belalAuthor Commented:
Whole query has 3 columns used; query is used just to check presence of a row with if exists()
2 cols in where and first 1 col from where in select clause; so covered query; one index gets used.
Execution plan has 29% on index seek and 71% key lookup. As said same query run in less than a minute off-peak hrs where in peak hours ( when lots of inserts in bigtable) takes hours.
Scott PletcherSenior DBACommented:
Hmm, suprised SQL would need a "key lookup" for an "EXISTS".

Must be the "-- another routine".

If that code is UPDATEing or doing some other type of modification, it might have to wait for already running INSERTs and/or other running modifications to finish.
ivan_belalAuthor Commented:
if a row found only then -- another routine which is a delete executed. 99% of the times, it skips another routine since row will not be found. But I am required to do a check. I was wondering on key lookup myself !
Anthony PerkinsCommented:
This may be a simple case of parameter sniffing...
Scott PletcherSenior DBACommented:
The DELETE might well have to wait for INSERTs, depending on the specific situation.  Maybe when  you hit that first DELETE, that statement ends up pausing, and that is causing your massive delays?  That should be relatively easy to confirm or disprove.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.