I have an indexed query that returns in < 1 second when I run it in SQL Server Management Studio (SQL Server 2008), but when it runs in my Delphi XE3 code, it takes 4 - 5 seconds.
I've never experienced this before. I'm hoping someone has some idea of what might be going on. As you can see below, the query is not overly complex. I tried putting in in a stored procedure and tried getting rid of the "declare" section and nothing seems to make any difference.
I have other queries that are MUCH more complex, yet perform the same in Delphi as they do in Management Studio. The only big difference is that this is the first time I've used CTEs in a query (in Delphi).
I'm using a TADOQuery component. Also, I use the same TADOQuery component for all of my queries, so it's not a component setting. Well, actually I use 3 because I sometimes need to go off and do somethings and don't want to lose my original result set. But I use the same component for all of my major queries.
Thank you for any help you can give me.
Mike
declare
-- First date
@dtDate date = '2012-12-15',
-- Number of days to show
@numDays integer = 4;
with DateInspection as
(
select * from Inspection where cast(ScheduledDate as date) >= @dtDate and cast(ScheduledDate as date) < dateadd(day, @numDays, @dtDate)
),
DateSummary as
(
select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate,
-- How many per day?
(select count(cast(ScheduledDate as date)) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate as date)) NumPerDay,
-- How many per hour per day?
(select count(cast(ScheduledDate as date)) from DateInspection
where (cast(ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))=(cast(i.ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))) NumPerHourPerDay
from DateInspection i
where ScheduledDate is not null and Cancelled=0 and PrintedByBUID is null
)
select tt.StartDate, ds.*, (select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay, b.SalesOrder, cast(datepart(hh, ds.ScheduledDate) as varchar) InspHour
from (select * from TimeTable where cast(StartDate as date) >= @dtDate and cast(StartDate as date) < dateadd(day, @numDays, @dtDate)) tt
left outer join DateSummary ds on tt.StartDate=cast(ds.ScheduledDate as date)
left outer join Batch b on ds.BatchID=b.BatchID
order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;
mlmcc