FetchAll troubles when opening a large table with a sort

jc82 · Post by **jc82** » Thu 03 Feb 2011 15:36

Hello

I have a problem using the FetchAll Property on a TMSQuery object.
My query is based on a table with 2 000 000 records.
When opening a list, I want to use the fetchAll=false property, Then Open is very fast, but in some case, it takes 2 minutes, instead of...1 second in most cases.
(fetchRows=100)

Ex :
- select * from MyTable : 1 sec
- select * from MyTable order By Field2 : 1 sec
- select Field2 from MyTable order by Field2 : 2 minutes !!! (it should be faster than sample2, I choose just one field !!)

It is very strange...
here is a code sample :

Msaccess::TMSConnection* l_Db = new Msaccess::TMSConnection(this);
l_Db->Options->MultipleActiveResultSets = true;
l_Db->Server = "xxxx";
l_Db->Username = "xx";
l_Db->Password = "xx";
l_Db->Database = "xxx";
l_Db->Connect();

Msaccess::TMSQuery* l_Query = new Msaccess::TMSQuery(this);
l_Query->CursorType = ctDefaultResultSet;
l_Query->FetchAll = false;
l_Query->FetchRows = 100;
l_Query->SQL->Text = "select Field2 from MyTable order by Field2";
l_Query->Connection = l_Db ;
l_Query->Execute();

Has someone an idea of what's wrong ?

Thanks a lot !

Using SDAC 4.80.058 with BCB5

AndreyZ · Post by **AndreyZ** » Fri 04 Feb 2011 13:00

Hello,

I cannot reproduce the problem. Please specify the following:
- the exact version of SQL server and client. You can find it in the Info sheet of TMSConnection Editor;
- the table structure (script) you are using.

jc82 · Post by **jc82** » Fri 04 Feb 2011 16:44

- I use SQL Server 2008 SP1 with Native Client. With the TMSConnection I have ClientVersion = "10.0.2531.0" and ServerVersion = "10.00.2531"

- To reproduce the problem you can use this table :

Code: Select all

CREATE TABLE [dbo].[PMEELEMENT](
	CODE [varchar](24) NOT NULL,
	[CODEBIBLIO] [varchar](3) ,
	[REFERENCE] [varchar](20) ,
	[TYPEELEM] [varchar](2) ,
	[CLENATURE] [numeric](38, 0) ,
	[LIBELLE] [varchar](100) NOT NULL,
	[LIBELLECOM] [varchar](4000) ,
	[LIBELLETEC] [varchar](4000) NULL,
	[CODEFAMILLE] [varchar](24) NULL,
	[CODEFABRICANT] [varchar](5) NULL,
	[CODEPOSTE] [varchar](5) NULL,
	[OPTIONPA] [varchar](1) ,
	[CODEFOURPRINC] [varchar](8) NULL,
	[CODESSTPRINC] [varchar](8) NULL,
	[PA] [numeric](38, 6) ,
	[COEFPERTE] [float] ,
	[DEBOURSE] [numeric](38, 6) ,
	[COEFFG] [float] ,
	[PR] [numeric](38, 6) ,
	[COEFBEN] [float] ,
	[PV] [numeric](38, 6) ,
	[COEFVENTE] [float] ,
	[OPTIONPVIMPOSE] [int] ,
	[TYPEPVIMPOSE] [varchar](1) ,
	[OPTIONPOSE] [int] ,
	[CLEMOPOSE] [numeric](38, 0) NULL,
	[TEMPS] [numeric](38, 6) ,
	[OPTCOMPOPOSE] [int] ,
	[OUVTARIFCOMPO] [varchar](1) ,
	[UNITEACHAT] [varchar](5) NULL,
	[UNITEVENTE] [varchar](5) NULL,
	[UNITESTOCK] [varchar](5) NULL,
	[RATIOSTOCK] [float] NULL,
	[RATIOVENTE] [float] NULL,
	[CATEGORIE] [varchar](1) ,
	[TVA] [varchar](1) NULL,
	[COMPTEACHAT] [varchar](13) NULL,
	[COMPTEVENTE] [varchar](13) NULL,
	[CLEMETRE] [numeric](38, 0) NULL,
	[GESTSTOCK] [int] ,
	[SUIVICHANTIER] [int] ,
	[IDIMAGE] [numeric](38, 0) NULL,
	[BIBLIOIMPORT] [varchar](30) NULL,
	[DEBBIBLIOIMPORT] [numeric](38, 6) ,
	[PVBIBLIOIMPORT] [numeric](38, 6) ,
	[PROTECTBIBLIOIMPORT] [varchar](20) NULL,
	[NOTE] [varchar](max) NULL,
	[DATECREATION] datetime NULL,
	[DATEMODIFICATION] datetime NULL,
	[TYPEUNITE] [varchar](1) ,
	[DERPA] [numeric](38, 6) ,
	[PAMP] [numeric](38, 6) ,
	[PAMF] [numeric](38, 6) ,
	[CODEGAMME1] [varchar](8) NULL,
	[CODEGAMME2] [varchar](8) NULL,
	[VALORISATIONSTK] [varchar](1) NULL,
	[GESTIONCONTREMARQUE] [int] ,
	[CODEBARRE] [varchar](20) NULL,
	[DATEMAJDERPA] datetime NULL,
	[STOCKPHYS] [numeric](38, 6) ,
	[EXIGIBILITE] [varchar](1) ,
	[BIBLIOLIEN] [varchar](400) NULL,
	[LIE] [numeric](38, 0) NULL,
	[ROWID] [timestamp] ,
 CONSTRAINT [BTG_ELEMENT_PK] PRIMARY KEY CLUSTERED 
(
	CODE ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [BTG_ELEMENT_LIBELLE] ON [dbo].[PMEELEMENT] 
(
	[LIBELLE] ASC
)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) ON [PRIMARY]
GO

- Here is a script to insert 1 000 000 records :

Code: Select all

declare
@i as numeric(38,0),
@l_Code varchar(24),
@l_Lib varchar(100)

SET @i=1
while @i Execute(); statement is very fast (immediat)

- with "SELECT LIBELLE FROM PMEELEMENT ORDER BY LIBELLE" the l_Query->Execute(); statement is very slow (20 seconds !) and with 2 000 000 records, 2 minutes...


very strange...

Thanks.

AndreyZ · Post by **AndreyZ** » Tue 08 Feb 2011 11:13

I wasn't able to reproduce such query execution time differrence. The "SELECT * FROM PMEELEMENT ORDER BY LIBELLE" statement execution took almost zero time and the "SELECT LIBELLE FROM PMEELEMENT ORDER BY LIBELLE" took about 2 seconds. The investigation showed that in the second case server response took about 2 seconds (almost all the time of query execution). The same results I had with ADO. You can check it using the following code:

Code: Select all

  TADOConnection* l_Db = new TADOConnection(this);
  l_Db->ConnectionString = "your_connection_string";
  l_Db->LoginPrompt = False;
  l_Db->Open();

  TADOQuery* l_Query = new TADOQuery(this);
  l_Query->CursorType = ctKeyset;
  l_Query->MaxRecords = 100;
//  l_Query->SQL->Text = "select * from pmeelement"; // fast
//  l_Query->SQL->Text = "select * from pmeelement order by libelle"; // fast
  l_Query->SQL->Text = "select libelle from pmeelement order by libelle"; // slightly more than 2 seconds
  l_Query->Connection = l_Db;
  Cardinal tm = GetTickCount();
  l_Query->Open();
  ShowMessage(IntToStr(GetTickCount() - tm));
  l_Query->Close();
  l_Query->Free();
  l_Db->Free();

We cannot influence such behaviour of SQL Server.

AndreyZ · Post by **AndreyZ** » Tue 08 Feb 2011 11:15

Please make sure you aren't using the TMSQuery.IndexFieldNames property. When this property is used, it turns on the FetchAll option and all data is fetched from the server. IndexFieldNames cannot work with not fetched data.

jc82 · Post by **jc82** » Tue 08 Feb 2011 15:00

No, I'm not using the IndexFieldNames property.

How can I test your ADO code with BCB5 ?

jc82 · Post by **jc82** » Tue 08 Feb 2011 16:29

I have made a rapid test using ADO in VB.
With a simple sample using "MaxRecords = 100" , the result is immediate in all cases.

You don't have such time difference, but you have a difference. Can you try with a table with more records to see if difference grows ?
In my sample you can delete all records in table PMEELEMENT and reuse my script to fill it with 2 000 000 lines.

I don't know what to do...

AndreyZ · Post by **AndreyZ** » Fri 11 Feb 2011 15:18

Hello,

We have investigated this question. This behaviour is connected with the specifity of SQL Server work. You can solve this problem in two ways:
1) set the TMSQuery.ReadOnly property to True;
2) add the "SET ROWCOUNT 1000;" string to the query in the following way:

Code: Select all

l_Query->SQL->Text = "SET ROWCOUNT 1000; select libelle from pmeelement order by libelle";

jc82 · Post by **jc82** » Tue 15 Feb 2011 11:06

Hello,
Thanks for your answer.

With my table with 2000000 records,
1) If I use TMSQuery.ReadOnly = True, this query is effectively very fast :

Code: Select all

SELECT LIBELLE FROM PMEELEMENT ORDER BY LIBELLE

But, If just add two fields, I have again very bad results (50 seconds)

Code: Select all

SELECT CODE,LIBELLE,REFERENCE FROM PMEELEMENT ORDER BY LIBELLE

And this query is strangely immediate :

Code: Select all

SELECT * FROM PMEELEMENT ORDER BY LIBELLE

I can't manage to use the fetchall property correctly...
With ADO and MaxRecords property, it is ok with all queries...

2) I can't use SET ROWCOUNT 1000; because if the end-user scrolls in the list, he wiil never see other records, he wiil only see the 1000 first...

AndreyZ · Post by **AndreyZ** » Thu 17 Feb 2011 14:42

We cannot influence such SQL Server behaviour.
When you set TADOQuery.MaxRecords to 100, ADO doesn't allow fetching more then 100 records from the server, and SDAC has the same behaviour using SET ROWCOUNT 100;

jc82 · Post by **jc82** » Wed 23 Feb 2011 16:33

Hello

Thanks for your help.
I have understood why we obtain such difference.
The difference is in the execution plan of sql server.

In the "select *" the entire query is slow but SQL Server begins to retrieve the first rows very quickly, instead of the other query with 3 fileds.

So when i use the SQLServer hint "OPTION(fast 1000) open is very fast !!

But now, I have others problems using MARS (MultipleActiveResultSets = true) and the transactions...

AndreyZ · Post by **AndreyZ** » Thu 24 Feb 2011 08:38

Please specify the problems you've encountered with MARS and transactions.

jc82 · Post by **jc82** » Thu 24 Feb 2011 09:24

Hello

I have 2 problems. I think I'ts not a specifity of SDAC, but MARS...:

- When i active MARS on the connection, I can't launch an explicit transaction with a TMSQuery using "BEGIN TRAN" :
(Error 3997 : A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.)

- When i open a TMSQuery with the fetchAll=FALSE property, I can't launch a TCustomConnection::Starttransaction until all records are fetched on this TMSQuery (Error 3988 : New transaction is not allowed because there are other threads running in the session)

AndreyZ · Post by **AndreyZ** » Thu 24 Feb 2011 15:18

You are right, these problems are connected with MARS, but not SDAC. You can find information about MARS and transactions here: http://blogs.msdn.com/b/cbiyikoglu/arch ... -3983.aspx