Here's my setup:

Pivot table uses an ADODB connection to pull from a single Access
database table that contains about 1 million records. A user form
contains parameters that will filter the dataset so the ADODB
connection doesn't have to pull in the entire table.

The problem is, even with filters the query still moves at a slow
pace. Does the size of the table have an effect on the query
performance---ie, regardless of how or what's filtered, the bigger the
table the slower the query speed??

Is there a way to improve the query performance? Maybe adjusting the
"MaxBuffer" setting in the cache...not sure what that is even. Or
something else?

I always specified criteria using the SQL Where clause. This allows the
database to use it's own optimisation techniques to retrieve the data
fastest.

I've never used the Filters feature of ADO.
Perhaps using Server Side connection will speed things up - just a guess.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"John" wrote in message
om...
Here's my setup:

Pivot table uses an ADODB connection to pull from a single Access
database table that contains about 1 million records. A user form
contains parameters that will filter the dataset so the ADODB
connection doesn't have to pull in the entire table.

The problem is, even with filters the query still moves at a slow
pace. Does the size of the table have an effect on the query
performance---ie, regardless of how or what's filtered, the bigger the
table the slower the query speed??

Is there a way to improve the query performance? Maybe adjusting the
"MaxBuffer" setting in the cache...not sure what that is even. Or
something else?

(John) wrote ...

Pivot table uses an ADODB connection to pull from a single Access
database table that contains about 1 million records. A user form
contains parameters that will filter the dataset so the ADODB
connection doesn't have to pull in the entire table.

The problem is, even with filters the query still moves at a slow
pace. Does the size of the table have an effect on the query
performance---ie, regardless of how or what's filtered, the bigger the
table the slower the query speed??

Is there a way to improve the query performance? Maybe adjusting the
"MaxBuffer" setting in the cache...not sure what that is even. Or
something else?

I assume you are referring to filtering on the database server side
e.g. using a WHERE clause in a SELECT query

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.

If I add a WHERE clause to filter on an INTEGER column e.g.

WHERE MyIntCol = 18

the row count goes down to approx 60K rows, it takes a total of 2.2
seconds i.e. more filtering but less rows takes less time to fetch. If
I add a more complicated WHERE clause to get a similar row count e.g.

WHERE (MyKeyCol=18 AND MyDataCol < 190) OR Col1 < 23

it takes 7.4 seconds. So more complicated filtering has increased the
execution time.

If I add ORDER BY clauses, the execution time increases e.g.

SELECT MyKeyCol, MyDataCol, Col1
FROM MillionRowTable
ORDER BY MyDataCol

takes 35.8 seconds. Ordering has placed a large overhead on the
process.

This is just one table. Creating JOINs to other tables adds further
overhead.

Bear in mind none of my columns are indexed. Also, because I hadn't
run these queries before, no execution plans existed. For best
performance, I'd use stored procedures on the server side, with
parameters to take values from the client, and have indexes (and
regularly rebuild them <g) on the columns used to filter and sort.
And, because Jet determines the primary key to be the clustered index
(physical order) for the table, I'd choose the primary key very
carefully.

Do you get the impression there are a lot of factors involved? Rather
than ADO settings, the big factors are schema design and sql code. If
you post some details here (or perhaps in the .access.tabledesign
newsgroup) I'm sure you'll get some useful feedback on optimization.
You may want to start by reading this article:

http://support.microsoft.com/default...b;en-us;172199

" Visual Basic allows you to retrieve data from Jet databases (MDB
files) by using ... (SQL). These query operations can be made more
efficient by implementing some of the suggestions in this article."

Jamie.

--

(John) wrote ...

Thanks for the detailed reply, Jamie...that gives me a lot to look at
and play around with. Eventually, I'll need to go from Access to SQL
Server. Maybe that'll help speed things up too.

Better to get the schema/query design correct than rely on a 'faster'
system <g. Good luck.

Jamie.

--

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? It only takes
5.1s to get 1M records into a recordset? Are u using this recordset
for a pivot table? (I'm kinda new to sql and using Excel like
this...I'm better with Access.)

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

(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.

--