[AccessD] Terrible performance like I have never seen before

jwcolby jwcolby at colbyconsulting.com
Thu Jul 8 11:41:17 CDT 2010


Doug,

 > Thanks for the details.  All my forms (300+) are bound, and I have a couple of thousand queries 
:(   I guess the logical first step is to start unbinding the forms...

DON'T do that!

I am a bound kind of guy.  I am way to old to get back into the bound / unbound thing but you do NOT 
have to rewrite the application from scratch.

You can simply link the tables to the same table in SQL Server and be up and running, and probably 
running faster than you were because SQL Server will now hand you back result sets rather than JET 
having to do the "ask for index / select / get pieces of file / etc / ad nasium".

That said, it will help if you will do some basics like filter to just the last few records but even 
that is not required.  Whatever worked previously should work now.  You can go do the optimizations 
later.

Test / YMMV etc.

I was getting abysmal performance when I moved my tiny little billing data to a SQL Server instance 
running on my (now woefully underpowered, 2 year old) Dell laptop development machine.  I finally 
moved the database to run on my far more powerful SQL Server box and it runs like lightning.

The moral is, you can get sucky performance from SQL Server but it is likely not oging to happen if 
you have a good SQL Server box.

Just do the conversion, and TEST IT.  Use the Access upgrade wizard, get it working and see what you 
see.

John W. Colby
www.ColbyConsulting.com


Doug Steele wrote:
> Hi Jim:
> 
> Thanks for the details.  All my forms (300+) are bound, and I have a couple
> of thousand queries :(   I guess the logical first step is to start
> unbinding the forms...
> 
> Doug
> 
> On Thu, Jul 8, 2010 at 8:11 AM, Jim Lawrence <accessd at shaw.ca> wrote:
> 
>> Hi Doug:
>>
>> My approach was rather straight forward but there is not real quick way to
>> do it right hence the nearly month long odyssey to get the system moved.
>> Fortunately the client was on a monthly contract so I could spread the work
>> out over a couple of months as they are not likely to just accept a 10K
>> bill...but as they are on a monthly contract that is exactly what they will
>> pay. ;-)
>>
>> I installed a MS SQL on the client's site and used the Upsizing wizard to
>> construct all the tables on the SQL and rebuilt all the queries in Stored
>> Procedures. (I have done this before so I have all basics are pre-built
>> like: Add, Delete, Update, first, next, previous, last, goto-record... with
>> their associated UDF (User defined function... same as a function in
>> Access)) The queries have to be rebuilt to compile with SP standards.
>>
>> The application usually has to under go a few changes.
>> No bound... that is what a SQL server is for; it does the data management.
>> I
>> populate all the forms, reports, list and combo boxes via recordsets. I
>> usually populate a form one record at a time as it is usually so fast that
>> the client doesn't notice any delays.
>>
>> Those recordsets are assembled through a SQL interface module. Rather than
>> go into a page by page explanation suffice to say its sole purpose is to
>> connection with the SQL, retrieve and update recordsets through an ADO OLE
>> interface. The only tricky parts can be from dealing with combo boxes and
>> reports but newer versions of Access take a lot of the grunt work out.
>>
>> If you want to know more I will answer you questions one at a time.
>>
>> Jim
>>
>>
>>



More information about the AccessD mailing list