XE2 SELECT TOP XXX Problem/BUG

_Dejan_ · Post by **_Dejan_** » Thu 19 Jan 2012 13:54

Hi,
I using SDAC(Standard) long time in my projects together with Delphi 2009 but 2 weeks ago Im change Delphi 2009 with XE2(SP3)... Because HDD is die Im install Win 7 ultimate x64 (Before XP SP3 32bit)...
Last year we have problems with sql server because very often we get locked tables. 1 week ago we are find that problem is with SDAC component when we use TOP in SELECT query. Table is locked no mather if we have setup lockmode to lmNone on query, we are override locking issue when we change IsolationLevel on Connection from default ilReadCommitted to ilReadUnCommitted and now table(When we run select query) is not locked anymore but simple query still take more than 10 seconds.

I will try explain how we reproduce this error...
1.)We have table1 which have cca. 65.000.000 records
2.)On button click we run next query:

Code: Select all

try
  MSQuery1.SQL.Clear;
  MSQuery1.SQL.Add('SELECT TOP 20 table1.dt, table1.field1 + ' + QuotedStr(' ') + ' + CAST(table1.field2 AS varchar) AS test1, table1.field3 + ' + QuotedStr(' ') + ' + CAST(table1.field4 AS varchar) AS test2, table1.field5, table1.field6 FROM table1');
  MSQuery1.SQL.Add('WHERE table1.fieldx=:fieldx');
  MSQuery1.SQL.Add('ORDER BY table1.dt DESC');
  MSQuery1.ParamByName('fieldx').Value:='1234';
  MSQuery1.Open;
 finally
  MSQuery1.Close;
 end;

If for example we in database have only 10 records which have fieldx='1234' then query in SDAC component need more than 20 seconds to complete.

If for example fieldx='1234' have more than 20 records but not in last 2 years(records are inserted cca. 1-10/s) it still need 20 seconds or more...

if for example fieldx='1234' have more than 20 records but inserted in last few months records are returned fast but still a lot of slower than ADO return it.

Im try use prSQL and prNative and both produce same result.

Problem is not in SQL Server because ADO component return same result in few ms same as SQL Manager(All three are tested on same PC)!!!

Any suggestion?

AndreyZ · Post by **AndreyZ** » Thu 19 Jan 2012 16:56

Hello,

We need more information for the investigation. Please specify the following:
- a script to create the table1 table.
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of SQL server and client. You can learn it from the Info sheet of TMSConnection Editor.

_Dejan_ · Post by **_Dejan_** » Thu 19 Jan 2012 17:39

hi,
Thanks for your fast reply. I will provide you details now which I can(Im finish work for today and Im home)...
Details:
SDAC 6.1.4 for RAD Studio XE2(Standard Edition)

Microsoft SQL Server: 09.00.5000
Microsoft SQL Native Client: 9.00.3042.00

