speed up slow list box query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a list box on a form and the contents of the list box are derived
from a query such as "Select Distinct(Table.Field) from Table Where
left(table,field,1) >=0;"

Then based on user selection using the program, the list box source might
change to a different field and/or table query.

The tables could have 200,000-500,000 records in them. The query on the list
box source presents the user with a realistic subset.

However, when the source change it takes 10 seconds for the list box to be
populated and available for use.

IS there any means of making this faster?
 
Hi Steve

The first thing to do is ensure that the field in question is indexed in the
table.

I'm a little confused about why you should want to compare a string -
Left(...) with a number - 0. If you want to list only those entries where
table.field begins with a particular letter, then use the Like operator:
... where table.field like "M*"
 
Thanks for the info. The table in use is controlled by our MRP system so its
data types and indexes are controlled by it - that is essentially what we
are looking for numbers in a text field. We have a group of similar items in
that table that we want the users to select from and it is a text field, but
our items begin with 0, 1, 2, 3, or 4. For the particular user selections,
we want the list box to contain all items starting with a 0,1,2,3, or 4
then. Based on some options the users selects at a different time we do
indeed query based on the text like you indicate - ie, field like "AS*" etc
when we want the list box populated with that group of selections only, etc.
I'm linked to the table so I don't think I can permanently reset the
indexes. Is this some I can do on the fly that effects only the table as
linked to my Access db and not the original table itself?



Graham Mandeno said:
Hi Steve

The first thing to do is ensure that the field in question is indexed in the
table.

I'm a little confused about why you should want to compare a string -
Left(...) with a number - 0. If you want to list only those entries where
table.field begins with a particular letter, then use the Like operator:
... where table.field like "M*"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Steve said:
I have a list box on a form and the contents of the list box are derived
from a query such as "Select Distinct(Table.Field) from Table Where
left(table,field,1) >=0;"

Then based on user selection using the program, the list box source might
change to a different field and/or table query.

The tables could have 200,000-500,000 records in them. The query on the
list
box source presents the user with a realistic subset.

However, when the source change it takes 10 seconds for the list box to be
populated and available for use.

IS there any means of making this faster?
Click to expand...
Click to expand...
 
Hmmmm.... What is the source table? SQL Server? Oracle?

Would it be possible to have someone write a stored procedure to pass a
"like" parameter to? For example:

Create proc StevesList(@Pattern nvarchar(20) = '%')
as
SELECT DISTINCT
t.field
FROM
table t
WHERE
t.field like @Pattern;

Then, in your Access front-end, you can create passthrough queries to
execute your stored procedure - for example:
exec StevesList '[0-4]%'
or
exec StevesList 'AS%'

You could do it with just one saved query, and change the SQL property of
the querydef to return different lists.

Then just base your list's RowSource on that query.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Steve said:
Thanks for the info. The table in use is controlled by our MRP system so
its
data types and indexes are controlled by it - that is essentially what we
are looking for numbers in a text field. We have a group of similar items
in
that table that we want the users to select from and it is a text field,
but
our items begin with 0, 1, 2, 3, or 4. For the particular user selections,
we want the list box to contain all items starting with a 0,1,2,3, or 4
then. Based on some options the users selects at a different time we do
indeed query based on the text like you indicate - ie, field like "AS*"
etc
when we want the list box populated with that group of selections only,
etc.
I'm linked to the table so I don't think I can permanently reset the
indexes. Is this some I can do on the fly that effects only the table as
linked to my Access db and not the original table itself?



Graham Mandeno said:
Hi Steve

The first thing to do is ensure that the field in question is indexed in the
table.

I'm a little confused about why you should want to compare a string -
Left(...) with a number - 0. If you want to list only those entries
where
table.field begins with a particular letter, then use the Like operator:
... where table.field like "M*"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Steve said:
I have a list box on a form and the contents of the list box are derived
from a query such as "Select Distinct(Table.Field) from Table Where
left(table,field,1) >=0;"

Then based on user selection using the program, the list box source might
change to a different field and/or table query.

The tables could have 200,000-500,000 records in them. The query on the
list
box source presents the user with a realistic subset.

However, when the source change it takes 10 seconds for the list box to be
populated and available for use.

IS there any means of making this faster?
Click to expand...
Click to expand...
Click to expand...
 
I think that possibly if you are very careful about writing
the query, you may get much better performance:

select distinct tbl.fld from tbl
where (tbl.fld > "0") and (tbl.fld < "1")

That example simplifies the criteria a great deal,
and simplifying the criteria is sometimes the key
to making queries faster.

If there are a great many records allowed by the
criteria, then DISTINCT can be very slow. On an
unindexed field, DISTINCT means that every value
needs to be compared to every other selected value,
and that will be slow.

(david)
 
You want to ensue that you can sue a index here.

using the left() function kills any ability to using indexing..as the index
routines can not work with functions.

So, if you can change the expression to "remove" the left function, then you
can get this work.

For example you could use

where ThatField is >= 0 and ThatField <= 4

The above should work..and if indexing can be used..then your performance
should be instant. And, if it is text, then use:

where ThatField is >= '0' and ThatField <= '4'
 
Back
Top