Re: Localizing SQL Server Data
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 2 Sep 2008 13:39:23 -0400
The best way to limit the data transferred through the wire is to built
limit on the data required, right at the start of the form or report with an
appropriate WHERE clause (or criteria). After all, even your
'date-stamp_version' solution, this is what you intend to do: bring ONLY the
data that has been modified, isn't? So, instead of bringing ALL the data,
use the appropriate conditions which will limit the data.
If you have joins with one-to-many match, you MAY find it faster to bring
the data inside local table (tables in the front end) and then, make the
join based on those local tables. Indeed, if your join result in most of the
data being repetition (in order to fill the rectangular "look" of a join),
if you do the join on the Server, that means that the same repeated data
will be transmitted many times over the wire; while if you make the join
locally, the data is send WITHOUT any repetition over the wire, so, in the
end, you may save time:
- empty the temp (local) table,
- copy the COLUMNS you want (and only them) from the server into you
local tables, for ONLY the records you know that your query is likely to
use. THESE two limitations may sound like extra work for you, but it
generally worth every second of your time in FORCING you to impose yourself
to FIND limitation at THIS level.
- make a local query implying the local tables.
Note: that also means you don't use HUGE combo box list on your form: more
than 100 items in a combo list box is just as useful as having no list at
all (and doing the check of membership explicitly, through your code, rather
than relying on NOT IN LIST), and *IS* a huge burden on the network. That
is also even more important to have small sub-form: instead, have the end
user explicitly open another form to see the extra details, AS REQUIRED,
instead of showing it, uselessly (and at a very high cost).
Final note, a large portion of the network bandwidth can be used by
listening to audio coming from Internet. You should make a policy to NOT
listen to Internet audio/video, mainly if the bandwidth is small.
Vanderghast, Access MVP
"LeonJ" <LeonJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1FA2EA1E-694B-4867-A4BA-7818EDB8613C@xxxxxxxxxxxxxxxx
I understand your point and will need to be careful to not migrate any
reports/forms to this method that are better done on the server because of
the extra data that would be sent.
There are a few augmenting facts. Many of the forms, especially, already
pull all, or almost all of the data. I didn't mention it, but I think the
processing on the server itself may be overloading it, too (I'm hoping
offloading some will help that out). And, my largest tables already have
an
updated column which tracks whenever something changes, so I will only
need
to pull the changed records for those tables.
Thanks
"Jeff Boyce" wrote:
Leon
I may be missing something...
If you pull ALL the data needed to run a query down to the local level,
won't that be more data on the line than you'd pull if you returned ONLY
the
data that meet the criteria in the query?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"LeonJ" <LeonJ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F5468DDE-2CD2-4DA5-AA23-487CC093FE38@xxxxxxxxxxxxxxxx
Hello,
Problem, our user base has grown tremendously and the very
user-friendly
front-end to a SQL Server backend is now taxing our 100mb local net.
We
are
a seasonal agricultural product distribution company so our busiest
times
create the slowest response times ... not good!
So, I'm dreaming up the quickest fix I can think of. For the slowest
and
most used reports and forms I think I could fairly quickly move the
queries
to get data from a local db. If I pull the data from the server direct
to
local tables (separate mdb, of course) and query from them, I may get
faster
response time for the report/form and will cause less network traffic.
First, Is this a good idea? Why or why not?
Leon
.
- Prev by Date: Force Access to Reapply Format Conditions
- Next by Date: Re: Force Access to Reapply Format Conditions
- Previous by thread: Force Access to Reapply Format Conditions
- Next by thread: Re: Localizing SQL Server Data
- Index(es):
Relevant Pages
|
