Most of the time, the speed is one of poor design on your part.
You move from a little car to a big Mac truck. However, if you try and drive
the big truck up a steep hill with the truck in top gear, then the big truck
will not seem any better at hauling goods then the small car.
So, much of getting sql-server to run well is to "design" for sql server.
Simply moving the data from a JET file share to sql server will not increase
your speed. In fact, we see WEEKLY posts in the sql server newsgroups about
how moving a JET application to sql server in fact runs slower!
In fact, if you are taking about a local hard disk file, then JET is usually
MUCH faster then sql server anyway. You usually will find a big drop in
record read performance when moving from a local JET file to sql server. JET
is faster then sql server in most cases! However, sql server is capable of
using MUCH less network bandwidth then is JET (assuming there is a
network!).
The main trick, or goal when you use sql server is to make sure as much work
occurs on the server, and NOT on the ms-access side.
Here is a few tips:
Don't use any sql that joins linked tables. So, if you have report that
shows a part num, and also needs to lookup the part description from anther
table, you typically just fire up the query builder, and throw in the parts
table, and drag in the description table. In fact, if you have server
lookups, I sure the standard approach is to fire up the access query
builder,a qne you just start dropping in one table after another (that what
I always do!). Well, that going to be very slow for ODBC tables, as you have
dropped LINKED TABLES into the query builder. Since you are using ODBC, then
jet has to join the two tables on the CLIENT SIDE! It can't request sql
server to join together two separate tables via ODBC, as JET knows NOTHING
about the relationships between each file! So,ODBC tables DO NOT join well
at all. (in fact, JET tries to piece them together on the client
side...really VERY messy).
The simple solution for the above is to build the query on the server side
(that what sql server calls a view). If you do that, then the report will
GLADLY accept the where clause, and performance is great. So, #1 is to watch
any part of your application that uses joins on linked ODBC tables...they
just craw like turtles.
Another area is watch forms with several combo boxes. Each combo box can
cause a whole sql statement to be sent to the server, and the form load time
can really be hit hard. You might get away with 1, or 2 combos on a
form...but after that...it really takes a hard hit (and, even 1 or 2 combos
will start to slow down the form load time noticeably). So, once again, set
the criteria, sorting, and the fields display/selection for the combo as a
view. You then bind the combo box directly to that view. This again really
speeds things up. Normally, what happens is you have some sql specified for
a combo box, but it is on a linked table. Once again, using sql statements
that run off of linked tables tends to be a bit slow (you can get away with
one, or so..but that is it!). So, build views for those combo boxes. It
saves some time, and you will notice the difference right away.
Another real big time save is simply to convert many of the reports sql
quires to pass-through. So, if you don't want to mess up the sql database
with a zillion views, then try using pass through quires...as you CAN THEN
freely join multiple tables that way.
Of course, if you have any processing code that updates a lot of records,
then once again, you want to make sure the query is pass-through, or even a
stored procedure on the server side.
So, just keep in mind that using sql with more then one table on odbc does
no work well. However, using views usually does fix that problem. Note that
can still quite well use the "where" clause on bound forms, and bound
reports. So, odbc linked to views works very well (and those view do take
the where clause of forms/reports very well indeed).
I am also sure that most, if not all of your forms are restricted to the
required record *before* the form is opened...right? Surely, you NEVER open
a form to invoice table, and THEN let the user search for a invoice? The
proper way is to "ask" the user what invoice to edit, and then launch the
form to allow the user to work on the ONE invoice. This is good design idea
regardless of using sql server, or JET. However, when it comes to sql
server, then this design approach works wonders. I always coded my JET
applications this way also.
In fact, the following search screen will give you and idea of what I mean:
http://www.attcanada.net/~kallal.msn/Search/index.html