[02/03] OPtimising sql loop

[Oliver1]

Hi All,
Hoping for a bit of help, I have a loop which runs between 5 and 100 updates say. It generates each update from the contents of a datatable (if you look at the code below I'm sure it will make sense).
Stupidly though I could run say this statement
UPDATE calldata SET calldata.status = 1 WHERE calldata.ext = 162
then 10 itterations later
UPDATE calldata SET calldata.status = 5 WHERE calldata.ext = 162
Hence making the first update pointless and a waste of database time. I some how need to check through the datatable to get the last update for each calldata.ext and only run that one, but have no idea how to do this.

Any help would be fab.

Dim sqlupdate As String
        Dim selstr As String = "SELECT atid, atext, atstatus FROM agentlog WHERE atid >" & after


        accessconnection.Open()
        Dim daupt As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(selstr, accessconnection)
        Dim extnum As Integer
        Dim status As Integer
        daupt.Fill(dsup, "callinfo")
        accessconnection.Close()

        Dim dr As DataRow

        For Each dr In dsup.Tables("callinfo").Rows

            extnum = CInt(dr(1))
            status = dr(2)
            after = dr(0)
            sqlupdate = "UPDATE calldata SET calldata.status = " & status & " WHERE calldata.ext = " & extnum
            Dim myCommand As New MySqlCommand(sqlupdate)
            myCommand.Connection = MySqlConnection
            myCommand.ExecuteNonQuery()

        Next
        dsup.Tables("callinfo").Clear()

[jcis]

The last Status in CallInfo would be the newest Status for a given Extnum? or in other words: last row in CallInfo, for a given Extnum, would have that last status you need? If this is true, you could try this Update in just 1 SQL Command (No loop):

UPDATE CallData
   SET CallData.Status = C.Status
  FROM CallData S 
       INNER JOIN CallInfo C
          ON S.Extnum = C.Extnum

It would be even better to JOIN just with Max(Status) in CallInfo for a given Extnum, but it seems it's not possible to create an Aggregate in a query like this, or at least, i didn't find the way to do it.

[szlamany]

Maybe work into the WHERE clause

AND C.STATUS=(SELECT MAX(C1.STATUS) FROM...)

Although I fail to see how the MAX and the "latest" relate in your situation.

Please give more details about the "source" table you are updating from.

Also - is this MS SQL SERVER?? Have you ever used STORED PROCEDURES?

[Oliver1]

Thanks for the replyies, to clarify I am getting the data from an access table, I am then updating a mysql table.
I could run a query against the access database that would only pull back the latest update for each ext but a query like this takes lots more time to run than the simple query I am running. I have no control over the access database and so I am stuck with using access.

The key to the whole process is speed as the below bit of code runs every few seconds. For an overview our phone system logs the current staus of all our extensions to an access table, I then read this info and put it into mysql, every client pc then has a program which gets the current status of each extension by querying the mysql database. They cannot query the access database directly as unsuprisingly access just bombs if hit by that many queries.

[szlamany]

If speed is the key and you cannot change the SOURCE query then load the source query data into an array of some sort.

There must be a low and high limit to the extension numbers in the office - you could have an array that references each extension - and loop through the recordset - load the STATUS in the array. Each successive time you hit the same extension you are changing the STATUS in the array. Once done with that loop the array and write to the output database.