Parameterized queries slow

[JensFudge]

Hi,
I am currently helping a customer move a rather large project (2 million code lines) from Delphi 2007 to Tokyo.
In the D2007 version they have UniDAC 6.2.7
In the Tokyo version they have UniDAC 7.0.2

There must be a significant change between the two versions as parameterized queries are extremely slow in 7.0.2.
There is a table with 4 million records in a SQL Server 2012
The SQL is
select [Field1], [Field2] from Table1 where (Id1=:id1) and (Id2=:id2) and (Id3=:id3) order by Field1 ASC
The id fields are all Varchar fields, and there is an index consisting of these three fields.
In the D2007 version with UniDAC 6.2.7, the call to "open" or "Refresh" is a zero to 10-milliseconds thing.
In the Tokyo version with UniDAC 7.0.2 however, its a 7 to 9 seconds thing.... Not milliseconds... Seconds..

If I change the querystring and concatenate the values into the SQL, it is a zero to 10 milliseconds thing.

Can you verify that this is a 7.0.2 problem? or seomething else? Maybe there is a property I have to set now..

Thanks for your great products

Kind regards
Jens Fudge

[ertank]

Hello,

Just a suggestion to help you possibly narrow down the problem scope.
1) Can you compare SQL commands sent to server with UniDAC 6.2.7 and UniDAC 7.0.2.
TUniSQLMonitor component and dbMonitor utility combination can help you do that.

https://www.devart.com/dbmonitor/download.html

2) I am not sure, but it sounds like old project is not fetching all rows, but your new project is fetching all rows. For new version, check TUniQuery.SpecificOptions and see if "FetchAll" is set to True which is default for SQL Server. I do not know where that parameter is set for your old version.

Thanks & regards,
Ertan

[JensFudge]

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

• TUniConnection

• TSQLServerUniprovider

• TUniQuery

• TUniQuery

• TButton

• TButton

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

[ertank]

And there are 3,761,366 rows in the table

If it is not confidential, and you share a backup of 2012 version. I might try to execute same on my side as a test run. I know CPU and other hardware will have impact on runtime, but will be another test on 7.3.9 version of UniDAC.

If you want to keep it off the record, you can reach me from: ertan.kucukoglu [at] gmail.com

[JensFudge]

Thanks Ertan,

I am currently creating a version of the database that does not contain anything confidential.
I do realize however that I was mistaken in Server version, the one I can create a backup for you in, is in SQL Server 2014, and not as I posted in 2012.

I hope thats fine, and I will send it to you as soon as I am finished creating it. Just so you don't think I have forgotten, this might wait until 20180830 as I won't be in the office again until then

[JensFudge]

I have sent you an email Ertan, at the provided address,

Thanks for taking a look

Jens

[ertank]

Hello,

I got below results on SQL Server 2016 Express 64bit running on Windows 10 64bit OS. Project is compiled as 32bit Executable using Delphi 10.2.3 (with all hotfix and patches applied as today) on same computer.

Code used is exactly same given earlier in this thread. One difference is that I used "UniQuery1.Prepare()" for both parameterized and no parameter queries and used "GetTickCount()" for timing. There were no DB aware components, or DataSource (DBGrid or similar) linked to TUniQuery.

Database is a single table database, containing exact same data as JenFudge provided it.

My results are:
Parameterized queries execute time: 500 to 800 miliseconds. Lower numbers being 2nd or 3rd runs after data cached.
No parameter queries: 0 to 15 milliseconds. Higher numbers being initial runs without data cached on SQL Server.

Main problem as indicated in an email from JenFudge is that there is a routine calling such queries in a loop of about 100 times. Which becomes as a big delay.

Just wanted to provide another test case. My feeling is that there really seems to be a problem on that matter.

I will try to make same test run on a PostgreSQL server to see if this is server specific or a general parameter processing problem in UniDAC in a couple of days using exactly same data and hardware.

