🚀 go-pugleaf

On Tue, 8 Aug 2006 18:04:33 +0300, "Cenk" <cenk1536@yahoo.com> wrote:

>but this loop takes appox. 45 minutes to complete!
Argh! This seems an enormous amount of time...

> CountQuery->CommandText = "select [bid],[fTime] from ["+table+"] order by 
>[fTime] ASC";
Have you tried to create an index for the ftime field?  It's possible it could
help...

Also, you can try to call "vector<T>::reserve()" before you start to insert
items: this maybe would reduce the amount of reallocations to make room for
your data.

Hope this helps!

Ciao

Giuliano

- Use server-side cursor for this procedure. This way you will not cache all 
your rows in memory.

- Use fields variables:

TField* fBid = CountQuery->FieldByName ("bid");
..

while ...
  v_time.push_back(fBid->AsDateTime);
...

How much time takes line CountQuery->Open(), and how much - while loop?

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Cenk" <cenk1536@yahoo.com> �������/�������� � �������� ���������: 
news:44d8a854@newsgroups.borland.com...
> Hi,
>
> i have a code sample below,
>
> TADODataSet *CountQuery = new TADODataSet(this);
>  CountQuery->ConnectionString = "Provider=SQLOLEDB.1;Integrated 
> Security=SSPI;Persist Security Info=False;Initial Catalog=Raw;Data 
> Source=CENK";
>  CountQuery->CommandTimeout = 0;
>
>
>  if  (CountQuery->Active)
>     CountQuery->Close ();
>
>
> CountQuery->CommandText = "select [bid],[fTime] from ["+table+"] order by 
> [fTime] ASC";
> CountQuery->Open ();
>
>  while (!CountQuery->Eof) -------> table has 1000000 rows of data inside 
> but this loop takes appox. 45 minutes to complete!
>  {
>
>     v_bid.push_back(CountQuery->FieldByName ("bid")->AsFloat);
>
>           v_time.push_back(CountQuery->FieldByName ("fTime")->AsDateTime);
>
>           CountQuery->Next ();
>        }
>
> why is this too slow? any suggestions?
> thanks
> 

Can you give me an example of server side cursor? 

Hi,

i wanna know, which one is faster?

ExecSQL() or the code segment below,

ADOQuery1->Insert();
ADOQuery1->FieldByName("id")->AsInteger = 2 ;
ADOQuery1->FieldByName("Name")->AsString = "sms";
ADOQuery1->Post();

//Before open query:
CountQuery->CursorLocation = clUseServer;

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Cenk" <cenk1536@yahoo.com> �������/�������� � �������� ���������: 
news:44d8f8de$1@newsgroups.borland.com...
> Can you give me an example of server side cursor?
> 

Prepared TADOCommand should faster for MS SQL Server. For MS Access 
ADOQuery->Inser()/Post() may be faster in some cases. For MS SQL Server the 
fastest method (> 100 000 rows per second on simple table without indexes) 
is bulk copy rowsets (IRowsetFastLoad interface), but it is low-level OLEDB 
interface and it is not available from ADO.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Cenk" <cenk1536@yahoo.com> �������/�������� � �������� ���������: 
news:44d90947@newsgroups.borland.com...
> Hi,
>
> i wanna know, which one is faster?
>
> ExecSQL() or the code segment below,
>
> ADOQuery1->Insert();
> ADOQuery1->FieldByName("id")->AsInteger = 2 ;
> ADOQuery1->FieldByName("Name")->AsString = "sms";
> ADOQuery1->Post();
>
> 

You have been given some good advice here. I'll try to add to it.

"Cenk" <cenk1536@yahoo.com> wrote in message 
news:44d8a854@newsgroups.borland.com...
> Hi,
>
> i have a code sample below,
>
> TADODataSet *CountQuery = new TADODataSet(this);
>  CountQuery->ConnectionString = "Provider=SQLOLEDB.1;Integrated 
> Security=SSPI;Persist Security Info=False;Initial 
> Catalog=Raw;Data Source=CENK";
>  CountQuery->CommandTimeout = 0;

// Why aren't you using a TADOConnection and creating the 
TADODataset on it.  Is there any particular reason you need a new 
connection for the dataset?

>
>

// You have just created CountQuery.  There's no reason for this.

>  if  (CountQuery->Active)
>     CountQuery->Close ();
>
>
> CountQuery->CommandText = "select [bid],[fTime] from 
> ["+table+"] order by [fTime] ASC";

// "ASC" is the default,but ifyou're a stickler ...
// When you know you'regoing to be dealing with a lot of records 
...
CountQuery->CursorLocation = clUseServer;
CountQuery->CacheSize = 1000;
CountQuery->LockType = ltReadOnly;
CountQuery->CursorType = ctOpenForwardOnly;

> CountQuery->Open ();

// As VVV has said:
    VField * fBid = CountQuery->FieldByName ("bid");
    VField * fTime = CountQuery->FieldByName ("fTime");
// This avoids the overhead of looking up the field by name on 
each iteration of the loop.

// We're guessing the table may contain 1000000 rows.  So let's 
be generous.
    v_bid.reserve( 1500000 );
    v_time.reserve( 1500000 );
// Note that if there are 1500001 rows, the vectors will realloc 
to 3000000 elements.

>
>  while (!CountQuery->Eof) -------> table has 1000000 rows of 
> data inside but this loop takes appox. 45 minutes to complete!
>  {
>
>     // v_bid.push_back(CountQuery->FieldByName 
> ("bid")->AsFloat);
        v_bid.push_back( fBid->AsFloat );
>
>          // v_time.push_back(CountQuery->FieldByName 
> ("fTime")->AsDateTime);
        v_time.push_back( fTime->AsDateTime );
>
>           CountQuery->Next ();
>        }
>
> why is this too slow? any suggestions?

Yes, see above.

> thanks

You're welcome.

As someone else said, you'd need an index on fTime for the ORDER 
BY for decent performance.

ADO needs to be pretty finely tuned for good performance.  It 
might take some experimentation.

Good Luck,

- Arnie