why does adding the where statement to this sql make it run so much slower?

I have inherited a stored procedure and am having problems with it takes a very long time to run (around 3 minutes). I have played around with it, and without the where clause it actually only takes 12 seconds to run. None of the tables it references have a lot of data in them, can anybody see any reason why adding the main where clause below makes it take so much longer?

ALTER Procedure [dbo].[MissingReadingsReport] @SiteID    INT,
                                              @FormID    INT,
                                              @StartDate Varchar(8),
                                              @EndDate   Varchar(8)
As
  If @EndDate > GetDate()
    Set @EndDate = Convert(Varchar(8), GetDate(), 112)

  Select Dt.FormID,
         DT.FormDAte,
         DT.Frequency,
         Dt.DayOfWeek,
         DT.NumberOfRecords,
         Dt.FormName,
         dt.OrgDesc,
         Dt.CDesc
  FROM   (Select MeterForms.FormID,
                 MeterForms.FormName,
                 MeterForms.SiteID,
                 MeterForms.Frequency,
                 DateTable.FormDate,
                 tblOrganisation.OrgDesc,
                 CDesc = ( COMPANY.OrgDesc ),
                 DayOfWeek = CASE Frequency
                               WHEN 'Day' THEN DatePart(dw, DateTable.FormDate)
                               WHEN 'WEEK' THEN
                               DatePart(dw, MeterForms.FormDate)
                             END,
                 NumberOfRecords = CASE Frequency
                              开发者_JAVA技巧       WHEN 'Day' THEN (Select TOP 1 RecordID
                                                      FROM   MeterReadings
                                                      Where
                                     MeterReadings.FormDate =
                                     DateTable.FormDate
                                     And MeterReadings.FormID =
                                         MeterForms.FormID
                                                      Order  By RecordID DESC)
                                     WHEN 'WEEK' THEN (Select TOP 1 ( FormDate )
                                                       FROM   MeterReadings
                                                       Where
                                     MeterReadings.FormDate >=
                                     DateAdd(d
                                     , -4,
                                     DateTable.FormDate)
                                     And MeterReadings.FormDate
                                         <=
                                         DateAdd(d, 3,
                                         DateTable.FormDate)
                                     AND MeterReadings.FormID =
                                         MeterForms.FormID)
                                   END
          FROM   MeterForms
                 INNER JOIN DateTable
                   ON MeterForms.FormDate <= DateTable.FormDate
                 INNER JOIN tblOrganisation
                   ON MeterForms.SiteID = tblOrganisation.pkOrgId
                 INNER JOIN tblOrganisation COMPANY
                   ON tblOrganisation.fkOrgID = COMPANY.pkOrgID
          /*this is what makes the query run slowly*/
          Where  DateTable.FormDAte >= @StartDAte
                 AND DateTable.FormDate <= @EndDate
                 AND MeterForms.SiteID = ISNULL(@SiteID, MeterForms.SiteID)
                 AND MeterForms.FormID = IsNull(@FormID, MeterForms.FormID)
                 AND MeterForms.FormID > 0)DT
  Where  ( Frequency = 'Day'
           And dt.NumberofRecords IS NULL )
          OR ( ( Frequency = 'Week'
                 AND DayOfWeek = DATEPART (dw, Dt.FormDate) )
               AND ( FormDate <> NumberOfRecords
                      OR dt.NumberofRecords IS NULL ) )
  Order  By FormID  

Based on what you've already mentioned, it looks like the tables are properly indexed for columns in the join conditions but not for the columns in the where clause.

If you're not willing to change the query, it may be worth it to look into indexes defined on the where clause columns, specially that have the NULL check

Try replacing your select with this:

FROM   
                (select siteid, formid, formdate from meterforms 
                 where siteid = isnull(@siteid, siteid) and
                        meterforms.formid = isnull(@formid, formid) and formid >0
                 ) MeterForms
                 INNER JOIN 
                 (select formdate from datetable where formdate >= @startdate and formdate <= @enddate) DateTable
                   ON MeterForms.FormDate <= DateTable.FormDate
                 INNER JOIN tblOrganisation
                   ON MeterForms.SiteID = tblOrganisation.pkOrgId
                 INNER JOIN tblOrganisation COMPANY
                   ON tblOrganisation.fkOrgID = COMPANY.pkOrgID
          /*this is what makes the query run slowly*/ 
          )DT

I would be willing to bet that if you moved the Meterforms where clauses up to the from statement:

 FROM (select [columns] from MeterForms WHERE SiteID= ISNULL [etc] ) MF
      INNER JOIN [etc]

It would be faster, as the filtering would occur before the join. Also, having your INNER JOIN on your DateTable doing a <= down in your where clause may be returning more than you'd like ... try moving that between up to a subselect as well.

Have you run an execution plan on this yet to see where the bottleneck is?

Random suggestion, coming from an Oracle background:
What happens if you rewrite the following:

AND MeterForms.SiteID = ISNULL(@SiteID, MeterForms.SiteID)
AND MeterForms.FormID = IsNull(@FormID, MeterForms.FormID)

...to

AND (@SiteID is null or MeterForms.SiteID = @SiteID)
AND (@FormID is null or MeterForms.FormID = @FormID)