Re: Like query slow on number field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Sep 29, 4:56 pm, "Jeff Boyce" <nonse...@xxxxxxxxxxxx> wrote:
How many records are in the table?

55,657

Where is the data stored?
Where are you asking from?

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)?

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?

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.

Yes, indexed with no duplicates (although for string fields, indexed
with or without duplicates is fast either way).
.

Relevant Pages

  • Re: alter column name
    ... first it allows my customer to execute the stored procedure in the ... query tool and get the table displayed as a grid in a simple format. ... I can bring the table into an existing asp page. ... When the customer is done reviewing the table, ...
    (microsoft.public.sqlserver.programming)
  • declaration of functions return pointers
    ... I have executed the below program whichm does not accept to execute ... int customer_no; ... struct customer_record customer; ... char another_customer; ...
    (comp.lang.cpp)
  • Re: 5 Line Assembler Demos
    ... and have a single op code execute the demo of your ... What is your domain or customer number?" ... Paul: The best way is to limit the size of the executable (and thus any ...
    (comp.sys.acorn.programmer)
  • CursorLocation playing havoc with search attempts!!
    ... However, as the queries are often executed on a customer table with greater then 50,000 records we use a 2 step process to show the entire customer records. ... To execute a Seek as you know you need a cursorlocation of clUseServer. ... Any ideas on how we can use a Seek on the table to find the record we want, then change the cursor location to clUseClient before we display the data and still record the position of the record we want?? ... We've tried bookmarks, but the bookmark is not preserved when you need to close the table connection to reset the cursor location. ...
    (borland.public.delphi.database.ado)