Re: Help me convince the dev manager. Please.

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Rohan Hattangdi (RohanHattangdi2003_at_hotmail.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 18:51:14 -0400

I tried this with a simple example in Northwind and it shows an index scan
same as the method with the non-sargable clause - just as it would appear
you see in your database.

I will try this with a larger query to see where it goes.

I can not really use NOLOCK because dirty reads will be an issue for our
data except in a certain small number of cases.

Like I mentioned before, I do not want this to be a "put on the blinders"
and follow this rule sort of thing. I suppose I must have come off like that
in my initial post.

I just want people to consider dynamic SQL as an alternate to using the
non-sargable clauses they are using now.

Rohan

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eiIl3CGOEHA.128@TK2MSFTNGP12.phx.gbl...
> > <column name> = ISNULL(<var name>, <column name>)
> >
> > The variable has a default of NULL. If the optional parameter is not
> passed
> > in, the column matches itself.
> >
> > This is causing index scans instead of seeks. It is causing an excessive
> > number of locks to be acquired. Our database servers are already under a
> > large load ... the locks get escalated and cause blocking and
dead-locks.
>
> I haven't tested this thoroughly, but have you checked out the
implications
> of changing the ISNULL to WHERE col = CASE WHEN @param IS NOT NULL THEN
> @param ELSE col END? On my small resultset the execution plans looked the
> same, but maybe in your result they would be different.
>
> What about using NOLOCK on the queries? I know this is not a recommended
> approach but if you can sacrifice dirty reads in order to eliminate
locking
> and blocking, that might be a better approach than forcing everyone to
write
> dynamic SQL.
>
> I am not 100% opposed to dynamic SQL but I know there are other ways you
can
> try to improve performance without forcing that hand...
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>

Relevant Pages

  • Re: invalid column error when updating a new column that was added by alter table
    ... As the column doesn't exists at parse time, ... One way around it is to do the update with dynamic SQL. ... Tibor Karaszi, SQL Server MVP ... If i run the alter statement and update ...
    (microsoft.public.sqlserver.server)
  • Re: Variable Column Name in SELECT
    ... variable column names is usually not a good solution for any problem, ... SQL Server MVP ... >> Dynamic sql with output parameter ... >> exec sp_executesql @dynamicsql, N'@TotalRecords int output', ...
    (microsoft.public.sqlserver.programming)
  • Re: using the Execute statement including variables
    ... see http://www.sommarskog.se/dynamic_sql.html for dynamic SQL ... SQL Server MVP ... > SELECT * from @TableName doesn't work unless I do something like this ... > The EXEC statement doesn't accept variable. ...
    (microsoft.public.sqlserver.server)
  • Re: dynamic column names
    ... You would have to use dynamic SQL. ... Tibor Karaszi, SQL Server MVP ... "raybouk" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: EXEC (select... ) problem Help!
    ... >Now this is a simplified version of a more comples query which is forcing me ... do try to solve this without dynamic SQL. ... Consider posting your real problem here; ...
    (microsoft.public.sqlserver.mseq)