Solved

Query Efficiency: Minimizing Database & Server Traffic.

Posted on 2006-07-13
19
199 Views
Last Modified: 2010-04-06
View the thread that spawned this: http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21914991.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.
0
Comment
Question by:cipiWeb
  • 16
  • 3
19 Comments
 

Author Comment

by:cipiWeb
ID: 17099799
Comment from kevp75
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
0
 

Author Comment

by:cipiWeb
ID: 17099808
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)...
0
 

Author Comment

by:cipiWeb
ID: 17099818
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.Connection")
 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.
0
 

Author Comment

by:cipiWeb
ID: 17099822
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.
0
 

Author Comment

by:cipiWeb
ID: 17099827
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
0
 

Author Comment

by:cipiWeb
ID: 17099830
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.
0
 

Author Comment

by:cipiWeb
ID: 17099837
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(youConnectionString,"*","theTable","id = value","id DESC",10,1,MyArray,strError,isEOF,lngRecordCount)
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,recordRow) & "<br />") 'display the results when using * for your fields, 0 is always the first column
  next 'do the loop
end if

HTH
0
 

Author Comment

by:cipiWeb
ID: 17099843
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.
0
 

Author Comment

by:cipiWeb
ID: 17099848
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(youConnectionString,"theFields","theTable","theFilter","theSort",numberOfRecordsToDisplay,PageNumber,ArrayName,strError,isEOF,lngRecordCount)

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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:cipiWeb
ID: 17099854
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")
0
 

Author Comment

by:cipiWeb
ID: 17099862
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(youConnectionString,"*","theTable","id = value","id DESC",10,1,MyArray,strError,isEOF,lngRecordCount)
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(recordColumn,recordRow) & "<br />") 'display the results when using * for your fields, 0 is always the first column
       next 'do the loop
  next 'do the loop
end if
0
 

Author Comment

by:cipiWeb
ID: 17099869
***END COMMENT REPOSTING***
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17100974
Hi there.  Have you gotten this to work for you?
0
 

Author Comment

by:cipiWeb
ID: 17101133
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.
0
 
LVL 25

Accepted Solution

by:
kevp75 earned 300 total points
ID: 17101331
:)
take your time, it's alot to read over  :)
0
 

Author Comment

by:cipiWeb
ID: 17110607
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.
0
 

Author Comment

by:cipiWeb
ID: 17129537
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?
0
 

Author Comment

by:cipiWeb
ID: 17140627
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
0
 
LVL 25

Expert Comment

by:kevp75
ID: 17140912
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  :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now