Usizing to Sql Server performance (1 Viewer)

nonlinearly

Registered User.
Local time
Today, 11:52
Joined
Jun 13, 2012
Messages
21
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
:confused:Thanks
 
Last edited:

nonlinearly

Registered User.
Local time
Today, 11:52
Joined
Jun 13, 2012
Messages
21
I removed all fields from form and I added them again. The problem disappears!!!
I don't know what happens and why...:confused::confused::confused:
 

Minty

AWF VIP
Local time
Today, 18:52
Joined
Jul 26, 2013
Messages
10,355
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.
 

nonlinearly

Registered User.
Local time
Today, 11:52
Joined
Jun 13, 2012
Messages
21
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!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:52
Joined
Feb 19, 2013
Messages
16,553
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.
 

nonlinearly

Registered User.
Local time
Today, 11:52
Joined
Jun 13, 2012
Messages
21
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...
 

Users who are viewing this thread

Top Bottom