Tornado API Library - V12.26.07

z.dbCountSQL Property

User supplier SQL query to obtain the record count as the only return.

Product Version

All
[Visual Basic]
Public Property dbCountSQL As String
[C#]
public string dbCountSQL {set;}

Property Value

Obj.dbCountSQL = "Query Statement | none"

KeywordDescription
Query StatementAny valid query to obtain the record count. If return is a GROUP BY then the following SQL will return incorrect count -

.dbCountSQL = "Select Count(*) from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion)"

The correct way to specify CountSQL in an SQL with GROUP BY is -

.dbCountSQL = "Select Count(1) FROM (Select Count(*) from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion)"

noneThis key word will suppress the record count in the status bar. [1-5] instead of [1-5:300]

QuickProp Keyword Abbreviation

cq

Remarks

This is a relative important property in performance enhancement.

Obtaining the record count of the recordset which is a very expensive operation. Use this ptoperty as much as possible as the altenative is to count the records by advancing the record pointer. When manipulating a large database, it is very undesirable. There are only 3 conditions that the bottom of the recordset is fetched and displayed as [x - y : total] -
  1. The dbRecordCount is specified (Fast!)
  2. dbRecordCount = -1
  3. dbCountSQL text is specified
In case like MS-SQL queries that includes "DISTINCT". Count (*) or Count(1) used by the system to 'guess' will not 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.

During the straight count worst case, do not use the roiginal SQL like -

SELECT F1, F2, F3.... FROM TABLE, WHERE ....ORDER BY ....

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

IF DISTINCT, ALL or DISTINCTROW is in the SQL then the SQL will not be touched and (SELECT COUNT(1) (Original SQL - ORDER BY) will be used. ORDER BY will always be discarded in CountSQL.

Example

X.dbCountSQL="SELECT COUNT(1) FROM TABLE"
Obj.dbCountSQL="SELECT COUNT(*) FROM (SELECT DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT" & _
  " WHERE EMP.DEPTNO(+) = DEPT.DEPTNO GROUP BY DEPT.DEPTNO, DNAME)"
X.dbCountSQL = "SELECT count(*) FROM [Order Details] AS OrdD JOIN Products as Prd ON" & _
  " OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID"

Dim Mydb As New Tornado.z
  With Mydb
      .dbQP = "U=2| M=Grid| S=2| DSN=Nwind| gdf=0,1,2| Q=Employees| TH=Title=CountSQL"
      .dbCountSQL = "SELECT Count(1) from Employees"
      .ASPdbNET()
  End With

 Dim WHERE, GROUPBY, MainSQL, CountSQL
 WHERE = " WHERE OrderID < 123456 "
 GROUPBY = " GROUP BY ShipRegion "
 MainSQL = "Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS " & WHERE & GROUPBY
 CountSQL = "Select Count(1) FROM (" & MainSQL & ")"
 Response.Write("MainSQL = " & MainSQL)
 Response.Write("CountSQL = " & CountSQL)
 Dim x As New Tornado.z
 With x
   .dbUnit = 1
   .dbDSN = "NWIND"
   .dbMode = "GRID"
   .dbPageSize = 5
   .dbSQL = MainSQL
   .dbCountSQL = CountSQL
   .ASPdbNET()
 End With

For MS-SQL use the following syntax to count the rows(groups) with a GROUP BY clause -

.dbCountSQL = Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS  WHERE OrderID < 123456  GROUP BY ShipRegion) AS X

See Also

z Class | Tornado Namespace | dbSQL