On 7 Feb 2002, at 14:41, jon-david schlough wrote:

> Hi.
>
> I am a junior developer building a system for a mid-tier content
> services (not porn) company. I'm new to this technology and this list.
> So, first of all, hi!
>
> We budgeted an entire month into choosing an RDBMS. I would be happy
> to write a prose "novice user experience" piece as we continue with
> implementation.
>
> I have never run a database system by myself (I'm kinda young), so I
> was initially attracted to mysql for a number or reasons. Among them:
> a robust user community, usability, speed, tons of third party tools,
> and simplicity. Figured out that while InnoDB table types make mysql
> more robust and basically transactional (people have pointed out to me
> that there are no hard-fast rules for RDBMS i.e. ACID compliance), it
> lacks triggers, stored procedures, and views. Now, everyone screams
> CODE THEM INTO THE APPLICATION YOU LAMER!! But the spec for the
> project calls for an implementation using MS Access as a client...no
> flexibility there, hence no mysql. Another option was coding a FoxPro
> layer sitting between Access and mysql, a route I was genuinely afraid
> of...
>

If your stuck with Access as a client, you have to make sure that
ALL queries are pass-thru queries, otherwise performance goes
straight down the commode. Say for example you have the
following query:

SELECT I1.INV_NB, I2.ITEM, C1.NAME, P1.DESCRIPTION,
P1.PRICE
FROM INVOICES I1
JOIN INV_DETAIL I2 ON I1.INV_NB = I2.INV_NB
JOIN CUSTOMER C1 ON C1.CUST_NB = I1.CUST_ID
JOIN PARTS P1 ON P1.PART_NB = I2.PART_NB
WHERE I1.STATUS = 'O'

What Access sends to the engine is this:

SELECT * FROM INVOICES
SELECT * FROM INV_DETAIL
SELECT * FROM CUSTOMERS
SELECT * FROM PARTS

Then Access builds it's own query to deal with the data, which
means if you have 5,000,000 invoices, 100,000 customers, 50,000
parts and 25,000,000 invoice detail records, well even if only 3
invoices have a status of 'O' (for open) if you have less then Gigabit,
performance will be Zzzzzzzzzzz.

However if you use pass-through queries and write the SQL in FB
SQL then you get back your 3 records.

Paul
Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com