troubleshooting Question

Query fast in Management Studio, slow in Delphi XE3

Avatar of ussynth
ussynth asked on
Microsoft SQL ServerDelphi
11 Comments1 Solution1031 ViewsLast Modified:
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;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros