Thanks Razzak, I've got a couple of questions.
1. Does QUALCOLS have any effect on TEMP tables? I have many places where I do massive updates on temp tables and wonder if this would help avoid the I/O errors associated with this. 2. Does QUALCOLS have any effect when INSERTing many rows in either a temp or permanent table? Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A. Razzak Memon Sent: Tuesday, February 26, 2013 11:19 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Tip of the Day: Optimizing R:BASE Multi-User Environment Tuesday, February 26, 2013 Tip of the Day: Optimizing R:BASE Multi-User Environment Product: R:BASE eXtreme 9.5 (32/64) Build..: 9.5.2.20128 or higher www.rupdates.com Section: Multi-User Environment Considerations The ability to fine tune R:BASE itself is one of the many enhancements added over the years. Most often, R:BASE is able to make the right decisions about the best way to process commands. Generally, you know your database and application code better than anyone else. However, you should always take advantage of the latest enhancements and syntax, and would pick a more efficient method if given the opportunity. Did you know that there are three settings that can improve performance in a multi-user environment? . STATICDB . FASTLOCK . QUALCOLS STATICDB limits structural changes to the database, and activates a read-only schema mode. When STATICDB is set to ON, permanent changes cannot be made to the database structure (table and column names, for example). New tables can be created, but they are temporary and visible only to the user who created them. By restricting changes to the database structure, R:BASE does not continually re-read the structure to check for changes. Hence, multi-user applications run faster. All users connected to a multi-user database must have the same setting for STATICDB. The default is OFF. In a multi-user environment, a user who first connects to a database with STATICDB set to ON engages that database to operate in a read-only schema mode, whereby any user must have their STATICDB setting ON in order to connect to that database. SET FASTLOCK ON for faster multi-user performance while modifying data. With FASTLOCK ON, R:BASE does not place a table lock on the table, allowing for greater throughput. A table lock is only needed to prevent structure changes. FASTLOCK can only be set to ON when STATICDB is set to ON, and both FASTLOCK and STATICDB must be set ON before the database is connected. QUALCOLS setting is used to turn page locking OFF and use row locking instead and the ROWLOCKS setting is no longer used to turn row locking ON or OFF. If you know that your application mainly updates or deletes data a row at a time, rather than many rows, SET QUALCOLS 2 for row locking. Use 10 for page locking or row locking as appropriate. Use 2 for row locking only, not page locking. In this case, R:BASE locks a row, reads the row, makes the change, and then releases the row. Keep in mind that the QUALCOLS setting can be changed dynamically and can be different for different users using the same database. In most cases the SET QUALCOLS 2 is a good combination. However, use SET QUALCOLS 10 for page locking when you are doing an UPDATE and/or DELETE affecting many rows in a table. Here is a routine that you can use to automate the process of CONNECTing the database in a network environment with SET STATICDB ON, SET FASTLOCK ON, and SET QUALCOLS 2. -- Example -- Start here .. -- MyApp.DAT Startup Application File -- Start Fresh CLEAR ALL VARIABLES LABEL StartFresh DISCONNECT SET QUOTES=NULL SET QUOTES=' SET DELIMIT=NULL SET DELIMIT=',' SET LINEEND=NULL SET LINEEND='^' SET SEMI=NULL SET SEMI=';' SET PLUS=NULL SET PLUS='+' SET SINGLE=NULL SET SINGLE='_' SET MANY=NULL SET MANY='%' SET IDQUOTES=NULL SET IDQUOTES='`' SET CURRENCY '$' PREF 2 B DISCONNECT SET STATICDB OFF SET ROWLOCKS ON SET FASTLOCK OFF SET TIMEOUT 120 SET FEEDBACK OFF LABEL Start DISCONNECT SET STATICDB ON SET FASTLOCK ON SET QUALCOLS 2 SET MESSAGES OFF SET ERROR MESSAGES OFF SET ERROR MESSAGE 2495 OFF CONNECT dbname IDENTIFIED BY ownername SET ERROR MESSAGE 2495 ON SET MESSAGES ON SET ERROR MESSAGES ON -- Check the availability of database IF SQLCODE = -7 THEN CLS PAUSE 2 USING 'Unable to Connect the Database.' + CAPTION ' Your Application Caption Here ...' + ICON WARNING + BUTTON 'Press any key to continue ...' + OPTION MESSAGE_FONT_NAME Tahoma + |MESSAGE_FONT_COLOR RED + |MESSAGE_FONT_SIZE 11 + |THEMENAME R:BASE Longhorn CLOSEWINDOW EXIT ENDIF -- Enforce Database Default Settings SET QUOTES=' SET DELIMIT=',' SET LINEEND='^' SET SEMI=';' SET PLUS='+' SET SINGLE='_' SET MANY='%' SET IDQUOTES='`' SET CURRENCY '$' PREF 2 B SET NULL ' ' SET DATE FORMAT MM/DD/YYYY SET DATE SEQUENCE MMDDYY SET DATE YEAR 30 SET DATE CENTURY 19 CLS EDIT USING ApplicationMainMenu RETURN -- End here ... Notes: 01. Correct the appropriate lines accordingly if you wish to use STATICDB, FASTLOCK, and QUALCOLS settings. 02. Verify the CHARacter SETtings related to your database. 03. Verify the DATE and TIME settings related to your database. 04. Re-visit the routine that closes the application to either EXIT or go back to the Database Explorer or R> prompt. 05. Create for yourself a startup file with SET STATICDB OFF prior to CONNecting the database when you are in "development mode" and making structural changes, when no one else is connected to the same database. -- Example DISCONNECT SET STATICDB OFF CONNECT dbname IDENTIFIED BY ownername RETURN The options and example detailed above can help you reach maximum performance with R:BASE, but since each application and database is a unique set, only a trial and error process identifies which options help in your situation. Do not forget that different operating systems, hardware configurations, available memory, band-width, system security, and installed anti-virus settings can also affect performance. Experiment with the known options and see which ones improve performance for your application. You, the developer of your application, have the opportunity to set R:BASE internal parameters to gain optimum performance for each application. Stay productive, my friends! Very Best R:egards, Razzak. www.rbase.com www.facebook.com/rbase www.RazzakMemon.com
