I have a stored procedure that runs on my Production database in 0.394 seconds. However, when I try to
run the SP on a DEV box it takes well over 15 minutes to run *every* time.
I have checked indexes and everything seems to be identical (I did a backup/restore to the Dev box in
the first place). The only major difference (besides the computer hardware) is that the Production DB
is SQL Server 2000 and DEV is SQL Server 2005.
Production environment hardware:
Xeon 1.6GHZ (x2)
Dev environment hardware:
P4 3GHZ (x1)
I can't imagine the hardware difference is causing that kind of slow down, but I didn't want to rule it
out. And I really don't think that is the problem because other queries seem to run ok (I haven't
tested all 175 of them yet though).
Also, just so you know, the # of records in the tables is 1399, 3528, 28442, 8230, 100 and 19 so we're
not talking about loads of data. Like I said before, the query runs in less than a second in
production. Any suggestions as to what might be causing this in my DEV environment?
Here is the query from the stored procedure that is taking so long to run... Keep in mind that I have
replaced declared and temporary variables with hard-coded variables in several places for simplicity
SELECT DISTINCT(rfq.rfqid), rfq.startdate
FROM dbo.rfqs AS rfq
LEFT JOIN dbo.organizations AS org ON rfq.orgid = org.orgid
LEFT JOIN dbo.bidsubmissions AS bs ON rfq.rfqid = bs.rfqid
LEFT JOIN dbo.rfqtimext AS rfqet ON bs.subid = rfqet.subid
LEFT JOIN dbo.timezones AS tz ON rfq.tzid = tz.tzid
WHERE ((org.spid IN (1) AND rfqet.newendtime IS NULL
AND rfq.enddate > DATEADD(hh, tz.offset, GETUTCDATE())
AND rfq.enddate < '12/31/3000 23:59:59.999'
AND org.[name] LIKE '')
(org.spid IN (1) AND rfqet.newendtime IS NOT NULL
AND rfqet.timeextID = (SELECT MAX(timeextID) FROM dbo.rfqtimext AS rte
LEFT JOIN bidsubmissions AS bs ON rte.subid = bs.subid
LEFT JOIN dbo.rfqpause AS rp ON rte.pauseid = rp.pauseid
WHERE bs.rfqid = rfq.rfqid OR rp.rfqid = rfq.rfqid)
AND rfqet.newendtime > DATEADD(hh, tz.offset, GETUTCDATE()))
AND rfqet.newendtime < '12/31/3000 23:59:59.999'
AND org.[name] LIKE ''
OR rfq.ispaused = 1)