|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem executing long running query using ADO.Net and DB2I have written a small windows application in VB.Net to test ADO.Net performance while executing long running query. The query works most of the time but fails sometime. I am trying to connect DB2 using federated database. The query returns around 25K records from a total count of 10 million records based on criteria. It takes roughly 20 mins to complete whenever the query works. Following error is returned when the query fails: [DB2/NT] SQL1822N Unexpected error code "-30081" received from data source "ECDTS". Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". Any help will be appreciated! I am not able to figure from where I should start debugging this problem. Many Thanks, Ram Hi,
I would say that 20 minutes is a pretty bad performance and I am sure it is not a problem of ADO.NET. Most likely when you select records based on some criteria, fields, involved into this criteria are not indexed in a database and basically database engine loops through the whole table to find matching records. Try to analyze it and build indexes. After that you should get results in a seconds. Show quote news:1112022174.018107.18890@f14g2000cwb.googlegroups.com... > Hi Guys, > > I have written a small windows application in VB.Net to test ADO.Net > performance while executing long running query. > > The query works most of the time but fails sometime. I am trying to > connect DB2 using federated database. The query returns around 25K > records from a total count of 10 million records based on criteria. It > takes roughly 20 mins to complete whenever the query works. > > Following error is returned when the query fails: [DB2/NT] SQL1822N > Unexpected error code "-30081" received from data source "ECDTS". > Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". > > Any help will be appreciated! I am not able to figure from where I > should start debugging this problem. > > Many Thanks, > Ram > Hi Val,
Thanks for your reply. I agree the performance here has to do with indexes not been created on the table. But I get the same error on other queries too that takes not even a second. This error occurs few times and not always. I need to know the real cause of the problem so it can be fixed. I have seen similar error messages in other groups but the replies point to creating federated database. The environment on which these queries are executed is a federated database and so that is definitely not the problem. Thanks, Ram Val Mazur (MVP) wrote: Show quote > Hi, > > I would say that 20 minutes is a pretty bad performance and I am sure it is > not a problem of ADO.NET. Most likely when you select records based on some > criteria, fields, involved into this criteria are not indexed in a database > and basically database engine loops through the whole table to find matching > records. Try to analyze it and build indexes. After that you should get > results in a seconds. > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > <tukaram.thatiko***@gmail.com> wrote in message > news:1112022174.018107.18890@f14g2000cwb.googlegroups.com... > > Hi Guys, > > > > I have written a small windows application in VB.Net to test ADO.Net > > performance while executing long running query. > > > > The query works most of the time but fails sometime. I am trying to > > connect DB2 using federated database. The query returns around 25K > > records from a total count of 10 million records based on criteria. It > > takes roughly 20 mins to complete whenever the query works. > > > > Following error is returned when the query fails: [DB2/NT] SQL1822N > > Unexpected error code "-30081" received from data source "ECDTS". > > Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". > > > > Any help will be appreciated! I am not able to figure from where I > > should start debugging this problem. > > > > Many Thanks, > > Ram > > Hi,
Is it consistent with the specific queries or not? Show quote news:1112112273.518233.209120@l41g2000cwc.googlegroups.com... > Hi Val, > > Thanks for your reply. I agree the performance here has to do with > indexes not been created on the table. But I get the same error on > other queries too that takes not even a second. This error occurs few > times and not always. I need to know the real cause of the problem so > it can be fixed. I have seen similar error messages in other groups but > the replies point to creating federated database. The environment on > which these queries are executed is a federated database and so that is > definitely not the problem. > > Thanks, > Ram > > Val Mazur (MVP) wrote: >> Hi, >> >> I would say that 20 minutes is a pretty bad performance and I am sure > it is >> not a problem of ADO.NET. Most likely when you select records based > on some >> criteria, fields, involved into this criteria are not indexed in a > database >> and basically database engine loops through the whole table to find > matching >> records. Try to analyze it and build indexes. After that you should > get >> results in a seconds. >> -- >> Val Mazur >> Microsoft MVP >> >> http://xport.mvps.org >> >> >> >> <tukaram.thatiko***@gmail.com> wrote in message >> news:1112022174.018107.18890@f14g2000cwb.googlegroups.com... >> > Hi Guys, >> > >> > I have written a small windows application in VB.Net to test > ADO.Net >> > performance while executing long running query. >> > >> > The query works most of the time but fails sometime. I am trying to >> > connect DB2 using federated database. The query returns around 25K >> > records from a total count of 10 million records based on criteria. > It >> > takes roughly 20 mins to complete whenever the query works. >> > >> > Following error is returned when the query fails: [DB2/NT] SQL1822N >> > Unexpected error code "-30081" received from data source "ECDTS". >> > Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". >> > >> > Any help will be appreciated! I am not able to figure from where I >> > should start debugging this problem. >> > >> > Many Thanks, >> > Ram >> > > Hi Val,
It is not really consistent with specific queries. Few new queries after we discovered this problem last month failed with the same error. SQL1822N Unexpected error code "-30081" received from data source "WECDWTS3". Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". Even some of the earlier queries which were working now fails. I suspect some process is issuing an interrupt that might be closing the DB2 connection but not very sure which one. Below is the query which works most of the times but fails at times UPDATE LSTS.ACCT_REVIEW ACREV SET LSTS_ACCT_STATUS ='Verified', LSTS_ACCT_STAT_DT = CURRENT DATE WHERE (DIVISION_NO = 3) AND (BLOCK_NO = 3515) AND (LOT_NO = 160) AND (LSTS_ACCT_STATUS <> 'Verified') AND (LSTS_ACCT_STATUS <> 'Collections Eligible') AND (0 < (SELECT COUNT(*) FROM WECDWTS3.CSS_LIEN_DROP_RSN LDR Where((VER_FLAG = 'Y') AND (DROP_FLAG <> 'Y')AND (YEAR(VER_CURR_DATE) = YEAR(CURRENT DATE)) AND (LDR.ACCOUNT_NO = ACREV.ACCOUNT_NO))); Values for column DIVISION_NO, BLOCK_NO AND LOT_NO are dynamically assigned through string concatenation. Please let me know if you can think of anything else. Thank you, Ram Val Mazur (MVP) wrote: Show quote > Hi, > > Is it consistent with the specific queries or not? > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > <tukaram.thatiko***@gmail.com> wrote in message > news:1112112273.518233.209120@l41g2000cwc.googlegroups.com... > > Hi Val, > > > > Thanks for your reply. I agree the performance here has to do with > > indexes not been created on the table. But I get the same error on > > other queries too that takes not even a second. This error occurs few > > times and not always. I need to know the real cause of the problem so > > it can be fixed. I have seen similar error messages in other groups but > > the replies point to creating federated database. The environment on > > which these queries are executed is a federated database and so that is > > definitely not the problem. > > > > Thanks, > > Ram > > > > Val Mazur (MVP) wrote: > >> Hi, > >> > >> I would say that 20 minutes is a pretty bad performance and I am sure > > it is > >> not a problem of ADO.NET. Most likely when you select records based > > on some > >> criteria, fields, involved into this criteria are not indexed in a > > database > >> and basically database engine loops through the whole table to find > > matching > >> records. Try to analyze it and build indexes. After that you should > > get > >> results in a seconds. > >> -- > >> Val Mazur > >> Microsoft MVP > >> > >> http://xport.mvps.org > >> > >> > >> > >> <tukaram.thatiko***@gmail.com> wrote in message > >> news:1112022174.018107.18890@f14g2000cwb.googlegroups.com... > >> > Hi Guys, > >> > > >> > I have written a small windows application in VB.Net to test > > ADO.Net > >> > performance while executing long running query. > >> > > >> > The query works most of the time but fails sometime. I am trying to > >> > connect DB2 using federated database. The query returns around 25K > >> > records from a total count of 10 million records based on criteria. > > It > >> > takes roughly 20 mins to complete whenever the query works. > >> > > >> > Following error is returned when the query fails: [DB2/NT] SQL1822N > >> > Unexpected error code "-30081" received from data source "ECDTS". > >> > Associated text and tokens are "10054 * 0 TCP/IP SOCKETS recv ". > >> > > >> > Any help will be appreciated! I am not able to figure from where I > >> > should start debugging this problem. > >> > > >> > Many Thanks, > >> > Ram > >> > > >  |
|||||||||||||||||||||||
Other interesting topics