Re: A few thousands simple queries seem to clog SQL server (for a while)

From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 10/16/04 

Date: Sat, 16 Oct 2004 01:18:00 -0400

Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

"Patrick Questembert" <patrickq@hotmail.com> wrote in message
news:1097897424.Nmcbmj5E6hUTOZfcpmn9sQ@teranews...
>I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1 Gamma on
>Windows XP. The summary of the problem is this: I am doing a series of
>queries (no updates) using the OleDB DataReader, and after a few thousands
>queries, the SQL server seems to be clogged and starts failing all
>requests. Then, after a minute or two, things return to normal. It is as if
>my series of queries exhausted a resource on the server which time heals.
>
> I am a newcomer to OleDB and SQL so it is possible that I am misusing the
> OleDB .NET objects. Or am I expecting too much from MySQL? Details below.
>
> After successfully running a few thousands queries of the type:
>
> OleDbCommand catCMD = DatabaseConnection.CreateCommand();
> catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS mysum
> FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
> myReader = catCMD.ExecuteReader();
>
> at some point, the ExecuteReader command stops working, with no specific
> error:
>
> System.Data.OleDb.OleDbException: No error information available:
> E_FAIL(0x80004005).
> at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
> hr)
> at
> System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
> dbParams, Object& executeResult)
> at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
> executeResult)
> at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
> behavior, Object& executeResult)
> at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
> behavior, String method)
> at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.OleDb.OleDbCommand.ExecuteReader()
>
> Note that I am always closing any DataReader I am using, but leaving the
> OleDBConnection open (and sometimes also reuse the OleDbCommand object on
> it).
> The first error materializes at random times (but always after a fair
> number of queries, maybe 10,000). When that first error happens, most
> subsequent queries fail as well, including attempts to re-open the
> connection to the database. Even a completely different application
> talking directly to the server, such as the mysql utility, stops working
> (fails to connect to the database).
> Then, usually, waiting a few minutes helps and things get back to normal.
>
> I went so far as inserting a 5 minutes delay in my code any time I would
> get an error ... and it completely solves the problem (although hardly a
> real-life solution)!
>
> I would appreciate feedback on:
> - is this likely to be a problem with my code? I mean, should ANYTHING I
> do in my code cause a separate application like mysql.exe to fail?
> - is it a MySQL 4.1 Gamma robustness issue? Should I stay away from MySQL
> for "serious" applications?
> - as I mentioned, nothing I am trying to do in the program at the point of
> error seems to help (except sleeping for a couple of minutes) - MySQL
> seems to be simply unreachable for a while, busy dealing with memory leaks
> or whatever. But for the more normal cases in the future where such an
> error could be transient, any good practices (such as closing + re-opening
> the connection to the database?
>
> Much obliged,
> Patrick
>

Relevant Pages

  • Re: DSN pointing to old server?
    ... However, I did not have too many tables and queries in place, so I ... I also created the queries by copying the text and then recreating the ... objects and then supplying the connection string when recreating them? ... Oracle db to a new server. ...
    (microsoft.public.access.externaldata)
  • Re: DSN pointing to old server?
    ... tabledef/querydef objects and recreate them. ... Access stores metadata and connection ... However, I did not have too many tables and queries in place, so I ... Oracle db to a new server. ...
    (microsoft.public.access.externaldata)
  • Lost connection to MySQL server during query
    ... Queries am Tag bringen keinen Fehler. ... Der Server hat einen Average ... Connection" nur Probleme gefunden die permanent auftreten und nicht nur ...
    (de.comp.datenbanken.mysql)
  • Re: Connection to another Server
    ... Your Access front-end uses an ODBC connection to connect to ... I had a number of queries referencing the newly updated server from ... Please look at my query and see if changes need to be made or give me a ...
    (microsoft.public.access.queries)
  • Re: Outgoing POP3 email missing/lost/not received
    ... Funny thing is that I have had this ISP for 8 years and it has always been ... It looks like when you last ran CEICW, you set the ISP's mail server to: ... Internet Connection Wizard. ... After the wizard completes, the following network connection ...
    (microsoft.public.windows.server.sbs)