Getting away from Recordsets, use GetRows()

ASP Free Forums Sponsor:

June 13th, 2008, 03:19 AM

baseballdude_

Expert Learner

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Feb 2005

Location: Wisconsin

Posts: 1,879

Folding Points: 22104 Folding Title: Starter Folder

Time spent in forums: 1 Week 5 Days 12 h 1 m
Reputation Power: 62

Send a message via Yahoo to baseballdude_

Send a message via Google Talk to baseballdude_

Classic ASP/VBScript - Getting away from Recordsets, use GetRows()

I won't go into detail on why using GetRows()'s two dimensional arrays is so much better, but what I will say is that using GetRows() is FAR, FAR, FAR more efficient than iterating through a recordset. For more information, check out this link.

Anyhow, instead of the following traditional code:

ASP Code:

Original - ASP Code

' This code would make 4n + 3 calls to the database.
' If the table has 100 rows, that's 403 calls to the database!
 
strSQL = "SELECT ID, Name FROM users;"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open strSQL, Conn ' 1 call to database
If Not RS.EOF Then ' 1 call to database
    While Not RS.EOF ' n + 1 calls to database
        ID = RS("ID") ' n calls to database
        Name = RS("Name") ' n calls to database
        Response.Write("<p>" & Name & " (" & ID & ")</p>" & vbCrLf)
        RS.MoveNext() ' n calls to database
    Wend
End If
RS.Close()
Set RS = Nothing

This code will produce the same output, but at much faster, more efficient speeds:

ASP Code:

Original - ASP Code

' This code makes only 3 calls to database, no matter how many rows!
 
strSQL = "SELECT ID, Name FROM users;"
Set objRS = Conn.Execute(strSQL) ' 1 call here
If Not objRS.EOF Then arrRS = objRS.GetRows() ' 2 calls here
Set objRS = Nothing
If IsArray(arrRS) Then
    For i = LBound(arrRS, 2) To UBound(arrRS, 2)
        ID = arrRS(0, i)
        Name = arrRS(1, i)
        Response.Write("<p>" & Name & " (" & ID & ")</p>" & vbCrLf)
    Next
    Erase arrRS
End If

To shorten code, you could create the following function:

ASP Code:

Original - ASP Code

Function GetRSArray(strSQL)
    Set objRS = Conn.Execute(strSQL)
    If Not objRS.EOF Then arrRS = objRS.GetRows()
    Set objRS = Nothing
    GetRSArray = arrRS
End Function
 
' Example usage
arrRS = GetRSArray("SELECT ID, Name FROM users;")
 
If IsArray(arrRS) Then
    For i = LBound(arrRS, 2) To UBound(arrRS, 2)
        ID = arrRS(0, i)
        Name = arrRS(1, i)
        Response.Write("<p>" & Name & " (" & ID & ")</p>" & vbCrLf)
    Next
    Erase arrRS
End If

Comments on this post

Guddu agrees: good one

__________________
www.xoise.com - www.ourfreegames.com - www.playtouchgames.com - www.randomtools.net - www.xenocide-rpg.com

September 18th, 2008, 09:38 PM

aemunathan

Contributing User

Join Date: May 2008

Posts: 67

Time spent in forums: 1 Day 27 m 11 sec
Reputation Power: 5

hi
i tried using get rows as per your thread....its working but am not able to write in a table the content of the array as am not that intelligent guy in asp....beginner only so.....can any body find what is the error?? it displays one header with value and not the circle name i intended to display...and one cell data. thats all nothing else...actually i need to display the first row as header ...values of (0,0),(0,1)...and so on and the values in row 2 of the table (1,0),(1,1)...and so on

Code:

<!--#include virtual="\envision\include\dbconnection.inc"-->

<!--#include virtual="\envision\include\settings.asp"-->

<!--#include virtual="\envision\activeusers.asp"-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<meta name="Description" content="Information architecture, Web Design, Web Standards." />
<meta name="Keywords" content="your, keywords" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="Distribution" content="Global" />
<meta name="Author" content="Aemunathan - aemunathan.r@gmail.com" />
<meta name="Robots" content="index,follow" />

<link rel="stylesheet" href="/envision/css/Envision.css" type="text/css" />
<link rel="stylesheet" href="/envision/css/menuh.css" type="text/css"/>

<style type="text/css" media="screen">
#menuh{float:none;}
body{behavior:url(/envision/css/csshover.htc); font: 70%/1.5em Verdana, Tahoma, arial, sans-serif;}
#menuh ul li{float:left; width: 100%;}
#menuh a{height:1%;font:bold 0.9em/1.4em arial, sans-serif;}
</style>
<script language="javascript" type="text/javascript" src="/envision/js/datetimepicker.js">
</script>

<title>Consolidated PFS report...</title>

</head>

<%
			If Session("userGood") = False or IsNull(Session("userGood")) = True then
				Response.Redirect"/envision/default.asp"
			End If
			strUserName = Replace(Session("userName"), "'", "''")
			userID = Clng(Session("userID"))
			userAdmin = Replace(Session("userAdmin"), "'", "''")
			Session("userID") = userID
