SQL 7.0 sp performance 1

[dbljackson]

I am using SQL Server 7.0.

I have a couple of queries that perform very different from the SQL Analyzer vs. Application GUI.

I have a query that accepts four parameters.

@date as datetime
@Qual_no char(2)
@Patt_no char(2)
@Sku_no as char(16)

select qualcode,pattcode,orderdate,qtyordered,sku
from tblCustDemandHist
where orderdate between @Date - 56 and @Date + 55
and qualcode=@Qual_no
and pattcode=@Patt_no
and sku=@Sku_no

If I set the parameter values by hand in the Analyzer and run the query the results are returned almost instantly ( 2-3 seconds for 5 records).

When created as a stored procedure and called interactively
from the Analyzer with "Exec" command or from the Application GUI the results can take 30 seconds.

Any suggestions most appreciated.

TIA

 

[MalcolmW]

My WAG would be that the line
where orderdate between @Date - 56 and @Date + 55
is slowing things down, perhaps because of the implicit type conversion that is preventing this from being optimized.
One way to avoid this would be
declare @FromDate datetime, @ToDate datetime
set @FromDate = @Date - 56
set @ToDate = @Date + 55
and then change your where clause
where orderdate between @FromDate and @ToDate
That way the implicit type conversion only occurs once per stored proc, rather than once per record in the table.
Malcolm

 

[dbljackson]

Thanks Malcolm,

I actually have several of these aggregations so that may be the problem.