Usizing to Sql Server performance

[nonlinearly]

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

 

[nonlinearly]

I removed all fields from form and I added them again. The problem disappears!!!
I don't know what happens and why...

 

[CJ_London]

see this link about moving to database considerations before upsizing

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

 

[Minty]

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]

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]

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]

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...