This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
Query fast in Management Studio, slow in Delphi XE3
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
-- 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.Schedule
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.Sched
left outer join Batch b on ds.BatchID=b.BatchID
order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;
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
-- 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.Schedule
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.Sched
left outer join Batch b on ds.BatchID=b.BatchID
order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Thanks for asking. I'm just stepping through the code and watching what happends when I hit the "Open" statement (below). It takes 4 - 5 seconds before I hit the first line of code after "Open".
SQL.Clear;
SQL.Add('declare ' + #13 +
' -- First date ' + #13 +
' @dtDate date = ' + QuotedStr(FormatDateTime('
' -- Number of days to show ' + #13 +
' @numDays integer = ' + IntToStr(iDayCount) + '; ' + #13 +
' with DateInspection as ' + #13 +
' ( ' + #13 +
' select * from Inspection where cast(ScheduledDate as date) >= @dtDate and cast(ScheduledDate as date) < dateadd(day, @numDays, @dtDate) ' + #13 +
' ), ' + #13 +
' DateSummary as ' + #13 +
' ( ' + #13 +
' select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate, ' + #13 +
' -- How many per day? ' + #13 +
' (select count(cast(ScheduledDate as date)) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate
' -- How many per hour per day? ' + #13 +
' (select count(cast(ScheduledDate as date)) from DateInspection ' + #13 +
' where (cast(ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))=(cast(i.Schedule
' from DateInspection i ' + #13 +
' where ScheduledDate is not null and Cancelled=0 and PrintedByBUID is null ' + #13 +
' ) ' + #13 +
' select tt.StartDate, ds.*, (select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay, b.SalesOrder, cast(datepart(hh, ds.ScheduledDate) as varchar) InspHour ' + #13 +
' from (select * from TimeTable where cast(StartDate as date) >= @dtDate and cast(StartDate as date) < dateadd(day, @numDays, @dtDate)) tt ' + #13 +
' left outer join DateSummary ds on tt.StartDate=cast(ds.Sched
' left outer join Batch b on ds.BatchID=b.BatchID ' + #13 +
' order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;');
Open;
SQL.Clear;
SQL.Add('declare ' + #13 +
' -- First date ' + #13 +
' @dtDate date = ' + QuotedStr(FormatDateTime('
' -- Number of days to show ' + #13 +
' @numDays integer = ' + IntToStr(iDayCount) + '; ' + #13 +
' with DateInspection as ' + #13 +
' ( ' + #13 +
' select * from Inspection where cast(ScheduledDate as date) >= @dtDate and cast(ScheduledDate as date) < dateadd(day, @numDays, @dtDate) ' + #13 +
' ), ' + #13 +
' DateSummary as ' + #13 +
' ( ' + #13 +
' select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate, ' + #13 +
' -- How many per day? ' + #13 +
' (select count(cast(ScheduledDate as date)) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate
' -- How many per hour per day? ' + #13 +
' (select count(cast(ScheduledDate as date)) from DateInspection ' + #13 +
' where (cast(ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))=(cast(i.Schedule
' from DateInspection i ' + #13 +
' where ScheduledDate is not null and Cancelled=0 and PrintedByBUID is null ' + #13 +
' ) ' + #13 +
' select tt.StartDate, ds.*, (select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay, b.SalesOrder, cast(datepart(hh, ds.ScheduledDate) as varchar) InspHour ' + #13 +
' from (select * from TimeTable where cast(StartDate as date) >= @dtDate and cast(StartDate as date) < dateadd(day, @numDays, @dtDate)) tt ' + #13 +
' left outer join DateSummary ds on tt.StartDate=cast(ds.Sched
' left outer join Batch b on ds.BatchID=b.BatchID ' + #13 +
' order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;');
Open;
i'm not that good any more at sql server, but on the delphi side
do you have any grids attached (and datasource)
> loading the grid takes time too
how many records are you returning ?
the best way is to start a trace and see what is actually happening
the trace will also contain the real time spent on the database
http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx
do you have any grids attached (and datasource)
> loading the grid takes time too
how many records are you returning ?
the best way is to start a trace and see what is actually happening
the trace will also contain the real time spent on the database
http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx
Fetch minimum fields - just you need. Try to set in Adoquery:
qry.CursorType:=ctKeyset;
qry.LockType:=ltReadOnly;
qry.DisableControls;
try
qry.Open;
finally
qry.EnableControls;
end;
Thank you both for the suggestions. I'd hoped sinisav's suggestion would help, but it does not.
Geert_Gruwez - I don't have a component tied to my query. I never use DB components, I end up doing too many things with the data that's returned. So instead I just loop through the result set in my code. The query I'm currently runnign returns 15 records. I put a check in to time the query and it's taking 8.335 seconds just to do the "open" command. No time is spent building a list (or retrieving data from the result set). I take the query (using Clipboard.AsText := SQL.Text;) and paste it into MS Management Studio and that result set is returned in < 1 second.
I've just never seen anything like this before and I'm getting desparate!
Thanks for trying.
Mike
Geert_Gruwez - I don't have a component tied to my query. I never use DB components, I end up doing too many things with the data that's returned. So instead I just loop through the result set in my code. The query I'm currently runnign returns 15 records. I put a check in to time the query and it's taking 8.335 seconds just to do the "open" command. No time is spent building a list (or retrieving data from the result set). I take the query (using Clipboard.AsText := SQL.Text;) and paste it into MS Management Studio and that result set is returned in < 1 second.
I've just never seen anything like this before and I'm getting desparate!
Thanks for trying.
Mike
8 seconds to establish a connection seems quite excessive.
What does your connection string look like?
Where is your program running, relative to the database?
Is your program running with the same authentication as you are running the mgt. console?
What does your connection string look like?
Where is your program running, relative to the database?
Is your program running with the same authentication as you are running the mgt. console?
Maybe there is some additional parsing/processing of your string. Try the following. I tried eliminating the comments and the carriage returns.
SQL.Add('declare ')
SQL.Add('@dtDate date = ' + QuotedStr(FormatDateTime('yyyy-mm-dd', datePlanner.Date)) );
SQL.Add('@numDays integer = ' + IntToStr(iDayCount) + '; ');
SQL.Add('with DateInspection as ( ');
SQL.Add('select * from Inspection where cast(ScheduledDate as date) >= @dtDate and cast(ScheduledDate as date) < dateadd(day, @numDays, @dtDate) ), ');
SQL.Add('DateSummary as ( ');
SQL.Add('select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate, ');
SQL.Add('(select count(cast(ScheduledDate as date)) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate as date)) NumPerDay, ');
SQL.Add('(select count(cast(ScheduledDate as date)) from DateInspection ');
SQL.Add('where (cast(ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))=(cast(i.ScheduledDate as varchar) + cast(datepart(hh, ScheduledDate) as varchar))) NumPerHourPerDay ');
SQL.Add('from DateInspection i ');
SQL.Add('where ScheduledDate is not null and Cancelled=0 and PrintedByBUID is null ) ');
SQL.Add('select tt.StartDate, ds.*, (select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay, b.SalesOrder, cast(datepart(hh, ds.ScheduledDate) as varchar) InspHour ' );
SQL.Add('from (select * from TimeTable where cast(StartDate as date) >= @dtDate and cast(StartDate as date) < dateadd(day, @numDays, @dtDate)) tt ');
SQL.Add('left outer join DateSummary ds on tt.StartDate=cast(ds.ScheduledDate as date) ');
SQL.Add('left outer join Batch b on ds.BatchID=b.BatchID ');
SQL.Add('order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;');
Probably unrelated to your immediate problem, but:
1. Don't use any form of SELECT * or even SELECT alias.*, get in the habit of defining the columns you need and only the columns you need.
2. Using the something like the following condition is a very bad idea:
WHERE CAST(ScheduledDate AS date) >= @dtDate
AND CAST(ScheduledDate AS date) < DATEADD(day, @numDays, @dtDate)
If you have and indexes on ScheduledDate they will be ignored as you have an expression on the left hand side. What is the data type for ScheduledDate?
3. Instead of this: COUNT(CAST(ScheduledDate AS date)) simply use COUNT(*). The expression is pointless and even if the Query Optimizer is smart enough to figure that out, it just makes the code read better for someone else.
1. Don't use any form of SELECT * or even SELECT alias.*, get in the habit of defining the columns you need and only the columns you need.
2. Using the something like the following condition is a very bad idea:
WHERE CAST(ScheduledDate AS date) >= @dtDate
AND CAST(ScheduledDate AS date) < DATEADD(day, @numDays, @dtDate)
If you have and indexes on ScheduledDate they will be ignored as you have an expression on the left hand side. What is the data type for ScheduledDate?
3. Instead of this: COUNT(CAST(ScheduledDate AS date)) simply use COUNT(*). The expression is pointless and even if the Query Optimizer is smart enough to figure that out, it just makes the code read better for someone else.
I added a memo box and now can copy my test query into my Delphi app and run it in any format I desire. Below is my new query. Running this in Management Studio takes < 1 second. In my app it's 7.463 seconds.
If I comment out this one line: "(select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay," then the query returns in 1.640 seconds.
I have dozens of queries running in my Delphi apps that are more complex than this one. Most return hundreds or thousands of records (this one only returns 14 rows). Most return in < 1 second.
I've already changed what I'm doing just slightly to overcome this issue. I was just hoping that someone would have seen this before and have an answer.
Thanks for all the help.
Mike
declare
-- First date
@dtDate date = '2012-12-15',
-- Number of days to show
@numDays integer = 4;
with DateInspection as
(
select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, Cancelled, PrintedByBUID from Inspection where ScheduledDate >= @dtDate and ScheduledDate < dateadd(day, @numDays, @dtDate)
),
DateSummary as
(
select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate,
-- How many per day?
(select count(*) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate
-- How many per hour per day?
(select count(*) from DateInspection where ScheduledDate=i.ScheduledD
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
from (select * from TimeTable where StartDate >= @dtDate and StartDate < dateadd(day, @numDays, @dtDate)) tt
left outer join DateSummary ds on tt.StartDate=cast(ds.Sched
left outer join Batch b on ds.BatchID=b.BatchID
order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;
If I comment out this one line: "(select max(NumPerHourPerDay) from DateSummary where SDate=ds.SDate) MaxPosPerDay," then the query returns in 1.640 seconds.
I have dozens of queries running in my Delphi apps that are more complex than this one. Most return hundreds or thousands of records (this one only returns 14 rows). Most return in < 1 second.
I've already changed what I'm doing just slightly to overcome this issue. I was just hoping that someone would have seen this before and have an answer.
Thanks for all the help.
Mike
declare
-- First date
@dtDate date = '2012-12-15',
-- Number of days to show
@numDays integer = 4;
with DateInspection as
(
select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, Cancelled, PrintedByBUID from Inspection where ScheduledDate >= @dtDate and ScheduledDate < dateadd(day, @numDays, @dtDate)
),
DateSummary as
(
select InspectionID, BatchID, InspQuantity, ScheduledDate, SortOrder, cast(ScheduledDate as date) SDate,
-- How many per day?
(select count(*) from DateInspection where cast(ScheduledDate as date)=cast(i.ScheduledDate
-- How many per hour per day?
(select count(*) from DateInspection where ScheduledDate=i.ScheduledD
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
from (select * from TimeTable where StartDate >= @dtDate and StartDate < dateadd(day, @numDays, @dtDate)) tt
left outer join DateSummary ds on tt.StartDate=cast(ds.Sched
left outer join Batch b on ds.BatchID=b.BatchID
order by tt.StartDate, ds.ScheduledDate, ds.SortOrder;
>>I've already changed what I'm doing just slightly to overcome this issue. I was just hoping that someone would have seen this before and have an answer.
I see this every day, but on an oracle database
i never look at the delphi side for tuning the queries (allthough i am a delphi developer)
... all information is stored in the database
> how many buffergets, how many diskreads, and how long the query takes
buffergets relate to cpu, diskreads to io on disk, etc
based on the information on the database i look where indexes are needed
and if those don't help investigate if there is a better way of writing the query
> I rewrite the query and pass the advice on to the delphi devs
for me it looks like you have a query which needs tuning
> you need a mssql dba tuning expert for this
I see this every day, but on an oracle database
i never look at the delphi side for tuning the queries (allthough i am a delphi developer)
... all information is stored in the database
> how many buffergets, how many diskreads, and how long the query takes
buffergets relate to cpu, diskreads to io on disk, etc
based on the information on the database i look where indexes are needed
and if those don't help investigate if there is a better way of writing the query
> I rewrite the query and pass the advice on to the delphi devs
for me it looks like you have a query which needs tuning
> you need a mssql dba tuning expert for this
Premium Content
You need an Expert Office subscription to comment.Start Free Trial