EXISTS Performance Problem

We recently migrated from 2000 to 2005 (I know...early adopters) and our
first day in production we ran into a bunch of performance issues.
We have some conditional checks in a stored procedure that return out
different values based on the existence of data in specific tables or views.
I have narrowed down the bottle neck to one of the views that we are
checking. The view itself has less than 10000 rows and if you query the view
directly, there is no performance problem.
We use IF EXISTS to check each of the tables and views and then return out
accordingly. The IF EXISTS that checks this last view takes over two minutes,
where the whole proc ran in under 10 seconds in 2000. Have changes been made
to EXISTS in 2005? And for general knowledge, how does EXISTS do its
processing, cursors?On Nov 15, 5:28 pm, Clark Kent <ClarkK...@.discussions.microsoft.com>
wrote:
> We recently migrated from 2000 to 2005 (I know...early adopters) and our
> first day in production we ran into a bunch of performance issues.
> We have some conditional checks in a stored procedure that return out
> different values based on the existence of data in specific tables or views.
> I have narrowed down the bottle neck to one of the views that we are
> checking. The view itself has less than 10000 rows and if you query the view
> directly, there is no performance problem.
> We use IF EXISTS to check each of the tables and views and then return out
> accordingly. The IF EXISTS that checks this last view takes over two minutes,
> where the whole proc ran in under 10 seconds in 2000. Have changes been made
> to EXISTS in 2005? And for general knowledge, how does EXISTS do its
> processing, cursors?
Hi Clark,
I'm pretty sure that EXISTS is a set based operation. Have you done
all the basics (e.g. updated statistics, checked the execution plan,
checked that all your indexes migrated correctly)? If these have been
done, I'm afraid you'll probably need to post your DDL to get a more
insightful answer...
Regards,
J|||Did you UPDATE STATISTICS after the conversion? That is absolutely
required to get good performance. Try that first.
Performance of EXISTS tests generally depends on the use - or not - of
suitable indexes. Nothing in particular changed in that regard with
2005, but there are certainly cases where the optimizer chooses a
poorer path in 2005 for a query that worked fine in 2000. Every
version of the optimizer has weak spots. Whatever version of SQL
Server you use when you create an application if you come across such
an optimizer problem you work around it. At this point, assuming that
updating the statistics did not fix the problem, I would simply work
on the query and table indexes to find a way to work around it.
Roy Harvey
Beacon Falls, CT
On Thu, 15 Nov 2007 09:28:01 -0800, Clark Kent
<ClarkKent@.discussions.microsoft.com> wrote:
>We recently migrated from 2000 to 2005 (I know...early adopters) and our
>first day in production we ran into a bunch of performance issues.
>We have some conditional checks in a stored procedure that return out
>different values based on the existence of data in specific tables or views.
>I have narrowed down the bottle neck to one of the views that we are
>checking. The view itself has less than 10000 rows and if you query the view
>directly, there is no performance problem.
>We use IF EXISTS to check each of the tables and views and then return out
>accordingly. The IF EXISTS that checks this last view takes over two minutes,
>where the whole proc ran in under 10 seconds in 2000. Have changes been made
>to EXISTS in 2005? And for general knowledge, how does EXISTS do its
>processing, cursors?|||I am pretty sure we have a nightly maintenance plan set up that updates
statistics and rebuilds indexes and the like. I will double check with our
DBA that those process came across in the conversion.
Thanks for the help.
"Roy Harvey (SQL Server MVP)" wrote:
> Did you UPDATE STATISTICS after the conversion? That is absolutely
> required to get good performance. Try that first.
> Performance of EXISTS tests generally depends on the use - or not - of
> suitable indexes. Nothing in particular changed in that regard with
> 2005, but there are certainly cases where the optimizer chooses a
> poorer path in 2005 for a query that worked fine in 2000. Every
> version of the optimizer has weak spots. Whatever version of SQL
> Server you use when you create an application if you come across such
> an optimizer problem you work around it. At this point, assuming that
> updating the statistics did not fix the problem, I would simply work
> on the query and table indexes to find a way to work around it.
> Roy Harvey
> Beacon Falls, CT
> On Thu, 15 Nov 2007 09:28:01 -0800, Clark Kent
> <ClarkKent@.discussions.microsoft.com> wrote:
> >We recently migrated from 2000 to 2005 (I know...early adopters) and our
> >first day in production we ran into a bunch of performance issues.
> >
> >We have some conditional checks in a stored procedure that return out
> >different values based on the existence of data in specific tables or views.
> >I have narrowed down the bottle neck to one of the views that we are
> >checking. The view itself has less than 10000 rows and if you query the view
> >directly, there is no performance problem.
> >
> >We use IF EXISTS to check each of the tables and views and then return out
> >accordingly. The IF EXISTS that checks this last view takes over two minutes,
> >where the whole proc ran in under 10 seconds in 2000. Have changes been made
> >to EXISTS in 2005? And for general knowledge, how does EXISTS do its
> >processing, cursors?
>