User Tools

Site Tools


optimizing_set_processing_using_the_where_driver_string.htm
Navigation:  SQL Drivers >====== Optimizing using the WHERE driver string ====== Previous pageReturn to chapter overviewNext page

Regarding SQL databases, standard SET(key,key) processing is not always the most efficient approach. This is because the SET(key,key) statement sets the starting part of the search, but not the ending part of the search.

Consider this code sequence:

CLEAR(PHO:Record)

PHO:CategoryID = 1

SET(PHO:FK_CategoryKey,PHO:FK_CategoryKey)

LOOP

 IF Access:Photos.Next() <;> LEVEL:Benign

   BREAK

 END

 IF PHO:CategoryID <;> 1

   BREAK

 END

END

To achieve better performance use the following sequence:

CLEAR(PHO:Record)

PHO:CategoryID = 1

SET(PHO:FK_CategoryKey,PHO:FK_CategoryKey)

SEND(Photos, '/WHERE CategoryId = ' &amp; PHO:CategoryID &amp; ) !Eliminates PHO:CategoryID checking LOOP  IF Access:Photos.Next() <;> LEVEL:Benign    BREAK  END END For the best performance, use the following sequence: CLEAR(PHO:Record) PHO:CategoryID = 1 SET(PHO:FK_CategoryKey)   !SET(KEY,KEY) not needed SEND(Photos, '/WHERE CategoryId = ' &amp; PHO:CategoryID &amp; '') LOOP  IF Access:Photos.Next() <;> LEVEL:Benign   BREAK  END END All of the filtering you need is defined and optimized in the /WHERE driver string via the SEND command. If you notice that the backend is doing a full table scan, this would indicate that a design problem is possible on the backend database. Clarion simply sends a SELECT statement to the server. It does NOT tell the server how to fulfill that SELECT. That decision is left up to the server. For example, if you send to the server: SELECT * FROM MyTable WHERE myField > 23 ORDER BY myField If there is no corresponding index on the server, then the server has no choice but to do a full table scan. In that case, instead of rewriting all of the Clarion code to use a Prop:SQL statement, the time would be better spent on optimizing the database design. Using PROP:Where (or SEND(FILE, '/WHERE…..')) can limit the result set you get, and even eliminate using a key in the SET statement. Clarion does this by setting or adding to the WHERE clause containing your condition, greatly reducing the load on the server. Note: Do not use the standard clarion field name syntax (e.g., pre:columnname). For example:  SET(MyFile)  MyFile{PROP:WHERE} = 'CUSTOMERID = ' &amp; LOC:CUSTOMERID  LOOP    NEXT(MYFILE)    IF ERRORCODE() THEN BREAK.    ! process record here  END will retrieve all the MYFILE records for a single customer.  SET(MYF:K_DATE)  MYFILE{PROP:WHERE} = 'CUSTOMERID = ' &amp; LOC:CUSTOMERID will do the same, but order them by the date (whatever is in MYF:K_DATE). Using PROP:Where also eliminates the hassle of handling “field alignment” when using PROP:SQL to retrieve records. It accomplishes what you would get if you intend to do the equivalent of a “SELECT *”. You can also improve performance by using a BUFFER statement, like this:  BUFFER(MyFile, 20)  SET(MYF:K_DATE)  MYFILE{PROP:WHERE} = 'CUSTOMERID = ' &amp; LOC:CUSTOMERID See Also: WHERE

optimizing_set_processing_using_the_where_driver_string.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1