The SQL Answer Cowboy

By Andy Stapleton

Posted December 7 1999

Andy "Cowboy" Stapleton is the acknowledged Clarion SQL guru and a regular presenter at Clarion conferences around the world. His company, Cowboy Computing Solutions, produces SQL templates and classes for Clarion.

If you have an SQL question for Andy, click here.

Question:

We use your templates for some of our browses but in other places it just isn't practical (switching DCT properties from TPS to SQL etc where your browses wouldn't work with TPS). Lately we tried having Clarion generate a browse pointing at an SQL file and it worked fine, except the browse takes forever to switch between tabs (primary key is a single long and unique (takes 30 seconds), where others can take up to 160 seconds). Is there some simple PROP or switch we could use to speed these browses up even just a little? Your template browses, when browsing the same file, take less than a second switching from any sort order we have tried!

Ivan Faulkner

Answer:

Using PROP:SQLFILTER or PROP:ORDER you can reset the ORDER BY. Here is some testing you can do: Turn on the driver trace program (drvtrace.exe) so you can monitor the Sql Statements that Clarion is generating during the execution of the program. You will more than likely find that several fields are in the ORDER BY that you don't wish to have. Using the PROP:ORDER or the PROP:SQLFilter to modify the statement that is generated will alleviate some of your problems. Under certain circumstances , Clarion will generate an order by clause that has every field in the primary key, and also every field in the browse. Hence your delay.