About Us

Jamie Collins

(John) wrote ...

I have a million row, three column table with no indexes in a Jet .mdb
database on my local machine. Using ADO in Excel VBA code, using

SELECT MyKeyCol, MyDataCol, Col1
FROM MillionRowTable

it takes 5.1 seconds to get all rows into a recordset.

Jamie, are you connecting to an Access DB or SQL db?

Despite what you may have read, MS Access is not a database. Rather,
it is both a database management tool and a RAD (rapid application
design) package for data centric applications. It can act as a front
end to a variety of databases but the most common are the Microsoft
trio: Jet, SQL Server and MSDE. The default database for MS Access is
Jet; in fact, even a non-Jet MS Access application stores the settings
for forms, reports, etc in Jet database tables. When I say I have a
Jet .mdb, I'm being very clear <g.

Now, some may argue that a .mdb is merely an ISAM but when we use
Jet's sql engine I thank we can call it a database. MS Access users
are more used to wizards and designers but there is sql behind the
scenes. So, even though Jet syntax is nowhere near SQL-92 standard, I
guess we can call a Jet .mdb a sql database <g.

Forgive my teasing. But if you mean Microsoft SQL Server then at least
say SQL Server or MSSQL.

It only takes
5.1s to get 1M records into a recordset?

Indeed and note the premises: Jet 4.0 .mdb and OLE DB provider, local
machine (spec?), three named columns, no WHERE or ORDER BY clauses
etc.

I exported the table to a database in the SQL Server 2000 installed on
my local machine and ran the same query in the same way (i.e. ADO
recordset using OLE DB provider in Excel VBA etc) and it took 10.7
seconds.

Would you recommend using ADO in Excel VBA code to assign a pivot's
recordsource?

Yes but only because I'm an advocate for using code, which is easier
to maintain than GUI processes (didn't someone say 80% of a
developer's time is spent on maintenance?) I have no information on
whether an ADO recordset as the source for an Excel pivot table gives
better 'performance' than the alternatives.

Jamie.

--

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Running speed of Micrsoft Query with Multiple parameters	Ken	Excel Discussion (Misc queries)	1	March 19th 07 11:21 PM
Improve speed of saving a large Excel file	Elmer	Excel Discussion (Misc queries)	3	February 5th 07 07:15 AM
Can you speed UP drag speed?	Ryan W	Excel Discussion (Misc queries)	1	October 24th 05 06:09 PM
How to improve visuality?	Metallo[_3_]	Excel Programming	6	October 8th 04 06:13 AM
Howto Improve speed?	Andy	Excel Programming	0	January 19th 04 04:39 PM

Menu

About Us