Database lookup speed improvement

[Ashank4vba]

Hi,

Can you help me come up with the best solution for this problem?
I have a lookup table in an Access database with some 4 million records. The table contains only 2 fields say 'a' and 'b'. I am currently searching for a value in 'a' and retrieving the corresponding value in 'b'. Both fields are indexed with duplicates allowed (There is a separate autonumber primary key). The searches have always been almost instantaneous - thankfully, untill now.

A slight modification to this is needed, wherein I need to lookup for a value which matches a part of value in field 'a' and retrieve a part of 'b'. I used 'LEFT()' and 'DISTINCT' functions and the query works fine except that it is now very slow. I need to automate this process and hence I need fast lookups. My questions are:

1)Why is this happening?
2)Should I create another table with just the part of 'a' values for this kind of lookup Or is there a better alternative?

Thanks a lot in advance.

-cheers
Ashank
[P.S: I've seen tables in some access dbs without any primary key. How is that possible? Thanks for ur ans]

 

[Crowley16]

it's the fact that you're using the left() function...

what you are doing is getting a value from the table, and then lefting() that value, and then testing...

you have to remember that left is a built in function which does things in the background and will take time, and if you do it 4 million times, then it will take lots of time...


--------------------
Procrastinate Now!

 

[Amiel]

There may be no better alternative. We are talking additional processor utilization to execute for each of four million records.

I like your suggestion of creating an additional table to store the parced values.




Amiel
amielzz@mp4.it

http://amiel.cx.la

 

[hkaing79]

Do you have a sample list of field a and b and what you'd like to capture?

 

[Ashank4vba]

Hi All,

Thanks for your replies. Well, as for some samples, this is what they look like


a = 12345678912 01, b = 23451678923 01
a = 98765432198X 02, b = 918273645X91 01

and so on...
The format is 11 or 12 alphanumeric chars followed by spaces (2 or 3) and 2 numbers. The lengths of both a and b are always 16.

The part of the fields that I was talking about is the part before the blanks/spaces.

Thanks.

-cheers
Ashank

 

[PHV]

HAve you tried the Like operator in the WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alvechurchdata]

The part of the fields that I was talking about is the part before the blanks/spaces.

Sounds like the value ought to be split into two separate fields - one for the bit before the blank and one after. This ought to improve speed because you will be searching and retrieving entire fields.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[hkaing79]

Geoff Franklin is a wise wise man. Listen to him.

Else, you're stuck with PHV's recommendation...something like...

WHERE field = "* 01" or something like that