Loop takes too much time to reach EOF (1 Viewer)

[cpampas]

Hello,
I have query that list all the posible sums of three numbers in a table. the table only has 70 records, but when i runs the query returns about 275000 records

'step 1 (the query that returns the 275000 records)
SELECT DISTINCT a.valor + b.valor + c.valor AS sum_of_3_numbers, a.valor AS aValue, b.valor AS bValue, c.valor AS cValue FROM tblSBNC_P AS a, tblSBNC_P AS b, tblSBNC_P AS c WHERE a.Punt = 0 AND b.Punt = 0 AND c.Punt = 0 AND a.ID <> b.ID AND a.ID <> c.ID AND b.ID <> c.ID

step 2
after this i do a loop where I try to match "sum_of_3_numbers" for an exact match with one record from another table. step 1 does not take long time to process but the loop does, it might hang for 5 minutes before it reaches the end of the recordset

Do While Not rsSums.EOF
    sumValueStr = rsSums!sum_of_3_numbers
    ' Replace the dots with commas to handle the number format correctly
    sumValueStr = Replace(sumValueStr, ",", ".")
    
    
    ' Open recordset for tblSCNB_P to find matching record for sumValue
    Set rsSCNB = db.OpenRecordset("SELECT * FROM tblSCNB_P WHERE valor = " & sumValueStr & " AND punt = 0", dbOpenDynaset)
    ' Check if a matching record is found for sumValue


    If Not rsSCNB.EOF Then
 
        aValue = rsSums!aValue
        bValue = rsSums!bValue
        cValue = rsSums!cValue
        ' Update cor field in tblSBNC_P for aValue, bValue, and cValue
        db.Execute "UPDATE tblSBNC_P SET cor =" & bc & " WHERE valor IN (" & Replace(aValue, ",", ".") & "," & Replace(bValue, ",", ".") & "," & Replace(cValue, ",", ".") & ")"
        ' Update cor field in tblSCNB_P for -sumValue
        db.Execute "UPDATE tblSCNB_P SET cor =" & bc & " WHERE valor = " & Replace(sumValueStr, ",", ".")


        Exit Sub
    End If


    rsSCNB.Close
        rsSums.MoveNext
    Loop

how can i reduce the time the code executes? another query instead of the loop? or is there a way to limit the amount of values the loop tries to match?

 

[theDBguy]

Hi. Since your tables only have 70 records, are you able to post a sample copy of your db for testing?

 

[cpampas]

Hello, thanks for your reply
I did make a copy of the database. so I have 70 records in one table, and a few records in the other one. I this case a match is found in a few seconds , but that is because as soon as I get a match I exit the loop, but it could take much more depending on where in the recordset the sum of 3 records is. if you delete the matching records in tblSCNB_P, wich are 137,81 and 88,15, and then run the code it will take 3 or 4 minutes to complete the loop, if instead of 70 records I had 100 i suspect it would take half an hour

My regional setting are formatting decimal numbers with commas , i dont know if it will be compatible with your regional settings

 

[June7]

Get that many records because query is a Cartesian product of 3 datasets. Every record of each set is associated with every record of each of the other sets. tblSBNC_P has 71 records: 71 x 71 x 71 = 357,911. Some records are excluded by the WHERE clause.

You open recordset of several hundred thousand records, loop that recordset to find a match in tblSCNB_P and if match is found, exit loop? Regardless if there might be other matches? I wonder if it would be faster to loop tblSCNB_P recordset and run FindFirst method on the big dataset.

I tested a query joining the big query to tblSCNB_P and got 126 records. Took about 6 seconds to run.
SELECT tblSCNB_P.ID, tblSCNB_P.valor, Query1.sum_of_3_numbers, Query1.aValue, Query1.bValue, Query1.cValue
FROM Query1 INNER JOIN tblSCNB_P ON Query1.sum_of_3_numbers = tblSCNB_P.valor;

Variable bc is declared as integer but does not get set so it is just 0. What purpose does it serve?

 

[ebs17]

First query: You identify too many variants. It should be better

' qrySum
SELECT DISTINCT
   a.ID,
   a.valor + b.valor + c.valor AS sum_of_3_numbers,
   a.valor AS aValue,
   b.valor AS bValue,
   c.valor AS cValue
FROM
   tblSBNC_P AS a,
   tblSBNC_P AS b,
   tblSBNC_P AS c
WHERE
   a.Punt = 0
      AND
   b.Punt = 0
      AND
   c.Punt = 0
      AND
   a.valor < b.valor
      AND
   b.valor < c.valor

So a second query, no loop

SELECT
   T.ID,
   T.valor,
   X.sum_of_3_numbers,
   X.aValue,
   X.bValue,
   X.cValue
FROM
   tblSBNC_P AS T
      INNER JOIN qrySum AS X
      ON T.valor = X.sum_of_3_numbers
WHERE
   T.Punt = 0
ORDER BY 
   2

 

[ebs17]

The first query should run a little faster if you don't have reservations about subqueries and so filter the base tables first before applying the CROSS JOIN duplication.

SELECT DISTINCT
   a.ID,
   a.valor + b.valor + c.valor AS sum_of_3_numbers,
   a.valor AS aValue,
   b.valor AS bValue,
   c.valor AS cValue
FROM
   (
      SELECT
         id,
         valor
      FROM
         tblSBNC_P
      WHERE
         Punt = 0
   ) AS a,
   (
      SELECT
         id,
         valor
      FROM
         tblSBNC_P
      WHERE
         Punt = 0
   ) AS b,
   (
      SELECT
         id,
         valor
      FROM
         tblSBNC_P
      WHERE
         Punt = 0
   ) AS c
WHERE
   a.valor < b.valor
      AND
   b.valor < c.valor

Setting an index on valor in both tables would also be helpful.

 

[cpampas]

thanks June and ebs, the way was definetly a query not a loop, your examples worked nicely