Irregular execution

• ajsnyman

  Right there with Babe

  Points: 732

  More actions

  November 14, 2011 at 2:02 am

  #259856

  Hi All,

  Hope someone can shed some light on the time difference in my query:

  The following query runs 49 sec on 11 000 000 records

  declare

  @tableName varchar(100),

  @fieldName varchar(100),

  @key varchar(50),

  @subKey varchar(50),

  @auditDataTypeID int,

  @userid int,

  @fromDate datetime,

  @toDate datetime

  set @tableName = 'dbo.requisition'

  set @fieldName = ''

  set @key = '700087610'

  set @subKey = ''

  set @auditDataTypeID = 0

  set @userid = 0

  set @fromDate = NULL

  set @toDate = NULL

  SELECT

  ad.auditDataID,

  ad.auditDataTypeID,

  ad.entryDate,

  ad.fieldName,

  ad.,

  ad.subKey,

  ad.newValue,

  ad.oldValue,

  ad.tableName,

  ad.userID

  FROM

  dbo.auditData ad

  WHERE

  tableName = @tableName

  AND ((fieldName = @fieldName) OR (@fieldName = ''))

  AND (( = @key) OR (@key = ''))

  AND ((subKey = @subKey) OR (@subKey = ''))

  AND ((auditDataTypeID = @auditDataTypeID) OR (@auditDataTypeID = 0))

  AND ((userID = @userid) OR (@userID = 0))

  AND ((entryDate >= @fromDate) OR (@fromDate IS NULL))

  AND ((entryDate < @toDate) OR (@toDate IS NULL))

  Changing on line cause the query to run at 0sec on the same amount of records

  declare

  @tableName varchar(100),

  @fieldName varchar(100),

  @key varchar(50),

  @subKey varchar(50),

  @auditDataTypeID int,

  @userid int,

  @fromDate datetime,

  @toDate datetime

  set @tableName = 'dbo.requisition'

  set @fieldName = ''

  set @key = '700087610'

  set @subKey = ''

  set @auditDataTypeID = 0

  set @userid = 0

  set @fromDate = NULL

  set @toDate = NULL

  SELECT

  ad.auditDataID,

  ad.auditDataTypeID,

  ad.entryDate,

  ad.fieldName,

  ad.,

  ad.subKey,

  ad.newValue,

  ad.oldValue,

  ad.tableName,

  ad.userID

  FROM

  dbo.auditData ad

  WHERE

  tableName = @tableName

  AND ((fieldName = @fieldName) OR (@fieldName = ''))

  AND (( = @key))

  AND ((subKey = @subKey) OR (@subKey = ''))

  AND ((auditDataTypeID = @auditDataTypeID) OR (@auditDataTypeID = 0))

  AND ((userID = @userid) OR (@userID = 0))

  AND ((entryDate >= @fromDate) OR (@fromDate IS NULL))

  AND ((entryDate < @toDate) OR (@toDate IS NULL))

  Hope someone can advise, if I don't get a solution to this I will need to change it to a dynamic query.

  Any help would be greatly appreciated.

  Kind regards 🙂

  AJS

• Devendrakumar Shirbad

  SSC-Forever

  Points: 42493

  More actions

  November 14, 2011 at 2:16 am

  #1406989

  AND (( = @key) OR (@key = ''))

  I guess the line above will lead to Index Scan (or can skip the index).

  AND (( = @key))

  It should go for Index Seek.

  I am just guessing. Please verify the same in your system. Many guys here may provide you good advice if you post the execution plans for both the scenarios.

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  November 14, 2011 at 2:17 am

  #1406992

  You'll need to go for dynamic query

  http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  November 14, 2011 at 2:18 am

  #1406993

  Dev (11/14/2011)

  ---

  AND (( = @key) OR (@key = ''))

  I guess the line above will lead to Index Scan (or can skip the index).

  AND (( = @key))

  It should go for Index Seek.

  Those 2 are not synonymous. Yes, it'll give an index seek (providing there is an index), but it completely changes the logic of the query as well.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ajsnyman

  Right there with Babe

  Points: 732

  More actions

  November 14, 2011 at 2:55 am

  #1407011

  Thank you very much for your replies and help.

  We'r converting it to dynamic now.

  All the best.

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  November 14, 2011 at 3:46 am

  #1407031

  Something I keep forgetting...

  If you're on 2008 SP2 or later, you should be able to just add OPTION (RECOMPILE) to that query (the non-dynamic form) and get optimal plans for each execution. Doesn't work on SQL 2005 and is broken on SQL 2008 RTM and SP1

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ajsnyman

  Right there with Babe

  Points: 732

  More actions

  November 14, 2011 at 4:08 am

  #1407044

  Interesting, took execution time down to 1 sec, but the dynamic is still faster.

  Thanks for the info, will surely be useful in the future 🙂

  Kind regards