migration performance question

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I migrate a system from MS Access backend to SQL Server backend and
using same MS Access frontend.

It takes 2 minutes and half to run the report using MS Access backend
and it takes about 30 seconds using SQL Server backend.

I just wonder is how much performance will improve from MS Access to
MS SQL server backend.

I tried to run the query that it takes 5 seconds to get data using SQL
server backend and takes about 10 seconds to run the query from user
interface and it takes about 30 seconds once I run the report link to
the record source.

I just wonder does MS Access user interface and report have a lot over
head to run SQL Server backend record source or I am doing something
wrong.

I thought that migration from MS Access to SQL Server backend will
improve performance dramatically.

Your help is great appreciated,
 
I thought that migration from MS Access to SQL Server backend will
improve performance dramatically.
Click to expand...

It can be improved, it can be degraded, it can remain about the same. The
devil is in the details!

See Tony Toews' discussions on his Performance FAQ and his upsizing pages at
http://www.granite.ab.ca/access.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
m:
I thought that migration from MS Access to SQL Server backend will
improve performance dramatically.
Click to expand...

No, it improves some things and will degrade others. It all depends
on the specifics of each particular operation -- some will be handed
off by Jet/ACE to the server, some will be processed inefficiently
on the client. It all depends on context.

For what it's worth, my experience is that I'm terrible at guessing
which operations will be improved and which will cause problems. In
almost all my upsizing projects, I've had one or two particular
operations that I was sure would have to be completely re-written to
run efficiently, but, in fact, ran just fine. Likewise, I had one or
two operations that I thought would be just fine that turned out to
be absolute dogs against SQL Server and then had to be redesigned.

So, it's not really predictable.

Moving the data store to a server is no magic bullet, and the
benefits in performance are very often much less than expected. That
is why you should never upsize JUST for performance reasons unless
you are prepared to do a major rewrite to wring the maximum
performance out of the new configuration.
 
Thanks

David-W-Fenton said:
m:


No, it improves some things and will degrade others. It all depends
on the specifics of each particular operation -- some will be handed
off by Jet/ACE to the server, some will be processed inefficiently
on the client. It all depends on context.

For what it's worth, my experience is that I'm terrible at guessing
which operations will be improved and which will cause problems. In
almost all my upsizing projects, I've had one or two particular
operations that I was sure would have to be completely re-written to
run efficiently, but, in fact, ran just fine. Likewise, I had one or
two operations that I thought would be just fine that turned out to
be absolute dogs against SQL Server and then had to be redesigned.

So, it's not really predictable.

Moving the data store to a server is no magic bullet, and the
benefits in performance are very often much less than expected. That
is why you should never upsize JUST for performance reasons unless
you are prepared to do a major rewrite to wring the maximum
performance out of the new configuration.
Click to expand...
 
Thanks for helping,
I just remove my chart in the report and it runs fine now.
It seems that MS Access does some internal queries for the chart for
my applications,

Thanks again for helping,
 
Moving the data store to a server is no magic bullet, and the
benefits in performance are very often much less than expected. That
is why you should never upsize JUST for performance reasons unless
you are prepared to do a major rewrite to wring the maximum
performance out of the new configuration.
Click to expand...

I would've put it more along the lines of upsize and then fix what is
broken and then what is the most irritating from the users viewpoint.

A client upsized a timesheet app with 900K records in the main
transaction table and did some tweaking once things settled down. The
screen showing the last weeks worth of transactions came up so fast
"It was scary fast" in the words of the senior payroll clerk. Instead
of taking 30 seconds like it did before. A bit of tweaking and
reports run multiple times every day would be displayed in a second or
two rather than 20 seconds as before.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
I would've put it more along the lines of upsize and then fix what
is broken and then what is the most irritating from the users
viewpoint.
Click to expand...

Well, I agree with what you said because in comparison to what I
wrote, you left out the "JUST for performance reasons" part of my
formulation. There are many benefits to upsizing that can justify it
and that have nothing to do with performance per se.
A client upsized a timesheet app with 900K records in the main
transaction table and did some tweaking once things settled down.
The screen showing the last weeks worth of transactions came up so
fast "It was scary fast" in the words of the senior payroll clerk.
Instead of taking 30 seconds like it did before. A bit of
tweaking and reports run multiple times every day would be
displayed in a second or two rather than 20 seconds as before.
Click to expand...

Yes, of course, but I was addressing the use of performance as the
only justification. In that case, you don't really know what you're
getting into, and the maximum performance can only be acquired from
doing a lot of work (whatever the low-hanging fruit there might be).
 
If you keep using SqlServer in the same context as a MSAccess
db (file
server) instead of converting your queries to TSql so the work is
done by SqlServer first, then there wouldn't likely be any
dramatic increase in performance.
Click to expand...

Not necessarily. It all depends on what is happening in each query.
The point is that you can't COUNT on an improvement (indeed, you may
see terrible performance degradation!), but it's quite common for
much of an app to run faster without changing a line of code or SQL.
 
Back
Top