variable substitution vs. executing dynamic strings

First off_ pure - uneducated - hacker - novice - alert!! If you are offended by
silly questions close this posting immediately!

I'm sure this has come up 1,000K times before and I have googled the issue_
Still have not found a satisfactory explanation.

I have a table 'alarms' with several defined indexes. The index alarm_id is just
what you would expect a auto increment key field.

In the follow code returns around 1768 records. WHY is the second query so much
faster than the first? Executing strings in sprocs seems insane, not to mention
bug-ugly to write_ but for whatever reason the evaluation of the variables for
the first query seem to make it take significantly longer to run_ If I up the
returned record count to 500K or so the difference is minimal. That implies to
me that the compile and substitution time is the issue not the actual reading of
the data. Query execution plan shows a 9:1 speed difference. Trace shows a 1.5:1
difference. Not sure I beleve either one of them but in practice using my stop
watch the difference is noticable.

(This is actually an sproc where the start / end variables are passed_ The
actual query in the sproc has a number of joins etc however all that stuff was
removed as it appears to be irrelevant. The server an 8 cpu machine running
sql2K)

declare @start int
declare @end int
declare @string nvarchar(500)
select @start = 35300000
select @end = 35301768

SELECT *
FROM alarm
WHERE (alarm_id BETWEEN @start AND @end)

select @string = 'SELECT * FROM alarm WHERE (alarm_id BETWEEN '''+ cast(@start
as nvarchar(8)) + ''' AND '''+ cast(@end as nvarchar(8)) + ''')'

exec(@string)

.

Relevant Pages

  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Need assistance with query
    ... for and the query that you are looking for is a complicated query to write ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Strange SQL results
    ... declare @t1 table ... id int null ... Notice how I'm allowing nulls for id, since I don't have your DDL. ... I did not tried Join query, but I got proper data set by running following ...
    (microsoft.public.sqlserver.server)
  • creating jobs programmatically causes deadlocks
    ... I'm using the sproc below to create jobs(using transaction) in an automated ... The sproc is run using the ADO.NET ... DECLARE @ReturnCode INT ...
    (microsoft.public.sqlserver.dts)
  • Re: Is this an MSSQL bug?
    ... > I have the following query on MSSQL 2000. ... > declare @t table(i int identity, j int, k int) ... > An explicit value for the identity column in table '@t' can only be ...
    (microsoft.public.sqlserver.programming)
Loading