Speed of code VS searching a table

[John K]

Hello,
When you have data the will not change from a performance stand point is it better to store the data in a table or write code? With what I doing it would be quicker to store the info within a table. I want the best performance I can get. This info is a major part of this database. One other question about performance, does Access have limits on the processer and memory like SQL express?
Thank you,
John

 

[boblarson]

What kind of data? One way is to read it from the table upon opening and sending it to arrays which then can be accessed quickly. But it depends on what it is as to what is really going to work the best.

 

[Galaxiom]

As Bob says it depends on where it is being used.

In code, the fastest way to access values that don't change is by defining Constants. The values of constants are substituted for the reference to the constant directly into the code when it compiles.

A value that is hard coded into a saved query is also very fast. A saved query has its execution plan stored the first time it runs.

A dynamic query where the sql is constructed in VBA is slower because the execution plan must be constructed each time.

Values stored in a table are also fast because the entire query can be handled in the database engine.

It is largely an acedemic question on modern computers because we are discussing milliseconds. It can make a difference in very busy database.

Queries that are entirely in the

 

[John K]

The data that I will be working with is driving distance between two cities. For now the database is not busy but I hope it will be at some point. This info will be used as one factor of several the will create and email. I do know that you can use a formula to caculate distance between to giving points but this does have some errors. That formula caculates straight line. For example acrossed large lakes, for what I am doing that formula will not work. At this point I am working with a small number of locations about 1000 but as the number of locations increases the size of the table will get very large. I may be wrong but I think there is about 42,000 US zip codes in the US. I dont know that I will ever get to that point but I feel the most important part of creating a database is not build what you need today but looking at what you may need tomorrow.
Thank you,
John

 

[Galaxiom]

I feel the most important part of creating a database is not build what you need today but looking at what you may need tomorrow.

Good policy.

Maybe look into scripting access to Google Maps. That will give you door to door distances and travel times.

 

[John K]

I am aware of the google maps and the script. Right now I plan on keeping my database off line other than when it is sending the email. I also have to think about my internet speed. I will be using DSL, if my database grows as I hope it will I dont think scripting for google maps will be the best thing to do.

 

[The_Doc_Man]

The question isn't first how you store what you want to store, but how you want to retrieve it. Stay with me for a moment because I'm going somewhere specific with this.

If you have a bunch of linear distances between points A and B such that you form a chain of distances as you figure routes, the problem of computing what you have starts to increase as the number of possible destinations increase. At least in theory this is a humongous problem in combinatorial math. The possible routes start going up non-linearly as the number of starting and ending points (and intermediates) increase.

Where this is going is that whether you store your data on disk or in memory is almost (and I did say 'almost') meaningless - if it takes you forever to find what you wanted to find. Further, as your memory arrays grow, your program's memory performance can start to suffer. For 50 or 100 destinations A and B, maybe not so badly. But looking to grow, you need to consider that a decent search algorithm that spans 50 points is a wallowing pig when it spans 500 points. Which would happen if it is a non-linear search algorithm.

Therefore, I respectfully suggest that storage isn't your problem. Your search algorithm should be number one on your list of things over which to agonize.