Converting JET Queries to MSDE Stored Procedures

[Robert1105]

We are converting to MSDE in Access2000. The biggest challenge seems to be
migrating the queries to MSDE stored procedures. I was trying to find out if
there are any tools out there that can make this migration easier.

I would appreciate any advice and comments to help make this migration
easier. We have about 300 queries to recreate as stored procedures.

 

[Albert D.Kallal]

You don't mention if you are talking about a adp project here, or a linked
mdb file?

Are you using the enterprise tools to manage the MSDE (you can do this).

Assuming a linked mdb file, then you have a few choices:

#1 - don't do anything. If most of those select queries don't involve
multiple tables, then you don't have to change them, and performance is
usually ok. And, for ones that do have multiple tables/joins, you simply
move the sql to sql server, create a view, and then link to this view. You
then use the view name in place of the query name on the mdb access side.

#2 - move all of the sql to sql server, and create a view for each query. Do
note that the functionally equivalent of a view in sql server is the SAME as
a saved select query in ms-access.

Also, it is not clear why you are moving select queries to stored
procedures? A query in ms-access should as a general rule be moved to a view
in sql server. And, you ONLY need to do this for queries that don't perform
well (this assumes you are going to continue to use a mdb, and not a ADP
project). Choosing linked tables, or a adp project will depend on how much
code you have. If you got a lot of dao code, then you are better off to keep
the file as a mdb, and link the tables to the msde.

Are you converting existing dao code in your application, or are you just
migrating some data, and not bothering to fix/change the code in the mdb
file? (I just want to point out that in a ADP project, none of your dao code
will work).

 

[Robert1105]

Our current plan is to use a linked mdb file as our application is quite
involved and has a lot of dao code.

We are using Access to manage the MSDE. Are you referring to using
Enterprise Manager in SQL? We looked at this but could not find the DMO
Tools in our SQL 7.0 version? Is this a special add-in?

Our reasons for conversion are:

1) Gain access to stored procedure for a number of our queries that are very
complex and slow in the present environment. We are hoping this will improve
some of our performance issues. We are also doing some optimization as well.

2) Offer us an upsizing opportunity to move to a full SQL database down the
road.

3) Enable us to link to other sources easier using the SQL database.

We are not contemplating ADP as this point as the migration effort is too
big for us at this time becuase of our use of dao code.

If you have any other recommendation for us, we are definitely open to other
ideas.

Thank you for your response.

Robert Sombach

 

[Albert D.Kallal]

Our current plan is to use a linked mdb file as our application is quite
involved and has a lot of dao code.

Good, I also find that the above makes the most sense for existing
applications.

We are using Access to manage the MSDE. Are you referring to using
Enterprise Manager in SQL? We looked at this but could not find the DMO
Tools in our SQL 7.0 version? Is this a special add-in?

Yes, you need to install the sql server tools to manage the MSDE. The MSDE
does NOT come with the sql enterprise manger tools. However, there is a neat
way to get the enterprise manager for free. Simply download the trial
edition of sql server (I don't know if it is still available, now that we
got sql 2005 express). When you download the trial edition of sql server,
simply install the CLIENT TOOLS ONLY, and you get a copy of the enterprise
manager for free, and this is NO expiry date on the client tools for sql
server.

So, yes, I am saying to manage the MSDE with the sqls erver client tools. In
fact, if you don't use a ADP project, then how were you managing the MSDE
from a mdb file anyway? (can't do much with a mdb to manage the MSDE). So,
it makes a lot of sense to get the sql server client tools running on your
computer, as then you get the SAME tools you use with the full blown version
of sql server, and yet you can do this all for free, and legal too!

1) Gain access to stored procedure for a number of our queries that are
very
complex and slow in the present environment. We are hoping this will
improve
some of our performance issues. We are also doing some optimization as
well.

Remember, a local jet data database as compared to a local sql server (FULL
or MSDE) tends to run quite a bit faster then sql server. This is nor for
all cases, but as general rule I find JET quite a bit faster then sql
server when no network is involved. Of course, this assumes you don't have
code that does dump things like try and open a recordset in the middle of a
loop. If by converting some VBA code that opens a reocrdset in the middle of
the loop to a stored procedures, you see a ENORMOUS increase in speed, but
that increase is only due to removing the opening of the reocreset in the
loop

Anyway, the above is a great way to get the enterprise manager installed,
and the EM does work very well with the MSDE.

 

[Robert1105]

Thanks Albert.

I was wondering if there is any information comparing Jet performance to
MSDE and SQL? We are hoping to obtain performance improvement as we have 7
to 9 users loggin into our solution in many of its applications. Our goal is
to satify the entry level with MSDE and offer an upsizing opportunity using
SQL. We are hoping that we will not see less performance with MSDE for our
entry level users. Are our expectations unrealistic?

