asked on

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)
begin
-- another routine
end

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

tim_cs

Yes, the query is only finding the first row that matches your conditions. After finding 1 row it stops looking through bigTable.

ivan_belal

ASKER

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 ?

LCSandman8301

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

LCSandman8301

adding a with nolock to the select should improve your performance during peak hours. it sounds like a blocking issue

ivan_belal

ASKER

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_belal

ASKER

I already have nolock hint in the query; anything else pls

LCSandman8301

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_belal

ASKER

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 ?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ivan_belal

ASKER

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 Pletcher

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_belal

ASKER

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 Perkins

This may be a simple case of parameter sniffing...

Scott Pletcher

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.