Performance problem with scrollable cursor?

Hi all, Can anyone clarify this issue please. We've been benchmarking dynamic SQL with WATCOM SQL and Informix 4.1. Our application set up a spreadsheet control in Visual Basic talking to a DLL for openning cursor and fetching rows. The test table has 5000 rows and 37 columns. With WATCOM SQL, we can position anywhere in the spreadsheet and it appears to us that it's instantaneous to fetch in 50 rows, whether you are positioned on the top of the table or at the bottom of the table. Same thing took 11 seconds for Informix 4.1 when we try to fetch the last 50 records from the first fetch which return the first 50 records. Why? What we did was setting up a scrollable cursor and fetch in the absolute rows, depending on where the spreadsheet position is. In this case, we just drag the scroll thumb from the top to the bottom of the spreadsheet control. Informix SQL reference manual states that the database hase to create a temporary table on the server to hold the active set of data. The data transfer would explain the performance degradation but it allows you to fetch first,last or any intermediate rows and fetch rows repeatedly without having to close or reopen the cursor. So I guess my questions are: How can we avoid that performance degradation? Did we do things correctly in Informix? Does this problem happen with other Unix database vendor such as Oracle and Sybase? (we have plans to port to these DBMS). thanks Bach -- -------------------------------------------------------------------------- |Internet: aruba!bachn@uu2.psi.com Bach Nguyen |UUCP : ...!uunet!uupsi2!aruba!bachn Club Zed | --------------------------------------------------------------------------