Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Slow performance after Migration from Access to SQL back-end Expand / Collapse
Author
Message
Posted Friday, May 7, 2010 1:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 8, 2012 6:52 PM
Points: 18, Visits: 43
Hi.
i was able "after a long time of trial and error" to migrate access database to sql 2005 and use linked tables to access the sql backend from the MS Access front end .MDB file. using upsizing wizard.
all seemed to be fine. working remotely, local adding records.
one thing is driving me crazy is that when we search against a table in access it takes long time.
i ran the profiler on sql server and i see alot of "exec sp_execute'
i am not very god in access . but on SQL i tried to create indexes"non clustered" on the fields we search by, and nothing..

please point me in the right direction!

please if any one can help please let me know...

Thank you
Post #917675
Posted Friday, July 2, 2010 10:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 20, 2010 6:12 AM
Points: 105, Visits: 172
ehamouda (5/7/2010)
Hi.
i was able "after a long time of trial and error" to migrate access database to sql 2005 and use linked tables to access the sql backend from the MS Access front end .MDB file. using upsizing wizard.
all seemed to be fine. working remotely, local adding records.
one thing is driving me crazy is that when we search against a table in access it takes long time.
i ran the profiler on sql server and i see alot of "exec sp_execute'
i am not very god in access . but on SQL i tried to create indexes"non clustered" on the fields we search by, and nothing..

please point me in the right direction!

please if any one can help please let me know...

Thank you
Post #946988
Posted Friday, July 2, 2010 10:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 20, 2010 6:12 AM
Points: 105, Visits: 172
1. Where is SQLServer running? Is it on the same file server that the .mdb file was on or was it on another server? Is the other server located in the same facility as the file server?

2. What results do you see when you use SQL Server Management Studio? SQL Server MS is GUI interface that allows you to work with a database directly in a manner similar to Access DB window. If you run a query in Management Studio, the performance should be similar to that when the same query is run in Access. For the sake of testing, try a simple query such as selecting the Top 100 rows of a table.

3. Do the Access forms use query objects or is the SQL Statement explicity stated in the RecordSource of the form? Now that you've upsized to SQL Server, you do need to go through and check all of the queries and RowSource's of the Forms & Reports and shift the work of doing table JOIN's to SQL server via Views. When Access does a join, regardless of the backend database, that adds overhead right off the bat. It has to do them when connecting to an Access DB, however it can all be shiftted to the backend when using a commerical database such as SQL Server.

Side Note: If you have tables that the users should NOT be able to update that should be READ ONLY, create a stored procedure that returns the records in that table.
Post #946995
Posted Friday, April 15, 2011 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 1, 2011 12:48 AM
Points: 1, Visits: 5
Hi,

I am experiencing another issue while Migrating the Backend database from MS Access 2003 to SQL Server 2005. Have also got linked tables. The front end is MS Access 2003.

I have migrated using the wizard and it was successful including the Data import. But when I run the front end (Access 2003), I get errors like File not found (error 53 on a form) or Invalid use of Null Error 3078, something like that.... Though I get these errors, I am still able to run the application with errors. Could you please let me know how to progress on this... If you have faced any errors of this kind, please help.

Thanks.
Post #1094205
Posted Thursday, April 28, 2011 10:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264
This is not the first time that I heard of a performance issue after migrating from Access to SQL Server.

I would answer David's question because they are very important.

Placement of the mdb and the Servers is very important as was previously recommended.

Also if you see sp_prepare, sp_execute, sp_unprepare that is Access trying to execute a lot of individual queries and this results in a lot of network traffic. Access may execute sever individual queries for on Local Access Query and it does the processing on the workstation. I would recommend creating Stored Procedures and rather than creating views use in-line views or subqueries in your SP's

You can run the wizard to convert you tables but do you have the correct indexes. You should have a Primary Key Constraints on each Table. You should have FK Constraints and they should be Indexed to improve performance.

Make sure that you have to Clustered Index on the correct Column.

After you updating Statistics?

Are your mdb's stored on a Server or on each individual workstation?

If you have the time convert Local Access queries and extract SQL Code from behind the forms. Choose queries in which you get you Maximum Return on your Investment.

There is a lot more to do. When you migrated to SQL Server did you have a Development or at least a QA Environment?

If you can you may want to set up a Test environment so that it makes it easier to identify problems.

Are you using SQL Server or Windows Authentication?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1100370
Posted Friday, April 29, 2011 12:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:44 PM
Points: 959, Visits: 2,884
I'm with the Corgi on this one. I've done many migrations from native Access to Access front end with SQL Server back end. I used the upsizing wizard once many years ago and never used it since then. I examine each table and create it on the SQL Server side now.

There's no substitution for understanding what is going on at both ends and being somewhat expert at both SQL Server and Access.

For bound forms based on queries to linked tables the queries usually arrive at the SQL Server side fairly intelligently - if the tables have the proper indexes on them (always re-link the table after adding an index). There is, however, a tipping point in what Access can handle in a bound form. I had an order entry system that maxed out at around 45,000 orders and then it became slow. The order form was bound to the orders table and the order detail sub form bound to the order detail table. There were a few bound controls - mostly combo boxes - that were bound to customers and products and so forth.

Remember that when you open a bound form, the entire record set is read. When the tipping point is reached you have to come up with a different strategy for the user forms. There's no way around it that I've been able to come up with. My solution to the order entry problem was to create local Access tables for orders and detail in the Access MDB. The form was bound to those local tables (each user had a copy of the MDB on their desk top). There was VBA code that could read in the desired order/order detail into the local tables. The user would edit that and then submit the changes (kind of like a web front end) and then VBA code would push it back out to SQL Server. This has been in place for 10 years with the number of orders now at around a million. There is no performance problem.

The idea is that when you reach the tipping point, you have to get creative, write code and create search forms that will find what the user wants and only deal with that in the main forms.

If you haven't reached the tipping point, then you have to analyze the queries that Access is sending. Get a test server and database for SQL Server and link your Access tables there. Run the Profiler and capture what Access is sending when you open a form. Are your joins on indexed columns and so forth.
Todd Fifield
Post #1100994
Posted Friday, April 29, 2011 12:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:08 PM
Points: 1,508, Visits: 2,686
If you are using an ODBC connection make sure the provider is the "SQL Native Client" version 9.x for 2005, and not "SQL Server" which is the old 2000 version. The native client can be downloaded it is sqlncli.msi
Post #1101009
Posted Friday, April 29, 2011 2:15 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264

I had some painful experiences using various versions of ODBC Drivers with Oracle Versions 8i, 9i, 10g & 11i.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1101054
Posted Friday, April 29, 2011 2:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:13 PM
Points: 144, Visits: 524
Note that you can also use SQL Server Native Client 10 (the current version) with SQL 2000 and 2005 as well as 2008 - it seems to be the most solid of the drivers. Another thing that hasn't been mentioned is adding a timestamp field to each of the SQL Server tables. Access versions from 2003 forward do that, but the doesn't add the timestamp to all tables. In multi-user situations not having a timestamp can cause errors such as you are seeing. See Using the Upsizing Wizard on the Microsoft Office site for detailed info on upsizing. That said, we always build the tables and relationships in SQL Server, as has been suggested.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1101062
Posted Wednesday, June 8, 2011 5:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 2, 2013 4:44 AM
Points: 237, Visits: 54
Hi! If you have large linked tables and join them using queries in Access you can have a performance problem. You can try to use views on the server instead of queries local.
Post #1121627
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse