Interesting SQL type Query and Issue

[jasen]

Hello. Newby here.

I am running queries to an embedded database. As such, I cannot perform traditional SQL joins type commands. One issue is that I am querying for around 5000 records at a time. This in itself is leading to a few performance issues application wise.

I will give a little background as to what I am doing....

I am running a query to a bunch of field devices that have an internal timestamp with the following query:

select name,"system_info.product_id","system_info.last_power_off","system_info.last_power_on",
"system_info.firmware_date",
"system_info.unit_num", "system_info.unit_group",
"system_info.station_name",
"system_info.current_time", "system_info.time_polled",
"system_info.current_time.high", "system_info.time_polled.high",
(("system_info.current_time.high" + 3600) - "system_info.time_polled.high")/60 AS Diff
from fisherroc
WHERE name IN
(
SELECT name FROM remote
WHERE proto = 'FisherRoc' AND onscan = 'yes') order by Diff

This yields all the information I need. With that I am running a for each loop to send a timesync from the server to all devices that do not have a Diff of 0.

From there I am producing a report and writing a CSV file from that in which I am selecting from another table as follows:

ForEach row AsDataGridViewRowIn DataGridView1.Rows
Dim name AsString = row.Cells(0).Value
Dim DataAdapter AsNewOdbcDataAdapter("Select TOP 1 rtu, meterID from analog where rtu = '" + name + "'", con)
Dim meterid AsNewDataSet()
DataAdapter.Fill(meterid)


Any suggestions as to speed this up, or any suggestions on a more efficient way to go about this would be GREATLY appreciated.

Thanks!