Those days, inorder to increase the websites/intranets features and interactivity it is essential to have a connectivity to a database wither its an oracle database or MS access or other types of databases, .. and with databases comes the issue of performance, so in this article i would like to share some of the things that a developer can do to maintain a reasonable performance and even adding more flexibility to the ASP code dealing with databases that has huge data that needs to be fetched.
in this article am using vbscript as a server-side script.
for convenience i will show the steps on how to connect to MS access database first and after that i will start on the tweaks that developers can do in the coding to enhance performance and adding flexibility
<%
dim conn
set conn=Server.CreateObject(“ADODB.Connection”)
conn.Provider=”Microsoft.Jet.OLEDB.4.0″
conn.Open “c:YOUR_DATABASENAME.mdb”
%>
first of all, after you set up a connectivity to the database, you create a query, for example:
<%
dim rs ‘recored set
set rs=Server.CreateObject(“ADODB.recordset”)
rs.Open “Select * from TABLE_NAME”, conn
%>
at this point not much can be done, connecting to the database and querying to get data is pretty much fixed.
the thing start when manipulating the data we just fetched, most of the people do is manipulating the data like the following code
<%
do while not rs.EOF
‘ manipulate data here, go through the query exucuted, movenext, movelast move previous, etc
response.write(rs(“FIELD_NAME”))
rs.MoveNext
loop
rs.Close
conn.Close
set rs=nothing
set conn=nothing
%>
the above code would work perfectly if you have to deal with one query, relatively small data set in the query, and only small code is used to manipulate the recordset, and as long as you want to deal with the data (in the above code) the recordset should stay open .. what if we wanted to do a new query while we are dealing with the existing one while it is still not closed? .. we would have to create another record set and set it and open connection to it, and this would make a nightmare interms of performance if we have a larg data and multiple quieries to deal with. to avoid this we can copy the query information to a multi-dimensional array and close the recordset so we can use it again while we are working with the existing data. instead of doing the previous code we can do the following code:
<%
dim rsArray
rsArray=rs.getrows()
rs.close
for i=0 to UBound(rsArray,2)-1
‘ …. work with the data here using a multi-dim array, its easier and faster, we can even
‘use another query with the same recordset, ..
‘ we can work with the old data and with the new query data without creating new record set
next
conn.Close
set rs=nothing
set conn=nothing
%>
after we fetched the information we needed into “rsArray” we finished from the record set and we closed it “rs.close” so we can use it later while dealing with the existing data without having to create a new recordset. now while we have a multi dimensional array in our hand, we can take advantage of that to our benefit, even we can redim it if we want to add new columns or rows, but when redim-ing the data is lost, so we have to use “preserve” keyword to preserve the data after rediming, for example if we have number of rows and we want to add a new one we do the following code
<%
.
.
.
dim cols
dim rows
cols = UBound(rsArray,1) +1
rows = UBound(rsArray,2) +1
redim preserve rsArray(cols,rows+1) ‘ we have extra row
%>
notice the preserve keyword only works when redim-ing the last dimension, if we want to redim the first one (or the second one or third if we have more then 2) we have to go around it, copying the array to another array and put the dimension we want to change in the last place, and then we can use the “redim preserve” and work with the temp. array.
As a conclusion, using the actual record sets while manipulating data and creating new ones whenever we need is not efficient, on the other hand knowing the features of the arrays we can use it to our advantage so dealing with arrays is more flexible and more efficient then the actual record set.
Related posts: