Hi Ertan,
First of all. Thanks for trying to help.
Second, please note, that the project is actually the same project that I am lifting from Delphi 2007 to Delphi Tokyo, so the running code in both versions are exactly the same, safe that I have ensured that the strings are unicode
By using dbMonitor I have the following:
Version 6.2.7 - Delphi 2007
The SQL in the component is
select PriceEtcDate, PriceEtcValue from Data_PricesEtc where (IdCode=:IdCodeFieldValue) and (Id2=:Id2FieldValue) and (Id3=:Id3FieldValue) order by PriceEtcDate ASC
In dbMonitor that is translates to
select [PriceEtcDate], [PriceEtcValue] from Data_PricesEtc where (IdCode=:IdCodeFieldValue) and (Id2=:Id2FieldValue) and (Id3=:Id3FieldValue) order by PriceEtcDate ASC
With the Parameters Tab displaying the three parameters DataType as String[12], String[6] and String[5]
The duration is on the dbMonitor marked as 0.000
It returns all rows that I need, and in this case that is 15 rows.
Version 7.02 - Dephi Tokyo
it is exactly the same code that is run
And dbMonitor gives exaclt the same result, but the Data Type of the parameters tab is now WideString[12], WideString[6], WideString[5]
The duration is on the dbMonitor 7.703
As it's the same database I get all 15 rows returned.
Test project on Delphi Tokyo UniDac 7.0.2
Just to be certain that it's not something else going on, I have created a small test project with the follwing components
The code for one of the buttons is
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
UniQuery1.SQL.Text := 'select [PriceEtcDate], [PriceEtcValue] from Data_PricesEtc where (IdCode=:id1) and (Id2=:id2) and (Id3=:id3) order by PriceEtcDate ASC';
UniQuery1.ParamByName('id1').AsString := ' DKK/DKK';
UniQuery1.ParamByName('id2').AsString := 'RISIKO';
UniQuery1.ParamByName('id3').AsString := 'PAPIR';
UniQuery1.Prepare;
UniQuery1.Open;
end;
and for the other
Code: Select all
procedure TForm1.Button2Click(Sender: TObject);
var
s : String;
begin
s := StringReplace('select [PriceEtcDate], [PriceEtcValue] from Data_PricesEtc where (IdCode=:id1) and (Id2=:id2) and (Id3=:id3) order by PriceEtcDate ASC', ':id1', ''' DKK/DKK''',[rfReplaceAll]);
s := StringReplace(s, ':id2', '''RISIKO''',[rfReplaceAll]);
s := StringReplace(s, ':id3', '''PAPIR''',[rfReplaceAll]);
UniQuery2.SQL.Text := s;
UniQuery2.Open;
end;
I get the results I feared. Button 2 uses StringReplace to replace the three parameters, which yields execution time of 0.000, Button 1 has the parameters and to be fair, this time "only" 3.451.
In the stringReplace version the dbMonitor gives me
select [PriceEtcDate], [PriceEtcValue] from Data_PricesEtc where (IdCode=' DKK/DKK') and (Id2='RISIKO') and (Id3='PAPIR') order by PriceEtcDate ASC
and of course nothing in the Parameters tab
In the parameterized version I have
select [PriceEtcDate], [PriceEtcValue] from Data_PricesEtc where (IdCode=:id1) and (Id2=:id2) and (Id3=:id3) order by PriceEtcDate ASC
and WideStrings on the parameter tab
Both queries gives me all the records.
The Table is defined in a SQL 2012 SQLserver (I have also tried on 2016)
Code: Select all
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Data_PricesEtc](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[LastUpdate] [datetime] NULL CONSTRAINT [DF_Data_PricesEtc_LastUpdate] DEFAULT (getdate()),
[IdCode] [varchar](12) NOT NULL,
[Id2] [varchar](6) NOT NULL,
[Id3] [varchar](6) NOT NULL,
[PriceEtcDate] [datetime] NOT NULL,
[PriceEtcValue] [float] NOT NULL,
[Key1] AS (((right(' '+[IdCode],(12))+left([Id2]+' ',(6)))+left([Id3]+' ',(6)))+CONVERT([varchar](8),[PriceEtcDate],(112))) PERSISTED NOT NULL,
CONSTRAINT [PK_BasicDataPricesEtc] PRIMARY KEY CLUSTERED
(
[Key1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The Index is defined as
Code: Select all
CREATE UNIQUE NONCLUSTERED INDEX [IX_IdCodeId2ID3PricesEtcDate] ON [dbo].[Data_PricesEtc]
(
[IdCode] ASC,
[Id2] ASC,
[Id3] ASC,
[PriceEtcDate] ASC
)
INCLUDE ( [PriceEtcValue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70)
GO
And there are 3,761,366 rows in the table
It is not really an option changing anything in the database, as many customers are using this software, and a db-change is really a huge thing.
But that should not be a good plan anyway. As I explained the 6.2.7 version on D2007 works perfectly but the 7.0.2 version on D-Tokyo doesn't work fast enough with parameterized queries.
I hope I have given you enough information too look into this.
Kind Regards
Jens Fudge