Hello,
we are using UniDAC with Interbase 2009 and Oracle 8.0.5 and everything works really fast.
Now I tried MS SQL-Server 2008 RC2 and I have sometimes poor performance. One example:
I made a small testprogram, which makes for every table of the database
TUniTable.Open;
...
TuniTable.Close;
In DBMonitor TUniTable.Open takes
with Interbase 0,000 sec.
with Oracle 0,000 - 0,020 sec.
with Sql-Server 0,000 - 30,000 sec.
So my questions are: Why is TUniTable.Open in SQL-Server so slow? And what can I do in UniDAC to speed up this behavior?
Thanks in advance for any idea.
Kind regards,
Gerd Brinkmann
invent GmbH
Performance question for UniDAC and MS SQL-Server
Hello,
The difference in the performance is connected with the default value of the FetchAll specific option. In the MSProvider the value of this option is set to True, in the OraProvider it is set to False. Please set this options to False in the TUniTable for MSSQL like
MyUnitable.SpecificOptions.Values['FetchAll']:= 'false';
and check the performance again.
If this option is set to false then the number of rows that will be transferred across the network at the same time is set in the TUniTable.FetchRows property. If it is set to true, all record from a table will be transferred at the same time.
The difference in the performance is connected with the default value of the FetchAll specific option. In the MSProvider the value of this option is set to True, in the OraProvider it is set to False. Please set this options to False in the TUniTable for MSSQL like
MyUnitable.SpecificOptions.Values['FetchAll']:= 'false';
and check the performance again.
If this option is set to false then the number of rows that will be transferred across the network at the same time is set in the TUniTable.FetchRows property. If it is set to true, all record from a table will be transferred at the same time.
Hello,
thank you for this information. I tried this and it works.
But this is no real solution. I have more than 500 TUniTables in my projects. And most of them are created without a form. I have no time to change them all...
What is the reason for this difference between MSProvider and OraProvider? When I use UniDAC, I wish that the components have for any provider the same behavior. Or there is a big documentation with all differences.
Is there anything that you can do? I think, that it's impossible to set FetchAll = false as default in the next version of TuniTable. Maybe you can make a global specific option for TUniConnection.
Kind regards,
Gerd Brinkmann
invent GmbH
thank you for this information. I tried this and it works.
But this is no real solution. I have more than 500 TUniTables in my projects. And most of them are created without a form. I have no time to change them all...
What is the reason for this difference between MSProvider and OraProvider? When I use UniDAC, I wish that the components have for any provider the same behavior. Or there is a big documentation with all differences.
Is there anything that you can do? I think, that it's impossible to set FetchAll = false as default in the next version of TuniTable. Maybe you can make a global specific option for TUniConnection.
Kind regards,
Gerd Brinkmann
invent GmbH
Hello,
You can use the following code to set the FetchAll specific option to False in all TUniTable instances that are connected to TUniConnection:
for i := 0 to UniConnection1.DataSetCount - 1 do
if UniConnection1.DataSets is TUniTable then
(UniConnection1.DataSets as TUniTable).SpecificOptions.Values['FetchAll']:= 'false';
for example, you can add this code to the OnCreate event.
We will investigate the possibility of change the default value of this option in one of the next builds/versions of UniDAC.
You can use the following code to set the FetchAll specific option to False in all TUniTable instances that are connected to TUniConnection:
for i := 0 to UniConnection1.DataSetCount - 1 do
if UniConnection1.DataSets is TUniTable then
(UniConnection1.DataSets as TUniTable).SpecificOptions.Values['FetchAll']:= 'false';
for example, you can add this code to the OnCreate event.
We will investigate the possibility of change the default value of this option in one of the next builds/versions of UniDAC.
Hi,
sorry, but this will not work. No, it will work only with a small projekt, one form with a TUniConnection and a TUniTable.
For example I have a project with 1 Datamodule which contains the TUniConnection, TUniConnectDialog, TInterBaseUniProvider, TOracleUniProvider, TSQLServerProvider and TUniSqlMonitor. And this project contains 50-60 forms, each form with 1-6 TUniTables. The forms will created by request.
When you say, I should use this code in the "OnCreate event" than the only place is the OnCreate event of every form.
And what ist about the 200 TUniTables, which are created by code:
MyUniTable := TUniTable.create (nil);
MyUniTable.Connection := ...
MyUniTable.Tablename := ...
MyUniTable.Open;
I see no solution for this.
I think, the only way is to replace TUniTable with a better version of this component.
Some thoughts to the possibilty of changing the default value:
As a UniDAC user I have to trust that all components (TUniQuery, TUniTable ... until CRBatchMove) uses the same default parameters. That is the reason why we are using UniDAC. One code, different databases! It's no problem to set some specific options in TUniConnection. That's normal: Charset, language, date format and so on. But it's a bad surprise, that I have to set specific options in the components!
And I wonder, whether I am the only one who use UniDAC with Oracle und SQL-Server. This problem must be known at DevArt for a long time.
And at least: Please give me an answer to my question from yesterday. What is the reason for this difference between MSProvider and OraProvider?
Kind regards,
Gerd Brinkmann
invent GmbH
sorry, but this will not work. No, it will work only with a small projekt, one form with a TUniConnection and a TUniTable.
For example I have a project with 1 Datamodule which contains the TUniConnection, TUniConnectDialog, TInterBaseUniProvider, TOracleUniProvider, TSQLServerProvider and TUniSqlMonitor. And this project contains 50-60 forms, each form with 1-6 TUniTables. The forms will created by request.
When you say, I should use this code in the "OnCreate event" than the only place is the OnCreate event of every form.
And what ist about the 200 TUniTables, which are created by code:
MyUniTable := TUniTable.create (nil);
MyUniTable.Connection := ...
MyUniTable.Tablename := ...
MyUniTable.Open;
I see no solution for this.
I think, the only way is to replace TUniTable with a better version of this component.
Some thoughts to the possibilty of changing the default value:
As a UniDAC user I have to trust that all components (TUniQuery, TUniTable ... until CRBatchMove) uses the same default parameters. That is the reason why we are using UniDAC. One code, different databases! It's no problem to set some specific options in TUniConnection. That's normal: Charset, language, date format and so on. But it's a bad surprise, that I have to set specific options in the components!
And I wonder, whether I am the only one who use UniDAC with Oracle und SQL-Server. This problem must be known at DevArt for a long time.
And at least: Please give me an answer to my question from yesterday. What is the reason for this difference between MSProvider and OraProvider?
Kind regards,
Gerd Brinkmann
invent GmbH
Hello,
This behavior is connected with the specificity of SQL Server - SQL Server doesn't allow you to have unfetched cursor by default, it possible only if the MARS (Multiple Active Result Sets) option is set to true (this option is available starting with MS SQL 2005). So we should set the FetchAll property to true by default.
This behavior is connected with the specificity of SQL Server - SQL Server doesn't allow you to have unfetched cursor by default, it possible only if the MARS (Multiple Active Result Sets) option is set to true (this option is available starting with MS SQL 2005). So we should set the FetchAll property to true by default.