SQL Server slower than Access

Question

SQL Server slower than Access

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

I know what you mean, guys. I love SQL Server like my dog loves me. Even though I am sometimes late with his supper, and I don't walk him enough, and I take him to the vet to get shots, and I give him a bath when he smells bad, he still wags his tail when I get home.

There is no "i" in team, but idiot has two.

DavePeck SSC Enthusiast Points: 143 More actions · Answer 2

Check the execution plan on View A, as someone already suggested. This will give you a very good idea of where the bottleneck(s) lie.

Also check the indexes on Table D. The upsizing Wizard puts all kinds of trash into upsized dbs, including multiple indexes on the same field.

When you look at the execution plan, look for table scans. The upsizing Wizard doesn't create clustered indexes, so converting an existing index to a clustered index might help.

Finally, when you rewrite your views, try to consolidate to a single view. Using derived tables within the view may help. Nested queries in Access are often the product of someone getting the "ambiguous outer join" error. You have more options for handling this situation in SQL Server views, without nesting.