SQL Server Performance Forum – Threads Archive
Slower Performance in Acc2000 Linked TablesI converted a access 2.0 to access 2000 and run macros which executes some query against the linked table from sql server. while running macro in access i got the records in 10secs whereas in access 2000 80secs please help me to improve the performance In access 2.0 the entire query is executed by sql server and the required results are returned to access. But in access 2000 passes the query without filter and group by to sql server eventhough the query has filter clause. so the all the records from the sql server table are returned to the linked table and then the filter and group by occurs in the access 2000 only which makes it very slow. These results we obtained with the help of sql profiler. Is there any settings/way to force access 2000 to pass
I think what youre asking is how to force the bulk of the processing to occur on SQL Server? Try the REMOTE join hint, see ‘FROM clause (described)’ in Books Online.
jvinoth, I have had similar experiences when accessing SQL server from Access 97 and 2K databases.
In the absence of creating an Access Data Project (ADP), my solution was to create a stored procedure on the server, a pass through query on the front end, and alter the SQL string at run time and then use it as a query.
In this way, all the code executes on the server.
Remember to set the return records to Yes in the PTQ, or you get nothing back.
I use this technique to cause updates on the server when I don’t want to wait for Access to get it’s act together (and it’s useful for returning the new Identity immediately when inserting new records). Hope this helps, Logicalman