cipiWeb
asked on
Query Efficiency: Minimizing Database & Server Traffic.
View the thread that spawned this: https://www.experts-exchange.com/questions/21914991/Dynamically-populating-a-2-dimensional-array-from-a-Database.html
Please note: THIS IS NOT A POINTER. I would like all comments posted here.
A discusion began on the above thread, where It lead me off topic, I believe the amount of work put into it by the expert assisting me and my growing curiosity warrants creating a new Question Thread for it so that the original Question could continue on without interference or competition for the points.
Kev's first response lead me to a site that linked to the following article: http://www.learnasp.com/advice/whygetrows.asp
Since reading it our conversation branched off into how soon can a connection be cut to a database to minimize the preassure put on it. Since then we have spoken at length about it. I will post the relevent comments below. I will leave this thread open for anyone who wishes to comment can, but to be fair, please note that the previous expert helping me has already earned a fair amount of points. As always, in the end, the accepted/assisted solution will determine who gets what points.
Please note: THIS IS NOT A POINTER. I would like all comments posted here.
A discusion began on the above thread, where It lead me off topic, I believe the amount of work put into it by the expert assisting me and my growing curiosity warrants creating a new Question Thread for it so that the original Question could continue on without interference or competition for the points.
Kev's first response lead me to a site that linked to the following article: http://www.learnasp.com/advice/whygetrows.asp
Since reading it our conversation branched off into how soon can a connection be cut to a database to minimize the preassure put on it. Since then we have spoken at length about it. I will post the relevent comments below. I will leave this thread open for anyone who wishes to comment can, but to be fair, please note that the previous expert helping me has already earned a fair amount of points. As always, in the end, the accepted/assisted solution will determine who gets what points.
ASKER
Comment from kevp75
Date: 07/11/2006 08:13AM PDT
Comment Accept
thank you for reading through those 3 links I provided (about the GetRows method)...
Date: 07/11/2006 08:13AM PDT
Comment Accept
thank you for reading through those 3 links I provided (about the GetRows method)...
ASKER
Comment from cipiWeb
Date: 07/11/2006 08:24AM PDT
Your Comment
Before continuing I have a question regarding the efficiency thing that each method presents. My queries currently look like the following:
Set DB = Server.CreateObject("ADODB
DB.Open "Database"
SQL = "SELECT * FROM Table"
Set RS = DB.Execute(SQL)
do while not RS.eof
'process query
RS.movenext
loop
Set RS = Nothing
DB.Close
Set DB = Nothing
Since I am setting the Record Set to a variable ( I am assume RS is an array, is this correct?) would I be able to close the database before the loop? How is this different from get rows?
Please note: I did not come up with this method of connecting and querying the database. It was the method used by the previous developer.
Date: 07/11/2006 08:24AM PDT
Your Comment
Before continuing I have a question regarding the efficiency thing that each method presents. My queries currently look like the following:
Set DB = Server.CreateObject("ADODB
DB.Open "Database"
SQL = "SELECT * FROM Table"
Set RS = DB.Execute(SQL)
do while not RS.eof
'process query
RS.movenext
loop
Set RS = Nothing
DB.Close
Set DB = Nothing
Since I am setting the Record Set to a variable ( I am assume RS is an array, is this correct?) would I be able to close the database before the loop? How is this different from get rows?
Please note: I did not come up with this method of connecting and querying the database. It was the method used by the previous developer.
ASKER
Comment from cipiWeb
Date: 07/11/2006 08:27AM PDT
Your Comment
I did read each of the links. I am trying to understand it before I proceed changing the way I code, as this could potentially affect a number of my other scripts.
Thank you Kev. All three of the links I found to be good resources I am going to bookmark.
Date: 07/11/2006 08:27AM PDT
Your Comment
I did read each of the links. I am trying to understand it before I proceed changing the way I code, as this could potentially affect a number of my other scripts.
Thank you Kev. All three of the links I found to be good resources I am going to bookmark.
ASKER
Comment from kevp75
Date: 07/11/2006 08:44AM PDT
Comment Accept
no, setting RS does not put in into an array, and you would need to display your data before closing the recordset using that method.
GetRows is probably the best way to go for using a disconnected recordset, however, you will have to weigh the options. Arrays are stored directly in server memory, which makes them infinatly faster that using a recordset method. However, if you have a table with alot of rows, it may slow down the entire server (by placing all those records into the system memory)
I use somethng quite similar to the getrows method. I had a function built for me awhile back, that I've found to be extremely fast no matter hom many records are in the table.
Although I had to pay to get it developed for me, if you would like I can share it with you.
an example how I use it can be seen at http://www.portalfanatic.com Everything on the site is stored in a SQL database, and I use my function for getting all the data.
Just say the word, and I'll post the function and how to use it for you
Date: 07/11/2006 08:44AM PDT
Comment Accept
no, setting RS does not put in into an array, and you would need to display your data before closing the recordset using that method.
GetRows is probably the best way to go for using a disconnected recordset, however, you will have to weigh the options. Arrays are stored directly in server memory, which makes them infinatly faster that using a recordset method. However, if you have a table with alot of rows, it may slow down the entire server (by placing all those records into the system memory)
I use somethng quite similar to the getrows method. I had a function built for me awhile back, that I've found to be extremely fast no matter hom many records are in the table.
Although I had to pay to get it developed for me, if you would like I can share it with you.
an example how I use it can be seen at http://www.portalfanatic.com Everything on the site is stored in a SQL database, and I use my function for getting all the data.
Just say the word, and I'll post the function and how to use it for you
ASKER
Comment from cipiWeb
Date: 07/11/2006 10:11AM PDT
Your Comment
"Word" :-D
Comment from cipiWeb
Date: 07/11/2006 12:01PM PDT
Your Comment
Kev, Yes please post the function, I just realized what I thought was amusing (in saying just the "word") may actually be confusing as to who I was responding to.
Date: 07/11/2006 10:11AM PDT
Your Comment
"Word" :-D
Comment from cipiWeb
Date: 07/11/2006 12:01PM PDT
Your Comment
Kev, Yes please post the function, I just realized what I thought was amusing (in saying just the "word") may actually be confusing as to who I was responding to.
ASKER
Comment from kevp75
Date: 07/11/2006 01:45PM PDT
Comment Accept
here goes.......
'Call SelectDB(cnn, "fields", "filter", "sort", rPerPage, pageNum, arrayName, strError, isEOF, lngRecordCount)
'isEOF to return TRUE or FALSE
'lngRecordCount to return the number of records
'myArray to be named whatever we name the Array to
Sub SelectDB(ByVal connSTR, ByVal fieldlist, ByVal table, ByVal myfilter, ByVal orderby, ByVal records, ByVal page, ByRef myArray, ByRef strError, ByRef isEOF, ByRef lngRecordCount)
If records <= 0 Then
strError = "Wrong parameter 'records' : " & CStr(records)
Exit Sub
End If
If page <= 0 Then
strError = "Wrong parameter 'page' : " & CStr(page)
Exit Sub
End If
Dim strSQL
strSQL = "SELECT " & fieldlist & " FROM " & table
If Len(myfilter) > 0 Then
strSQL = strSQL & " WHERE " & myfilter
End If
If Len(orderby) > 0 Then
strSQL = strSQL & " ORDER BY " & orderby
End If
Dim conn
Set conn = createobject ("ADODB.Connection")
Dim rs
Set rs = createobject ("ADODB.Recordset")
On Error Resume Next
conn.Open connSTR
If Err <> 0 Then
strError = "Can't connect to database: " & connSTR
Exit Sub
End If
rs.Open strSQL, conn, 3,3 ' I need "3" to get RecordCount
If Err <> 0 Then
strError = "Wrong SQL: " & strSQL
conn.Close
Set conn = Nothing
Exit Sub
End If
On Error Goto 0
If rs.EOF Then
isEOF = True
'strError = "No such records: " & strSQL
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Exit Sub
End If
Dim intFieldsCount
intFieldsCount = rs.Fields.Count
Dim iRecordCount
iRecordCount = 0
Dim iSkipRecords
iSkipRecords = (page - 1) * records
Dim i
i = 1
ReDim myArray(intFieldsCount, records)'<-This line gives the error
lngRecordCount = rs.RecordCount
'write(lngRecordCount)
'Response.End
Do While NOT rs.EOF
''write(Str(i) & "<br />")
If i > (iSkipRecords + records) Then
Exit Do
End If
If i > iSkipRecords Then
Dim j
For j = 0 To intFieldsCount - 1
myArray(j, iRecordCount) = rs(j)
Next
iRecordCount = iRecordCount + 1
End If
i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
If iRecordCount = 0 Then
isEOF = True
strError = "No such records: " & strSQL
Exit Sub
End If
If iRecordCount - records < 0 Then
ReDim Preserve myArray(intFieldsCount, iRecordCount)
End If
End Sub
to use it, you would do something like this:
Call SelectDB(youConnectionStri
if not isarray(MyArray) then 'determine if it is an array or not
response.write("There are no records.") 'if not then display a message saying no records
else
for recordRow = 0 to ubound(MyArray,2) - 1 'start to loop through the rows in the array
response.write(MyArray(0,r
next 'do the loop
end if
HTH
Date: 07/11/2006 01:45PM PDT
Comment Accept
here goes.......
'Call SelectDB(cnn, "fields", "filter", "sort", rPerPage, pageNum, arrayName, strError, isEOF, lngRecordCount)
'isEOF to return TRUE or FALSE
'lngRecordCount to return the number of records
'myArray to be named whatever we name the Array to
Sub SelectDB(ByVal connSTR, ByVal fieldlist, ByVal table, ByVal myfilter, ByVal orderby, ByVal records, ByVal page, ByRef myArray, ByRef strError, ByRef isEOF, ByRef lngRecordCount)
If records <= 0 Then
strError = "Wrong parameter 'records' : " & CStr(records)
Exit Sub
End If
If page <= 0 Then
strError = "Wrong parameter 'page' : " & CStr(page)
Exit Sub
End If
Dim strSQL
strSQL = "SELECT " & fieldlist & " FROM " & table
If Len(myfilter) > 0 Then
strSQL = strSQL & " WHERE " & myfilter
End If
If Len(orderby) > 0 Then
strSQL = strSQL & " ORDER BY " & orderby
End If
Dim conn
Set conn = createobject ("ADODB.Connection")
Dim rs
Set rs = createobject ("ADODB.Recordset")
On Error Resume Next
conn.Open connSTR
If Err <> 0 Then
strError = "Can't connect to database: " & connSTR
Exit Sub
End If
rs.Open strSQL, conn, 3,3 ' I need "3" to get RecordCount
If Err <> 0 Then
strError = "Wrong SQL: " & strSQL
conn.Close
Set conn = Nothing
Exit Sub
End If
On Error Goto 0
If rs.EOF Then
isEOF = True
'strError = "No such records: " & strSQL
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Exit Sub
End If
Dim intFieldsCount
intFieldsCount = rs.Fields.Count
Dim iRecordCount
iRecordCount = 0
Dim iSkipRecords
iSkipRecords = (page - 1) * records
Dim i
i = 1
ReDim myArray(intFieldsCount, records)'<-This line gives the error
lngRecordCount = rs.RecordCount
'write(lngRecordCount)
'Response.End
Do While NOT rs.EOF
''write(Str(i) & "<br />")
If i > (iSkipRecords + records) Then
Exit Do
End If
If i > iSkipRecords Then
Dim j
For j = 0 To intFieldsCount - 1
myArray(j, iRecordCount) = rs(j)
Next
iRecordCount = iRecordCount + 1
End If
i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
If iRecordCount = 0 Then
isEOF = True
strError = "No such records: " & strSQL
Exit Sub
End If
If iRecordCount - records < 0 Then
ReDim Preserve myArray(intFieldsCount, iRecordCount)
End If
End Sub
to use it, you would do something like this:
Call SelectDB(youConnectionStri
if not isarray(MyArray) then 'determine if it is an array or not
response.write("There are no records.") 'if not then display a message saying no records
else
for recordRow = 0 to ubound(MyArray,2) - 1 'start to loop through the rows in the array
response.write(MyArray(0,r
next 'do the loop
end if
HTH
ASKER
Comment from cipiWeb
Date: 07/12/2006 05:43AM PDT
Your Comment
Kev, I am having some trouble deciphering your code, and I have a vew questions.
Could you walk me through the code or comment it up? I would like to understand how the code works before I use it. It looks straight forward, but there are a few things not making sense to me.
Comment from cipiWeb
Date: 07/12/2006 05:54AM PDT
Your Comment
I have not actually tested it. the trouble I am having is simply understanding it.
Date: 07/12/2006 05:43AM PDT
Your Comment
Kev, I am having some trouble deciphering your code, and I have a vew questions.
Could you walk me through the code or comment it up? I would like to understand how the code works before I use it. It looks straight forward, but there are a few things not making sense to me.
Comment from cipiWeb
Date: 07/12/2006 05:54AM PDT
Your Comment
I have not actually tested it. the trouble I am having is simply understanding it.
ASKER
Comment from kevp75
Date: 07/12/2006 06:21AM PDT
Comment Accept
I will try...but as I said in a previous post
"Although I had to pay to get it developed for me, if you would like I can share it with you."
it was not commented when I got it. Except for the code under it.
it basically is this:
Call SelectDB(youConnectionStri
strError displays an error if there is any....
isEOF gives a boolean value if the table is at end of file or not...
lngRecordCount gives you the number of records in the table...
you have to change:
youConnectionString to your connection string-
theFields to whatever fields you want displayed, for example id, Name, or *
theTable is the table you are pulling data from
theFilter is the tables filter minus the WHERE part
theSort is the ORDER BY minus the words ORDER BY
numberOfRecordsToDisplay is a real number of how many records you want to display
PageNumber is used if you want to display each page of data (the page is determined hom many records you want displayed)
ArrayName can be whatever you want it to be
Date: 07/12/2006 06:21AM PDT
Comment Accept
I will try...but as I said in a previous post
"Although I had to pay to get it developed for me, if you would like I can share it with you."
it was not commented when I got it. Except for the code under it.
it basically is this:
Call SelectDB(youConnectionStri
strError displays an error if there is any....
isEOF gives a boolean value if the table is at end of file or not...
lngRecordCount gives you the number of records in the table...
you have to change:
youConnectionString to your connection string-
theFields to whatever fields you want displayed, for example id, Name, or *
theTable is the table you are pulling data from
theFilter is the tables filter minus the WHERE part
theSort is the ORDER BY minus the words ORDER BY
numberOfRecordsToDisplay is a real number of how many records you want to display
PageNumber is used if you want to display each page of data (the page is determined hom many records you want displayed)
ArrayName can be whatever you want it to be
ASKER
Comment from cipiWeb
Date: 07/12/2006 07:14AM PDT
Your Comment
I am grateful to you for sharing this, especially since it was something you had to pay for.
Is it safe to assume this works only for Microsoft Access?
You said you have not found faster code. This is more of a curiosity on my part, but what sort of difference have you seen in performance, how was it measured?
Once I run SelectDB( ), how do I go about processing the information? how would it differ from:
Set rs = DB.Execute(SQL)
strVAR = rs("Variable")
Date: 07/12/2006 07:14AM PDT
Your Comment
I am grateful to you for sharing this, especially since it was something you had to pay for.
Is it safe to assume this works only for Microsoft Access?
You said you have not found faster code. This is more of a curiosity on my part, but what sort of difference have you seen in performance, how was it measured?
Once I run SelectDB( ), how do I go about processing the information? how would it differ from:
Set rs = DB.Execute(SQL)
strVAR = rs("Variable")
ASKER
Comment from kevp75
Date: 07/12/2006 07:44AM PDT
Comment Accept
it was measure din a simple timer script (which may or may not be all that accurate....not sure)
nope, works for Access and SQL.
you would display it by doing something like this:
Call SelectDB(youConnectionStri
if not isarray(MyArray) then 'determine if it is an array or not
response.write("There are no records.") 'if not then display a message saying no records
else
for recordRow = 0 to ubound(MyArray,2) - 1 'start to loop through the rows in the array
for recordColumn = 0 to ubound(MyArray,1) - 1'start the loop through the columns
response.write(MyArray(rec
next 'do the loop
next 'do the loop
end if
Date: 07/12/2006 07:44AM PDT
Comment Accept
it was measure din a simple timer script (which may or may not be all that accurate....not sure)
nope, works for Access and SQL.
you would display it by doing something like this:
Call SelectDB(youConnectionStri
if not isarray(MyArray) then 'determine if it is an array or not
response.write("There are no records.") 'if not then display a message saying no records
else
for recordRow = 0 to ubound(MyArray,2) - 1 'start to loop through the rows in the array
for recordColumn = 0 to ubound(MyArray,1) - 1'start the loop through the columns
response.write(MyArray(rec
next 'do the loop
next 'do the loop
end if
ASKER
***END COMMENT REPOSTING***
Hi there. Have you gotten this to work for you?
ASKER
I have not been able to test it. I am working out the last of yeasterday's suggestions. Actually the thread I'm working on right now was the one that lead to this. Once I am done I will begin with this. Now that I have had the time to fully read each and every article, I am begining to understand a bit more of what your comments ment. If I can't get a response to you by tommorow (I don't think it will take that long) I should have feed back by Monday morning EST.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi kev, I haven't forgotten about ya. I just got swamped with a whole bunch of things. I don't know ehn I will get to this, I am hoping by next week, but I don;t know for certain. I will get to it eventually.
ASKER
Hi kev. Im still looking over the code. I have not been able to get to it yet. You said something earlier that I have a question on. You said you had to pay to get this code written. I am just a bit curious as to why? What problem did it solve for you?
ASKER
Well, I understand how to use your code, I haven't tested it yet, but I don't want to end up forgetting about it with my work load increasing. This is definatley something I will keep in my records.
Thank you again for your help.
~Nick
Thank you again for your help.
~Nick
I only bought it because I wanted to minimize my code on a per page basis. So I had an idea for a select function that I could use, and someone put it together for me.
anytime Nick :)
anytime Nick :)
ASKER
Date: 07/11/2006 05:47AM PDT
Comment Accept
have a look at these, they should get you pointed in the right direction:
http://www.asp101.com/samples/db_getrows.asp
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=161
http://www.stardeveloper.com/articles/display.html?article=2000080601&page=1