FetchAll troubles when opening a large table with a sort
Posted: 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
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