StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPerformance issue with/without parameters
text
Body
copied!<p>I have some performance issues.</p> <p>I have a table with about 2 million rows.</p> <pre><code>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) </code></pre> <p>I read them with C# and ADO.NET</p> <p>In the management studio (SQL Server 2008 R2), with that query : </p> <pre><code>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) </code></pre> <p>It take about 1 minute to have the first rows.</p> <p>But, with </p> <pre><code>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) </code></pre> <p>I get the first rows after 4 seconds.</p> <p>PS : I know, I have no % in the like.</p> <p>Edit: Here are the execution plans <a href="https://www.dropbox.com/sh/jgai5f9txbs84x6/EP5_hj8DNv" rel="nofollow">https://www.dropbox.com/sh/jgai5f9txbs84x6/EP5_hj8DNv</a> </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload