Clarion Community Help

| **Navigation:**  SQL Drivers >====== Optimizing using the WHERE driver string ====== | [[clarion functions used in sql filter statements.htm|{{btn_prev_n.gif|Previous page}}]][[coming future.htm|{{btn_home_n.gif|Return to chapter overview}}]][[performance considerations.htm|{{btn_next_n.gif|Next 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 1.htm|WHERE]]