Databases
StackOverflow2013
Postsdbo
POGenerated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql
    primarykey
    data
    text
    <p>I have a sql statement which is hardcoded in an existing VB6 app. I'm upgrading a new version in C# and using Linq To Sql. I was able to get LinqToSql to generate the same sql (before I start refactoring), but for some reason the Sql generated by LinqToSql is <strong>5x slower</strong> than the original sql. <em>This is running the generated Sql Directly</em> in LinqPad.</p> <p>The only real difference my meager sql eyes can spot is the <strong><em>WITH (NOLOCK)</em></strong>, which if I add into the LinqToSql generated sql, makes no difference.</p> <p>Can someone point out what I'm doing wrong here? Thanks! </p> <p><strong>Existing Hard Coded Sql</strong> (5.0 Seconds)</p> <pre><code>SELECT DISTINCT CH.ClaimNum, CH.AcnProvID, CH.AcnPatID, CH.TinNum, CH.Diag1, CH.GroupNum, CH.AllowedTotal FROM Claims.dbo.T_ClaimsHeader AS CH WITH (NOLOCK) WHERE CH.ContractID IN ('123A','123B','123C','123D','123E','123F','123G','123H') AND ( ( (CH.Transmited Is Null or CH.Transmited = '') AND CH.DateTransmit Is Null AND CH.EobDate Is Null AND CH.ProcessFlag IN ('Y','E') AND CH.DataSource NOT IN ('A','EC','EU') AND CH.AllowedTotal &gt; 0 ) ) ORDER BY CH.AcnPatID, CH.ClaimNum </code></pre> <p><strong>Generated Sql from LinqToSql</strong> (27.6 Seconds)</p> <pre><code>-- Region Parameters DECLARE @p0 NVarChar(4) SET @p0 = '123A' DECLARE @p1 NVarChar(4) SET @p1 = '123B' DECLARE @p2 NVarChar(4) SET @p2 = '123C' DECLARE @p3 NVarChar(4) SET @p3 = '123D' DECLARE @p4 NVarChar(4) SET @p4 = '123E' DECLARE @p5 NVarChar(4) SET @p5 = '123F' DECLARE @p6 NVarChar(4) SET @p6 = '123G' DECLARE @p7 NVarChar(4) SET @p7 = '123H' DECLARE @p8 VarChar(1) SET @p8 = '' DECLARE @p9 NVarChar(1) SET @p9 = 'Y' DECLARE @p10 NVarChar(1) SET @p10 = 'E' DECLARE @p11 NVarChar(1) SET @p11 = 'A' DECLARE @p12 NVarChar(2) SET @p12 = 'EC' DECLARE @p13 NVarChar(2) SET @p13 = 'EU' DECLARE @p14 Decimal(5,4) SET @p14 = 0 -- EndRegion SELECT DISTINCT [t0].[ClaimNum], [t0].[acnprovid] AS [AcnProvID], [t0].[acnpatid] AS [AcnPatID], [t0].[tinnum] AS [TinNum], [t0].[diag1] AS [Diag1], [t0].[GroupNum], [t0].[allowedtotal] AS [AllowedTotal] FROM [Claims].[dbo].[T_ClaimsHeader] AS [t0] WHERE ([t0].[contractid] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7)) AND (([t0].[Transmited] IS NULL) OR ([t0].[Transmited] = @p8)) AND ([t0].[DATETRANSMIT] IS NULL) AND ([t0].[EOBDATE] IS NULL) AND ([t0].[PROCESSFLAG] IN (@p9, @p10)) AND (NOT ([t0].[DataSource] IN (@p11, @p12, @p13))) AND ([t0].[allowedtotal] &gt; @p14) ORDER BY [t0].[acnpatid], [t0].[ClaimNum] </code></pre> <p><strong>New LinqToSql Code</strong> (30+ seconds... Times out )</p> <pre><code>var contractIds = T_ContractDatas.Where(x =&gt; x.EdiSubmissionGroupID == "123-01").Select(x =&gt; x.CONTRACTID).ToList(); var processFlags = new List&lt;string&gt; {"Y","E"}; var dataSource = new List&lt;string&gt; {"A","EC","EU"}; var results = (from claims in T_ClaimsHeaders where contractIds.Contains(claims.contractid) &amp;&amp; (claims.Transmited == null || claims.Transmited == string.Empty ) &amp;&amp; claims.DATETRANSMIT == null &amp;&amp; claims.EOBDATE == null &amp;&amp; processFlags.Contains(claims.PROCESSFLAG) &amp;&amp; !dataSource.Contains(claims.DataSource) &amp;&amp; claims.allowedtotal &gt; 0 select new { ClaimNum = claims.ClaimNum, AcnProvID = claims.acnprovid, AcnPatID = claims.acnpatid, TinNum = claims.tinnum, Diag1 = claims.diag1, GroupNum = claims.GroupNum, AllowedTotal = claims.allowedtotal }).OrderBy(x =&gt; x.ClaimNum).OrderBy(x =&gt; x.AcnPatID).Distinct(); </code></pre> <p>I'm using the list of constants above to make LinqToSql Generate IN ('xxx','xxx',etc) Otherwise it uses subqueries which are just as slow...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJason More
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGenerated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql
      1. USDave Markle
      1. VTFavorite
    2. VO
      singulars
      1. POGenerated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe biggest difference in the query plans seems to be the ContractId Index seek in the original sql only accounts for 1%, where as the LinqToSql query plan takes 22% of the search. Is that due to the parameters?
      singulars
      1. POGenerated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql
      1. USJason More
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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