Hi,

A client has a very important business critical database system, but
are after greater performance.

Currently they have two front end versions (one for the shop side of
the business - 2 users, one for the office workers - 8 users, held on
the local computers) connecting to 4 separate back-end data files, on
the same server.

The question is, could anyone point me towards the performance
benefits / problems with

a. Merging the four back-ends to one file
b. Upgrading to MS SQL Express.
I'm expecting to go the SQL way, as such what problems am I
looking at?
There are 90 tables over the four files - will I need to rename
all references to them when they change from tblXYZ to [dbo].[tblXYX]
There are a little over 200 queries (!!!!) - same question re
change of names
Any other problems I', likely to encounter - I've not done this
before....

in anticipation, many thanks

Jon

On 31/12/2009 12:47, J-P-W wrote:
Having created all of the new table links in the dbo.tblxxx you will be
able to rename them to whatever you want. Start by renaming your old
tables to tblxxx.old then rename the appropriate table to tblxxx. Then
all of your forms and queries will work without any additional changes.

Once you have the system working you can then delete the old tables and
compact the database.




--
bap (AT) shrdlu (DOT) com

On 31 Dec, 13:15, Bernard Peek <b... (AT) shrdlu (DOT) com> wrote:
Thanks for that, I assume we're keeping the old tables to stop the
queries breaking ??

On 31 Dec 2009, 13:15, Bernard Peek <b... (AT) shrdlu (DOT) com> wrote:
Any thoughts on the performance benefits? As this is, I guess, the
primary question?

"J-P-W" <jonpwebb (AT) gmail (DOT) com> wrote



You mean you keepign the old names the same....

Right???

In other words, make sure your linked talbes are same name.


Keep in mind that SQL server will not improve were solve any of your
performance issues.

In MOST CASES you'll see quite a good drop in performance when you move to
SQL server.

you also don't mention what kind of files and how much space and table size
is you have now?

I mean if you just have small file say really tiny ones with only two to
300,000 records, and you're running on a typical business office network
with only five to ten users, then records search in no time should be
absolutely instant without any delay now. in other words moving such a
system to SQL server will not improve performance, and as mention in many
cases you'll see a drop in performance.

SQL server is indeed a high performance system, and also a system that can
scale to many many users.

If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.

In other words...sql server is rather nice, and is a standard system used in
the IT industry.

However, before you convert..how well does your applciton run now?

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

The other issue is how well is the database setup?

Further..how well are the forms designed?

How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down to you notice?

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I dont think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I cant scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Again I can't stress the myth or false thing that's promoted so many times
in these newsgroups that says that the whole table is drawn down the network
when you use access without SQL server, this is simply incorrect and not
true.


Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKal...rch/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records dont apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Dont
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** Dont use quires that require more then one linked table

(this ONLY applies to odbc to sql server...you CAN and are FREE to do this
with a mdb JET file share..and also with ADP projects to sql server).

and in fact I would say that if you're using links tables, a good number of
the queries that involve more than one table actually run extremely well,
surprisingly well, so I'll rephrase this :

WATCH OUT for queries based on more than one table, if they run really slow,
then consider building them as pass through queries if they're being used
for reports - you'll find significant increase in performance here.


The reason for this is sometimes jet will get confused.

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
can mess up joining tables together. In fact, JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. The simple solution in these cases is to change the query to
view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or two combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view (JUST USE the view name...the sort, and any sql need to
be in the view). The result is quite good combo box load performance. (and
again, not very much work. There are other approaches that can even speed
this up more..but we have to balanced the benefits VS. the amount of work
and coding. I dont think once should re-code all combo boxes to a call back
with a pass-through reocrdset..but that can be a solution also).

If you are using a ADP access project, the above points about the joins
with more then one table does NOT apply..since all queries execute
on the sql server side. (perhaps you could consider converting the
application to a ADP project. I not so keen on ADP projects, but
if you don't know what you doing then it will at least FORCE you
to make most sql run on the server side. However, ODBC is just fine
and is usually EQUAL in performance if you do things right.

** Of course, if you do have sql with more then one table..then pass-though
is the best if using odbc. (again..this does NOT apply to a mdb JET file
share).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right. This approach works for both Jet on a network,
and ODBC to SQL server

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100s of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

So don't be under any false pretense that moving to SQL server will
magically increase the performance of your application: it will not!
Performance gains will only be had if you take specific advantage of SQL
server. And, if you have poor designs now, then moving poor designs to SQL
server will not help a lot.

You never really mentioned how many users and how many tables and how large
the datasets you have now are working, but if you're working with really
tiny tables only two to 300,000 records, and only maybe five are ten users
on a typical office network, it's hard to imagine any kind of delay that
would be noticeable to bring up a record from that file.

Its likely possible that your upgrading to SQL server because you're working
with millions of records, and maybe you have 50+ users. You have to think of
SQL server as simply a larger capacity system. An USA air craft carrier can
carry a crew of 5000 people and can do about 50 miles an hour. You can also
buy a nice little speedboat that carries 4 people, and it also does 50 miles
an hour. So in both cases the little speed boat with 4 people, or the
massive carrier with 5000 people both ONLY run across the water at 50 mph

So SQL server is not necessarily faster, but has greater capacity for more
users. And if you use SQL server correctly, then you can significantly
reduce your bandwidth costs, which of course will translate into
significantly improve performance.

And keep in mind if an network is not involved, then using access and jet
will actually be quite a bit faster than SQL server on your local machine
for many types of operations.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

On 1 Jan, 22:52, "Albert D. Kallal" <PleaseNOOOsPAMmkal... (AT) msn (DOT) com>
wrote:
Albert, Thank you for taking the time to respond - as you so often do.

Very interesting to read, I have less than 10 users, and the largest
table currently has 257,000 rows.

I had convinced myself the slow response was due to the number or
'questions being asked' when a record loads, that is there are
numerous "If value of this = that then enable/disable/change the
colour/ of various things" slowing it down [all requested by the
client, who has been 'warned'], and I guess I may have been right!

