I have a join query which is using about 7 tables. And the total records are in millions.
I am using paging to display 20 records at a time.
If I run the same query to get the count, it is taking time to get the result.

Is there any way to get the count (number of records) quickly.

An early response will be appreciated.

Regards,
Atiq Rahman

try this code.
you will change connection string.
use your connection

<%
set cn=server.CreateObject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")
cn.ConnectionString="Provider=Microsoft.Jet.OLEDB. 4.0;Data Source=C:\Documents and Settings\ccee\Desktop\aspchk\BIBLIO.MDB;Persist Security Info=False"
cn.Open

rs.Open "select * from authors order by author",cn,1,2
rs.PageSize=100


Response.Write("Total No Of Records = "& rs.RecordCount&"<br>")
Response.Write("Total No Pages @ "&rs.PageSize&" = "& rs.PageCount&"<br>")

pn=Request.QueryString("pn")

Response.Write("Page No = " & pn&"<br>")

if pn="" then
rs.AbsolutePage=1
else
rs.AbsolutePage=pn
end if

pv=1
do while rs.EOF=false and pv<=rs.PageSize

Response.Write(pv&"--"& rs(0)&"--"&rs(1)&"<br>")
pv=pv+1
rs.MoveNext
loop


for i=1 to rs.PageCount
%>
<a href=paging.asp?pn=<%=i%>><%=i%></a>
<%
next
%>

In order that the number of rows can be counted (I assume you are attempting something like SELECT COUNT(*) FROM ...), the query processor has to determine which rows make up the resultset of the query and then count them. For simple queries on a single table, the optimizer may be able to utilize information in the indexes or data statistics to short circuit constructing the resultset.

But, if you have a query like:
and there is no index on 'somecolumn', there is no choice but to scan the entire table and count the rows one by one to find the rows where the condition is true. If there are a large number of rows in the table, this will take some time.

The only thing you can do to improve this is to insure that an index exists on 'somecolumn'.

A similar situation holds for JOINS. In order to join matching rows, you have to find those rows first. The only way to find them is to, er, look for them, and this will be done by scanning the table one row at a time. You can make the query processor's job considerable easier (and more efficient) if you help it along and provide an index on the column(s) involved in the JOIN.

Thus, if the query consists of JOINS to other tables and complex conditions, you will have to insure that indexes exist on all the columns involved as JOIN conditions or WHERE predicates.

I'm sorry, but there's no such thing as a free lunch. :D If you ask "How many rows are there in a million row table joined with 6 other tables each of which contain millions of rows which meet the following complex set of conditions?" you don't really have much choice but to actually construct the resultset and count how many rows there are in there. And this will take some time, I'm afraid.

You might try executing the COUNT query in Query Analyzer and turn on the 'show execution plan' option. Check to be sure no table scans are present in the plan. If there are, consider placing an index on the column which is causing the scan (the plan will tell which column is causing the scan if you click on the operation is the plan display.)

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com