Jim Dettman
jimdettman at verizon.net
Sat Jan 24 14:52:07 CST 2015
That's the approach most take. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John R Bartow Sent: Saturday, January 24, 2015 01:22 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] ACCESS Left Behind Importance: High Thank you. I think that answered the question well enough. I have an app that has a few concurrent users at most and not a lot of data in the grand scheme of things. The client wants it ported to use SQLServer. If performance is not an issue after a simple (yea, right) switch over, I'd just leave it as is. If performance issues arise I could then address those of an issue by issue basis. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Saturday, January 24, 2015 8:22 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] ACCESS Left Behind There's a lot of wiggle room in that question<g> When using linked tables, JET will always try to push a query server side if it can even though it's not a pass-through specifically. It can't do that when: 1. You join to tables from different data sources. 2. You use Access or VBA Expressions in the SQL. 3. You use JET Specific SQL. 4. You use some SQL expressions such as DISTINCT or GROUP BY All those will force JET into processing the query locally. So depending on the app (how it was written), number of concurrent users, and the size of the record sets involved, you may or may not notice a tremendous difference between the two. I can say that in general when moving to SQL it is usually faster, but there have been cases where you actually end up slower. That's why when you consider a move to SQL Server, pushing the data should be considered just the start of the start of the process and not the whole process. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John R Bartow Sent: Friday, January 23, 2015 06:49 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] ACCESS Left Behind Importance: High Hi Jim, On the issue of performance would the stated example, when compared to using an a native access db, make a noticeable difference in a small office with very few users? John B -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Friday, January 23, 2015 8:14 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] ACCESS Left Behind While that's true to an extent, you just need to understand what's going on under the hood. You can get very acceptable performance just with linked tables, but some of the features that make Access easy to use will cause you a problem. Something as innocent as an IIF() in a query, which many use, and boom, lousy performance (any Access, VBA, or JET specific expression in a query forces JET to process the query rather than passing it off to SQL). Jim. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com