[AccessD] Terrible performance like I have never seen before

Jim Lawrence accessd at shaw.ca
Thu Jul 8 13:45:10 CDT 2010


Hi John:

I knew that comment would flush you out... but I agree with you in doing a
quick dry run first using the upsize/upgrade wizard.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, July 08, 2010 9:41 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Terrible performance like I have never seen before

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
>>
>>
>>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list