Does the case-sensitive key attribute still affect speed in Clarion 11 re SQL Indexes?

[StuAndrews]

Hi Folks,

In this thread (MATCH to Only Numbers - #7 by CarlBarnes with @CarlBarnes and @vitesse) there was a ClarionMag pdf put up from 2001 that, amongst other cool stuff, had an article “Avoid My SQL Mistakes!”. (which I initially took as “MySQL” not “My mistakes” - anyway, ha!)

In it, the author mentions that when dealing with SQL table declarations, all Clarion’s indexes must be defined with the Case Sensitive field checked:

If you don’t do this, the operation is slow because the view engine will turn all the fields into uppercase while it is collecting the data.

Does anyone know if this is still a thing with Clarion 11 + MSSQL?

[FominTools]

Clarion VIEW engine didn’t change significantly since then, and this makes sense. It is recommended to define Clarion dictionary key with “Case Sensitive” property checked for that reason. Besides, if you want ultimate performance, create matching index in SQL database. Otherwise, you rely on implicitly built SQL Server index, which is actually not too bad.

[StuAndrews]

Thanks @FominTools .

I actually thought the article was implying an index on both sides.

I’ve got indexes on both sides, for sure - I was interested in the case-sensitive speed question.

If you have indexes on the sql-side, matching the clarion key declaration - will the VIEW UPPER thing still impair speed?

[FominTools]

When browsing on a key having a Case Insensitive STRING KeyField (“Case Sensitive” property unchecked) with corresponding UPPER(KeyField) index on SQL side will force SQL Server to make use of existing index with no performance penalties. I’d use Clarion driver trace and/or SQL Server Profiler to make sure this is the case.

As for matching keys/indexes on both Clarion dictionary and SQL database - it depends… Having (a lot of) indexes can speed up select queries but slowing down database updates. You have to choose a balance. And keep in mind, SQL Server does a great job building necessary indexes behind the scenes implicitly to achieve best possible performance. You can rely on it without creating SQL server indexes at all.

What really changed significantly since 2001 - nowadays processors are hundreds times faster with plenty of memory available to cache database data. And most probably, you won’t notice any performance penalty until your database will become really huge.

[Rick_UpperPark]

I don’t know how far back you need to go in Clarion to find a version that does UPPER on column values for fields in an Index that have the NOCASE attribute, but this is just not true anymore.
I tested this in Clarion 9.1 and Clarion 11.

By capturing the actual statements send to the SQL Server, you can see that the Description column does not have UPPER applied to it while doing a GET, sorting on the column in a VIEW or when doing an ADD.
The captured statements are at the bottom of the file.
It’s possible the driver is querying the server to determine if the database collation is case insensitive, in which case UPPER would not make any difference. I have not tested with a case sensitive collation.

Here is a link to a little program that tests this theory.

https://gist.github.com/Rick-UpperPark/80e863ba22854af0468d954f6685de1f

[StuAndrews]

Rock on @Rick_UpperPark - Legendary work!

[Rick_UpperPark]

I can tell you that if it did include the UPPER, at least on an equality GET, it does make a difference in the execution plan.

For example, these 2 statements.

SELECT * FROM dbo.BidItemDetailDescr WHERE JobUnique = 1380 AND UPPER(Description) = 'DTES DED'
SELECT * FROM dbo.BidItemDetailDescr WHERE JobUnique = 1380 AND Description = 'DTES DED'

Even though neither of these statements returns any actual row, the execution plans shows that the first statement reads all 862 rows which match the JobUnique = 1380 portion of the filter.
The 2nd statement does not need to read those rows at all.

[FominTools]

@Rick_UpperPark

By capturing the actual statements send to the SQL Server, you can see that the Description column does not have UPPER applied to it

No wonder. The test program is not relevant.

TestView                      view(BidAlternatePackages),order('BAP:JobUnique,BAP:Description')
                              end

@Rick_UpperPark try to make use of the KEY with NOCASE attribute to sort the VIEW just like Clarion Browse or Report template does.

[FominTools]

…and GET(Key,Keypointer) or SET(Key,Key);NEXT(…)
instead of GET(File,Key)

!Test Get on index with no case
  BAP:JobUnique   =  20
  BAP:Description =  'Test GET on index'
  GET(BidAlternatePackages, BAP:JobUniqueDescriptionKey)

[FominTools]

Haven’t you created matching index in SQL database?

[Rick_UpperPark]

I’d say you need to show your code and proof, just like I did.

If I use this set(key,key) logic, here is the generated statements sent to the server:

  BAP:JobUnique   =  20
  BAP:Description =  'Test GET on index'
  Set(BAP:JobUniqueDescriptionKey,BAP:JobUniqueDescriptionKey)
  loop
    next(BidAlternatePackages)
    if ERRORCODE()
      break
    end
  end

declare @p1 int
set @p1=1073741826
declare @p2 int
set @p2=180150007
declare @p5 int
set @p5=4
declare @p6 int
set @p6=4
declare @p7 int
set @p7=-1
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 int,@P2 int,@P3 varchar(50)',N'SELECT BAPUNIQUE,JOBUNIQUE,DESCRIPTION,PACKAGEORDER,CHGDATETIME FROM DBO.BidAlternatePackages WHERE (JOBUNIQUE >= @P1 AND (JOBUNIQUE > @P2 OR (DESCRIPTION >= @P3 ))) AND  (JOBUNIQUE <> 0 OR DESCRIPTION <> '''') ORDER BY JOBUNIQUE,DESCRIPTION',@p5 output,@p6 output,@p7 output,20,20,'Test GET on index'
select @p1, @p2, @p5, @p6, @p7

Again, there is no UPPER in the statement.

Get(File,Key) is exactly the statement used by the ABC class File Manager FETCH methods.
GET(Key, KeyPointer) is used to re-get a row where you captured the value of POINTER(KEY). I don’t see how that is relevant to current question.

@FominTools

Haven’t you created matching index in SQL database?

This also is not relevant. Clarion does not use the indexes in the actual database for SQL. Clarion just used the columns from your table definition to include the columns in the statements for where or order by clauses. The SQL engine determines which indexes to use or not to execute the statement.

Keys are not used in directly in a VIEW sort statement. The key is used to generate the order from the columns contained in the key. Again, please show code demonstrating what you are saying.

[JonW]

I didn’t look hugely closely at Rick’s test program, but I also think it is probably not testing the right thing.

In my experience, the place where the case sensitivity makes a difference is in the ORDER BY clause. So if you have your browse sorted by your case-insensitive text column then Clarion will construct the statement as

select [your view fields]
from [your view or table]
where [any filters or range limits]
ORDER BY UPPER(yourtextfield)

If you have a small table, or your filters/range limits result in a small data set, this is not a big problem. The server will sort that small result set quickly. Where it is a problem is when you are trying to browse a whole large table or view, and the server has to retrieve all the rows and then sort them before it sends the requested data back to Clarion. You have to wait a while for your browse to populate.

If you have an index on the server on UPPER(yourtextfield), for example you are using Postgres and have the field defined as citext (case-insensitive text), then clarion using UPPER() is not going to make the query execute any slower than it normally would. And depending on the rest of the query, the server may have decided that retrieving the whole data set and then sorting (as opposed to adding rows in the order of the order by) is the way to go, and having the server-side index makes no difference.

[FominTools]

Driver trace of SET(Key,Key);NEXT is interesting. It is relevant and proofs MSSQL driver ignores NOCASE attribute when setting order of sequential records processing.

@Rick_UpperPark

Haven’t you created matching index in SQL database?

This also is not relevant. Clarion does not use the indexes in the actual database for SQL. Clarion just used the columns from your table definition to include the columns in the statements for where or order by clauses. The SQL engine determines which indexes to use or not to execute the statement.

Keys are not used in directly in a VIEW sort statement. The key is used to generate the order from the columns contained in the key. Again, please show code demonstrating what you are saying.

That’s almost correct. Except

1. my question is a direct reply to your particular message and it is relevant to that "statement reads 862 rows"

   

2. Keys can be used directly to set a VIEW sort order. OPEN(View);SET(Key) do the trick. Below please find slightly modified but equivalent fragment of your code.
   

   
   
   This variant of code is relevant because it makes use of the case insensitive key. What’s interesting, it also proofs MSSQL driver ignores NOCASE attribute.

3. In your test program you are focused on NOCASE key attribute, and you know that “The key is used to generate the order from the columns contained in the key”. So, for NOCASE key the {Prop:Order} generated by templates and ABC classes will be with UPPER(). This is because my suggestion was “try to make use of the KEY with NOCASE attribute to sort the VIEW just like Clarion Browse or Report template does”. If you want me to show some code, take a look at this one for instance.
   

   

@JonW

if you have your browse sorted by your case-insensitive text column then Clarion will construct the statement as

select [your view fields]
from [your view or table]
where [any filters or range limits]
ORDER BY UPPER(yourtextfield)

Great. Let’s split the Clarion into some layers here.

1. Data dictionary (with “Case Sensitive” checkbox turned OFF by default)
2. Templates
3. ABC and third-party classes
4. Database driver
5. SQL backend

The layers 1-3 still make use of NOCASE by adding UPPER to orders, filters, range limits, locators, e.t.c. (Please don’t ask me for demonstrating code, I’m a bit busy and lazy). This is a legacy approach inherited from ISAM file drivers. Personally, working with SQL databases, I used to turn “Case Sensitive” checkbox ON for decades in order to eliminate NOCASE attribute. Thanks Rick for not being lazy and for creating test program. The relevant results look interesting and little bit surprising for me, NOCASE attribute claimed as “supported”, but it is actually ignored by layer #4 (MSSQL driver). And this definitely makes sense. Why? Let me show one more tweaked piece of code relevant to the layer #5.

Hint: default SQL Server collation setting is Case Insensitive…
In MSSQL driver trace Rick will find something like this.

[FominTools]

@StuAndrews

article “Avoid My SQL Mistakes!”. (which I initially took as “MySQL” not “My mistakes” - anyway, ha!)

In it, the author mentions that when dealing with SQL table declarations, all Clarion’s indexes must be defined with the Case Sensitive field checked:

Does anyone know if this is still a thing with Clarion 11 + MSSQL?

YES

I’ve got indexes on both sides, for sure - I was interested in the case-sensitive speed question.

If you have indexes on the sql-side, matching the clarion key declaration - will the VIEW UPPER thing still impair speed?

YES
The VIEW UPPER thing will impair speed. Instead of NOCASE Clarion keys use SQL backend Case Insensitive (CI) collation settings to match SQL indexes.