Performance problem on client/server
Posted in 1996
Topics: Performance & Tuning, Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL
I am a newcomer to Informix (1 month) and have a performance issue in a 4GL program I inherited. The site I am at has 3 DG machines running Informix. Once is a standalone used for developement and the other two are structured such that one is designated as a server with all the data tables and the other contains all the programs. The program I am working on prints 52 invoices in about 40 seconds on the developement machine which has both test data and programs. When I run the same 4GL program on the client with the same 52 invoices it takes 50-60 minutes. How do I explain such a variance when the client/server boxes are much bigger, faster and less in service than the developement box at present.? My theory is that the way SQL statements in the 4GL program are forcing the entire tables (about 64000 rows) to be loaded down to client when executed. I have come to this conclusion because I have seen similar types of programming abuse in VB when failing to do a passthrough or using a stored procedure against the server. Is this what is going on here? BTW I have done a rlogin to the server and manually done the same SQL statements embedded in the program and had the server return the population in seconds; the selection criteria of all statements are entirely against indexed columns. If the entire table is, in fact being uploaded to the client every time then is the work around to use an SPL?