Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2019, 01:01 AM   #1
nonlinearly
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 3
Thanked 0 Times in 0 Posts
nonlinearly is on a distinguished road
Upsizing to Sql Server performance

Hi, I have migrated an Access 2010 database to Sql Server 2008.
When I open a continues form bounded to a table with 31.000 the form crawls until all records loaded. I did not have this problem before when the data was in Access backend. I realize that when I set the Recordset type property of the form to Snapshot (instead of Dynaset) the performance is much much better!!!
I migrated because I thought that it would be better but what a hellllll
Thanks


Last edited by nonlinearly; 07-11-2019 at 02:24 AM.
nonlinearly is offline   Reply With Quote
Old 07-11-2019, 02:26 AM   #2
nonlinearly
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 3
Thanked 0 Times in 0 Posts
nonlinearly is on a distinguished road
Re: Usizing to Sql Server performance

I removed all fields from form and I added them again. The problem disappears!!!
I don't know what happens and why...
nonlinearly is offline   Reply With Quote
Old 07-11-2019, 02:28 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Usizing to Sql Server performance

see this link about moving to database considerations before upsizing

https://www.access-programmers.co.uk...d.php?t=291269

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
nonlinearly (07-12-2019)
Old 07-11-2019, 02:31 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 165
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Usizing to Sql Server performance

To be honest it's not considered good design to open a form and load all the underlying records.

The normal route would be to load the form "empty" and to give the user some search features and then only retrieve those records that meet their needs.

As you get into bigger datasets this will become essential to stop things bogging down.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-11-2019, 02:52 AM   #5
nonlinearly
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 3
Thanked 0 Times in 0 Posts
nonlinearly is on a distinguished road
Re: Usizing to Sql Server performance

Quote:
Originally Posted by Minty View Post
To be honest it's not considered good design to open a form and load all the underlying records.

The normal route would be to load the form "empty" and to give the user some search features and then only retrieve those records that meet their needs.

As you get into bigger datasets this will become essential to stop things bogging down.
You are right.. this is the technique I use when I develop web applications due to browser considerations. But a desktop application is more flexible on this issue.
In this case, records are few to worry about, and the growth rate is only about 2500 new records per year!
Also the concurrent users are about 10... Not so many to create a problem with sql server!
nonlinearly is offline   Reply With Quote
Old 07-11-2019, 03:00 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,334
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Usizing to Sql Server performance

Quote:
But a desktop application is more flexible on this issue.
only if you have a very good LAN - and sql server will be on a different server - perhaps via a WAN.

When loading a table, Access will display the first few records whilst continuing to complete loading the recordset which gives the impression of a fast load. But with a large recordset, try taking an action once these first few records have loaded - such as a find or sort or go to last record. It won't complete until the recordset has been fully loaded.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-12-2019, 03:58 AM   #7
nonlinearly
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 3
Thanked 0 Times in 0 Posts
nonlinearly is on a distinguished road
Re: Usizing to Sql Server performance

On the occasion of this debate just a thought that came to me ...
I think that adp is a more truly client-server option but unfortunately Microsoft stopped supporting it. So even if I had the option to migrate to .adp (because I use Aceess 2010) I didn't want to for the sake of maintainability in the future.

Pity...


nonlinearly is offline   Reply With Quote
Reply

Tags
sql server 2008 , upsizing

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Performance Issue To Append 3 Millions Records From Access Table To SQL Server Table arjun5381 Modules & VBA 25 11-21-2016 02:45 AM
Performance implications in MDB with linked tables to SQL Server fmanci General 1 02-28-2010 12:47 PM
monitor server performance ? Smart SQL Server 4 04-18-2008 05:44 AM
Issues with bit type fields after usizing tables to SQL SERVER 7.0 roypython General 0 07-20-2005 09:06 PM
Performance issues when changed server IanW General 1 11-06-2002 05:14 AM




All times are GMT -8. The time now is 11:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World