slow using linked tables to SQL server 1

[drctx]

i've imported my back end database to sql server then linked the from end tables to the sql. there are about 3000 record and the searches and queries are extremely slow. is there anything that will speed it up?

i have ran the upsizing wizard and had it create an adp but i can run the adp with domain admin rights but regular uses can't becuase it sticks (dbo) after all the tables, views, and SPs. if i could remove the (dbo) from the end the i would use the adp since it is so much faster.

 

[dvannoy]

I would convert your front end to ADO using OLEDB. this is by far the best way to go.

 

[cheerio]

I assume that the end users have an mdb file and that it is just you trying to use adp.

When you upsize an mdb the tables are moved to the server but everything else stays in Access - in particular the queries.

This means that every query that is run requires Access to call for the 3000 records across the network, inspect each one and show the qualifying records.

What you need to do where possible is to convert the Access queries to the server dialect of SQL and store the queries on the server as views. What will now happen is that the server runs the queries and just sends the answer across the network.

Also take a look at Access pass through queries. These give some of the benefits of views without actually creating a view.

Finally, you may get further improvements by using stored procedures which can be executed from Access.

The reason the adp file executes faster is that all the queries are stored on the server. You can still do that with an mdb file. You can even link to a view as if it is a table.

 

[dvannoy]

cheerio, you make good points..

also drctx, keep in mind that SQL "Views" are not updatable, so if you have any forms based off query's you will have to change that as well. stored procedures will really improve performance and you can treat those as recordsets for reports etc..

 

[cheerio]

dvannoy it's not true that SQL views are not updateable. There are many circumstances when that is true because of ambiguity or because the view contains calculated fields or because it ignores fields that are required in the table.

In contrast, I actively use updateable views which join the main table being updated to a security table so that users see only those rows that they are allowed to see.

However, note that Access is unable to create an updateable link to a view unless you specify the key fields at the time the link is made.

There are more restrictions on pass through queries. They are fine for doing an update query such as giving everyone a pay rise but they cannot act as the data source for a form, at least for a bound form.