Tornado Class Library

Special Topic - SQL

User supplier SQL queries for various purposes. In the classical version of ASP-db, there were many 'guesses' in constructing the SQL to retrieve the records under various circumstances. In the Tornado version, user are given an opportunity to take control and specify the exact SQL statements. If none is supplied, the 'guessing' effort will take place. The most expensive 'guessing' operation is the dbCountSQL to obtain the 'Recordset Count'. Specify this property as much as possible.



Obj.dbCountSQL = "Query Statement | none"
This is the SQL to obtain the record count of the recordset which is a very expensive operation. Specify this as much as possible. There are only 3 conditions that the bottom of the recordset is fetched and displayed as [x - y : total]. 

Othewise, the records will be counted one at a time by advancing the record pointer. When manipulating a large database, it is most undesirable. 

In case like MS-SQL queries that includes "DISTINCT".  Count (*) or Count(1) seize to work and you must specify dbCountSQL="none" to do a straight count or an error will occur when Tornado tries to build the CountSQL. Recent ORACLE versions has ROWCOUNT to work with.

The worst case would be to query the DB and count one record at a time. In that case do not use the original SQL like SELECT F1, F2, F3.... FROM TABLE, WHERE ....ORDER BY ....

Use - SELECT F1 FROM TABLE WHERE...... (The minimum) to obtain the record count.



X.dbColSortSQL = "Select .... from ..... Where .... OREDR BY [[Col]]"



Obj.dbEditAddSQL = "Query Statement"



Obj.dbFilterSQL = "Query Statement"


Send comments on this topic.
Copyright 1998-2006 ASP-db