Hey all,
I've been reading up on all the articles dealing with this issue, and most seem to point to Pass through query for a better alternative for a database. Just thought I'd throw in our situation here though and see what some of you may think.
We have an SQL Server 2000 based database with an Access 2000 front end. The database has at least 40 tables which are all linked through Access 2000. There are at least 60 or so forms and as many reports with this thing too. The amount of queries is up there too with about 70 of them. This whole thing was designed under the linked table method and there are hundreds of lines of code that use the simplicity of the linked table method to retrieve the data. Trouble is (as many of you may already know), the performance is pretty sluggish. We've recently upgraded the client computers, and that has helped a lot, but we will still see some relatively simple record retrivals causing major performance issues.
The total amount of records in this database is about 50,000. That is, there are about 50,000 people in the database, with relational records in all the other tables, so yes, it does have lots of data (maybe not as much as an IBM thinktank database, but it's definitely not a small desktop thing).
So, I suppose my real question is, is there a way to speed up performance using the linked table method? All of our clients are Win 2K running off of an NT network. The SQL Server 2000 machine is a Win NT machine (a BDC actually), and our network is relatively small (about 14 clients in total). I like the ease of using linked tables and our Access 2000 MDE interface. I can quickly get into the tables through access, and even design some temp queries to troubleshoot any problems. Going to a Pass-through query based system may improve performance, but the level of work involved and the complexity with minor changes may be tremendous. I guess I'm just looking for opinions here. The lag time is bearable, but I'd like to see this thing running at a much faster pace, and there's really no real reason a computer with less than a 3 Mhz processor and less than 756 MB of RAM shouldn't be able to handle it (those are the specs of our new clients).
The final bite of the matter is the difference we see in performance based on location. The database programmers who made the Access interface created their ODBC connection using IP address, not named pipes. They actually reside in an office down the street, and they hit the data through a tunnel in our firewall. Here at the office, we use the named pipes method for our ODBC connection, and there should be no impedence of a firewall or other network traffic. Trouble is, the fellas down the road often see better performance (so they tell me) from their setup then from ours. I've already tried a simple experiment changing the ODBC connection on our end to an IP address rather than named pipes, and that had no effect. That kind of tells me that perhaps there is something network based that may be able to be tweaked to make the linked table method work better. All of our LAN lines are 10/100 based, and after doing some snooping on the client connection speed, all were running at around 100 mbps.
It's perplexing, and there are probably some more variables involved that I can't think of at the moment, but for general purposes, do you think there's something I'm overlooking?
Any ideas are appreciated (as always).
THANKS!
I've been reading up on all the articles dealing with this issue, and most seem to point to Pass through query for a better alternative for a database. Just thought I'd throw in our situation here though and see what some of you may think.
We have an SQL Server 2000 based database with an Access 2000 front end. The database has at least 40 tables which are all linked through Access 2000. There are at least 60 or so forms and as many reports with this thing too. The amount of queries is up there too with about 70 of them. This whole thing was designed under the linked table method and there are hundreds of lines of code that use the simplicity of the linked table method to retrieve the data. Trouble is (as many of you may already know), the performance is pretty sluggish. We've recently upgraded the client computers, and that has helped a lot, but we will still see some relatively simple record retrivals causing major performance issues.
The total amount of records in this database is about 50,000. That is, there are about 50,000 people in the database, with relational records in all the other tables, so yes, it does have lots of data (maybe not as much as an IBM thinktank database, but it's definitely not a small desktop thing).
So, I suppose my real question is, is there a way to speed up performance using the linked table method? All of our clients are Win 2K running off of an NT network. The SQL Server 2000 machine is a Win NT machine (a BDC actually), and our network is relatively small (about 14 clients in total). I like the ease of using linked tables and our Access 2000 MDE interface. I can quickly get into the tables through access, and even design some temp queries to troubleshoot any problems. Going to a Pass-through query based system may improve performance, but the level of work involved and the complexity with minor changes may be tremendous. I guess I'm just looking for opinions here. The lag time is bearable, but I'd like to see this thing running at a much faster pace, and there's really no real reason a computer with less than a 3 Mhz processor and less than 756 MB of RAM shouldn't be able to handle it (those are the specs of our new clients).
The final bite of the matter is the difference we see in performance based on location. The database programmers who made the Access interface created their ODBC connection using IP address, not named pipes. They actually reside in an office down the street, and they hit the data through a tunnel in our firewall. Here at the office, we use the named pipes method for our ODBC connection, and there should be no impedence of a firewall or other network traffic. Trouble is, the fellas down the road often see better performance (so they tell me) from their setup then from ours. I've already tried a simple experiment changing the ODBC connection on our end to an IP address rather than named pipes, and that had no effect. That kind of tells me that perhaps there is something network based that may be able to be tweaked to make the linked table method work better. All of our LAN lines are 10/100 based, and after doing some snooping on the client connection speed, all were running at around 100 mbps.
It's perplexing, and there are probably some more variables involved that I can't think of at the moment, but for general purposes, do you think there's something I'm overlooking?
Any ideas are appreciated (as always).
THANKS!
