Ncheema 2012-04-30 07:32:13
I need your suggestions: I have a software that uses MS Access
as a front end, and has MS SQL as a back end database. SQL Server is
running on approximately 2 GB of RAM. Database size is approximately 1
Problem I am having is that when I do queries using the front end. The
application responds very slowly on the network. I understand that I
am probably not providing enough information about the problem. But
can someone please comment on the following
1) Is it good idea to have MS Access as front end and SQL Server as a
2) if not what may be causing this slowness?
3) What are few things that I can look into for performance testing?
5) are there any know issues with ACCSS/SQL server combination?
6) I understand that SQL server may also be contributing to this poor
Performance, what are a few things that I can check to see about
7) What measures should I take bafore to decide, whether I want to fix
One or invest money on getting a new one? What should be a deciding
Considering that I do have some cash in hand for possibly a new off
8) If its network related, what kind of testing do think I can do?
Any Ideas, Help, Comments, by the ways.. all appreciated.
Thanks all for your help in advance.
form on a software that
(pete cresswel 2012-04-30 07:32:15
A lot of people would say it’s the Good Right And Holy Path for mission-critical
applications. Defintatley nothing wrong with it performance-wise unless the
machine hosing the DB is overtaxed.
Reading between the lines, it sounds like you’re running MS Access queries
Have you tried writing a stored procedure and calling it via ADO?
Also, a middle-of the road approach: Make the problem query into a
“Passthrough” query and, if necessary, tune the SQL for SQL Server.
Finally, somebody should check the joins in the problem query against the
indexes in the SQL Server DB.
Bruce pick 2012-05-20 04:10:01
Another solution, somewhat similar:
Copy your Access query and rebuild it as a view in SQL Server.
It likely will run faster there, and you can link to the view as you
would link to a SQL Server table.