As I said I've not previously upgraded a database from Access to SQL,
and was expecting significant performance increase.

I will take on board your thoughts and advise.

Thank you again,

Jon

"J-P-W" <jonpwebb (AT) gmail (DOT) com> wrote



So, you quite small in terms of table size, and quite small in terms
of # of users.

numerous "If value of this = that then enable/disable/change the
colour/ of various things" slowing it down [all requested by the
client, who has been 'warned'], and I guess I may have been right!

You can execute about millions instrucions or
more on the client side, and more users would not increase that
work load since that processing is occuring on each machine.

After you pulled one reocrd from that server then running 10 if's or
10 times that (100 if's) is not going to change performance one bit
here.


Keep in mind if those "tests" are not likey to be running on sql server
and if you just talking about an whole buunch of if's/then/else code
that not going to change your perforamnce one bit.

The areas you want to work on:

Assuming a split database, make sure you have a persitanct connection. This
can speed things up by 10 times or more. VERY important.

Watch the number sub forms you have, you might be loading up one record, but
if you have ten sub forms, then you're loading up ten hits to the server and
ten datasets. if those additional sub forms can or or place behind the tab
control, then such a tab control opt not to load up those forms tell the
actual tab is clicked on, the result means that if you have 2 subforms, or
ten sub forms, the speed of your form load will remain the same.


Opening up a record sets in code is very expensive. So is using dlookups()
in any kind of code loop. If you have to process 20 reocrds in a loop, and
for in each of the twenty recrods you then had to open another table
(another reordset), then then you're opening twenty tables. That will be
turtle slow.

Remember, the time it takes to open up one table is about the equivalent of
dragging about 20,000 records across the network. So, if you changed the
above twenty loop table that opens twenty other tables to a query that joins
in the other tables and now process that, your application will probably run
a thousand times faster.

There's no question the issue you have to be careful when doing things in
code that pulls more data from some place, but as a general rule adding more
code to a formal not slowed down by any perceptible amount if that code is
not causing any data to be pulled down the network.

You want to start looking at your form, and try to figure out what it is
that is slowing your application.

which it might wanna do is start with a form with no two combo boxes are
nothing and it's bound to the large table, then test some code where you
open the form:

eg:

dim lngID as long

lngID = inputbox("what reocord id")

docmd.OpenForm "frmEdit",,,"id = " & lngID

Try running the above test code on a client computer on your network. How
fast does the form open up? (when you hit that 250,000 record table, the
above form should load the form instantly).

Now start adding bits and pieces from the other form that runs slow.
Eventually will hit the point in which you find out what it is that is
slowing that other form down. You can be for certain it's NOT the amount of
code in that form that's going to slow down the load time, it's ONLY going
to be doing things that cause network activity or that code that has to WAIT
for data to be pulled from some place that slows things down...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

On Fri, 1 Jan 2010 16:10:13 -0800 (PST), J-P-W <jonpwebb (AT) gmail (DOT) com>
wrote:

In my experience performance is a function of design. Port a bad
database design to SQL Server or any database engine and it will not
magically work better.
Implement the requirements using sub-optimal procedures and again it
will never work well.
It should also be said that it is very rare to find a great business
analyst, great database designer, great application designer, and
great programmer in one person. Teamwork pays off.
Perhaps I should add "great project manager", one who can say No to a
client. You have issued "warnings"; it is very powerful to confront a
client with different implementations of a feature and together find
creative solutions. The first idea is not always the best idea.

SQL Server can definitely lead to higher levels of performance, but
you need to understand the new environment well, be willing to rewrite
some forms, experiment with different implementation alternatives
(e.g. more client/server and sqlpassthrough), question the wisdom of
some features that were hot a few years ago but are now not used as
much, etc.

-Tom.
Microsoft Access MVP

<clip>

<SNIP>

Gents, thanks!

I had a meeting with the client today, and worked out the actual slow
areas that are most concerning him, and sure enough the combo boxes
have raw SQL as their source, not saved queries. I think some of the
[required] conditional formatting is slowing things up a little.

I'm going to do some re-writing on the problem forms, also the more
troublesome computers are a little underpowered in RAM so they're
going to be updated also - can't do any harm that's for sure!

Regards

Jon

"J-P-W" <jonpwebb (AT) gmail (DOT) com> wrote

If you talking about an JET based application, the above will do ZERO, I
absolute repeat ZERO in terms of performance.

Where did you read the above? Changing a form from a table to a
saved query, or changing the combo box sql to a saved query is
going to do absolute nothing here. Where is this silly type
of solution coming from?

Changing the sql in a combo box to a saved query will not change performance
one bit.

In the case of having migrated to sql server, removing all sql and placing
ONLY an base table name (that is linked to sql server) will often help a
bit, but if you only have 1 or 2 combo boxes on the form, it will not help a
whole lot. in fact, if you change the sql in the combo box to a saved query
when using sql server, there is a good chance you see a slowdown UNLESS that
query is pass-though.

In your case (so far) you are talking about an non sql server application,
and changing the sql in the combo box to a saved query is a waste of clients
time, and it will accomplish zip, zero, nothing at all.

Where did you see the above suggesting? And, MORE important, WHY would it
change the speed of the combo box?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com