Database timeout too slow

Discussion in 'ASP .Net' started by Kevin Blount, Jul 26, 2006.

  1. Kevin Blount

    Kevin Blount Guest

    I have a site that uses a connection to an Oracle server, and I'm
    looking to make it intelligent enough so that if the database is
    offline for any reason (IT are updating, etc) then my site won't break.

    This is easy enough with a try/catch around my conn.Open(); command,
    but the time it takes for the catch to kick in is huge, relatively
    speaking. For example, I have one page that makes 2 seperate calls to
    my method to return a DataTable, and each one takes about 20 seconds to
    time out, meaning that the page takes about 40 seconds to finally load.
    It's possible that each page could make two connections, so every page
    would take this long, making the site un-navigable.

    I was wondering if anyone might know a technique for checking more
    quickly if a connection to the database is possible. I had looked at
    adding "Connection Timeout=1" to my connection string, but I found this
    this essentially skips the connection all together.

    My setup is that I use a VPN to connect to a remote Oracle database,
    alow my scripts to run locally but using data from this remote source.
    If I disconnect from the VPN and run my scripts, the VPN asks to get
    connected, essentially letting me know that an attempt to get the data
    is being made. If I cancel the VPN dialog (or close it entirely) that's
    when I have to wait 20 per conn.Open(); If I add "Connection
    Timeout=1;" to my connection string, I get no VPN dialog, so no attempt
    it being made, and even when the VPN *is* connected, my scripts don't
    return a DataTable.

    So, other than my failed attempt at fixing this, are there any quicker
    techniques for checking the connection to the database, or for
    returning a Timeout or other error sooner? I'm not against Oracle
    config changes, though I don't have permissions to our DB, so this
    would need to be approved by my IT dept first, and thus isn't as
    disirable.

    here's my method for returning the DataTable, in case it helps:

    public static DataTable selectQuery(string sSQLQuery, string
    sDBUsername, string sDBPassword)
    DataSet oDS = new DataSet();
    DataTable oDT = new DataTable();
    OracleDataAdapter oDA = new OracleDataAdapter();

    string sConnectString = "Data Source="+sDBServerName+";User
    Id="+sDBUsername+";Password="+sDBPassword+";";
    OracleConnection oConn = new OracleConnection(sConnectString);

    try
    {
    oConn.Open();
    oDA.SelectCommand = new OracleCommand(sSQLQuery, oConn);
    oDA.Fill(oDS, "myTable");
    oDT = oDS.Tables["myTable"];

    oDA.Dispose();
    oDS.Dispose();
    oConn.Close();
    oConn.Dispose();

    return oDT;
    }
    catch (Exception err)
    {
    oDA.Dispose();
    oDS.Dispose();
    oConn.Close();
    oConn.Dispose();
    ... code to log the error ...
    //return empty DataTable
    return oDT;
    }
    Kevin Blount, Jul 26, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Martin
    Replies:
    0
    Views:
    460
    Martin
    Aug 4, 2004
  2. SB
    Replies:
    0
    Views:
    364
  3. Mike Foster
    Replies:
    0
    Views:
    891
    Mike Foster
    Jun 23, 2003
  4. Mark Schaver

    Calendar display of information from database too slow

    Mark Schaver, Apr 21, 2004, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    254
    Mark Schaver
    Apr 21, 2004
  5. Mark Probert

    Timeout::timeout and Socket timeout

    Mark Probert, Oct 6, 2004, in forum: Ruby
    Replies:
    1
    Views:
    1,267
    Brian Candler
    Oct 6, 2004
Loading...

Share This Page