Darren
|
| Posted: 05/17/2002, 11:36 AM |
|
I have been through the CCS code (vbscript and asp) quite a bit now and it seems that it would be quite trivial to add a new item to the clsDataSource class that would allow passing of the total number of records to the navigator block. In fact I have adapted the code myself and it works fine. I assigned it from RecordsCount in the DoOpen() function in classes.asp and then read and displayed it by (temporarily) replacing the Navigator.Block variable Total_Pages in navigator.asp with the new item. It worked fine and showed the total number of records returned where it would normally display the last page number.
Please, please, please could you guys put it in before the final release so that the navigator block (or some other block) could display this information. It would be a much more elegant solution than having to write another count(*) query for every table!!!!
Thanks for listening,
Darren.
|
|
|
 |
DaveRexel
|
| Posted: 05/18/2002, 9:35 PM |
|
Hello Darren,
What a brilliant idea!
Thanks for pointing me towards more tweak-points in CCS.
I am trying to design an variation on the sorting component to show sums and averages at the feet of numerical columns.
Could you please post some code to illustrate your idea?
It would be good to have some pointers from the developers here.
Greetings
Dave
|
|
|
|
Darren
|
| Posted: 05/19/2002, 5:42 AM |
|
Hi Dave,
sure, I have put the code changes I made below. Unfortunately I am snowed under with work at the moment and so haven't had the time to take this further but you can see how it works. Please note this is only for BETA 4.
First add an item to clsDataSource in classes.asp file;
-----
Class clsDataSource
Public DataSourceType
Public DataSource
Public Errors, Connection, Parameters, CCSEvents
Public Recordset
Public CurrentCommand
Public SQL, Order, Where
Public PageSize
Public PageCount
Public Cmd
Public CountSQL
Public NumRecs 'ADDITION - New item
-----
Assign the new item in the DoOpen() function in classes.asp (most of this code is reproduced so that you can see where to make the change)
-----
Private Function DoOpen()
Dim Command
Dim builtSQL
Dim DataSource
Dim CountRecordset
Dim ResultRecordset
Set DataSource = new clsDataSource
Select Case CommandType
Case dsTable, dsSQL
If Len(CountSQL) > 0 Then
builtSQL = ParseParams(CountSQL & IIf(Len(Where) > 0, " WHERE " & Where, ""))
Set CountRecordset = OpenRecordset(builtSQL)
RecordsCount = CLng(CountRecordset.Fields(0).Value)
DataSource.NumRecs = RecordsCount 'ADDITION Assign new item
Set CountRecordset = Nothing
End If
-----
To use the variable in place of total_pages in the navigator, look for this line in navigator,asp
-----
' Set Total Pages
NavigatorBlock.Variable("Total_Pages") = LastPage
-----
and replace it with
-----
' Set Total Pages
NavigatorBlock.Variable("Total_Pages") = DataSource.NumRecs
-----
Thats it, it will now show wherever the variable Total_Pages is used in a navigator block.
Hope that helps,
Darren.
|
|
|
|
CodeCharge Support
|
| Posted: 05/23/2002, 4:41 AM |
|
Darren,
this feature will be added in one of future CCS releases.
|
|
|
|
Brent
|
| Posted: 05/23/2002, 7:14 AM |
|
The problem with this is you are executing 2 queries, one to count the rows
and the other to return the data. It is doubling the amount of work the
database server has to do because to count the rows it has to traverse all the
rows that are selected by the Where clause. This extra delay is really noticeable
for large tables because it takes literally twice as long, but even for small
tables it will limit the # of users your database server can handle because it
is doing twice as much work as necessary. (For small tables you won't realize
this until your server maxes out at 200 concurrent users instead of the expected
400 users.)
There is a solution though. You can make this more efficient because most
databases already know the number of rows that were returned by the query.
So you first execute the query to return the data as normal, then issue a
(for MySQL) a mysql_num_rows() to discover the number of rows in the query.
This eliminates the 2nd query to physically count the rows and you have effectively
cut the number of I/O in half! (This means delaying the assigning of the navigator
buttons/text until the query is actually opened.)
As Darren pointed out, you can then assign the mysql_num_rows() to a property
of the clsDataSource class.
This will allow your database server to support around twice as many users
or display your grids much faster (depending on how you look at it). :)
Brent
|
|
|
|
|
