Like query slow on number field

  • Thread starter Thread starter eselk2003
  • Start date Start date
E

eselk2003

I have table with a primary key of Long Integer type. I would like to
do a query like this:

SELECT * FROM MyTable WHERE MyKey Like "5*" ORDER BY MyKey

When I do that same query on any indexed string/text field, it returns
super-fast. I'm doing the query in VBA script and the query is a
dynaset, so it should just locate the first record and then return, so
if it is actually using the index, it should always be very fast to
find the first record.

I understand the problem is that 5* could be one of several possible
numbers, 5, 50, 500, 512, 50123, etc... so I think this makes it
impossible for Access to use the index. Is this correct, or am I
missing something?

Any way to have index keep an index by number for my normal stuff (and
the normal primary key stuff that Access likes), and also another
index for "text" type look-ups? I guess I could add another field, as
a text field, but then I would have to make sure I keep the text field
updated, so I'm not crazy about that idea, I don't like duplicate data
storage.
 
How many records are in the table?

Where is the data stored?

Where are you asking from?

How many fields are in the table (your SELECT * gets them all)?

Are you sure it's the getting that's slow, and not the ORDER BY?

You called it a primary key, so I'm assuming you have it indexed.

There are a lot of potential factors...

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
well i tested your statement on my table that has about 60000 records
took like .3 of a second to run if you think it is a string thing cast
your pk as a string

SELECT * FROM MyTable WHERE cstr(MyKey) Like "5*" ORDER BY cstr(MyKey)

though it did slow down a bit when i used order by

if your pk is an autonumber then you dont need order by therotecally

hope this helps

Regards
Kelvan
 
How many records are in the table?
55,657

Where is the data stored?
Where are you asking from?
Click to expand...

On a Win2003 server, over a 100mbps LAN, query is from a workstation
running XP.
How many fields are in the table (your SELECT * gets them all)?
Click to expand...

39 -- although see my code below, I was incorrect about the "SELECT *"
part, I'm only asking for one field.
Are you sure it's the getting that's slow, and not the ORDER BY?
Click to expand...

It is this one line of VBA script that takes about 3 to 4 seconds to
execute (when I press F8 on this line):

Set rs = CurrentDb.OpenRecordset("" & _
"SELECT CUSTOMER.[CUSTOMER #], " & _
"CUSTOMER.[" & SearchField & "] FROM CUSTOMER " & _
"WHERE (((CUSTOMER.[" & SearchField & "]) Like """ & find_text &
"*"")) " & _
"ORDER BY CUSTOMER.[" & SearchField & "];")

Here is a condensed version with variable set to the field that is
slow:

