SQL Statement Issue

  • Thread starter Thread starter David Doing
  • Start date Start date
D

David Doing

I have a SQL statement that takes our server 2 minutes to return results,
and when the program is waiting for the results it appears to be
hung/crashed. Is there a way to keep this from happening? It happens with
almost every program I write that reads data from our old AS/400. The
queries usually take 10 sec. to several minutes to return data from the
server. Can I use a timer to control this?

Here is the code:

strSQL = "SELECT F42119.SDDOC, F42119.SDDCT FROM BURNS.F42119 WHERE " & _
"F42119.SDIVD = " & strDate & " ORDER BY F42119.SDDOC"

daInvoices = New OleDb.OleDbDataAdapter(strSQL, strConnect)
'Takes 2 mintues to run and the program seems to be frozen
daInvoices.Fill(dtInvoices)
dgInvoices.DataSource = dtInvoices

Thanks,
David
 
If this is a windows program, I recommend you launch a separate thread, and
have that thread execute the query and bind the data.
 
Thank you for your quick response...
but, what should I put in the new thread, and how do I return the results to
the calling thread?

Thanks,
David
 
1) I'd investigate why it takes two minutes and address that first.
INdexes may help for instance although not necessarily a magic bullet.
It's not record size I'm guessing b/c you are probably only returning a
record or few records
2) > strSQL = "SELECT F42119.SDDOC, F42119.SDDCT FROM BURNS.F42119 WHERE "
& _
"F42119.SDIVD = " & strDate & " ORDER BY F42119.SDDOC"
Click to expand...
should be changed to "SELECT F42119.SDDOC, F42119.SDDCT From
BURNS.F42119 WHERE F42119.SDIVD = ? Order by F42119.SDDOC"

Then add a parameter to the parameters collection. That should also speed
things up slightly but that's not the source of the problem.

Finally, yes, seperate threads can also help the freezing up, but you will
still have the program waiting for a return set.
 
William,
Thank you for your response. Indexing most likely is the issue, plus the
fact that this server is 5 yrs. old and not too reponsive with 50+ users
logged in. Parameters will most likely speed things up as well, but what's
1.4 minutes vs. 1.5? The users understand it takes a few minutes to return
the records, they just don't like the fact that the interface appears frozen
the entire time. Threads will solve this problem, and I will begin
re-writing to include them.

Thanks.
David
 
Back
Top