Sincerely,

Robert Sombach

 

[Albert D.Kallal]

Thanks Albert.

I was wondering if there is any information comparing Jet performance to
MSDE and SQL? We are hoping to obtain performance improvement as we have
7
to 9 users loggin into our solution in many of its applications. Our goal
is
to satify the entry level with MSDE and offer an upsizing opportunity
using
SQL. We are hoping that we will not see less performance with MSDE for
our
entry level users. Are our expectations unrealistic?

You have to remember that MUCH of performance is a issue of design. As I
mentioned, you can't do dumb things like simply open up a form attached to a
large table, and expect the application to run well. If you got designs that
do that now, moving these designs to sql server will be disaster in terms of
performance.

Of course, in the above example, you simply 'ask' the user for a account
number, or whatever BEFORE you load the form. You can still use bound forms,
but you at least need some type of where clause to restrict the record(s)
that the form will work with.

When I mentioned that jet is normally faster then sql server, we are talking
about a single user application without a network. When you go multi-user,
then of course sql server is going to perform better. And, sql server will
scale to more users. Remember, a aircraft carrier and a small speed boat can
both do 40 miles hour on the water. It is just that one can CARRY a lot more
people (4 people vs 5000 people). Note however, that aircraft carrier is NOT
faster then the speed boat. You MUST keep this concept in mind when looking
at your designs. Sql server can carry a larger load, but is NOT necessary
faster.

So, that why I am asking at what point does your application slow down? Is
it ok with 2 users, but when you triple the user load to 6 users, is it now
too slow? And, if it is too slow with one user, then you are in trouble....

So, the first question you have to ask in terms of performance is after how
many users does the application slow down after? Is it ok with 3 users but
then when you got 6 or 7 users (2 times the user load), is the application
now too slow?

It is possible that you now have such large data tables and such large
amounts of data, that you have outgrown the JET engine, and need to switch
to sql server. However, you given no numbers terms of table sizes, and max
number of records in a given table.

Remember, switching to sql server will NOT fix poor performance problems on
it own. Your designs still need to be optimized here.

I have on occasions replaced mini-computer systems with a simple ms-access
file share (no sql server). When to upgrade that application to sql server?
Hum, in that application, I have 4 to 7 users. The application was medium
sized (about 160 forms). The back end part (the shared mdb file) had about
55 tables, and many tables where highly related. With 4 to 7 users on the
phone all day dealing with customers in a VERY interactive way, the
application performs near instant. On the other hand, the tables in this
application are VERY small. Most are only about in the 50,000 to 75,000
record range, and so with such small tables then you don't usually
experience a slow down anyway.

I think there is one form that takes almost 2 seconds to load, but a as
general rule with only 4 to 7 users, and such small data tables this
application really screams. I can't see why this application would not run
fast with 15 users.

So, assuming your data tables are much larger, and you got more users, then
certainly upgrading to sql server makes sense. And, sql server is more
reliable.

Just keep in mind that upgrading to sql server will not fix performance
problems, and we see weekly posts in the sql newsgroups that the ms-access
applications often slow down when converted to sql server. With good
designs, then of course sql server will SCALE to MANY more users,a nd you
should see a performance improvement.

 

[Robert1105]

Thanks Albert. This is great advise.

Based on the numbers you have provided, it seems we need to do more work in
the DB design (redesign) and optimization than I had thought.

We have 173 tables in our administration application and 142 in our main
client application. This is the application used by the main user group day
to day. There are a few large tables however, many of the tables have 4,000
to 5,000 records. Our largest table may get to 50,000 records. A large user
environment would have a database of 400 MB (max) today.

Most of our sites have 2 to 4 users. These generally run OK. We run into
issue when we have 8 to 9 users. Of course other issue affect performance
such as network design and server loading in our larger sites as well.

So our thoughts on performance is more an issue of being able to support the
larger sites that are adding more users. Also, we are looking at external
integration links to Web Sites and other services which would seem to be
easier in SQL.

Regards,

 

[33ana]

Our current plan is to use a linked mdb file as our application is quite
involved and has a lot of dao code.

We are using Access to manage the MSDE. Are you referring to using
Enterprise Manager in SQL? We looked at this but could not find the DMO
Tools in our SQL 7.0 version? Is this a special add-in?

Our reasons for conversion are:

1) Gain access to stored procedure for a number of our queries that are very
complex and slow in the present environment. We are hoping this will improve
some of our performance issues. We are also doing some optimization as well.

2) Offer us an upsizing opportunity to move to a full SQL database down the
road.

3) Enable us to link to other sources easier using the SQL database.

We are not contemplating ADP as this point as the migration effort is too
big for us at this time becuase of our use of dao code.

If you have any other recommendation for us, we are definitely open to other
ideas.

Thank you for your response.

Robert Sombach