Optimization...

[kalto]

Well, i'm currently in the process of transferring an Access Database to an SQL Server with an Access front end.

For now, i've upsized all of my table from access to SQL server. I've also "translated" manually most of my select query.

Everything is working fine, except for one thing. Seems like since I installed SP3a, performance went down quite a bit. Now my Access database is going MUCH faster then my SQL server... i did a little comparison:

Both the mdb and the sql server are residing on my local machine,
Exactly the same query, nothing special changed to fit in sql server
Returns around ~265 000 rows
Access: ~25 seconds
SQL Server (using either the query analyzer or calling the view from an access project): from 1:30 to 2 minutes...

Is this normal? I didn't expect an improvement for one user locally, but i also didn't expect that kind of performance degradation...

I'm not sure if it is really since i installed SP3a, anyway, i need SP3a as i had some problem with lock escalation.

 

[BobJacksonNCI]

Indexes?


HTH,
Bob

 

[kalto]

Well, my tables are all already indexed... i've ran the Index Tuning Wizard... and he couldn't offer me any improvement...

As of indexing views... from what i've seen, it isn't possible most of the time

 

[BobJacksonNCI]

I thought that possibly the upsizing wizard copied tables only and indexes had to be created on the SQL side.

Upon rereading, your situation may be normal. (whatever that is)

Bear in mind returning 265,000 rows on your local machine within Access is not the same as requesting SQL to send you 265,000 rows, even on the same PC. I.E., you're now involving BOTH SQL and Access for all of that data. The power of SQL is in returning just what you need.

Can you not do sum aggregation on the SQL side?



HTH,
Bob