How much to convert to SQL server

[Jan]

Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a new client has
requested that it be implemented with a SQL server back-end. I'm doing
my best to learn about SQL server, and I plan to leave the front-end
more or less as-is, just linking to the SQL server back end, but here's
a basic question:

The db has a front-end linked to two back-ends. One of the back-ends has
completely static data, and so in an all-Access installation it sits on
the C drive along with the front end. Only the 2nd backend sits
on the server.

Now, should I convert both back-ends to SQL server, or just the one on
the server? Reasons, pros, cons?

TIA.

Jan

[Rick Brandt]

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

I don't know why exactly the database runs slow with the tables on the
server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation, writing
to a Word document, and a variety of other tasks) went from taking 5
minutes down to less than one. I always blamed it on the slowness of
the client's network, but frankly didn't spend a lot of time
contemplating the reasons; the solution was very effective and I left it
at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model that
involves manipulating a large amount of stored data on other rental
units (that's the static part; the research is done once and stays the
same for a year), but it has to be compared and calculations made in
order to come to some recommendations on the newly-entered unit.
Thus, most of the data is static, but that new record has to be involved.

Make any sense?

So maybe the question is this:
if I haven't had a performance hit from running queries that involved
both local and server tables in the past, would I have that hit when the
"server" tables are SQL Server and the local tables are Access?

Impossible to predict, but on the surface I can think of no reason for
performance to be worse. I just don't see much reason to believe it would be
any better either.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Jan]

Well, it doesn't have to be better, it just has to be comparable.

Slightly different question here: will my testing on the local copy of
SQL server be in any way comparable to how the thing will behave in a
real production environment? I know that when I develop in general,
systems that work fast on my single-user machine can get very poky once
they hit the client's network. Is it the same with the developer's
edition of SQL server vs the full-blown one?

Rick Brandt wrote:

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

I don't know why exactly the database runs slow with the tables on
the server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation,
writing to a Word document, and a variety of other tasks) went
from taking 5 minutes down to less than one. I always blamed it
on the slowness of the client's network, but frankly didn't spend a
lot of time contemplating the reasons; the solution was very
effective and I left it at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model
that involves manipulating a large amount of stored data on other
rental units (that's the static part; the research is done once and
stays the same for a year), but it has to be compared and
calculations made in order to come to some recommendations on the
newly-entered unit. Thus, most of the data is static, but that new
record has to be involved.

Make any sense?

So maybe the question is this: if I haven't had a performance hit
from running queries that involved both local and server tables in
the past, would I have that hit when the "server" tables are SQL
Server and the local tables are Access?

Impossible to predict, but on the surface I can think of no reason
for performance to be worse. I just don't see much reason to believe
it would be any better either.

[Lyle Fairfield]

If it's been running for a few years undoubtedly it needs a complete
revision. Take the opportunity to trash the whole thing and start over.
I get sick to my stomach whenever I see anything of mine that is more
than two years old.

[robert d via AccessMonster.com]

I had a client ask me to substitue the Jet backend with SQL Server. The
logic they used for deciding which tables to put on SQL Server was:

"Our data needs to be on SQL Server".

Therefore, presumably data which was not theirs (like zip code tables, etc)
could remain local. Also, my proprietary tables could remain local as well.
Didn't do the job and have not thoroughly read the other posts in this thread.
Just passing on what their criteria was.
Jan wrote:

No transactions in this one.

If you are using transactions, you probably want to re-write
any transactions that include the static tables. If you use

[quoted text clipped - 63 lines]

>
>Jan

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[salad]

Jan wrote:

I don't know why exactly the database runs slow with the tables on the
server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation, writing
to a Word document, and a variety of other tasks) went from taking 5
minutes down to less than one.
That's a phenomenally long time. I call that coffee-break coding...as
in I want to run the process but take a break.

If I'm not getting results back in 5 seconds (absolute tops) or less.
then there is a problem. If course, I'm not dealing with millions of
records, but for a typical desktop app 5 minutes is way, way too long.

Back in the old days I'd immediately suspect a field I am filtering on,
in a where clause, is not indexed. But in Access where I see people
discussing their coffee-break queries they usually are doing sub-selects
in the query. That's nearly a guaranteed yawner of a query.

I suspect that most regulars in this newsgroup could get your query down
to a couple of seconds if they were working on your application.

I always blamed it on the slowness of the client's network, but frankly didn't spend a lot of time
contemplating the reasons; the solution was very effective and I left it
at that.
Time is money. That's why there's a FedEx. If I can shave 5 minutes of
processing on something that is done every weekday day by 10 users, in a
year the time saving is about 210 hours. You do that for a few more
processes and you're taking some significant time.
Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model that
involves manipulating a large amount of stored data on other rental
units (that's the static part; the research is done once and stays the
same for a year), but it has to be compared and calculations made in
order to come to some recommendations on the newly-entered unit.
Thus, most of the data is static, but that new record has to be involved.

