Board index » delphi » Retrieve ADO records faster than with the .Next method

Retrieve ADO records faster than with the .Next method

Hello!

 I have an Access database with aroun 100,000 records. My goal is to show
them all into a ListView.
 using ADO to retrieve them.
Unfortunately I found that using the .Next method to advance to the records
is very slow when we have to deal with so many records. It takes too much
time to fill the structures needed for ListView using the regular mechanism.

Is it any other faster way to access the underlying data from the recordset
? I mention that the selection query works fast enough, the slowest part is
when I read the rows from the record.

Thank you in advance,
Dan

 

Re:Retrieve ADO records faster than with the .Next method


try using other control.
ListView is the slower part.

Quote
"Dan Trutia" <soulofdes...@e{*word*277}.com> wrote in message

news:3bec491a_2@dnews...

Re:Retrieve ADO records faster than with the .Next method


Quote
>Is it any other faster way to access the underlying data from the recordset
>? I mention that the selection query works fast enough, the slowest part is
>when I read the rows from the record.

What is the problem with just using a tdbGrid to display the records?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Retrieve ADO records faster than with the .Next method


The DBGrid is good, but I would like a more customizable look. Of course, if
there is no other way, I will
create a new control which inherits DBGrid. By the way, if the program uses
dbgrid, then the BDE should be deployed. I want to avoid this.
Quote
> What is the problem with just using a tdbGrid to display the records?

Re:Retrieve ADO records faster than with the .Next method


Dan

BDE s not requiered for dbgrid
where did you get that idea from ???

David B.E        agros...@arava.co.il

Re:Retrieve ADO records faster than with the .Next method


Unfortunately not. I'm using it with the ownerdata property and after the db
records have been loaded into memory the control works very fast. The
slowest part is when I transfer the records from the recordset (ADO) into my
own structures. The ListView displays these structures, not directly the ADO
records (tryied, too slow).
Quote
> ListView is the slower part.

> "Dan Trutia" <soulofdes...@e{*word*277}.com> wrote in message
> news:3bec491a_2@dnews...

Re:Retrieve ADO records faster than with the .Next method


I was under this impression. After receiveing the previous message I have
checked and indeed, BDE is not required. Thank you.
Quote
"David B.E" <agros...@arava.co.il> wrote in message news:3bed3aa6_2@dnews...
> Dan

> BDE s not requiered for dbgrid
> where did you get that idea from ???

> David B.E        agros...@arava.co.il

Re:Retrieve ADO records faster than with the .Next method


Hi Dan,

Here is the combination I find fastest with large tables and ADO /
Access 2K:
- CommandType : cmdTableDirect
- CursorLocation : clUseServer
- CursorType: ctForwardOnly
- BlockReadSize : 1000 (has the same effect than DisableControls)

Caution: do not use First after you open the dataset - with this
combination of cursor type / cursor location, First will bring you on
the second record, not on the first one.

Instead of using the Fields property, use the Collect property (less
overhead):

MyMemoryValue := MyADODataSet.Recordset.Collect[0]; // Reads first field
...
MyADODataSet.Recordset.MoveNext;

I'd be interested to know if that also improves the speed for you.

Thrse

Re:Retrieve ADO records faster than with the .Next method


Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3bed6423_1@dnews...
| Hi Dan,
|
| Here is the combination I find fastest with large tables and ADO /
| Access 2K:
| - CommandType : cmdTableDirect
| - CursorLocation : clUseServer
| - CursorType: ctForwardOnly
| - BlockReadSize : 1000 (has the same effect than DisableControls)
|
| Caution: do not use First after you open the dataset - with this
| combination of cursor type / cursor location, First will bring you on
| the second record, not on the first one.

Really?  It should cause an error.  ctForwardOnly doesn't support First.

--

Quidquid latine dictum sit, altum viditur.
#319

Re:Retrieve ADO records faster than with the .Next method


Hi DRS,

Quote
> Really?  It should cause an error.  ctForwardOnly doesn't support

First.

Maybe the error is trapped somewhere?
In fact once you open the recordset in code, CursorType becomes
ctKeyset, but it apparently retains some behaviour from ctForwardOnly.

Thrse

Re:Retrieve ADO records faster than with the .Next method


Hello Therese,

Thank you for the idea. I will experiment it soon and I will keep you
informed of the results.

Today I tried to use DBGrid instead of ListView, but I noticed this
behavior: while at the top of the list the scrolling is fast, when I go to
the bottom of it the scrolling is much slower. I have tested it with
TADOQuery but it should be the same if TADOTable is used instead - the
datasource component is the same. However, just to be sure, I will
try it with TADOTable too.
Anyway, there were about 63,000 records. My goal is to handle at least
500,000 with
very small performance penalty...

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3bed6423_1@dnews...
Quote
> Hi Dan,

> Here is the combination I find fastest with large tables and ADO /
> Access 2K:
> - CommandType : cmdTableDirect

Re:Retrieve ADO records faster than with the .Next method


Hi Dan,

Quote
> Anyway, there were about 63,000 records. My goal is to handle at least
> 500,000 with
> very small performance penalty...

The application that uses the technique I mentioned reads 400,000
records in memory. According to the machine, the time to read them goes
from less then one minute to about three minutes. This is faster than
before, when the application used BDE. But in my case the memory data is
not used in a database application but in a graphic application, and is
not changed at runtime.

The problem with DBGrid is that you can not use it with any type of
cursors, otherwise it seems pretty fast, even with 400,000 records (I
just tried). Of course scrolling takes time, but I guess there will not
be many occasions where you will need scrolling through them - with as
many records, I suppose you will use filters or queries and search
methods...

Thrse

Re:Retrieve ADO records faster than with the .Next method


Hello,

  It works fantastically well ! Thank you very much. If you ever come to
Romania, you have a beer from me! :)

------------
SoftWise Development - Custom Software, Web & Graphic design
Visit our website at http://www.softwisedev.com

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3bed6423_1@dnews...
Quote
> Here is the combination I find fastest with large tables and ADO /
> Access 2K:
> I'd be interested to know if that also improves the speed for you.

Re:Retrieve ADO records faster than with the .Next method


Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3bed8bb7$1_1@dnews...
| Hi DRS,
|
| > Really?  It should cause an error.  ctForwardOnly doesn't support
| First.
|
| Maybe the error is trapped somewhere?
| In fact once you open the recordset in code, CursorType becomes
| ctKeyset, but it apparently retains some behaviour from ctForwardOnly.

How can that be?  It defeats the whole purpose of ctForwardOnly!  If you
want a KeySet you should specify it.  ForwardOnly exists to reduce the load
on the server - a load which increases significantly with a KeySet cursor.

Everything you describe is screwy and contrary to the way ADO is designed
and intended to work.

--

Quidquid latine dictum sit, altum viditur.
#319

Re:Retrieve ADO records faster than with the .Next method


Hi DRS,

Quote
> How can that be?  It defeats the whole purpose of ctForwardOnly!  If
you
> want a KeySet you should specify it.  ForwardOnly exists to reduce the
load
> on the server - a load which increases significantly with a KeySet

cursor.

Sorry, I  made a confusion with ctDynamic. When you specifiy clUseServer
with either ctOpenForwardOnly, ctDynamic or ctUnspecified, you encounter
the same problem with First. But the cursor type is changed to ctKeyset
only in the last two cases.

Quote
> Everything you describe is screwy and contrary to the way ADO is
designed
> and intended to work.

What do you mean by "everything"? ;-)
If you mean that calling First in the configuration I mentioned will
generate no error and bring you on the (CacheSize + 1)th record, you can
verify that yourself very easily. If you get different results, I am
ready to reconsider the question...
I agree that's not the way it's supposed to work. But it does the same
thing with asynchronous fetches and that's not the way it's supposed to
work either...

Thrse

Go to page: [1] [2]

Other Threads