SQL Server slower than Access

Question

SQL Server slower than Access

Tom Yamartino

SSC Journeyman

Points: 85
More actions
March 2, 2005 at 2:49 pm

#163746

Hi folks,
I am migrating from Access to SQL Server. Some views (that query other views) are slower on SS than Access, apparently because the referenced views are not indexed. These views can only be indexed if they query from tables directly, not other views. In other words:
View A --> queries View B --> queries View C --> queries Table D
A is slow. I can't index B because it queries C.
Any ideas on how to fix this without re-writing my views/queries?
Thanks!

Viewing 15 posts - 1 through 15 (of 17 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Check the execution plan of view a and check for bottlenecks and report back to us. It's most likely a sort that is being rerun too often or a missing index that is the culprit.

noeld SSC Guru Points: 96590 More actions · Answer 2

I hope you are not comparing access on a local machine against sql server on a remote machine!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Yes because SQL server when working with a normalized and well indexed db will ALWAYS FLAT OUT CRUSH ACCESS... unless the network or something out of his control stop it from transmitting fast enough.

noeld SSC Guru Points: 96590 More actions · Answer 4

Allow me to differ.

As long as you have to go through a bandwith bottleneck (compared with IDE/SCSI bandwith) SQL Server will be SLOWER than many Desktop DBs !

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Just went a little overboard... I guess I love sqlserver too much.. but my point is that under the same networking condition, sql server will not be slower than access.

Tom Yamartino SSC Journeyman Points: 85 More actions · Answer 6

To clarify - my query takes about 1 minute on SQL server, and about 25 seconds on desktop Access. The plan for the Access query shows that it is building temporary index files, and this allows it to work quickly. With the SS query I cannot index a crucial view because it references other views, not just tables.

I'm sure that if I could index the views it would be much faster than Access.

I have a feeling that I'm up a creek. I'll have to re-write my SS queries so they can all be indexed.

But if anyone has a shortcut or cheat of some kind, that would be cool.

Keywords: view, index, lazy programmer

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Where there's a will, there's a way but we need to you the ddl of the tables along with the relations and the indexes if we can be of any help...

Have you tried running theindex tuning wizard to see if a new index could help?

philcart SSC-Forever Points: 47797 More actions · Answer 8

Yes SQL Server will run a database that has been designed for Access much slower than Access itself.

If you're not prepared, or not able, to re-design your database to take advantage of SQL Servers enterprise features, then you'll get bad performance.

The fastest car in the land will run really slow if you feed it bad fuel.

Instead of re-writing your views so they can be indexed, re-design your base tables so you can extract the information you need via stored procedures.

--------------------
Colt 45 - the original point and click interface

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

OK, OK... I was wrong. Access can run faster than SQL server under certain circumstances .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Allow me to jump right in?

Any ideas on how to fix this without re-writing my views/queries?

I'd say, you're caught in a typical Access developing style. Been there myself, done the same mistake, had to rewrite most of this to get acceptable performance. But, as for the positive aspect, you will *never* make the same mistake again.

Just my $0.02 anyway

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Tom Yamartino SSC Journeyman Points: 85 More actions · Answer 11

Thanks all for your replies. I think I just have to roll up my sleeves.

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

"Next on Jerry Springer: Developers who love SQL Server too much..."

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Most of us wouldn't ear a living without it... Har dnot too love it just a little bit

noeld SSC Guru Points: 96590 More actions · Answer 14

I am with Remi on this one. SQL is what puts my food on the table, not access

* Noel