optimizing a query

Results 1 to 2 of 2

Thread: optimizing a query

  1. #1
    sinD Guest

    Default optimizing a query

    Hello. I&#039m running a query that consistently times out because of the amount of work I am forcing it to do. Although it is executed through a web page, it is only used by a single person who doesn&#039t mind the wait - as long as it works. <BR><BR>A little background on the situation: An accounting system spits out records into a table. This table needs to be cleaned up because there are multiple records for the same item. The only difference between each record is that there is a comments field with each record having a different value. I am taking this table and squashing the item back to a single record and making the comments field one string of comments. So, for example, if an item has 3 entries and 3 unique comments, the new single record has a comments field with a value like "comment 1 comment 2 comment 3". I&#039m happy with this strategy.<BR><BR>This code below works. However, there are almost 15,000 records it&#039s trying to loop through, so it bombs only part way through. What you see below is an attempt to grab the unique comments from the multiple records table, build a string out of them, them update the record in the smaller table...<BR><BR>Set conn = Server.CreateObject("ADODB.Connection")<BR>conn.Op en "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=111.222.333.444;DATABASE=myDB;UID=m yID;PWD=myPW;"<BR>Set rs =Server.CreateObject("ADODB.Recordset")<BR>sql = "SELECT * FROM tblUniqueRecs"<BR>rs.Open sql, conn, 1, 3<BR><BR>If Not rs.EOF Then<BR>Do Until rs.EOF<BR>**notes = ""<BR>**Set rs2 =Server.CreateObject("ADODB.Recordset")<BR>**sql 2 = "SELECT notes FROM tblMultRecs WHERE tblUniqueRecs.model=&#039"&rs("model")&"&#039"<BR> **rs2.Open sql2, conn, 1, 3<BR>**If Not rs2.EOF Then<BR>****Do Until rs2.EOF<BR>****notes = notes & rs2("notes") & " "<BR>****rs2.MoveNext<BR>****Loop<BR>** **rs2.Close<BR>****Set rs2 = Nothing<BR>**End If<BR>**rs("notes") = notes<BR>**rs.Update<BR>**rs.MoveNext<BR>Loop< BR> rs.Close<BR> Set rs = Nothing<BR>End If<BR><BR>Does anyone have any suggestions as to how I could structure this mess to run more smoothly?<BR><BR>Thanks!

  2. #2
    Join Date
    Dec 1969

    Default RE: optimizing a query

    Try this in instead of uing the inner Do..loop:<BR><BR> If Not rs2.EOF Then<BR> notes = rs2.GetString(,,," ")<BR> rs2.Close<BR> Set rs2 = Nothing<BR> End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts