FetchAll troubles when opening a large table with a sort
FetchAll troubles when opening a large table with a sort
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
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
- 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 :
- Here is a script to insert 1 000 000 records :
- 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
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.
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:We cannot influence such behaviour of SQL Server.
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();
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...
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...
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:
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";
Hello,
Thanks for your answer.
With my table with 2000000 records,
1) If I use TMSQuery.ReadOnly = True, this query is effectively very fast :
But, If just add two fields, I have again very bad results (50 seconds)
And this query is strangely immediate :
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...
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
Code: Select all
SELECT CODE,LIBELLE,REFERENCE FROM PMEELEMENT ORDER BY LIBELLE
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...
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...

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...

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)
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)
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