How can I Improve query speed?
 

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?

How can I Improve query speed?
 

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?

How can I Improve query speed?
 

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

--

How can I Improve query speed?
 

How can I Improve query speed?
 

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?

How can I Improve query speed?