%>

<body>


<!--#include virtual="\envision\include\logo2menu.inc"-->
<!--#include virtual="\envision\include\sidebar.inc"-->


<!-- main starts here -->
<br><br>
			<div id="main">

				<form name="chat_rep" action="ConsolChatRep.asp" method="post">
				<BR><br><BR><p align ="center">
				<b>No of MSISDNs registered for Chatting service between</b> </p>
							<table class="noborder" align="center">
							<tr><td>
							Start Date &nbsp </td>
							<td><input id="sd" type="text" name="Sdate" size="25" readonly>
							<a href="javascript:NewCal('sd','ddmmmyyyy')">
							<img class="nopadding" src="/envision/images/cal.gif" width="18" height="18"  alt="Pick a date"></a>
							</td>
							</tr><tr><td>
							End  Date &nbsp&nbsp</td>
							<td><input id="ed" type="text" name="Edate" size="25" readonly>
							<a href="javascript:NewCal('ed','ddmmmyyyy')">
							<img class="nopadding" src="/envision/images/cal.gif" width="18" height="18" border="0" alt="Pick a date"></a>
							</td></tr>
							</table>
							&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
							<input type ="submit" value ="Submit">
							&nbsp&nbsp&nbsp
							<input type ="reset" value="Reset">


				</form>


<%

dim conn
dim sdate
dim edate
sdate =Request.Form("Sdate")
edate =Request.Form("Edate")
if sdate<>"" then

Set conn = Server.Createobject("ADODB.Connection")
conn.connectionstring  = "Provider=OraOLEDB.Oracle;Data Source= xxxx;User Id=xx;Password=xx;"
conn.open

set objRS = Server.CreateObject("ADODB.recordset")




Function GetRSArray(strSQL)
Set objRS = Conn.Execute(strSQL)
If Not objRS.EOF Then
arrRS = objRS.GetRows()
Set objRS = Nothing
GetRSArray = arrRS
End If
End Function

arrRS = GetRSArray("SELECT  (SELECT COUNT(*) FROM lbsservice.bsnl_dating_users WHERE TRUNC(register_date) BETWEEN '" & sdate & "'AND '" & edate & "'  AND (msisdn LIKE '919447%' OR msisdn LIKE '919446%' OR msisdn LIKE '919495%' OR msisdn LIKE '919496%')) AS KERALA, (SELECT COUNT(*) FROM lbsservice.bsnl_dating_users WHERE TRUNC(register_date) BETWEEN '" & sdate & "' AND '" & edate & "'  AND (msisdn LIKE '919444%' OR msisdn LIKE '919445%')) AS CHENNAI,   (SELECT COUNT(*) FROM lbsservice.bsnl_dating_users WHERE TRUNC(register_date) BETWEEN '" & sdate & "'AND '" & edate & "' AND (msisdn LIKE '919443%' OR msisdn LIKE '919442%' OR msisdn LIKE '919486%' OR msisdn LIKE '919487%')) AS TAMIL_NADU,  (SELECT COUNT(*) FROM lbsservice.bsnl_dating_users WHERE TRUNC(register_date) BETWEEN '" & sdate & "'AND '" &edate& "'   AND (msisdn LIKE '919441%' OR msisdn LIKE '919490%' OR msisdn LIKE '919491%' OR msisdn LIKE '919440%') ) AS ANDHRA_PRADESH,  (SELECT COUNT(*) FROM lbsservice.bsnl_dating_users WHERE TRUNC(register_date) BETWEEN '" & sdate & "'AND '" & edate & "' AND (msisdn LIKE '919449%' OR msisdn LIKE '919448%' OR msisdn LIKE '919480%' OR msisdn LIKE '919481%')) AS KARNATAKA   FROM lbsservice.bsnl_dating_users   WHERE ROWNUM=1")
If IsArray(arrRS) Then
For i = LBound(arrRS, 2) To UBound(arrRS, 2)
CIRCLE = arrRS(0, i)
COUNT = arrRS(1, i)
Response.Write "<table border=1>"
Response.Write "<tr>"
Response.Write("<th align='left' bgcolor='#bfd4bf'>"  & CIRCLE & "</th>")
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" & COUNT & "</td>"
Response.Write "</tr>"
Next
Erase arrRS
End If
conn.Close
Set conn = nothing
End If
%>

</div> <!-- main ends here -->

		</div> <!-- content-wrap ends here -->


<!--#include virtual="\envision\include\footer.inc" -->

Aemunathan

October 8th, 2008, 08:04 PM

baseballdude_

Expert Learner

Join Date: Feb 2005

Location: Wisconsin

Posts: 1,879

Time spent in forums: 1 Week 5 Days 12 h 1 m
Reputation Power: 62

Sorry for the late reply.

Are you sure your query is returning results correctly? Make sure you test that in Access/SQL Server/phpMyAdmin first.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: