When this property is not blank,
it’ll be used to obtain the accurate record count instead of using the dbSQL
information. There are three options to obtain the
total number of records returned from the query specified by dbSQL -
- Use the dbCountSQL property to supply an efficient SQL statement to obtain
the accurate count.
- Leave the dbCountSQL blank and ASPDB will
direct the dbSQL statement to
construct the dbCountSQL. For example - SELECT a,b,c from TABLE ORDER BY a
will be converted to SELECT count (*) from TABLE for MS-SQL and SELECT count
(*) (SELECT a,b,c from TABLE ORDER BY a) for ORACLE.
- Specify dbCountSQL="lowspeed" will force ASPdb to count via
RS.MoveNext in which is extremely ineficient. Use this option when
there is absolutely no alternatives.
Obj.dbCountSQL="SQL statement"
|
Parameters
|
Description
|
| SQL statement |
- Valid SQL statement returning the count of dbSQL.
- "lowspeed" - force straight count using MoveNext
to obtain record count.
|
<%
response.write("Start=" & now())
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;HOTEL_ACCESS;;Both;;;;ACCESS;5"
X.dbSQL="SELECT * FROM FRANK WHERE City = 'PLANO' ORDER BY CITY"
'do not need the ORDER BY to get count
X.dbCountSQL="SELECT count(*) FROM FRANK WHERE
City = 'PLANO'"
X.ASPDB
response.write("End=" & now())
%>
<%
response.write("Start=" & now() & "<BR>")
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;;;Grid;;;;ORACLE,Server;5"
X.dbDSN="Provider=MSDAORA; data source=dell500; user id=HOTEL; password=hotel"
X.dbSQL="SELECT * FROM Frank where city LIKE 'A%'"
X.dbRSCursor=0
X.dbCountSQL="SELECT count(*) FROM Frank where city LIKE 'A%'"
X.ASPDB
response.write("End=" & now())
%> |
Example - ORACLE
| Using the ODBC Tester to validate the dbCountSQL property. Note that
when using COUNT(*) to obtain record count in ORACLE, ORDER BY cannot be
used! Always validate the CountSQL statement in ODBC tester.
|
 |
Obj.dbDSN="Provider=MSDAORA; data
source=MyServer; user id=scott; password=tiger"
Obj. dbDBType="ORACLE"
Obj.dbSQL="SELECT DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT WHERE
EMP.DEPTNO(+) = DEPT.DEPTNO GROUP BY DEPT.DEPTNO, DNAME"
Obj.dbCountSQL="SELECT COUNT(*) FROM (SELECT
DEPT.DEPTNO, DNAME, SUM(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO
GROUP BY DEPT.DEPTNO, DNAME)"
Example - SQL7
X.dbSQL = "SELECT OrdD.ProductID AS ProdID,SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID"
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"
- Record count is
done only once in a session until a reset or reload is performed.
- Only RS(0) will be
picked off the returning RS from CountSQL as the returning count. Therefore,
in the SQL7 example, the CountSQL will result a value of 12. The rest of the
recordset will be ignored.
- This is the second most important property in performance tuning. The
first and most important property is the "cheating" property
dbRecordCount in which the user knows the count ahead of time. This would
eliminate the entire counting process.
- Records are only counted once in the new session startup. Th evalue
remains in session variablle until a Reset or Reload is issued. Still, do
not under estimate the performance degradation record counting can impose
and provide an efficient counting SQL if possible.
- Beware that SQL databases are usually case sensitive in
field names.
- MS-ORACLE OLEDB drivers does not support the
RecordCount property.
- even though SELECT Count(*) FROM Table works, SELECT Count(*) (Original SQL statement) seemed to
always work. So, use this property for ORACLE. Always delete the ORDER BY
clause in the dbCountSQL statement.
- For MS-SQL, seemed like deleting all the SELECT fields
and replace by a Count(*) will work.