How do I speed it up

[Paul Mendlesohn]

I am looking up data in tables with 100k records and the module progresses
really slowly. I am using query defs to access the recordsets, perhaps this
is a slow way to do it, the code I am using is

strSQL = "PARAMETERS [PayerID] TEXT;"
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID
=[RecordID];"
Set qdf2 = CurrentDb.CreateQueryDef("", strSQL)
qdf2.Parameters("RecordID") = strSubMemID
Set rstSubs = qdf2.OpenRecordset
intPatientID= rstSubs.PAtientID

Is there a faster type of recordset to look up data, and if so how do I
write the WHERE clause (that's why I always use the query def procedure as
it's easy to write without all the '''')
Thanks
Paul

 

[Robert Morley]

I'm a little rusty on my DAO optimizations, but one thing that would speed
it up would be to create the query as a permanent query instead of as a
temporary query every time.

Also, make sure that you have an Index defined on SubMemID. Make it a
Unique Index or Primary Key of you can, as those will typically be faster,
but obviously if that's inappropriate, then make it a regular Index.

Also, I'm not sure how PayerID fits in here. It looks like you're creating
it as a parameter, but then you're never using it. Was that just a typo, or
is my DAO rustier than I realized?


Rob

 

[Olduke]

I'm a little rusty on my DAO optimizations, but one thing that would speed
it up would be to create the query as a permanent query instead of as a
temporary query every time.

Also, make sure that you have an Index defined on SubMemID. Make it a
Unique Index or Primary Key of you can, as those will typically be faster,
but obviously if that's inappropriate, then make it a regular Index.

Also, I'm not sure how PayerID fits in here. It looks like you're creating
it as a parameter, but then you're never using it. Was that just a typo, or
is my DAO rustier than I realized?

I'm sure you already do this, but I'll ask anyway.
How often do you run Repair/Compact Database?
If it's been a while, this may speed it up.

Rob

I am looking up data in tables with 100k records and the module progresses
really slowly. I am using query defs to access the recordsets, perhaps this
is a slow way to do it, the code I am using is

strSQL = "PARAMETERS [PayerID] TEXT;"
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID
=[RecordID];"
Set qdf2 = CurrentDb.CreateQueryDef("", strSQL)
qdf2.Parameters("RecordID") = strSubMemID
Set rstSubs = qdf2.OpenRecordset
intPatientID= rstSubs.PAtientID

Is there a faster type of recordset to look up data, and if so how do I
write the WHERE clause (that's why I always use the query def procedure as
it's easy to write without all the '''')
Thanks
Paul

 

[Paul Mendlesohn]

Thanks, the index made all the difference, from 3 hours to 4 minutes wow.
The parameter was typed wrong in my example, should have said PayerID.

It works fine now.
Best regards
Paul

I'm a little rusty on my DAO optimizations, but one thing that would speed
it up would be to create the query as a permanent query instead of as a
temporary query every time.

Also, make sure that you have an Index defined on SubMemID. Make it a
Unique Index or Primary Key of you can, as those will typically be faster,
but obviously if that's inappropriate, then make it a regular Index.

Also, I'm not sure how PayerID fits in here. It looks like you're creating
it as a parameter, but then you're never using it. Was that just a typo, or
is my DAO rustier than I realized?

I'm sure you already do this, but I'll ask anyway.
How often do you run Repair/Compact Database?
If it's been a while, this may speed it up.

Rob

I am looking up data in tables with 100k records and the module progresses
really slowly. I am using query defs to access the recordsets, perhaps this
is a slow way to do it, the code I am using is

strSQL = "PARAMETERS [PayerID] TEXT;"
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID
=[RecordID];"
Set qdf2 = CurrentDb.CreateQueryDef("", strSQL)
qdf2.Parameters("RecordID") = strSubMemID
Set rstSubs = qdf2.OpenRecordset
intPatientID= rstSubs.PAtientID

Is there a faster type of recordset to look up data, and if so how do I
write the WHERE clause (that's why I always use the query def procedure as
it's easy to write without all the '''')
Thanks
Paul

 

[BeWyched]

Thanks, the index made all the difference, from 3 hours to 4 minutes wow.
The parameter was typed wrong in my example, should have said PayerID.

It works fine now.
Best regards
Paul

I'm a little rusty on my DAO optimizations, but one thing that would speed
it up would be to create the query as a permanent query instead of as a
temporary query every time.

Also, make sure that you have an Index defined on SubMemID. Make it a
Unique Index or Primary Key of you can, as those will typically be faster,
but obviously if that's inappropriate, then make it a regular Index.

Also, I'm not sure how PayerID fits in here. It looks like you're creating
it as a parameter, but then you're never using it. Was that just a typo, or
is my DAO rustier than I realized?

I'm sure you already do this, but I'll ask anyway.
How often do you run Repair/Compact Database?
If it's been a while, this may speed it up.

Rob

Paul Mendlesohn wrote:
I am looking up data in tables with 100k records and the module progresses
really slowly. I am using query defs to access the recordsets, perhaps this
is a slow way to do it, the code I am using is

strSQL = "PARAMETERS [PayerID] TEXT;"
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID
=[RecordID];"
Set qdf2 = CurrentDb.CreateQueryDef("", strSQL)
qdf2.Parameters("RecordID") = strSubMemID
Set rstSubs = qdf2.OpenRecordset
intPatientID= rstSubs.PAtientID

Is there a faster type of recordset to look up data, and if so how do I
write the WHERE clause (that's why I always use the query def procedure as
it's easy to write without all the '''')
Thanks
Paul

 

[BeWyched]

Hi Paul

Your 4 minutes still seems very slow especially as your SQL statement looks
quite simple. e.g. I have a similar statement that searches against some 300k
records. It returns result in less than a couple of seconds.

A couple of things that might help:

1. Is the back-end data split from the front-end? If so, is the back-end
remote (on a WAN or similar) in which case performance will be lousy as
Access is very inefficient in moving data across networks. If this is the
case you might want to explore using SQL Server for the back-end instead.

2. You could try:

strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID = " & RecordID
' RecordID is your [RecordID] value
Set rstSubs = CurrentDb.OpenRecordset(strSQL)
intPatientID= rstSubs!PAtientID ' Note the !

This assumes RecordID is an integer. If it is a string use:
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID = '" & RecordID &
"'"
where '" and "'" are, exagerated ' " and " ' "

Cheers.

BW

Thanks, the index made all the difference, from 3 hours to 4 minutes wow.
The parameter was typed wrong in my example, should have said PayerID.

It works fine now.
Best regards
Paul

I'm a little rusty on my DAO optimizations, but one thing that would speed
it up would be to create the query as a permanent query instead of as a
temporary query every time.

Also, make sure that you have an Index defined on SubMemID. Make it a
Unique Index or Primary Key of you can, as those will typically be faster,
but obviously if that's inappropriate, then make it a regular Index.

Also, I'm not sure how PayerID fits in here. It looks like you're creating
it as a parameter, but then you're never using it. Was that just a typo, or
is my DAO rustier than I realized?

I'm sure you already do this, but I'll ask anyway.
How often do you run Repair/Compact Database?
If it's been a while, this may speed it up.

Rob

Paul Mendlesohn wrote:
I am looking up data in tables with 100k records and the module progresses
really slowly. I am using query defs to access the recordsets, perhaps this
is a slow way to do it, the code I am using is

strSQL = "PARAMETERS [PayerID] TEXT;"
strSQL = "SELECT * From SubscriptionMembers WHERE SubMemID
=[RecordID];"
Set qdf2 = CurrentDb.CreateQueryDef("", strSQL)
qdf2.Parameters("RecordID") = strSubMemID
Set rstSubs = qdf2.OpenRecordset
intPatientID= rstSubs.PAtientID

Is there a faster type of recordset to look up data, and if so how do I
write the WHERE clause (that's why I always use the query def procedure as
it's easy to write without all the '''')
Thanks
Paul