For Table structure if you need exact table structure I would like if is possible send it to you by email(I can't upload it here) if not I will rename all field names and post it here.
for Data in table you can make one for loop which insert cca. 50 milion records(I can make you demo example and send it to email if you want but I will need some time to do it...

AndreyZ · Post by **AndreyZ** » Thu 19 Jan 2012 17:55

I need only a script to create the table1 table, I don't need your data. You can send the script to andreyz*devart*com.

_Dejan_ · Post by **_Dejan_** » Fri 20 Jan 2012 06:02

AndreyZ wrote:I need only a script to create the table1 table, I don't need your data. You can send the script to andreyz*devart*com.

Hi Andrey,
Yesterday Im send you scripts. Please confirm that you are receive email.

AndreyZ · Post by **AndreyZ** » Fri 20 Jan 2012 12:22

I have investigated the problem. This problem occurs because SDAC doesn't retrieve information about parameters by default as ADO does. To solve the problem, you should set the TMSQuery.Options.DescribeParams property to True, and call the TMSQuery.Prepare method before its opening. Here is a code example:

Code: Select all

try 
  MSQuery1.SQL.Clear; 
  MSQuery1.SQL.Add('SELECT TOP 20 table1.dt, table1.field1 + ' + QuotedStr(' ') + ' + CAST(table1.field2 AS varchar) AS test1, table1.field3 + ' + QuotedStr(' ') + ' + CAST(table1.field4 AS varchar) AS test2, table1.field5, table1.field6 FROM table1'); 
  MSQuery1.SQL.Add('WHERE table1.fieldx=:fieldx'); 
  MSQuery1.SQL.Add('ORDER BY table1.dt DESC'); 
  MSQuery1.ParamByName('fieldx').Value:='1234'; 
  MSQuery1.Options.DescribeParams := True;
  MSQuery1.Prepare;
  MSQuery1.Open; 
finally 
  MSQuery1.Close; 
end;

_Dejan_ · Post by **_Dejan_** » Fri 20 Jan 2012 13:24

Im make few quick tests and looks that work ok. I will report again when I will change all queries in my big application

Thanks for your help and time.

AndreyZ · Post by **AndreyZ** » Mon 23 Jan 2012 11:05

We are looking forward to hearing from you.

_Dejan_ · Post by **_Dejan_** » Mon 23 Jan 2012 12:29

Im make some test but this do not work as expected. Sometime query return empty resultset. If I remove Prepare from code it return data...

_Dejan_ · Post by **_Dejan_** » Tue 24 Jan 2012 07:13

This do not work as must more than 50% of all query's do not work(Get exception when I open query)...
Here is one simple example:
1.)I put TMSQuery on Form(MDIChild):

Code: Select all

object Form_1_view2: TMSQuery
    Connection = DataModule1.Connection
    AutoCalcFields = False
    Options.DescribeParams = True
    Left = 40
    Top = 608
  end

2.)In code I open multiple queryies in same time with OpenDatasets() but if I again do not setup Connection again(No mather if Im in GUI already setup it I get exception.

Code: Select all

   Form_1_view2.Connection:=DataModule1.Connection;
   Form_1_view2.SQL.Clear;
   Form_1_view2.SQL.Add('SELECT * FROM stranke');
   Form_1_view2.SQL.Add('WHERE stranke.r_objekt_id=:r_objekt_id');
   Form_1_view2.ParamByName('r_objekt_id').Value:=DataSource1.DataSet.FieldByName('id').Value;
   Form_1_view2.Prepare;

...

   DataModule1.Connection.OpenDatasets([Form_1_view2,
                                        Form_2_LED]);

If I remove:

Code: Select all

Form_1_view2.Connection:=DataModule1.Connection;

from code get exception but this is another issue and is here in SDAC long time...
If I have in code Form_1_view2.Prepare then I get exception:

Code: Select all

Syntax error, permission violation, or other nonspecific error

If I remove Prepare then query run normaly.

DataSource1 return me right id so next line is not problematic same is if I manualy write some integer in code:

Code: Select all

DataSource1.DataSet.FieldByName('id').Value

Table structure is very simple:

Code: Select all

CREATE TABLE [dbo].[stranke] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [stranka_id] int NOT NULL,
  [r_objekt_id] int NOT NULL,
  [msrepl_synctran_ts] timestamp NOT NULL,
  [racunovodstvo_objekt_id] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [mc_racunovodski_klient] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [PK___1__32] PRIMARY KEY CLUSTERED ([id])
)

Any sugesstion why this simple thing do not work?

AndreyZ · Post by **AndreyZ** » Tue 24 Jan 2012 09:47

You have two mistakes in your code:
1. You have to define connection of all datasets that you want to open before opening them.
2. You have to set parameter values after preparing SQL statement.
Here is a code with all needed corrections:

Code: Select all

Form_1_view2.Connection:=DataModule1.Connection; 
Form_1_view2.SQL.Clear; 
Form_1_view2.SQL.Add('SELECT * FROM stranke'); 
Form_1_view2.SQL.Add('WHERE stranke.r_objekt_id=:r_objekt_id'); 
Form_1_view2.Prepare; 
Form_1_view2.ParamByName('r_objekt_id').Value:=DataSource1.DataSet.FieldByName('id').Value;
Form_2_LED.Connection := DataModule1.Connection;
DataModule1.Connection.OpenDatasets([Form_1_view2, Form_2_LED]);

Also, we recomend to use param properties that return the exact type of value (like AsInteger, AsString, etc..) instead of using the Value property.

_Dejan_ · Post by **_Dejan_** » Tue 24 Jan 2012 10:37

Hi AndreyZ,
Thanks for your help and time. I will try your last example.
1.)I have already created query for Form_2_LED in code but I didn't write it on forum... It is very similar as Form_1_view2(Connection, SQL ...) ...
2.)Im set parameter values before Prepare because you are on example few posts back do same thing

I usualy use AsInteger or some other type and Im try use it also in example but didn't help so Im leave Value...
I will test and report back...

AndreyZ · Post by **AndreyZ** » Tue 24 Jan 2012 13:09

You are right, I made a mistake in the code example, I used the Value property instead of AsString. It is possible to set parameter values before preparing if you don't use the Value property. If the Value property is used and the DescribeParams property is set to True, parameter values are set to unassigned values on preparing operation. To avoid this problem, you should use param properties that return the exact type of value. The following code example are correct:

Code: Select all

Form_1_view2.Prepare;
Form_1_view2.ParamByName('r_objekt_id').Value:=DataSource1.DataSet.FieldByName('id').Value;

Form_1_view2.ParamByName('r_objekt_id').AsInteger:=DataSource1.DataSet.FieldByName('id').AsInteger;
Form_1_view2.Prepare;

Form_1_view2.Prepare;
Form_1_view2.ParamByName('r_objekt_id').AsInteger:=DataSource1.DataSet.FieldByName('id').AsInteger;

, but this code example is not correct:

Code: Select all

Form_1_view2.ParamByName('r_objekt_id').Value:=DataSource1.DataSet.FieldByName('id').Value;
Form_1_view2.Prepare;