SELECT [CUSTOMER #], [CUSTOMER #]
FROM CUSTOMER
WHERE ((([CUSTOMER #]) Like "5*"))
ORDER BY [CUSTOMER #]

I did just notice that when SearchField is [CUSTOMER #], I end up
asking for the customer# field twice, but I don't think that would
effect speed very much.

I use it for a list that updates as the user types in a textbox. If
SearchField is any indexed string/text field, the above line of code
executes in less than a second (fast enough that you don't notice much
delay while typing). If SearchField is a non-indexed field, it takes
4+ seconds to execute. If SearchField is an indexed numeric field, it
takes 3+ seconds to execute, which I imagine is because it isn't/can't
use the index... at least until I read Lord Kelvan's reply, it seems
he is able to get <1 second results (could it just be because he used
cstr(), of course I'll try it to find out).

I haven't tried without the ORDER BY. I have tried changing it to
"SELECT TOP 1", but that didn't speed it up at all, in which case I
would think ORDER BY wouldn't matter since I am only asking for 1
record.
You called it a primary key, so I'm assuming you have it indexed.
Click to expand...

Yes, indexed with no duplicates (although for string fields, indexed
with or without duplicates is fast either way).
 
Are you sure it's the getting that's slow, and not the ORDER BY?
Click to expand...

It is this one line of VBA script that takes about 3 to 4 seconds to
execute (when I press F8 on this line):

Set rs = CurrentDb.OpenRecordset("" & _
"SELECT CUSTOMER.[CUSTOMER #], " & _
"CUSTOMER.[" & SearchField & "] FROM CUSTOMER " & _
"WHERE (((CUSTOMER.[" & SearchField & "]) Like """ & find_text &
"*"")) " & _
"ORDER BY CUSTOMER.[" & SearchField & "];")

Here is a condensed version with variable set to the field that is
slow:

SELECT [CUSTOMER #], [CUSTOMER #]
FROM CUSTOMER
WHERE ((([CUSTOMER #]) Like "5*"))
ORDER BY [CUSTOMER #]
Click to expand...

I removed the duplicate [CUSTOMER #] from the SELECT line, and I also
removed the "ORDER BY" part. The query is <1 second now, if I search
on 1 or 2 digits. Once I get to 3 or 4 digits, the query takes longer
than 3 seconds again (but for a text field, it is <1 second no matter
how much text I enter).

I imagine, as I stated in my original post, it has to do with
searching a numeric field in a manor that is optimized for strings.
I've written an ISAM database engine, and I know with my database
engine I couldn't use the index very well for this type of search, but
I was hoping Access with all of its glory might have a simple way for
me to say "Index this field numericly (the default for numeric fields)
and also alphabeticaly, without me having to add another field and
make sure both stay in sync"... granted, in this one case the field is
an auto-number field so it never changes, so the "keep in sync" part
isn't too bad. I'd like a general solution to be able to search any
numeric field, quickly, in this manor.
 
I'm probably not understanding why you'd index a field both numerically and
alphabetically. For example, I see lots of posts from folks who've decided
that a zipcode is a "number" and are confused when the leading zeros
disappear. Changing that to a text field means the "0" is just another
character.

But how could a field be both a number and a text?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Are you sure it's the getting that's slow, and not the ORDER BY?
Click to expand...

It is this one line of VBA script that takes about 3 to 4 seconds to
execute (when I press F8 on this line):

Set rs = CurrentDb.OpenRecordset("" & _
"SELECT CUSTOMER.[CUSTOMER #], " & _
"CUSTOMER.[" & SearchField & "] FROM CUSTOMER " & _
"WHERE (((CUSTOMER.[" & SearchField & "]) Like """ & find_text &
"*"")) " & _
"ORDER BY CUSTOMER.[" & SearchField & "];")

Here is a condensed version with variable set to the field that is
slow:

SELECT [CUSTOMER #], [CUSTOMER #]
FROM CUSTOMER
WHERE ((([CUSTOMER #]) Like "5*"))
ORDER BY [CUSTOMER #]
Click to expand...

I removed the duplicate [CUSTOMER #] from the SELECT line, and I also
removed the "ORDER BY" part. The query is <1 second now, if I search
on 1 or 2 digits. Once I get to 3 or 4 digits, the query takes longer
than 3 seconds again (but for a text field, it is <1 second no matter
how much text I enter).

I imagine, as I stated in my original post, it has to do with
searching a numeric field in a manor that is optimized for strings.
I've written an ISAM database engine, and I know with my database
engine I couldn't use the index very well for this type of search, but
I was hoping Access with all of its glory might have a simple way for
me to say "Index this field numericly (the default for numeric fields)
and also alphabeticaly, without me having to add another field and
make sure both stay in sync"... granted, in this one case the field is
an auto-number field so it never changes, so the "keep in sync" part
isn't too bad. I'd like a general solution to be able to search any
numeric field, quickly, in this manor.
 
I'm probably not understanding why you'd index a field both numerically and
alphabetically.
Click to expand...

I'll admit I don't know what type of indexes Access uses, but I
imagine it isn't too different than a b-tree, and is probably some
variation (b-tree+, etc).

If an index is a numeric index, the nodes would be in numeric order,
for example:

50
100
500
800
5000

So if I wanted to do a "Like 5*" query, it would have to jump around
in the index to find each record (which is almost the same as no index
at all).

If the index was text-based, those same values would be in this order
in the index:
100
50
500
5000
800

So that same "Like 5*" query could just jump to the "50" record, then
just go one at a time until the 800 record. This is how an optimized/
normal index query should work.

I know a b-tree index isn't flat as it may appear in my example. I'm
just trying to keep it simple since I have trouble drawing a b-tree in
a message... pretty much my above example would just be the leaf-nodes
of the tree.
 
Beyond my understanding ... but maybe one of the other newsgroup readers can
respond.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm probably not understanding why you'd index a field both numerically
and
alphabetically.
Click to expand...

I'll admit I don't know what type of indexes Access uses, but I
imagine it isn't too different than a b-tree, and is probably some
variation (b-tree+, etc).

If an index is a numeric index, the nodes would be in numeric order,
for example:

50
100
500
800
5000

So if I wanted to do a "Like 5*" query, it would have to jump around
in the index to find each record (which is almost the same as no index
at all).

If the index was text-based, those same values would be in this order
in the index:
100
50
500
5000
800

So that same "Like 5*" query could just jump to the "50" record, then
just go one at a time until the 800 record. This is how an optimized/
normal index query should work.

I know a b-tree index isn't flat as it may appear in my example. I'm
just trying to keep it simple since I have trouble drawing a b-tree in
a message... pretty much my above example would just be the leaf-nodes
of the tree.
 
access cannot do that the alternative would be to use a make table
query that would create the table but cast the values as text with the
index built on it and it should work then but thats just a guess

Regards
Kelvan
 
I have table with a primary key of Long Integer type. I would like to
do a query like this:

SELECT * FROM MyTable WHERE MyKey Like "5*" ORDER BY MyKey

When I do that same query on any indexed string/text field, it returns
super-fast. I'm doing the query in VBA script and the query is a
dynaset, so it should just locate the first record and then return, so
if it is actually using the index, it should always be very fast to
find the first record.

I understand the problem is that 5* could be one of several possible
numbers, 5, 50, 500, 512, 50123, etc... so I think this makes it
impossible for Access to use the index. Is this correct, or am I
missing something?
Click to expand...

Sort of: you are almost there. There is a concept called "sargability". In
order for a query to use an index, the criterion needs to be "sargable".This
article is a little out-of-date, but it explains the basic concept well:
http://www.sql-server-performance.com/sql_server_performance_audit8.asp

You are telling the query engine to compare a Long value to a string value.
In order for it to do so, it has to convert the Long to a string.

That means your criterion is not sargable: you are forcing Access to do a
table scan in order to convert the Long values to string in order to compare
them to the string criterion. No index can be used when data coersion (or
any other function) needs to be performed on the data in a field before
performing a comparison.
Any way to have index keep an index by number for my normal stuff (and
the normal primary key stuff that Access likes), and also another
index for "text" type look-ups? I guess I could add another field, as
a text field, but then I would have to make sure I keep the text field
updated, so I'm not crazy about that idea, I don't like duplicate data
storage.
Click to expand...

Unfortunately, that is the only way.
In SQL Server, you could create a persisted calculated column upon which an
index could be created. You could also create an indexed view. Neither of
these options is possible in Access as far as I know.
 
Unfortunately, that is the only way.
In SQL Server, you could create a persisted calculated column upon which an
index could be created. You could also create an indexed view. Neither of
these options is possible in Access as far as I know.
Click to expand...

Thank you, and those are the kinds of features I was looking/hoping
for.

I wanted to use SQL Server, but the people I work for had a hard
enough time going from Paradox to Access (took 3 years of testing
before we finally went live last week)... maybe in the year 2050+,
when SQL server is obsolete, I'll be able to start using it (probably
even then, only a really old version). =[
 
well you can still use access and sql server by using an odbc
connection so people will still see access but the database will be in
sql server and only the dba will have to touch the server

Regards
Kelvan
 
well you can still use access and sql server by using an odbc
connection so people will still see access but the database will be in
sql server and only the dba will have to touch the server

Regards
Kelvan
Click to expand...

True, and Access is a good front-end for SQL, although I think if you
are going to use SQL server you should design things a certain way
(client-server-friendly). The guys I work for are used to a lot of
client-side-cursor type stuff, like lists that update quickly as you
type, and that let you scroll top-to-bottom of 100,000+ records
quickly. SQL server seems better suited for typing in your search
string, then clicking a Find button, or doing things that return
smaller recordsets... that is just my example, I'm not sure if that is
correct in all cases, but I'm sure anyone would agree you design
things differently if you know you're using MSSQL vs JET data engine/
format (or Jet Blue vs Jet Red).
 
Back
Top