Thanks for various replies, ended up in hospital so I couldn't deal with
them. Have found somewhere to host MySQL, and exported my BE database (tables
only) to it. There appear to be minor problems, but nothing significant. The
problem is the speed. Impossibly slow. My main form takes nearly 10 minutes
to load, and a number of seconds to go from 1 record to the next. It is just
not usable. There are a number of subforms, combos and queries all running to
generate the form. In some cases the queries are using either built-in Access
functions or user defined functions. Am I wasting my time?
Thanks
Phil

Hi Phil,

My guess is that you have a lot of processing going on inside of Access
itself that needs to go back and forth between the application and the
server. This can create a severe 'drag' on the operational speed of your
app. Perhaps you can post a sample that represents how one of these
forms works so we can take a look.

The other things to look for are:
- Is indexing turned on for the DB
- Is the DB Server hardware exclusive to you or shared?
- Is the table type InnoDB or MyISAM?
- LAN or WAN connection speed

I find that most of the performance issues I face come from not 'keeping
the wire cold', and forgetting to let the DB server do the work instead
of Access (where appropriate). I am guessing that a few Pass-Through
queries will make a lot of difference if arent already using these.

Let us know.
--
Cheers

The Frog

On 09/08/2011 17:36:57, The Frog wrote:
Blimey, that was a quick response. Ink wasn't dry on my question Great
Difficult to post a sample main form has 4 subforms, 2 with a combo box on
them, 10 or more combo / list boxes, 3 tabs and involve some 25 or so tables
with lots or queries and sub queries - so yes - a lot of data to go back &
forth, especially with a search. I am brand new to MySQL, so forgive me if I
give stupid replies. Not sure what you mean by "Is Indexing turned on - does
this relate to Access or MySQL? DB Server is shared
Table type is InnoDB as I understand that is the only type that supports
relationships Will investigate what a "Pass-through" query is. I am guessing
this is held on the server, so reluctant to use it as I use the same FE
database with a number of BE databases, and all but one of these is for
single user use. The existing databases are well established, and I don't
really want a major redesign problem. Just for one of these databases, a
number of people need access to the information, hence looking at MySQL
Thanks again
Phil

One quick response: a pass-through query exists on the client machine, but
is executed on the server. "Pass-through" tells Jet or ACE, "Keep your
cotton-pickin' hands off my SQL, just pass it through to the server DB."

They can enhance performance, but they can later turn out to be a
maintenance nightmare... with functionality in two places, server and
client. That means whoever maintains the database has to have skills in
both. For several years, I worked as a subcontractor to a contractor to a
client who specified, "Keep your functionality on the Access side. It's
easier and less expensive to find a competent Access contractor than to find
a competent Access-and-Informix contractor."

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Hi Phil,
I am now strongly suspecting that your queries are all Access based
and not MySql based. As a simple test, can you take one of your
existing queries that takes a while to run, for arguments sake fix
any variables to known values for the test, then run the query and
time it. Then do the same as a passthrough (select query type in the
query designer menu, I think it is under SQL specific / special.). If
I am right it should run a buttload faster.

Indexing is used to make queries run faster and also to enforce
referential integrity. Typically the fields in WHERE clauses and ON
clauses are indexed on tables so that the queries need only look up
the index for what is needed instead of scanning the entire table(s)
data.

Can you post some of your SQL for us to look at?

--
Cheers

The Frog

Hi again Phil,

I was thinking about the comments from Larry about multiple skillsets
being required to do the upscaling. I would have to say that in essence
he is right, but only because that is what upscaling is - moving the
heavy lifting (so to speak) to a 'bigger' back end. The same issues
arise with any non MDB / ACCD back end.

One of the reasons that I like MySQL with an Access FE is that MySQL is
damned easy to use and at the same time extremely powerful (not
suffering from the limits imposed by the free versions of SQL Server for
example). It is also extremely prevalent in use on the web and in many
businesses. The SQL dialect is extremely similar to Access's own
dialect, so close in fact that I think anyone moving an Access SQL query
to MySQL could probably do so without having to do anything to it except
in some rare instances (maybe handling dates). In short you get a hell
of a package at a hell of a good price (free for the community version
which I have never needed to shift from), complete with most all the
'big boy' features of expensive databases.

In short, for doing the job you need to do, a server BE is probably your
best solution. MySQL or for that matter any server BE all have some
differences to Access itself.

I just wanted to offer a counter opinion to Larry's quite valid point. I
must also ask Larry: Who actually uses Informix nowadays anyway? I
havent seen it in use in a company since probably the late '90s.

How did you go with the SQL passthrough scenario? I seem to recall that
I had some code somewhere to do the table re-linking and query
re-linking. I will have to have a dig for it.
--
Cheers

The Frog

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:j1rn48$dp1$1 (AT) speranza (DOT) aioe.org:

You're loading data before your user can actually do anything with
it. That violates the key principle behind a proper client/server
design, i.e., don't retrieve any more data than necessary for the
task at hand.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 10/08/2011 9:39 PM, David-W-Fenton wrote:
Amen to that. The less that travels down the wire the better. The
fastest code is the code you dont run..... (I am sure there are plenty
more of these)

--
Cheers

The Frog

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

I worked on the application that used Informix, off-and-on, from 1995 - 2001
when it was the corporate standard database for a client. That client
merged with another company and the application was no longer needed.

Sometime, IBM bought Informix, and seems to still offer it... see
http://www-01.ibm.com/software/data/informix/. They even offer a free
Developer Edition... it wasn't obvious to me on a quick scan whether any
other editions were free, but they might have something equivalent to MS SQL
Express.

It was a stable, solid database when I used it... our app had just under 300
users scattered over the US and Mexico on a WAN (Access 2, Jet, ODBC,
Informix)... and the client had other applications with thousands of users.
I would expect that under IBM's tender loving care, it is still a stable,
solid database, perhaps even more so.

We had a few Views (looked identical to Tables to Access, but forced some
work to the back end) for very complex Reports, and one passthrough query to
invoke a stored procedure to force a monotonically-increasing unique ID --
it created a unique id, created a record, and returned the id.

We had to re-create the links each time we switched the database from the
test database to the production database (a release cycle) -- refreshing the
link did not work. But, we had code to do it, so it was no great burden.

Larry

On 8/10/2011 3:01 PM, Access Developer wrote:

http://www.mysql.com/why-mysql/marketshare/

Bob