Is Optimizer bypassing Use of Indexes for this Dynamic SQL proc

Question

Is Optimizer bypassing Use of Indexes for this Dynamic SQL proc

Express12

SSCoach

Points: 18514
More actions
December 1, 2005 at 1:25 pm

#88821

This PROC is performing very poorly. I double checked - and all predicated columns are indexed. Is it possible that the dnamically rendered WHERE clause is disabling SQL's optimizer from utilizing the indexes here?
CREATE PROCEDURE [dbo].[Manual_AdminApplicationSearch_v2]
    @limit int = 20,
    @StatusID int ,
    @AuditStatusID int =NULL,
    @FromSubmitDate datetime = NULL,
    @ToSubmitDate datetime = NULL
AS
SET NOCOUNT ON
DECLARE @Where nvarchar(500)
DECLARE @SQLResults nvarchar(4000)
DECLARE @SQLCount nvarchar(4000)
DECLARE @ParmDefinition nvarchar(1000)
/*
   * We're going to pass in these parameters
   */
   SET @ParmDefinition = N'@StatusID int, @AuditStatusID int =NULL, @FromSubmitDate datetime = NULL, @ToSubmitDate datetime = NULL'
/*
   * Go ahead and build out the WHERE clause. We have several optional parameter
   * we have to deal with.
   */
   SET @Where = ''
   -- First optional parameter: audit status
   IF NOT @AuditStatusID IS NULL
   BEGIN
      SET @Where = ' AND application.AuditStatusID = @AuditStatusID'
   END
   -- Optional date filters
   IF NOT @FromSubmitDate IS NULL
   BEGIN
      SET SET @Where = @Where + ' AND application.DateSubmitted > @FromSubmitDate'
   END
   -- Optional date ... end this time
   IF NOT @ToSubmitDate IS NULL
   BEGIN
      SET SET @Where = @Where + ' AND application.DateSubmitted <= @ToSubmitDate'
   END
   -- COUNT statement.
   SET @SQLCount =
   'SELECT Count(*) As TotalCount FROM
dbo.CW_Applications application
  INNER JOIN dbo.Persons person ON person.ID = application.PersonID

WHERE
  application.StatusID = @StatusID ' + @Where
   -- Actual SQL statement
   SET @SQLResults =
   'SELECT TOP ' + cast(@limit as nvarchar(10)) + '
  person.ID AS PersonID,
  person.LastName,
  person.MiddleName,
  person.FirstName,
  application.ID AS ApplicationID,
  application.StatusID AS ApplicationStatusID,
  application.AdminCreatedApplication,
  application.AuditStatusID,
  application.DateOpened,
  application.DateSubmitted,
  application.DateAuditOpened,
  application.CertificationTypeID,
  address.City,
          address.State,
  address.CountryCode
FROM
  dbo.CW_Applications application
   INNER JOIN dbo.Persons person ON person.ID = application.PersonID
   LEFT OUTER JOIN dbo.Addresses address ON person.PrefMailAID = address.ID
WHERE
  application.StatusID = @StatusID ' + @Where
   -- The caller first wants the total number of records
   EXEC sp_ExecuteSQL @SQLCount, @ParmDefinition, @StatusID, @AuditStatusID, @FromSubmitDate, @ToSubmitDate

   -- Then the actual records. This is generally the PageSize x TotalPages. The
   -- calling app will ignore the first pages.
   EXEC sp_ExecuteSQL @SQLResults, @ParmDefinition, @StatusID, @AuditStatusID, @FromSubmitDate, @ToSubmitDate
GO
BT

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Gagandeep Singh SSCommitted Points: 1513 More actions · Answer 1

Try the Select without the sp_execute with all parameters and check the performance. Isolate the problem further by trying it locally on the server and from the client to rule out NW bottlenecks. Check the execution plan for Table Scans or implicit conversions, outdated statistics, etc.