excel - SQL Query Code Extremely Slow and Fails Often -

i creating vba script iterates through large amount of cells (708), , runs 2 sql queries on each cell.

the issue having code takes considerable amount of time complete. on top of that, application stops responding , sends "not responding" message.

below section of code. there same, occupies different range of cells. these both query 2 databases - totaling 4 runs of code.

code exerpt:

    cn.open strconnection      = 2 433         strsql = "select sum(counts) ( select sum([time down]) counts tbldailydowntimemach [operation] ='" & cells(i, "b").value & "' , [type of maintenance] = 'breakdown' " & filter & " union select sum([time down]) counts tbldailydowntimeassy [operation] = '" & cells(i, "b").value & "' , [type of maintenance] = 'breakdown'" & filter & ");"         set rs1 = cn.execute(strsql)          if isnull(rs1.fields(0)) or rs1.fields(0) = 0             goto nextrow         end if          cells(i, "d").value = rs1.fields(0)  nextrow:     next 

filter variable found in portion of code date range, nothing special.

if there more efficient way of accomplishing task?

there dramatically more efficient way of doing this. querying database twice each row extremely "chatty". best way handle write 1 or 2 queries include data needed rows, return results background tab, , use vlookup match data need.

this approach gets rid of unnecessary round trips database, , bonus eliminates loop in vba code, inefficient.