If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Retrieval Performance Questions
My Access databases are only used as data repositories for Excel to call.
Tables are either built by importing a Worksheet, or importing a text file. As the calls to the database are many, I have a bunch questions on how to make them as fast as possible. I'm using Dao.DBEngine.36 to retrieve. Would ADO be faster? The mdb files are in 2002 file format. They are much larger than if in 97 file format. Would the retrieval be faster, or slower, if I convert back to 97? The smaller 97 format would make it easier to propagate the files to our branches. All the tables together are 28 MB. But one of the tables has 40,000 lines and is 27 MB of this. Some tables are tiny, with just a single record with two columns. Is there a performance hit when retrieving the small tables when the big table is in the same database? I gave the big table a column that is Indexed (No Duplicates). All retrieves will be for a single record on the indexed key. Would adding a primary key make such calls faster? Some tables are Indexed (Duplicates OK). Any call to these tables will be to the indexed columns, and will want all the records with that key. Would adding a primary key make such calls faster? On the 27 MB table sometimes I just want the value from a single field. The entire table has 15 fields (plus the key field). Would it be faster if I had another table that only had that single field? Thanks, Don donwiss at panix.com. |
#2
|
|||
|
|||
Answers in-line
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Don Wiss" wrote in message ... My Access databases are only used as data repositories for Excel to call. Tables are either built by importing a Worksheet, or importing a text file. As the calls to the database are many, I have a bunch questions on how to make them as fast as possible. I'm using Dao.DBEngine.36 to retrieve. Would ADO be faster? It's doubtful that ADO would be faster. DAO was designed specifically for Jet databases, whereas ADO is a generic approach (which means more layers of abstraction) The mdb files are in 2002 file format. They are much larger than if in 97 file format. Would the retrieval be faster, or slower, if I convert back to 97? The smaller 97 format would make it easier to propagate the files to our branches. Again, it's doubtful that changing would speed things up. Since Access 2000, text is stored in Unicode, so it takes 2 bytes per character, rather than the 1 byte per character that Access 97 and previous used. If you want to try switching to 97 format, be sure to use DAO 3.5x rather than 3.6. How are you propagating the files? MDB files tend to zip to a significantly smaller size. All the tables together are 28 MB. But one of the tables has 40,000 lines and is 27 MB of this. Some tables are tiny, with just a single record with two columns. Is there a performance hit when retrieving the small tables when the big table is in the same database? Not sure of the relevance of this. If a table is big, you can't make it smaller. Denormalizing or splitting tables will lead to far more problems than it will solve. I gave the big table a column that is Indexed (No Duplicates). All retrieves will be for a single record on the indexed key. Would adding a primary key make such calls faster? I believe all tables should have primary keys. However, an index that's set as Indexes (No Duplicates) should perform the same as a Primary Key. And anytime you have an index that doesn't allow duplicates, it's certainly a candidate key. Some tables are Indexed (Duplicates OK). Any call to these tables will be to the indexed columns, and will want all the records with that key. Would adding a primary key make such calls faster? Again, I don't see the relevance of this. The only way adding a primary key would help would be if you looked up by the primary key. If you're currently looking up by specific fields that are not sufficient to be a primary key, you really have no choice but to do your lookups that way! On the 27 MB table sometimes I just want the value from a single field. The entire table has 15 fields (plus the key field). Would it be faster if I had another table that only had that single field? Doubtful. You're far better off having a correct data model than trying to manipulate your model to try and gain nanoseconds of response time. |
#3
|
|||
|
|||
"Douglas J. Steele" wrote:
Don Wiss wrote: The mdb files are in 2002 file format. They are much larger than if in 97 file format. Would the retrieval be faster, or slower, if I convert back to 97? The smaller 97 format would make it easier to propagate the files to our branches. Again, it's doubtful that changing would speed things up. Since Access 2000, text is stored in Unicode, so it takes 2 bytes per character, rather than the 1 byte per character that Access 97 and previous used. If you want to try switching to 97 format, be sure to use DAO 3.5x rather than 3.6. The problem with 3.5 is it is not present on new Office XP installations. I had been using it, and got into trouble with them. I did ask that it be added to new Office XP installs, but since decisions like that are made in Zurich, I doubt it. Why 3.5 over 3.6 for 97 files? I do distribute one database still in 97, just in case someone loads up an old spreadsheet which calls 3.5. But this being a new database would never be called by an old spreadsheet. How are you propagating the files? MDB files tend to zip to a significantly smaller size. Very true, but after the IT fellow pushes them out over a slow WAN there is no way to expand them. It isn't a big issue. He can just let the job process for a while. Thanks for the response. Don donwiss at panix.com. |
#4
|
|||
|
|||
3.6 assumes the text is unicode, so it would (unnecessarily) trying to do
conversions, therefore being slower. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Don Wiss" wrote in message ... Why 3.5 over 3.6 for 97 files? I do distribute one database still in 97, just in case someone loads up an old spreadsheet which calls 3.5. But this being a new database would never be called by an old spreadsheet. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Masonic Emblems Where can I get online ? Thanks | Vanman | General Discussion | 3 | September 23rd, 2004 08:26 PM |
FAQ - frequently asked questions - please read before posting - June posting unofficial | Charles Kenyon | New Users | 4 | June 28th, 2004 02:58 PM |
A Complex Mail Merge Question | Erik Tice | Mailmerge | 2 | May 29th, 2004 05:58 AM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - May | Charles Kenyon | New Users | 6 | May 24th, 2004 05:55 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - April | Charles Kenyon | New Users | 0 | April 26th, 2004 03:34 PM |