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
|
|||
|
|||
Query Speed
Hi,
I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. Could someone please provide suggestions for how I may be able to speed up the performance of my queries? The database itself is only 1.56 MB in size (it’s only a test database as I’m in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports). Thanks, Manuel |
#2
|
|||
|
|||
Query Speed
Check out SQL pass-through queries. They do the processing out on the
database server and only return the requested records. If you do a standard select query from within Access, Access needs to get at all the records on the server then return the proper records. http://support.microsoft.com/default.aspx/kb/303968 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Manuel" wrote: Hi, I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. Could someone please provide suggestions for how I may be able to speed up the performance of my queries? The database itself is only 1.56 MB in size (it’s only a test database as I’m in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports). Thanks, Manuel |
#3
|
|||
|
|||
Query Speed
Seeing your queries might help.
Also how fast is your PC? that can be a significant factor. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Manuel" wrote: Hi, I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. Could someone please provide suggestions for how I may be able to speed up the performance of my queries? The database itself is only 1.56 MB in size (it’s only a test database as I’m in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports). Thanks, Manuel |
#4
|
|||
|
|||
Query Speed
Manuel
Factors that affect query speed: * Indexing -- are the underlying tables/views indexed on every field used in joins, sorts, selection? * Network -- network speed; NICs; traffic * functions -- your query involves "local" functions (i.e., functions that exist in Access but not in your data warehouse) Any of those apply in your situation? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Manuel" wrote in message ... Hi, I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. Could someone please provide suggestions for how I may be able to speed up the performance of my queries? The database itself is only 1.56 MB in size (it's only a test database as I'm in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports). Thanks, Manuel |
#5
|
|||
|
|||
Query Speed
Thanks for enlightening me in regards to Pass-Through queries; I’d heard
about them before but had never actually used them. Unfortunately, for whatever reason, the pass-through query is not any quicker than the regular select query I had. Any other suggestions? Thanks, Manuel "Jerry Whittle" wrote: Check out SQL pass-through queries. They do the processing out on the database server and only return the requested records. If you do a standard select query from within Access, Access needs to get at all the records on the server then return the proper records. http://support.microsoft.com/default.aspx/kb/303968 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Manuel" wrote: Hi, I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. Could someone please provide suggestions for how I may be able to speed up the performance of my queries? The database itself is only 1.56 MB in size (it’s only a test database as I’m in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports). Thanks, Manuel |
#6
|
|||
|
|||
Query Speed
In message , Manuel
writes Hi, I have a database which contains tables which are linked (via ODBC) to tables in my company's data warehouse. The tables vary in size and number of records, but the larger tables can contain upwards of 2 Million records. When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query. The delay will probably be caused by the way Access queries the central database, forcing the server to do more work than it should. You are probably going to have to apologise to the database administrator for slugging the performance of the database for all of the other users. You aren't the first person to do this. It is one reason why a lot of companies don't allow users to connect Access databases to their production servers. Others have already mentioned pass-through queries. Bear in mind that pass-through queries should be written in the dialect of SQL used by the server and not the version used by Access. You need to understand more about the server system in order to write code that runs efficiently on the server. I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance. It does, mostly. But ODBC uses a subset of SQL that can result in your Access databases sending commands to the server that it finds difficult to execute. What type of database are you connecting to? -- Bernard Peek |
Thread Tools | |
Display Modes | |
|
|