Simple code leads to poor performance.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple code leads to poor performance.

Author

Topic

Hariarul
Posting Yak Master

160 Posts

Posted - 2007-09-08 : 04:15:03

Hi all,

We have stored procedures that takes in several parameters and the search is done on the tables for random conditions.

Basically, we check whether the parameter is NULL or the value of the parameter is used to compare with the column.We are not using any dynamic queries for this purpose.

We feel that doing this way doesn't use the indexes properly and the performance is very poor.

Example:

if there below code is used in the procedure, the response is fast...

SELECT Column1 , Column2 , Colum3
FROM Table1
WHERE
Table1.Column1 = @Var1 AND
Table1.Column6 LIKE @Var6

if the below code is used in the procedure , the performance is very very poor...

SELECT Column1 , Column2 , Colum3
FROM Table1
WHERE
((Table1.Column1 = @Var1) OR (@Var1 IS NULL))
((Table1.Column6 LIKE @Var6) OR (@Var6 IS NULL))

Is there any way to handle this scenario ( except dynamic queries )

Any help would be appreciated.

Thanks,

Hariarul

Kristen
Test

22859 Posts

Posted - 2007-09-08 : 05:10:27

"Is there any way to handle this scenario ( except dynamic queries )"

Well dynamic queries, done server-side, using sp_ExecuteSQL, are very effective - the only downside I can see is that they need table-level permissions.

An alternatively is to check the parameters and depending on which ones are NOT NULL create a Temporary Table with a list of matching PKs - in effect a number of small queries specific to certain of the parameters.

And then JOIN the Temporary Table to the main tables for the final "filter" of the data.

IF @Var1 IS NOT NULL
INSERT INTO #TEMP
SELECT MyPKCol
FROM Table1
WHERE Table1.Column1 = @Var1
ELSE
IF @Var6 IS NOT NULL
INSERT INTO #TEMP
SELECT MyPKCol
FROM Table1
Table1.Column6 LIKE @Var6

SELECT Column1 , Column2 , Colum3
FROM #TEMP AS T
JOIN Table1 AS Table1
ON Table1.MyPKCol = T.MyPKCol
WHERE
((Table1.Column1 = @Var1) OR (@Var1 IS NULL))
((Table1.Column6 LIKE @Var6) OR (@Var6 IS NULL))

Kristen

Jenda
Starting Member

29 Posts

Posted - 2007-09-11 : 07:12:32

quote:
Originally posted by Hariarul
We have stored procedures that takes in several parameters and the search is done on the tables for random conditions.

Basically, we check whether the parameter is NULL or the value of the parameter is used to compare with the column.We are not using any dynamic queries for this purpose.

We feel that doing this way doesn't use the indexes properly and the performance is very poor.

It doesn't use indexes at all.

If you do not want to build the query in a string and execute it using EXEC or sp_executesql (which I would not recomend anyway since it's prone to SQL Injection attacks and forces the server to regenerate the execution plan every time) there are basically two options. Either use a few IFs and specific queries or UNION ALL and specific queries:


IF (@Var1 is NULL)
 IF (@Var6 is NULL)
  SELECT Column1, Column2, Column3
    FROM Table1
 ELSE
  SELECT Column1, Column2, Column3
    FROM Table1
   WHERE Column6 LIKE @Var6
ELSE
 IF (@Var6 is NULL)
  SELECT Column1, Column2, Column3
    FROM Table1
   WHERE Column1 = @Var1
 ELSE
  SELECT Column1, Column2, Column3
    FROM Table1
   WHERE Column1 = @Var1 and Column6 LIKE @Var6


SELECT Column1, Column2, Column3
FROM (
  SELECT *
    FROM Table1
   WHERE @Var1 is NULL and @Var6 is NULL
UNION ALL
  SELECT *
    FROM Table1
   WHERE @Var1 is NULL and Column6 LIKE @Var6
UNION ALL
  SELECT *
    FROM Table1
   WHERE Column1 = @Var1 and @Var6 is NULL
UNION ALL
  SELECT *
    FROM Table1
   WHERE Column1 = @Var1 and Column6 LIKE @Var6
) as Table1

The performance will be similar, the second format allows you to add more conditions (for columns that do not have indexes) or join detail tables just once instead of repeating them for each query.

It's not particularly nice, but AFAIK these are the only ways to allow the server to generate an optimal execution plan once and reuse it for later invocations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources