Re: ADO->Jet, seek method EOF, all subsequent setting the index fa
- From: Brett Morien <Brett Morien@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 25 Apr 2005 18:51:04 -0700
I think I had tried this method in one of my original attempts, but was
disappointed with the results. I wasn't sure, so I reimplemented using a
command object with a replaceable parameter, and there's too significant of a
performance hit compared to using table direct access with an index on the ID
column.
10,000 lookups using command method: 58.672 seconds
10,000 lookups using table direct method: .719 seconds.
So, as I'd determined, the best performing method available is to open a
table direct recordset into my important table, and use the Index property
and Seek methods to galavant around the table. I still have the issue where
seeking to a record which does not exist (while using a non-primary key
index) puts the cursor at an EndofFile position, and any further setting of
the Index property throws a COM error DB_E_ROWSNOTRELEASED indefinitely.
There isn't much information out there in google land or anywhere else on
this problem, so I'm wondering if I'm pioneering a new method of data access
(not likely), or if there's an easy fix somebody knows about.
- Brett
"Stephen Howe" wrote:
> > I'm using ADO in C++ to access a Jet 4.0 engine Access database. For
> > optimization reasons, we are leaving one main table open at all times,
> since
> > it needs to respond in real time, and opening/closing that table is very
> time
> > consuming.
>
> Is it? I am not sure what you mean by opening/closing a table.
>
> If I was doing this
>
> Looking for single Rows:
> I would setup a Command object with an-adhoc SQL query on the PK and Record
> object.
> This way you don't even have the expense of constructing a Recordset for a
> single row - expensive. Just keep feeding the Command parameter with a new
> parameter and close the Record object when finished. When you have _really_
> finished, destroy Command and Record object.
>
> Looking for multiple Rows:
> I would setup a Command object with an-adhoc SQL query on the PK and
> Recordset object. Just keep feeding the Command parameter with a new
> parameters and close the Recordset object when finished. When you have
> _really_ finished, destroy Command and Recordset object.
> Also consider using record binding - it gains about 10% for big number of
> records
>
> You want to minimise
>
> Command/Record/Recordset creation/destruction
> SQL Query Engine having to reevaluate the query every time round
> The above does just that - you just feed in another parameter every time you
> want more data and reuse existing temporary Stored Procedures, Record and
> Recordset objects.
>
> Stephen Howe
>
>
>
.
- References:
- ADO->Jet, seek method EOF, all subsequent setting the index fails
- From: Brett Morien
- Re: ADO->Jet, seek method EOF, all subsequent setting the index fails
- From: Stephen Howe
- ADO->Jet, seek method EOF, all subsequent setting the index fails
- Prev by Date: Re: ADO uninstall - how to?
- Next by Date: Re: ADO->Jet, seek method EOF, all subsequent setting the index fa
- Previous by thread: Re: ADO->Jet, seek method EOF, all subsequent setting the index fails
- Next by thread: Re: ADO->Jet, seek method EOF, all subsequent setting the index fa
- Index(es):
Relevant Pages
|
|
