February 18, 2015 at 3:26 am
HI,
When analyzing the performance problem of a server process, I found that logical reads are very high and in fact it increments gradually. Finally by using SQL Profiler we identified "if exists" statement , on which logical reads increments. The "if exists" is a part of sub procedure which is executed in cursor.
IF EXISTS ( select 'X'
from@Tablevariable A
whereCol1 not in
(
select Col1
fromView1 (nolock)
)
)
Any suggestion on why the reads increments for this statement in profiler?.
Is server actually processing in that way or it just the profiler summing the reads for specific processes?
Thanks in Advance
February 18, 2015 at 7:27 am
You have multiple issues here in my opinion.
1) You have a table variable in your query. Table variables have no statistics, so the optimizer assumes there is exactly 1 row of data. This often leads to horrible execution plans. Use a temporary table instead.
2) You're using NOLOCK. Do you understand what that means?
That said, try to optimize the query inside the EXISTS. If you need assistance with that, please post the actual execution plan and we'll try to help.
Another thing that I noticed is that sometimes the optimizer creates different plans for SELECT 1 WHERE EXISTS (...query...) compared to IF EXISTS (... query...). Sometimes decoupling the IF and the EXISTS assisgning to a variable produces better plans.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply