efficient way of selecting if

[Mike Green]

Hi Guys
Can someone advise on the most efficient way of doing this please.
I have a form that list all the records in a database. The user can select
the records listed via an option group that has the different record
categories. When the category is selected then the SQL in the back ground
changes the records on the page to show the filtered records. There are 5
options in the option group option one, option two, option three, option
four and option all records (five). At the moment I am using an after
update event on the option frame like
If me.Framex = 1 then getSQLone
else
if me.Framex = 2 then getSQLtwo
etc etc

But this seems to take a while to run is there a better way?
Thanks in advance.
Mike

 

[Linq Adams via AccessMonster.com]

"But this seems to take a while to run"

What exactly do you mean by "a while?"

How many records, total, are we talking about here?

It will probably help if you post an example of you SQL code.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Mike Green]

Hi Linq

The SQL is fine, if I run the individual SQL it will resolve instantly,
there are 615 records to filter. Previously each SQL was run from its own
command button. This is a legacy Access database that I have inherited and
the schema is not ideal. When I use the if statements to select which SQL
to run then the system seems to pause for about 2 minutes before selecting
the SQL and then presenting the results. I was just looking for a better
way of using the If statements because that is the only other thing that I
have introduced and now there is a delay in returning the results.

Your advice with the If statements will be appreciated.

 

[Albert D. Kallal]

Hi Linq

The SQL is fine, if I run the individual SQL it will resolve instantly,
there are 615 records to filter.

615 records is so small, that other VERY VERY significant issue or detail
must be at stake here.

you don't mention the total size of the database here. perhaps you have 75
million records, and your results are 600 records? Perhaps you have 610
records, and your sql results in 600 of those records? As you can see the
number of records read being returned is not a whole heck very useful
information and unless you give me some ideas as the total datasets eyes
that you're searching.

As a general rule, you can pull and filter about 100,000 records in well
under a 1/2 second, or even faster in ms-access when all things are ideal.
As I said other details such as networks etc would have to be brought into
this discussion to trace down what your problem here is.

Assuming a small file of 615 records, that likely should be returned BEFORE
you mouse click finished it's upward travel as a result of a click...

When I use the if statements to select which SQL to run then the system
seems to pause for about 2 minutes before selecting the SQL and then
presenting the results.

ok, detail time:

is your data base split?, is a network involved ? Is a wan involved? are you
using sql server?

Your advice with the If statements will be appreciated.

MS access can easily execute about 80 million or more "if's" per second. So,
the "if" is not the probem here.

This is a old story about someone telling me they took a rope, but forgot to
tell me a cow is attached to the rope.

What does the "getSQLtwo" function do here? How does this function produce
its sql?

Right off the top of the bat, if your databases is split then I would
suggest implementing what is called a persistent connection. You seem to
hint that the SQL when one run by itself instantly runs -- thus the symptoms
you present are typically fixed when you implement the persistent
connection. However, lets just simply answer few the above questions first
here, and then we should be able to track down exactly what the bottleneck
is, or even if the bottleneck can be fixed...