Here are some helpful tips to help you take full advantage of unique strengths and features of the OpenBase SQL database when building your software.Video: Performance Optimization Techniques
SQL Query Performance
Which is faster: Stored Procedures or Selects?
It is always going to be faster to perform a select directly than calling a stored procedure that performs the select and returns the result.
Which is faster: Subqueries or joins?
Non-correlated subqueries are often faster than joins. You can use a subquery in place of a join when the columns from the table do not need to be returned.
A non-correlated subquery is when the subquery does not contain a reference to the main query.
Slower: (correlated subquery)
select t1.col1 from table1 t1 WHERE EXISTS (select t2.ForeignKey from table2 t2 t2.ForeignKey = t1._rowid)
Faster: (non-correlated subquery)
select t1.col1 from table1 t1 WHERE t1._rowid IN (select t2.ForeignKey from table2 t2)
Improving performance for compound keys and other values
It is always better to use a single key than a compound value. However, when you have to use a compound key and search performance is a priority, use a derived column and search that.
ALTER TABLE actors ADD COLUMN movie_actor varchar index derived movie_id + “:” + actor_id
Use the derived column values for faster searching.
Which is faster: Inner or Outer joins?
Using outer joins often results in slower queries. While the SQL optimizer is still able to choose indexes, adding an outer join often fixes the order in which the outer-joined tables are evaluated.
Which is faster: using views or tables?
Selecting data through a view offers no advantage over incorporating the query that the view is based on into your select. Queries that reference views are translated into queries that reference the tables directly.
If you use views, make sure to always use unique table aliases to avoid naming conflicts.
(Views will be re-implemented as a class of tables in a future version of OpenBase SQL, allowing performance to be optimized by using views.)
Use of greater/lessthan
When performing a query that constrains the result to a range, it is usually better to use a BETWEEN clause instead of specifying the range as two constraints.
SELECT date FROM schedule where date BETWEEN ‘Jan 1, 1969’ AND ‘Jan 1, 1980’
Don’t do this:
SELECT date FROM schedule where date => ‘Jan 1, 1969’ AND date <=‘Jan 1, 1980’
When you cannot use BETWEEN because of your development environment, specify the constraint that will produce the smallest result first.
Doing your own optimization
It is possible to over-ride the OpenBase SQL query optimization system by using the OPTIMIZED WHERE syntax in place of WHERE. Here is an example:
SELECT t1.title, t2.author FROM movie t1, author.t2 OPTIMIZED WHERE t1.title LIKE “The*” AND t1.author_id = t2._rowid
Using OPTIMIZED WHERE, the constraints and tables will be evaluated in the order they appear in the query.
OpenBase SQL provides at least three options for searching text: Using LIKE with no Index, using LIKE with a word index and using a Lucene index, which is called through a stored procedure.
The word index offers real-time maintenance and integration with standard SQL syntax. But for very large data sets where multiple constraints are used, we’ve found the Lucene index to be faster.
The Lucene index has drawbacks in that it is static and must be regenerated. A Lucene index is most useful for searching email and support archives, or text that does not change often. It is not suitable for situations where the index needs to be constantly updated.
For more information on Lucene indexes see the Built-in Stored Procedures page.
The FIGG Score takes a text string and assigns a score to it based on how well it matches the keys passed into it. The higher the score, the closer the match.
SELECT TITLE, FIGGSCORE(TITLE,”Star”) from MOVIE
The FIGG Score Blob takes a blob key as its first parameter, allowing the entire Blob text to be scored regardless of length.
SELECT TITLE, FIGGSCOREBLOB(COMMENT,”Star”) from MOVIE
Using Set Operations
A varchar, or char type, can be used to collect a set of related record keys for fast searching. It can be used for a variety of things, including: connecting a small set of group or category choices to a record; specifying user access to records; or specifying record options, which may be configurable in another table.
While all of these operations can also be done using one-to-many relationships, using a Set will de-normalize data, reduce the number of joins and improve performance.
A Set is represented as a collection of numbers that might represent option numbers or primary keys from another table. Set operations create strings in numerically ascending order that look like this:
If you want to search for a record that has both 50 and 1000 as members, you can search for them using a LIKE operator as follows:
SELECT * FROM contact WHERE filter LIKE “*|50|*|1000|*”
It is recommended that your set column, in this example called filter , is indexed. This will provide the fastest searching possible.
The following functions make it easy to add and remove a set value from a series of records.
The AddMemberToSet() function provides a way to add a new member to the existing-set-string. If added, the new set string is returned. If the member exists, the existing-set-string is returned.
update contact set filter = AddMemberToSet(filter,"206") WHERE ...
The RemoveMemberFromSet() function will remove the member from the set string, if it exists, and return the new set string.
update contact set filter = RemoveMemberFromSet(filter,"206") WHERE ...
Stored Procedure Subqueries
OpenBase now allows you to call stored procedures as subqueries. The use of procedures as subqueries are limited to selects and inserts. They cannot be used for updates or deletes.
select TITLE t1 from MOVIE t1 where t1.MOVIE_ID IN (call FindMoviesForRole(‘Luke Skywalker’) )
This capability becomes especially useful if you have to query other databases. Calling the RemoteJDBCQuery() stored procedure as a subquery, for instance, allows a result from Sybase or Oracle to be used to evaluate a query in OpenBase SQL. RemoteJDBCQuery() works with any JDBC compliant database.
It is important to note that only non-correlated subqueries will work with stored procedures. Each procedure is called only once, so the parameters have to be static. You cannot pass in column names as parameters and use it as a function.
Strategies for Truncating Results
It is sometimes desirable to truncate search results—or return a range of records within a result. This is especially true when you have a result of 2 million records but your users only want to see the first few hundred. In these cases, it is sometimes more efficient to tell the server to return just what you want.
The RETURN RESULTS keywords can be appended to the end of your SQL select to return a range of records or truncate a result. The example below shows how to truncate a search result after the first 100 rows.
SELECT * FROM MOVIES RETURN RESULTS 100
A second example below returns the second hundred rows by specifying a range. In this case, 100 is the starting point and 200 is the stopping point.
SELECT * FROM MOVIES RETURN RESULTS 100 TO 200
Another approach is to use server-side cursors. This works well, for example, when you are interested in all the records but only 100 at a time.
Creating SQL Cursors
To create an SQL Cursor, start first with an SQL Select or stored procedure call. Here is an SQL statement that uses the pubs database:
SELECT au_fname, au_lname FROM authors ORDER BY au_lname
Then, to create a cursor that uses this SQL result set, precede the SQL with the CREATE CURSOR command. Here is an example that creates a cursor named myAuthors:
CREATE CURSOR myAuthors AS SELECT au_fname, au_lname FROM authors ORDER BY au_lname
Just creating a cursor will return nothing. To get the results, you need to FETCH.
Fetching SQL Cursor Results
Once a SQL cursor is created, you can retrieve the data using the FETCH command. Here is an example of a FETCH command that specifies five result rows:
FETCH 5 FROM myAuthors
The label ‘myAuthors’ is the name of the cursor as specified in the CREATE CURSOR statement. A second call to FETCH will get the next five in the result set.
Rewinding Cursor Results
There may be cases where you want to rewind a result set to the beginning so it can be fetched again. OpenBase SQL Cursors allow you to do this using the REWIND command. Here is an example using the myAuthors cursor:
After a cursor result set has been reset using REWIND, the FETCH command will begin at the very beginning.
It is very important to close SQL cursors after you are finished with them. To close a cursor, issue the CLOSE CURSOR command. Here is an example that closes the myAuthors cursor:
CLOSE CURSOR myAuthors
Unique Key Generation
To insert a new unique key into your record, you first need to generate a unique key value using the NEWID command.Or you can use one of the API calls and then insert it into your record using an insert statement. Note: NEWID only works correctly on columns that have a UNIQUE INDEX.
The NEWID command follows this syntax
NEWID FOR table column
NEWID FOR table column n
Here are some examples of generating a new unique key using the NEWID command:
NEWID FOR MOVIE _rowid
The next command will generate 50 new unique keys and return them at once.
NEWID FOR MOVIE _rowid 50
Creating Auto-Generating Key Columns
Every OpenBase SQL record has a column called _rowid which uniquely identifies it within a table. However, there may be times when you want to create your own auto-generating key value, similar to _rowid. To do this, we recommend using a derived column based on _rowid.
CREATE TABLE foo (MyKeyColumn longlong UNIQUE INDEX DERIVED _rowid)
This will create a derived column, MyKeyColumn, which will automatically be set to the value from the _rowid column.
Creating and Updating Users
The OpenBase Manager includes graphical tools that make it easy to manage database users. But for those who need to manage users programmatically, this section offers some instruction.
OpenBase SQL database users are stored in the _SYS_USERS table. Records that are inserted into this table are automatically used for database user authentication. The password() function is specifically designed to insert an encrypted password into the record. Here is an example:
update _SYS_USERS SET password = password(“mypassword”) where username = "admin"
Error Condition Notification
OpenBase provides an SQL interface, which enables client applications to centrally alert system administrators to error conditions through the OpenBase Manager alarm system. Here are some example SQL calls:
ALERT MESSAGE "This is a message" ALERT WARNING "This is a warning highlighted in blue" ALERT ERROR "This is a error highlighted in red" ALERT SEVERE "This is a severe error highlighted in red with yellow background”
For more information on this topic, please see the 410_AlertMessageSystem document.