esql question/tuning?

Hi, we want to tune our esql-Application. What we have is an codefragment that build up an select statement every time (DECLARE/PREPARE/OPEN) because every select goes on in an different table. But - and this is the idea - what if we could 'save' the different DECLARED/PREPARED-cursors and reuse them with OPEN ... USING Nice Idea eh... BUT how i do this in Informix. I play around with if (do-this-only-one-time) { PREPARE sclt FROM "SELECT id FROM thisTab ...." DECLARE scltCurs CURSOR FOR sclt ?? How do i save this cursor (and the memory behind it) in e.g. an hashArray with index 1 ?? Now i want to declare/prepare the next cursor PREPARE sclt FROM "SELECT id FROM thatTab ...." DECLARE scltCurs CURSOR FOR sclt ?? How do i save this cursor (and the memory behind it) in e.g. an hashArray with index 2 ?? and so on... } aktCurs=hashArray[whichCursor]; // access to the array getting the prepared/declared and saved cursor OPEN aktCurs USING .... FETCH ... What i need is an dynamic access to the esql-Variables and the structures behind. Any Idea, or is there someone who have done this already. Best regards Dirk Hellmann dirk.hellmann@laufenberg.com

I am a newcomer to the world of ESQL/C (done most of my work in 4gl), so treat my comments with a degree of caution. I have been thinking along the same lines and came up with a few solutions. 1. Use independent functions with static variables. Each task (insertion into a table is a task, update of a table is another task) would be handled by its own special function. A static variable within the function would be initialized to 0. The first call to the function sets this variable to 1 after preparing the statement. Subsequent calls avoid the declare/prepare because they are with an "if static_variable = 0" statement. Problem : What if the application disconnects and then reconnects (all prepares are lost)? Current solution : Following the EXECUTE code, test specifically for error # 410. If encountered (it means the PREPARE has been 'lost'), force repreparation. 2. Have a single function which does all PREPARing working with a global array with 2 elements (sql_number int, statement_id char(18)). The arguments to the function would be (1) sql_number - a unique number identifying the sql (2) the string to be prepared (3) force_prepare flag 1=Always prepare. The function would return the statement_id corresponding to the sql_number. Other functions needing to EXECUTE statements would always ask for a statement_id from this function. Internally, the function would check the global array to see if the sql_number passed to had been prepared earlier. If so, it would just return the associated statement_id. If not, it would PREPARE the statement after assigning it a unique statement_id, populate the array with that information, then return the statement_id to the caller. The caller has to test for the 410 error; if encountered, it would have to reissue the call to the preparer function with the force_prepare arguement turned on. 3. Use Stored procedures. Unfortunately, there seem to be additional costs associated with the SP call - I'm getting the impression that they are related to copying of the arguments into "Informix's space" (the costs associated with the TEXT variable are documented). I still haven't finalized on the method to use. In tests I have done, 1 and 2 score slightly better than 3, except when using TEXT, in which case they are a lot better. However, if the PREPAREd statement cannot be reused extensively, option 3 is best. Hope this helps. Let me know what you discover. Cheers Rudy Dirk Hellmann wrote: > Hi, > we want to tune our esql-Application. What we have is an codefragment > that build > up an select statement every time (DECLARE/PREPARE/OPEN) because every > select goes on in an different table. But - and this is the idea - what > if we could > 'save' the different DECLARED/PREPARED-cursors and reuse them with > OPEN ... USING > > Nice Idea eh... > > BUT how i do this in Informix. I play around with > > if (do-this-only-one-time) > { > PREPARE sclt FROM "SELECT id FROM thisTab ...." > DECLARE scltCurs CURSOR FOR sclt > > ?? How do i save this cursor (and the memory behind it) in e.g. an > hashArray with index 1 > > ?? Now i want to declare/prepare the next cursor > PREPARE sclt FROM "SELECT id FROM thatTab ...." > DECLARE scltCurs CURSOR FOR sclt > > ?? How do i save this cursor (and the memory behind it) in e.g. an > hashArray with index 2 > ?? and so on... > } > > aktCurs=hashArray[whichCursor]; // access to the array getting the > prepared/declared and saved cursor > > OPEN aktCurs USING .... > > FETCH ... > > What i need is an dynamic access to the esql-Variables and the > structures behind. > Any Idea, or is there someone who have done this already. > > Best regards > Dirk Hellmann > dirk.hellmann@laufenberg.com

