[AccessD] was SQL Server queries - appending strings

In fact there are valid reasons for going to SQL Server, without expecting a
massive speedup.  Things like:

BE expansions, compactions not happening because users still in the db, lock
files that don't clear even when every one is out of the db, lock files that
can't be cleared at ALL without resetting the server, compacts fail as a
result.

Backups not happening because of open MDB files (SEE ABOVE).

Memo fields causing locks on adjacent records preventing users from saving
their memo records.

MDB Database corruptions due to the well known problems with memo fields,
causing maintenance for someone to clean up these corrupted memo fields.

The ever present possibility of MDB corruptions due to NIC problems, power
outages at workstations as they write to the MDB etc.

Limits on the numbers of indexes available in Access due to referential
integrity using indexes for foreign keys, reducing the remaining indexes
available for dates, strings etc.

And finally, there will be parts of the app that can be sped up (I think)
even using ODBC, things like a "claim find" search screen that doesn't need
updateable recordsets, where the entire thing may be able to be pushed out
to the server.

This client is climbing past 50 concurrent users where when I started they
had about 25.  They are doing the obvious, buying a new dual proc server to
run this thing, putting the data store into SQL Server (assuming I say
'go'), splitting their LAN into segments, with gigabit links to the routers,
etc.

This client has a massive amount of data that they want available
"instantly", thus the tabbed metaphor.  They kept asking for more and more
things, some of which required some time consuming things like "TOP 1" from
child tables, available at a glance.  I explained that each of these slowed
the app down, but they wanted them.  Over time, a dozen of these kinds of
things, each slowing the whole down only a bit, the whole lot slowed the app
down a lot.  They wanted it, they asked for it, they were informed that it
would cause slowdowns, they insisted, they got it.

The biggest issue I have at the moment is that (in A2K which is what they
have) recordsets cannot be set as the recordsource without making the result
uneditable.  If I could do that, I'd be in much better shape than I am.
There appears to be no real alternatives (in A2K) to use much of the
capabilities of SQL Server to offload processing to the server, yet still
end up with editable recordsets / forms.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Sunday, February 01, 2004 12:07 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] was SQL Server queries - appending strings


Hi Jim

> There is a lot of work in creating the 'data-interface' to MSSQL and
ADO-OLE
> is the only option (ODBC is too slow....) Stored Procedures are programs,
> subroutines and functions more than just queries. No 'query' import tools
> can work because though SQL SP work similar they are really quite
different
> in concept.

Too slow compared to what? Of course, ADP is the way to go for
Access/SQL Server, but - assuming a high quality LAN - speed of ODBC
compared to Jet and to the client's current needs may be fully
acceptable.

If John couldn't speak for himself, he would only need to quote the
messages on this thread to justify for the client that a total rebuild
of the app is too expensive - either it would kill the client's budget
or it would consume an unreasonable part of John's valuable time.

Now, we don't know why the client has obtained this SQL Server. Is it
an idea originated at the client without consulting John about the
consequences, or did John talk the client into it? In the first case
we have a classic example of a situation where the client may be a
fool but no one wins by stressing that point. Hooking the client's
data up via ODBC may quickly set his SQL Server into action with
little effort and within his budget, and he will be happy about his
decision; then later John can prepare a demo showing the advantages of
moving the app to an ADP but, if agreed to do so, at the costs of the
client.

This could very well be an example where (continued) business is more
important then technical excellence.

/gustav

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com