[ertank]

I completed my tests on PostgreSQL Server 10.5 64Bit

Structure is all same including indexes. Copied data using a CSV file.

It seems there is no difference of query executions with parameters and without parameters. On the other hand, query executions on PostgreSQL is always slower than is on SQL Server. Both parameterized and no parameter queries execute between 2400-3300ms lower end being after several executes.

This problem maybe specific to SQL Server after all.

[JensFudge]

I wonder if there is a Devart tech support reading this question?

[Stellar]

We investigated the issue with long execution of parameterized queries on the latest version of UniDAC 7.3.9., but unfortunately, did not find any problems. The execution times are comparable, and differ in a couple of milliseconds, it is acceptable.
The speed of execution of parameterized queries will be faster than not parametrized if the same queries are executed, but with different parameter values.
Since MS SQL Server will build a query execution plan only once and will use it in the future. The more complex the query is, the greater the difference in the execution time of parameterized and non-parameterized queries will be. For example:

Code: Select all

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').DataType := ftString;
UniQuery1.ParamByName('id2').DataType := ftString;
UniQuery1.ParamByName('id3').DataType := ftString;
UniQuery1.Prepare;

for i := 0 to 1000 do
begin
  UniQuery1.Close;
  UniQuery1.ParamByName('id1').AsString := id1;
  UniQuery1.ParamByName('id2').AsString := id2;
  UniQuery1.ParamByName('id3').AsString := id3;
  UniQuery1.Open;
end;

[Stellar]

The speed issue of executing parameterized queries was earlier discussed at the forum:
forums.devart.com/viewtopic.php?p=69497#69497

Substituting a value into a SQL query instead of parameters (or macros) can improve performance in queries that are rarely executed. If a query is frequently executed, and the parameters are often changed, then using this approach reduces performance compared to parameters usage.

If you want us to implement the feature, please post it at our user voice forum:
devart.uservoice.com/forums/104635-delphi-data-access-components/suggestions/1540101-forced-sending-queries-to-the-server-without-param
If the suggestion gets a lot of votes, we will consider the possibility to implement it.

[JensFudge]

Hi Stellar

Thanks for taking the time to reply. I'm however not quite sure I'm satisfied with your reply :-)
I have a real project with a real application running in production, where the performance is as you describe, acceptable and even fast. This is in Delphi 2007 with UniDAC 6.2.7

In Delphi Tokyo with UniDAC 7.0.2 it is much slower

I have built a test project in Delphi Tokyo with UniDAC 7.0.2 opening up this one table with and without parameterized queries. There is still a really bad performance on parameterized queries.

Ertan has very kindly assisted to see if he could reproduce the problem if he uses the same table as I do. So I created a backup of that one table, sent to Ertan, and he gets similar results. Parameterized queries are significantly slower on this particular table, with the indexes that are set.

I do appreciate the fact, that you can't reproduce the issue on another table in your SQL database. But since both Ertan and I can reproduce the issue with the backup of my SQL server database file, I would really like to ask if you could create a database with my backup file restored, and try the query on that?

[Stellar]

We have investigated the execution speed of parameterized queries for many times, and always came to the same conclusion that simple queries without parameters are executed faster than executing similar parametrized queries. Ertan sent us a test sample, which we also investigated and came to the same conclusions. Unfortunately, we cannot influence the behavior of MS SQL Server.

If the speed of execution of the same query differs significantly in different versions of UniDAC, then please check whether there is the specified issue with the latest version of UniDAC 7.3.9, for this you can download the trial version of UniDAC.
If the issue is reproduced on the latest version of UniDAC, please send us a full sample demonstrating this issue, as well as a script for creating database objects.

[JensFudge]

Thanks Stellar

I appreciate your answer. If I get time, I will get the latest version and reproduce the problem on that.
Until now the workaround will have to suffice

Have a great day, and thanks for the good components you produce

Kind Regards
Jens Fudge