Link to home
Start Free TrialLog in
Avatar of Robert Becskei
Robert Becskei

asked on

Delphi , MSSQL 2008r2 , ftDateTime AdoQuery Parameter Slow ... String Fast

Hi all,

I have Delphi XE7 and MSSQL 2008r2 . Have quiet a large table here (50.000 records) . I have a DateTime field in MSSQL.

I have a Adoquery in delphi with parameter. :mydate ftDatetime , NumericScale 3 , Precision 23 , Size 16 , Value Null (default , I've let Delphi populate ) .

Problem is I provide the query the datetime from DateTimePicker1 like this :

  if ViewQuery.Active then ViewQuery.Close;
  ViewQuery.Parameters.ParamByName('mydate').Value:=DateTimePicker1.Date;
  ViewQuery.Open;

Open in new window


TimeOut after 30 secs .

I solved it like this , changed the parameter :mydate to string , NumericScale 0 , Precision 0 , Size = -1 , Value Null .

  if ViewQuery.Active then ViewQuery.Close;
  ViewQuery.Parameters.ParamByName('mydate').Value:=inttostr(yearof(DateTimePicker1.Date))+'-'+inttostr(monthof(DatetimePicker1.Date))+'-'+inttostr(dayof(DateTimePicker1.Date));
  ViewQuery.Open;

Open in new window


I send the date to SQL exactly like it is stored internally . This is lightning fast .

Can someone explain to me , why is it not working as fast when I specify the parameter as ftDateTime?

Regards
Robert
Avatar of ste5an
ste5an
Flag of Germany image

Try using adDBTimeStamp instead of ftDateTime.

ViewQuery.Parameters.ParamByName('mydate').ParameterObject.Type_ := adDBTimeStamp;
ViewQuery.Parameters.ParamByName('mydate').Value:=DateTimePicker1.Date;

Open in new window


Sometimes you'll end up in a unwanted conversion, which can cause this.

I send the date to SQL exactly like it is stored internally . This is lightning fast .
hmm, no. Don't mix up data storage and data format, which plays an important role when using date/times. Thus your column data type is for sure DATETIME?
Avatar of Robert Becskei
Robert Becskei

ASKER

Yes it is DateTime .

and I only have ftTimeStamp . I dont have anything with adDBTImeStamp . This is AdoQuery .

Conversion is happening 100% . The dateformat here is 09.03.2018 on the server it is 2018-03-09 .
You need to set it on the ParameterObject.Type_ in code.

Conversion is happening 100% . The dateformat here is 09.03.2018 on the server it is 2018-03-09 .
This is irrelevant.
To be sure what type is - make separate adoquery and fetch data from same table.
Then check that field DataType property and make sure that is ftDateTime.
assumptions ... really bad
if you want certain results then you need to tell the server explicitely what to do, don't leave it guessing

also depends on how you limit the results
the text of the query determines the performance

Conversion is happening 100% . The dateformat here is 09.03.2018 on the server it is 2018-03-09 .
> This is irrelevant.

This is not irrelevant ! you leave open the assumption that the server will convert the date correctly ... it might, but not always
where datefield = convert(datetime, :dt, 126)

Open in new window


query.paramByName('DT').AsString := FormatDateTime('yyyy-mm-ddThh:nn:ss.zzz', DateTimePicker1.Date); 

Open in new window


sample from
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
 CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601

always give the db the converted format
especially in international apps
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.