> 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.
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.