SQL Statement Issue

[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

[Marina]

If this is a windows program, I recommend you launch a separate thread, and
have that thread execute the query and bind the data.

"David Doing" <do***@cny-golf.com> wrote in message
news:Oe**************@TK2MSFTNGP09.phx.gbl...

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

[David Doing]

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
"Marina" <nospam> wrote in message
news:ec**************@TK2MSFTNGP10.phx.gbl...

If this is a windows program, I recommend you launch a separate thread, and have that thread execute the query and bind the data.

"David Doing" <do***@cny-golf.com> wrote in message
news:Oe**************@TK2MSFTNGP09.phx.gbl...

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

[William Ryan]

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" 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.
"David Doing" <do***@cny-golf.com> wrote in message
news:Oe**************@TK2MSFTNGP09.phx.gbl... 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:

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

[Armin Zingler]

"David Doing" <do***@cny-golf.com> schrieb

Thank you for your quick response...
but, what should I put in the new thread,
The execution of the query.
and how do I return the
results to the calling thread?

http://msdn.microsoft.com/library/en...isualBasic.asp

especially sub topic "Parameters and Return Values for Multithreaded
Procedures"

--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

[David Doing]

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

"William Ryan" <do********@comcast.nospam.net> wrote in message
news:ua**************@tk2msftngp13.phx.gbl...

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"

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.
"David Doing" <do***@cny-golf.com> wrote in message
news:Oe**************@TK2MSFTNGP09.phx.gbl...

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:

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