How to speed up querying of 1 million records in a table?

[Guest]

How to speed up querying of 1 million records in a table? I know websites do
this all the time with their data. How else does one of a million bank
customers log in to their Bank of America account in a matter of a second (or
less!)

I want an employee to be able to type an account # in a text box on a Access
form then immediately see a list box pop up with all of the order #s
associated with the order specified. That seems to work perfectly and quick.

But then in my case, the employee selects/highlights one or several of the
order #s that pop up in the list box. Then the click "View Order Details"
and that is where things get really slow.

I have a piece of code that Dirk helped me write to shows a query that
contains the order details for all of the selected orders. It works, but
really slowly. We are talking several minutes to retrieve and dispaly the
Order details for the selected orders. What can I do to speed the retrieval
up when the users clicks the button to view the rental order details?

Private Sub cmdViewROEs_Click()
Dim strROEs As String
Dim varItem As Variant

With Me!lstROEs
If .ItemsSelected.Count = 0 Then
MsgBox "You have not selected any ROEs for display."
Else
' Make delimited list of orders.
For Each varItem In .ItemsSelected
' Note: this assumes that the field ROE is numeric.
' strROEs = strROEs & "," & .ItemData(varItem)

' If it's text, use this:
strROEs = strROEs & _
",'" & .ItemData(varItem) & "'"

Next varItem

' drop leading comma
strROEs = Mid$(strROEs, 2)

' Add an operator to the list.
If .ItemsSelected.Count > 1 Then
strROEs = "In (" & strROEs & ")"
Else
strROEs = "= " & strROEs
End If

' Update the SQL property of the query we'll
' use to display the orders.
CurrentDb.QueryDefs("qryDisplayROEsInfo").SQL = _
"SELECT * FROM orderdata WHERE [HeroRO] " & strROEs

' Open the query to display the orders.
'DoCmd.OpenQuery "qryDisplayROEsInfo"


End If
End With

End Sub

 

[John W. Vinson]

How to speed up querying of 1 million records in a table? I know websites do
this all the time with their data. How else does one of a million bank
customers log in to their Bank of America account in a matter of a second (or
less!)

By using a big honking SQL/Server or Oracle server farm with dozens of
high-end computers servicing the request... not with a desktop Access

I want an employee to be able to type an account # in a text box on a Access
form then immediately see a list box pop up with all of the order #s
associated with the order specified. That seems to work perfectly and quick.

But then in my case, the employee selects/highlights one or several of the
order #s that pop up in the list box. Then the click "View Order Details"
and that is where things get really slow.

I have a piece of code that Dirk helped me write to shows a query that
contains the order details for all of the selected orders. It works, but
really slowly. We are talking several minutes to retrieve and dispaly the
Order details for the selected orders. What can I do to speed the retrieval
up when the users clicks the button to view the rental order details?

The first thing to check is... is HeroRO indexed in your table? I'd sure hope
so!

Access doesn't always optimize IN() clauses very well. You might do better to
create a little temp table with one field, HeroRO if you wish (the name is
immaterial); empty the table with a delete query, and have your code (or a
subform) fill this table with the criteria records. To do the retrieval just
Join this table to your master table.

John W. Vinson [MVP]

 

[Guest]

The linked table was not indexed. As soon as I made a copy of it (which took
a while), then queried against it with an index applied, it was lightning
fast!

How can I get around having to schedule an early am process to go make a
copy of this linked table (which I don't have ability to index) and then me
index it before employees get in and want to tap into this table/query in the
new db.

 

[John W. Vinson]

The linked table was not indexed. As soon as I made a copy of it (which took
a while), then queried against it with an index applied, it was lightning
fast!

Hah! Funny thing about that. Yes, indexes do help.

How can I get around having to schedule an early am process to go make a
copy of this linked table (which I don't have ability to index) and then me
index it before employees get in and want to tap into this table/query in the
new db.

Now why in heck can't the owner of this table index it!? Not having the
primary key indexed is totally foolish! Possibly it is indexed, and you have
it linked in such a way that Access isn't aware of the index? Try relinking it
and specifying this field as the primary key.

If it's not indexed, you'll need to do several steps to import it. With the
size of the table, I'd really recommend making a local copy of the table with
the appropriate indexes (you might need more than one). Daily, run a Delete
query to empty the table (don't delete the table, just delete all the
records); Compact the database (since Access won't recover the space occupied
by these million rows!); then run an Append query to populate it. If there are
(old? no longer relevant?) records that you won't need to retrieve, make it a
selective append query to include only records that you can be sure will be of
use.

John W. Vinson [MVP]

 

[David W. Fenton]

Access doesn't always optimize IN() clauses very well.

Another way to get an IN() clause to behave better is to take the
list and replace the commas with " OR", then pass it through
Application.BuildCriteria. That will change this WHERE clause:

WHERE OrderID IN (123, 456, 789)

to this:

WHERE OrderID=123 OR OrderID=456 OrderID=789

Application.BuildCriteria is an interface to the same part of the
expression service that processes the criteria you type into the QBE
critera cells, e.g., converting "Null" into "Is Null" and "*asdf*"
into LIKE "*asdf*". I find it very handy when doing custom query by
form.

 

[David W. Fenton]

Daily, run a Delete
query to empty the table (don't delete the table, just delete all
the records)

But do *not* delete the records by viewing the table, selecting all
records and hitting the DELETE key -- this will take a long, long,
long, long, long time with 1 million records. Instead, write a
DELETE query to do it. Jet is smarting enough to simply drop all the
records in one operation that takes place nearly instantly.