Make any sense?

So maybe the question is this:
if I haven't had a performance hit from running queries that involved
both local and server tables in the past, would I have that hit when the
"server" tables are SQL Server and the local tables are Access?
Well, I can see why the client may want to go to SQL Server, anything
that may help cut down that 5 minute processing time. I simply don't
see SQLServer as the optimum solution. If the time lag was bad before,
why will it be better with SQLServer? Doubtful. Will your client be
happy if, instead of a 5 minute lag time, you cut it down to 4 minutes?
I wouldn't. The more instantaneous you do something, the better.

You do mention a "large amount of data". That means nothing to me or
many developers. Does that mean millions of records? Or does it mean a
few thousand records with a couple hundred fields?

I would really look at your queries. If you use subselects in any of
them, change tactics. Check your indexing and filtering. Do some time
tests.
Jan

[Br]

Lyle Fairfield wrote:

I get sick to my stomach whenever I see anything of mine that
is more than two years old.

I'm like that...
--
regards,

Br@dley

[david epsom dot com dot au]

I don't know about different editions, but
nay code runs much faster against the SQL Server
installed on my PC than it does running against
the SQL Server in another building in another
suburb on another subnet with a different name
server with the other production databases
also loaded.

(david)

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Well, it doesn't have to be better, it just has to be comparable.

Slightly different question here: will my testing on the local copy of
SQL server be in any way comparable to how the thing will behave in a
real production environment? I know that when I develop in general,
systems that work fast on my single-user machine can get very poky once
they hit the client's network. Is it the same with the developer's
edition of SQL server vs the full-blown one?

Rick Brandt wrote:

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

I don't know why exactly the database runs slow with the tables on
the server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation,
writing to a Word document, and a variety of other tasks) went
from taking 5 minutes down to less than one. I always blamed it
on the slowness of the client's network, but frankly didn't spend a
lot of time contemplating the reasons; the solution was very
effective and I left it at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model
that involves manipulating a large amount of stored data on other
rental units (that's the static part; the research is done once and
stays the same for a year), but it has to be compared and
calculations made in order to come to some recommendations on the
newly-entered unit. Thus, most of the data is static, but that new
record has to be involved.

Make any sense?

So maybe the question is this: if I haven't had a performance hit
from running queries that involved both local and server tables in
the past, would I have that hit when the "server" tables are SQL
Server and the local tables are Access?

Impossible to predict, but on the surface I can think of no reason
for performance to be worse. I just don't see much reason to believe
it would be any better either.

[david epsom dot com dot au]

'Theoretically' anything can happen. Using a mixture of
local tables and ODBC links, Access is unable to do a
number of optimisations.

For example, if Access can't convert all of your nested
queries into a single SQL statement, it will run separate
SQL queries against the ODBC source. This never happens
with all-jet tables or with all-SS stored procedures:
Jet doesn't run SQL internally, and neither does SQL Server,
so there is no reason to construct separate SQL statements
for complex nested queries.

On the other hand, from what you say, you might be returning
a single record from your SQL Server, and matching that
to your cloud of static data. There is no reason to expect
that returning a single record from SQL Server will take
any longer than returning a single record from a File Server.

(david)

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

I don't know why exactly the database runs slow with the tables on the
server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation, writing
to a Word document, and a variety of other tasks) went from taking 5
minutes down to less than one. I always blamed it on the slowness of
the client's network, but frankly didn't spend a lot of time
contemplating the reasons; the solution was very effective and I left it
at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model that
involves manipulating a large amount of stored data on other rental
units (that's the static part; the research is done once and stays the
same for a year), but it has to be compared and calculations made in
order to come to some recommendations on the newly-entered unit.
Thus, most of the data is static, but that new record has to be involved.

Make any sense?

So maybe the question is this:
if I haven't had a performance hit from running queries that involved
both local and server tables in the past, would I have that hit when the
"server" tables are SQL Server and the local tables are Access?

Jan

[Anthony England]

"Br@dley" <[e-mail] > wrote in message
news:dt******** **@news-02.connect.com. au...

Lyle Fairfield wrote:

I get sick to my stomach whenever I see anything of mine that
is more than two years old.

I'm like that...
--
regards,

Br@dley

I'm like that with my wife.

[Rick Brandt]

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. .

If it's been running for a few years undoubtedly it needs a complete
revision. Take the opportunity to trash the whole thing and start over.
I get sick to my stomach whenever I see anything of mine that is more
than two years old.

I'm impressed. For me 6 months is usually enough :-)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com