Queries when using a SQL server back end, network optimization question

[SGT68]

I have converted an desktop Access database to SQL server. A medium sized app with 24 tables, 60 or so forms/subforms and about 120 queries. I created 2 split files, one containing the tables, and the other containing forms, queries, vba modules. I migrated the tables to SQL server using SSMA, and built an odbc connection for my front end. It works, except it is painfully slow. Practically unusable. I think i know why: Its cos most of the recordsets that drive my front end are query based. And in many instances the queries are comprised of queries, not just tables. So that is creating a serious network bottleneck - i think.
This is my question: Should i be creating my queries on the SQL server? Is that what people do when using Access front end with SQL server back end? Do away with Access queries? Or does the problem lie elsewhere? How do i network optimise my Access FE / SQL server BE? Please give me pointers and links that may be useful. Thanks to all in anticipation

 

[Minty]

How complex are the queries, how many tables are being joined? How many records are being returned by them?

If there are a lot of joins across many tables you will find a massive speed improvement by making the queries available as view in the backend.

 

[SGT68]

How complex are the queries, how many tables are being joined? How many records are being returned by them?

If there are a lot of joins across many tables you will find a massive speed improvement by making the queries available as view in the backend.

Minty,
Tables joined , between 2 and 5
records returned: in the hundreds.

View in the backend, yes thats what i was thinking. Never done that before, I will have to use SQL Management Studio, right?

 

[pbaldy]

Access is actually pretty good at passing queries back to SQL Server to process, so there must be something in them preventing that. Other alternatives include stored procedures and pass through queries, which would also force the processing to the server.

 

[Minty]

To be honest that doesn't sound very onerous. We have noticed a difference between using Gb Switches over 100Mhz ones, but other than that no real other network issues.

Yes you create the views in SSMS, it's a little different to Access but nothing overly complicated. Make sure once you save them you set permissions to at least Select for your users.

Can you post a query to look at in case there is anything obvious. In a lot of instances we use Sub forms with top level forms that help restrict the number of records being pulled in - but we also have forms with 1000's of records in the underlying source, and don't have any issues.

If you run the queries from the design view are they okay, or equally slow ?

 

[SGT68]

Access is actually pretty good at passing queries back to SQL Server to process, so there must be something in them preventing that. Other alternatives include stored procedures and pass through queries, which would also force the processing to the server.

Could it be that some of the queries are made up of queries?
Could it be that some of the tables use compound keys, which I've heard some of the Access greybeards frown upon?

 

[pbaldy]

Could it be that some of the queries are made up of queries?
Could it be that some of the tables use compound keys, which I've heard some of the Access greybeards frown upon?

It could certainly be the queries on queries, as Access probably wouldn't be able to pass that all back to the server. In that event the entire tables get sent over the network so Access can do the work.

I don't think it's the keys, as I've used them myself (but don't tell anybody).

 

[Minty]

Yes queries on queries will be slower - funnily enough I have just updated a complicated grouping query used as a basis for another query in a sub form and in access it took seconds (15-20) to load the form.

Moving the initial grouping query to the SQL Server has reduced this down to 1-2 seconds.

 

[SGT68]

To be honest that doesn't sound very onerous. We have noticed a difference between using Gb Switches over 100Mhz ones, but other than that no real other network issues.

Yes you create the views in SSMS, it's a little different to Access but nothing overly complicated. Make sure once you save them you set permissions to at least Select for your users.

Can you post a query to look at in case there is anything obvious. In a lot of instances we use Sub forms with top level forms that help restrict the number of records being pulled in - but we also have forms with 1000's of records in the underlying source, and don't have any issues.

If you run the queries from the design view are they okay, or equally slow ?

From design view the queries run ok, even the nested ones. How would i post a query for you to look at?

 

[Minty]

If they aren't massive just post up the SQL of the queries, and possibly the table structures.
Sometimes a jpg of the design view is really helpful (I'm a visual person)

You can obfuscate anything that is sensitive as long as we can get the jist of what you are doing.

 

[SGT68]

If they aren't massive just post up the SQL of the queries, and possibly the table structures.
Sometimes a jpg of the design view is really helpful (I'm a visual person)

You can obfuscate anything that is sensitive as long as we can get the jist of what you are doing.

ok, great I'll get that done tmrw. However now I've discovered an issue: The SQL Server I'm using is a WINDOWS AZURE SQL DATABASE on (Sharepoint with Access Services). I've connected to it with my desktop SSMS but being and Azure type it doesnt have the same Views applet that a normal SQL Server db has. It's given me this horrible text based interface instead of the point and click. Eearch!