CODEWITHSUNDEEP
sqlsql-serverperformancet-sql
Xavinou
Xavinou

Reputation: 802

Performance issue with/without parameters

I have some performance issues.

I have a table with about 2 million rows.

CREATE TABLE [dbo].[M8](
    [M8_ID] [int] IDENTITY(1,1) NOT NULL,
    [APPLIC] [char](8) NOT NULL,
    [NIVALERTE] [numeric](1, 0) NOT NULL,
    [LOGDH] [datetime2](7) NULL,
    [USERX] [char](20) NOT NULL,
    [TACHE] [char](3) NOT NULL,
    [PRG] [char](32) NOT NULL,
    [DOS] [numeric](3, 0) NOT NULL,
    [ERRNUM] [numeric](5, 0) NOT NULL,
    [LOGTXT] [char](200) NOT NULL)

I read them with C# and ADO.NET

In the management studio (SQL Server 2008 R2), with that query : 

SELECT 
    M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, 
    M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT
FROM 
    M8 AS M8 WITH(NOLOCK) 
WHERE 
    ((M8.APPLIC LIKE 'DAV' ) )
ORDER BY 
    M8.LOGDH DESC, M8.M8_ID ASC
OPTION (FAST 1)

It take about 1 minute to have the first rows.

But, with 

DECLARE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 as char(8) = 'DAV'

SELECT 
   M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, 
   M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT
FROM 
   M8 AS M8 WITH(NOLOCK) 
WHERE 
   ((M8.APPLIC LIKE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 ) )
ORDER BY 
   M8.LOGDH DESC, M8.M8_ID ASC
OPTION(FAST 1)

I get the first rows after 4 seconds.

PS : I know, I have no % in the like.

Edit: Here are the execution plans https://www.dropbox.com/sh/jgai5f9txbs84x6/EP5_hj8DNv

Upvotes: 4

Views: 347

Answers (2)

Martin Smith
Martin Smith

Reputation: 453067

Your table has 1,517,820 rows. Of these nearly one third (476,672) contain the value DAV (or more accurately the value DAV      as it is of CHAR(8) datatype so is padded out with trailing spaces.

In a LIKE comparison trailing spaces in the match_expression are not significant (though they are significant in the pattern itself).

Therefore the expression WHERE APPLIC LIKE 'DAV' does in fact match 476,672 rows. Neither of the execution plans estimate anywhere near this however. Though the faster plan (with variables) is three orders of magnitude closer.

+-----------------------+-----------+-----------+
|                       | Slow Plan | Fast Plan |
+-----------------------+-----------+-----------+
| Estimated # Rows      | 32        | 47,343    |
| Memory Grant          | 1 MB      | 333 MB    |
| Degree of Parallelism | 1         | 4         |
+-----------------------+-----------+-----------+

For the plan with the variables as SQL Server does not do variable sniffing (without e.g. the OPTION (RECOMPILE) hint) it falls back on guesses as to how many rows will match the predicate and comes up with an estimate that around 3.1% of the table will qualify.

Fast Plan

Fast

The plan with the literal value ought to have much better estimates. The screen shot you supplied of the DBCC SHOW_STATISTICS output (after adding another million rows) shows that DAV is definitely in there

Stats

Unfortunately it seems that although the trailing space in the column values are not significant for the query result their presence does mess up the cardinality estimates (Reported as a bug here and currently stated to be fixed in the next version). As a result of this problem it estimates only a handful of rows will be returned and comes up with the following plan.

Slow Plan

Slow

As well as performing half a million key lookups because of the poor cardinality estimates the memory grant is probably no where near adequate for the size of data being sorted resulting in spills to tempdb.

There are many work arounds you might consider if you can change the query or table schema.

  • Using = instead of LIKE
  • Changing the WHERE clause to LIKE CAST('DAV' AS CHAR(8))
  • Changing the column datatype to VARCHAR(8) (and ensuring all stored values are trimmed).
  • Dropping the current index being seeked (Index_A). You haven't supplied its definition but if it is a single column index on a column with few distinct values its presence may be more of a hindrance than a help (depending on your query workload))
  • Adding a covering index with key column APPLIC (and possibly LOGDH DESC, M8_ID ASC to avoid a sort) and the other referenced columns as INCLUDED.

Upvotes: 5

Yaroslav
Yaroslav

Reputation: 6534

Maybe these 2 questions will give you a better understanding of the performance issues involved on the LIKE and = operators:

Upvotes: 0

Related Questions