Client/Server slowdown
Posted in 1998
Topics: SQL Development & Query Writing, Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL, Migration, Import/Export & Data Conversion
I am a relative newcomer to Informix (1 month) but I have encountered a 4GL problem that has me stymied. The DG systems I am working on are configured such that all data physically reside on one machine designated as the server and all 4GL programs reason on another Informix DG box known as the client. There is a third DG box used for developement whereon both 4GL programs and test data reside. I inherited a 4GL problem that prints invoices. When I run 52 invoices through in the standalone developement box I am done in about 50 seconds worse case scenario when there is a lot of concurrent activity. The same invoices run in the client environment takes roughly an hour regardless of concurrent processes. At first I suspected volume in the tables till I saw the SELECT s in the program all were against indexed columns. Still I rlogin ed to the server machine and manually did each of the Selects in the program to eliminate this possibility. All returned their populations in seconds. How do I explain this wide variance in run times? Well the best I can surmise is that my selects are forcing my entire tables from the server to the client whereupon the select statement is then executed. Is this right? If so how do I eliminate this mass migration of data? Would putting the selects as stored procedures on the server help? I have encountered this problem before in Visual Basic where I did not pass through the SQL statement to the server and as a result my SQL statement executed only after the entire table was passed to my client. Again, is this what is happening here?