The secret key is that you can use a host variable to hold the name of a cursor. In this way you can have a array of cursornames and select the needed cursor when you open it by indexing according to which table you need to access. I assume that the queries are otherwise the same or at least compatible (ie the same number of columns and the same or compatible data types are returned by all versions) if not you can also have an array of sqlda structures selected using the same index. You build all of the cursors and/or sqlda structures at startup and then just use them. It is probably best to NOT have a USING clause in the DECLARE CURSOR statements but to instead have the USING clause for replaceable parameters in the queries. If different cursors use different host variables for parameter replacement these can be arrays or arrays of pointers also. I'll try a simple example below. Dirk Hellmann wrote: > > Hi, > we want to tune our esql-Application. What we have is an codefragment > that build > up an select statement every time (DECLARE/PREPARE/OPEN) because every > select goes on in an different table. But - and this is the idea - what > if we could > 'save' the different DECLARED/PREPARED-cursors and reuse them with > OPEN ... USING > > Nice Idea eh... > > BUT how i do this in Informix. I play around with > EXEC SQL BEGIN DECLARE SECTION; /* I don't remember if enums are supported by ESQL. If not use EXEC SQL DEFINE thisTabIdx 0 etc. */ enum {thisTabIdx,thatTabIdx,...} tabIndexes; char cursArray[10][19]; EXEC SQL END DECLARE SECTION; > if (do-this-only-one-time) > { strcpy( cursArray[thisTabIdx], "scltCursThis" ); strcpy( cursArray[thatTabIdx], "scltCursThat" ); > PREPARE sclt FROM "SELECT id FROM thisTab ...." PREPARE scltThis FROM "SELECT id FROM thisTab ...." > DECLARE scltCurs CURSOR FOR sclt DECLARE :cursArray[thisTabIdx] CURSOR FOR scltThis; > > ?? How do i save this cursor (and the memory behind it) in e.g. an > hashArray with index 1 > > ?? Now i want to declare/prepare the next cursor > PREPARE sclt FROM "SELECT id FROM thatTab ...." PREPARE scltThat FROM "SELECT id FROM thisTab ...." > DECLARE scltCurs CURSOR FOR sclt DECLARE :cursArray[thatTabIdx] CURSOR FOR scltThat; > > ?? How do i save this cursor (and the memory behind it) in e.g. an > hashArray with index 2 > ?? and so on... > } > > aktCurs=hashArray[whichCursor]; // access to the array getting the > prepared/declared and saved cursor /* OK so in my scenario hashArray returns the index of the correct cursor name. */ > > OPEN aktCurs USING .... OPEN :cursArray[aktCurs] USING ....; > > FETCH ... FETCH :cursArray[aktCurs] INTO ...; > > What i need is an dynamic access to the esql-Variables and the > structures behind. No you don't, you had the right idea. > Any Idea, or is there someone who have done this already. Do something similar all the time. Art S. Kagel

Dirk Hellmann wrote: > we want to tune our esql-Application. What we have is an codefragment > that build > up an select statement every time (DECLARE/PREPARE/OPEN) because every > select goes on in an different table. But - and this is the idea - what > if we could > 'save' the different DECLARED/PREPARED-cursors and reuse them with > OPEN ... USING > > Nice Idea eh... > > BUT how i do this in Informix. I play around with > > if (do-this-only-one-time) > { > PREPARE sclt FROM "SELECT id FROM thisTab ...." > DECLARE scltCurs CURSOR FOR sclt > > ?? How do i save this cursor (and the memory behind it) in e.g. an > hashArray with index 1 Store the unique name of the cursor (and statement) in one (or two) string variables. Reference those string variables in the statements: PREPARE :stmt_name FROM :stmt_string; DECLARE :crsr_name FROM :stmt_name; ... Now all you have to manage is the statement and cursor names. Don't forget to free both the cursor and the statement. You can free a statement immediately after you declare a cursor for the statement. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>