|
|
|
date: Mon, 04 Feb 2008 16:26:50 -0600,
group: microsoft.public.access.odbcclientsvr
back
Re: Access 2003 to linked SQL 2005 tables very, very slow
After having upsized your database to SQL-Server, the first thing to do
would be to update the statistics using the sp_updatestats procedure and
after that, to clear the caches on the SQL-Server:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
They are other things that could help your database - like reindexing the
tables - but updating the statistics is usually a good step in the right
direction.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Les Caudle" <DotNetWannabe@newsgroup.nospam> wrote in message
news:v14fq3hraqefg6b2ec019gb4e7hbdq3cn7@4ax.com...
> I've got a client who wants to keep running Access 2003, but wants the
> data
> located in sql 2005.
>
> I was able to migrate the data to SQL 2005, but when we attempted to run a
> query
> that took maybe 2-3 seconds for Access 2003 using internal tables (even
> when run
> on the network remotely thru a file share), took 30 seconds with Access
> 2003
> using tables in an SQL 2005 database.
>
> Is there anything I can do to speed this up?
> --
> Thanks in advance, Les Caudle
date: Thu, 7 Feb 2008 13:32:05 -0500
author: Sylvain Lafontaine sylvain aei ca (fill the blanks, no spam please)
Re: Access 2003 to linked SQL 2005 tables very, very slow
You might find something useful in the article at
http://msdn2.microsoft.com/en-us/library/bb188204.aspx.
--
Sco
M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
This email made with 100% recycled electrons!
Miscellaneous Access "stuff" at www.ScoBiz.com
"Les Caudle" <DotNetWannabe@newsgroup.nospam> wrote in message
news:v14fq3hraqefg6b2ec019gb4e7hbdq3cn7@4ax.com...
> I've got a client who wants to keep running Access 2003, but wants the
> data
> located in sql 2005.
>
> I was able to migrate the data to SQL 2005, but when we attempted to run a
> query
> that took maybe 2-3 seconds for Access 2003 using internal tables (even
> when run
> on the network remotely thru a file share), took 30 seconds with Access
> 2003
> using tables in an SQL 2005 database.
>
> Is there anything I can do to speed this up?
> --
> Thanks in advance, Les Caudle
date: Sat, 9 Feb 2008 11:24:18 -0700
author: M.L. Sco Scofield Sco at ScoBiz dot com
Re: Access 2003 to linked SQL 2005 tables very, very slow
Hi Les,
Something isn't right - MS SQL will take longer but it shouldn't impose a
10:1 penalty in most cases.
Assuming the network connection bandwidth is the same (SQL location vs.
remote Access connection), you might want to take a look at the query.
One way this can happen is if you have a query that joins multiple (fairly
large) tables on multiple fields where some of those fields, or the fields
in the where condition(s), are not indexed.
Access can return results a lot faster using Access tables with badly
optimized queries, than it will pulling that data back and forth via an ODBC
connection to a SQL engine.
#1. I would do the SQL maintenance as recommended by Sylvian LaFontaine's
msg.
#2. I would verify: the join fields and any fields referenced in the where
clause(s) are indexed.
Then try it again, see how it goes.
If you cannot shrink the number of fields or the quantity of records being
sifted through, and you have done the two steps above, then the question
becomes - is this a one time query that you build a report or an
informational form with? (Can you live with it?) Or, is this result
something you keep needing over and over in the application?
If a one time snapshot is good enough, a pass-through would be more
efficient, that might work.
Worst case scenario - one that I ran into not long ago... the ugly query was
used by a fairly complicated process of looking up project phase codes and
cost codes. A query with sub-queries, multiple field joins, several
conditions applied to more than one table's fields, bringing together tables
from more than one database. I didn't design the source tables - most of
them were part of a 3rd party vendor's proprietary accounting/costing system
and used on a look but don't touch basis.
I built the query, and it worked, but it looked like a mime part 1 of 2 and
as a pass-through, even as a stored procedure receiving parameters, the
latency wasn't going to work out. I was looking at a cost of 40+ minutes per
day spent waiting for the results plus unacceptable bandwidth utilization on
the LAN and SQL server.
Even if the theoretical expense was acceptable, this doesn't work in the
real world. The timesheet entry would become a full time job and/or the data
entry would get way behind; one does not do data entry efficiently when
the work rhythm is that badly interrupted. The entry person will hate the
application, soon hate their job, and eventually quit.
For each data entry row - based on the selected projectID and the selected
(0-9) master type code, that query had to run to populate the phase and job
cost code value lists. The hit wasn't nearly as bad as yours but it was 4
to 5 seconds - clearly unacceptable when entering maybe 500 or more rows per
day. In contrast, it was virtually seamless using links to a copy of the
same data tables in an Access database I used for development.
As an aside, I had the development data (accdb copy of tables) database on
an USB drive, (got a couple of nasty performance surprises when I flipped
the data source over to the SQL Server). The USB drive is a slick and safer
way to develop but look out for the performance hits - my first 'customer
demo' of this app was a mildly traumatic. The app worked but the bottlenecks
were painfully apparent. I explained it away as minor adjustments yet to be
accomplished and just a matter of optimization... but I was thinking "Oh
excrement, this is ugly!" A fine example of over-confidence being cured by
experience, I won't do that stupid trick again (demo without testing all of
the demo with the production data source, ahead of time).
My savior was the fact that, although the codes were being added and updated
by the accounting dept and project manager regularly, as the project
progressed. However, by the time this labor entry timecard data entry
process took place, the codes for that particular entry would normally be in
place.
My work-around:
I created local tables that mirrored only those fields and datasets required
by this application; only the data required to provide the results for the
troublesome query.
When my application was opened and somebody successfully logged into it - I
deleted *.* from my local tables, and pulled a copy of the current data into
my tables. This took about the same amount of time as running the query
once - however, as the data entry took place - using these smaller and local
tables, the lookup was virtually instantaneous- no apparent delay between
master code selection and tabbing over to the phase code column and being
able to select the phase from the combo box.
A button could have been placed on the top form to run the code that
refreshes the phase and cost information tables, but it wasn't necessary in
this instance. A code not available when the project time sheets are being
recorded means somebody (the project manager or the foreman) messed up or
the entry person can't read the writing; a foreman shouldn't be given a code
to use by his project manager unless the project manager has already (or
simultaneously) places it into the system.
This approach kind of runs against generally accepted good design principles
but in practice it worked for this specific situation. How this might apply
to
your situation depends on the workflow sequence of events.
Hope this helps, or gives you some outside of the box ideas.
Gordon
date: Thu, 14 Feb 2008 20:01:30 -0800
author: gllincoln
|
|
