October 31, 2003 at 1:53 pm
There is a registry setting somewhere that will generate a text representation of the query plan that Access/Jet is using. One of these days I'll research some more, curious what goes on. Might have to be an ODBC trace too.
Don't throw linked tables out, they can be pretty helpful.
Andy
November 5, 2003 at 7:38 am
quote:
Somehow I remembered you need linked tables to run PassThrough queries.
No You DON'T. You Just specify The ODBC Connection string and the QRY and everything is handled on the SERVER.
My 2 cents
* Noel
November 6, 2003 at 3:19 am
Where possible Jet will send efficient SQL to SQL Server when using linked tables and only retrieve the rows required.
However there are circumstances where Jet will return complete tables from SQL Server for local processing:
- Using functions in a WHERE clause which are not supported by SQL Server
- Query syntax not supported by the ODBC driver e.g. SELECT Top 10%
- Joins between SQL Server tables and local tables
I guess as you can not change the frontend, you will not have any control over the above.
You should be able to see what is going on though by starting up Profiler in SQL Server and looking at the SQL Statements being sent from Jet for each action in the Frontend.
If the frontend uses Access 97 you should also be aware of issues using certain types of files in SQL Server.
Use varchar rather than nvarchar because Access 97 interprets a varchar(50) field as 255 characters which will cause problems if user inputs more than 50 characters.
Also I recall a problem with Text fields (memo) with Access 97 and now only use large varchar e.g. varchar(4000) will be interpreted as a memo field in Access 97 - problem if you have no validation on field and user inputs more than 4000 characters.
November 25, 2003 at 12:18 am
Access Will Copy all the data from An SQL Server table to the Jet in Link table situation when you join a link table and a locla table on the same query
allthgo the optimazer tries to reduc the number of recrods fetched from the sql server it is not able to do every good job in this case
allso avoid using right or left join insted use union queries since they are a lot faster.
Select ID from a table where ID in (select iD From Second Table) is not recomended
Do Not send controls to the query insted send function that returns an atomic value.
This tips will make the application faster
November 25, 2003 at 9:21 am
I developed a small application (10 users, 12 tables, largest table 38K rows) using Access 2000 front end and SQL Server backend with linked tables. It has been running fine, and barely registers on my performance monitors for the server. I cranked up the profiler and saw Access did a good job bringing down only one screen at a time from the server when populating a grid.
I did have some sporadic network sharing problems when I had all users accessing the same MDB application file on the LAN. So I changed the users’ shortcuts to copy the MDB from a read only LAN folder to their temp directory and execute it from there. Not only did that fix the sporadic sharing problems, but allowed an easy mechanism to deploy updates. That technique could also work for a vb app, after the initial client install (unless a dll changes).
I am not sure how linked tables would scale up to a large number of users and really big tables, but I suspect it depends upon how the application is written.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply