User supplier SQL query to obtain the record count as the only return.
Obj.dbCountSQL = "Query Statement | none"
|Query Statement||Any 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)"
|none||This key word will suppress the record count in the status bar. [1-5] instead of [1-5:300]|
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] -
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.
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