RE: Changed to: Remote database performance

> From: Adam Lang
>
> I have had issues with getting decent response speeds from Windows
> applications I have written using ADO.  I know it isn't the
> AS/400 "per se",
> because the same query will run fast as hell if run with wrkqry.  It seems
> the problem is when trying to use the AS/400 as strictly a database and
> having thick clients access it for data.
>
> Are you saying that is the wrong approach?  I should have the database on
> it, build a server app on the AS/400 that does the actual
> querying and write
> an interface for my desktop client to talk to the server to handle data
> transmission and not rely on IBMs OLE DB drivers?

"Right" and "wrong" are emotionally charged adjectives, I think <grin>.  But
as you've found, ODBC isn't particularly the best performing interface.  If
you think about it, you're sending a string of uncompiled information that
must be parsed and converted to the appropriate data forms before the query
processor ever starts.  Next, you have to find all the appropriate objects
and choose the correct data paths.  Sure, there's lots of optimization, but
that's still a pretty significant amount of overhead to overcome, especially
on more complex queries.  It's even worse when you're only return a single
row or a small number of rows.

A direct client/server approach, on the other hand, can be done by simply
sending a message with the appropriate parameters to a data queue and then
receiving the results.  The server program can be optimized to use the right
data paths, and can be left running, avoiding startup and access path open
times.

Which one is appropriate depends on your application.  If you are, for
example, retrieving a customer record, I suspect it will be quicker to send
the customer number to a data queue serviced by a server that smiply CHAINs
to the customer master and returns the record.  If you are updating or
inserting a record, I've run benchmarks showing a native RPG program to be 4
to 10 times faster than the corresponding embedded SQL, which in turn is
faster than ODBC.


> This performance problem I have is also oriented with Crystal Reports too.

Now, if your data access is very flexible and user driven - that is, the
user can dynamically build their own sort sequences and totalling and things
like that, such as in Crystal Reports, then often ODBC is the lesser of two
evils.  The complexity of the server program required to handle every
possible user request is outweighed by the flexibility of the SQL
processor - and this is exactly the kind of application where SQL (and hence
ODBC) is possibly a good thing.  But you will have to pay for that
flexibility with performance.


> I have a Linux/PHP webscript that runs 2 left joins and uses DB2
> Connect to
> access the AS/400 and performance is a lot better.

I'm not very familiar with DB2 Connect, but it sounds like a more optimized
ODBC.  I believe that the ODBC requests are actually forwarded to the AS/400
and handled there.  How this differs from the ADO approach is beyond my
knowledge, except I know that DB2 Connect supports static SQL, which may be
why performance is better in your situation.  For me, comparing two SQL
implementations is a little like deciding whether I'd rather have hives or
chickenpox, but that's me.

One final difference is that a server can return different contents of each
row.  For example, if you have a complex database that has order header,
order line, shipment detail, and notes, you can retrieve all this data with
a single fetch, with the appropriate hierarchy structure intact (that is,
shipment detail under the order line it is attached to) and without
duplicating all of the data in the higher levels of the hierarchy.  The
fixed-format nature of SQL makes these kinds of fetches a bit more complex.