Database Connection Overhead

Discussion in 'ASP .Net Web Services' started by Timbo, Feb 26, 2009.

  1. Timbo

    Timbo Guest

    Hi There,

    I have a ASP.NET Web Service that retrieves various data from a SQL 2000
    database and returns results to the user. I have written individual
    functions for each kind of data set that I'm returning. All was working
    fine and now I've scaled it up so that potentially 50 people are making
    requests the web service is sometimes hanging. I'm using a hosted server,
    so at the moment I'm not sure if its IIS or SQL2000 that is falling over and
    re-starting itself.

    My main query at the moment is regarding database connections and if I'm
    doing it correctly... here is a small example..


    <WebMethod(Description:="Updates some data and return a string based on
    result.", EnableSession:=True)> _
    Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
    String

    Dim cmd As New SqlCommand
    Dim cn As New SqlConnection

    cn.ConnectionString = cnStr()
    cmd.Connection = cn

    ..... do some SQL stuff etc...

    cn.Close()
    cmd.Dispose()
    cn.Dispose()
    End Function

    I have about 20 different functions all doing something different. Is there
    an overhead by me declaring the Connection string and Command string
    everytime the function is called, could I not just declare them once at the
    top of my class? If so would it be thread safe?

    Is what I'm doing at the moment opening too many connections to the server
    therefore causing it to crash when too many people are making requests?

    I hope this makes sense and any advice or best practises would be greatly
    received

    Thanks.
    Tim
     
    Timbo, Feb 26, 2009
    #1
    1. Advertising

  2. Timbo () writes:
    ><WebMethod(Description:="Updates some data and return a string based on
    > result.", EnableSession:=True)> _
    > Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
    > String
    >
    > Dim cmd As New SqlCommand
    > Dim cn As New SqlConnection
    >
    > cn.ConnectionString = cnStr()
    > cmd.Connection = cn
    >
    > .... do some SQL stuff etc...
    >
    > cn.Close()
    > cmd.Dispose()
    > cn.Dispose()
    > End Function
    >
    > I have about 20 different functions all doing something different. Is
    > there an overhead by me declaring the Connection string and Command
    > string everytime the function is called, could I not just declare them
    > once at the top of my class? If so would it be thread safe?


    Now, I am not the right person to talk about thread-safeness in ASP .Net
    that I know next to nothing about, but I can't imagine that it would be
    thread-safe for five seconds.

    You seem to be doing the right thing, but there are two things that need
    qualification:

    o cnStr() should return the same result every time. If you are adding
    something unqiue each time, you effectively kill connection pooling.

    o If you run ExecuteReader, I think you need to close it explicitly,
    or else the command and connection will stay around, which again
    causes problem.

    > Is what I'm doing at the moment opening too many connections to the server
    > therefore causing it to crash when too many people are making requests?


    50 simultaneous users on a web site is not a startling number.

    As I said, I don't have any web-programming experience (I'm an SQL guy),
    but if I were you, I would try to have a local test environment, so you
    can track down where the bottleneck is, IIS or SQL Server.


    --
    Erland Sommarskog, SQL Server MVP,

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
     
    Erland Sommarskog, Feb 26, 2009
    #2
    1. Advertising

  3. Timbo

    sloan Guest

    You should investigate the EnterpriseLibrary.Data framework.
    Why?
    Because it encapsulates alot of these best practices for you, was written by
    experts and gets reviewed hundreds if not thousands of users.

    Go here:
    http://sholliday.spaces.live.com/feed.rss
    Find
    Multiple RDBMS Support and the Factory Design Pattern

    Dont' focus on the WCF so much.
    Find the CustomerSqlServerData(.cs) file/class and check out what is going
    on there.


    It also makes very "clean" code.
    Example:

    public override IDataReader
    CustomersGetSingleWithOrdersReader(string customerId)
    {

    IDataReader returnReader = null;
    try
    {
    Database db = this.GetDatabase();
    DbCommand dbc =
    db.GetStoredProcCommand(this.PROC_CUSTOMER_GET_SINGLE_BY_ID); // This is a
    CONST from earlier in the class


    //Again another CONST from above...
    db.AddInParameter(dbc, PARAMETER_CUSTOMER_ID,
    System.Data.DbType.String, customerId);


    returnReader = db.ExecuteReader(dbc);
    return returnReader;
    }
    finally
    {
    }
    }


    You care about 2 things in the above code. Which stored procedure to call.
    What parameters to give it.
    The EnterpriseLibrary.Data does the rest for you.


    From my experience, the only thing the EnterpriseLibrary.Data cannot protect
    you from (because it can't in this situation) is using an IDataReader AND
    THEN NOT CLOSING IT.
    That's a big no-no.





    "Timbo" <> wrote in message
    news:e9O%...
    > Hi There,
    >
    > I have a ASP.NET Web Service that retrieves various data from a SQL 2000
    > database and returns results to the user. I have written individual
    > functions for each kind of data set that I'm returning. All was working
    > fine and now I've scaled it up so that potentially 50 people are making
    > requests the web service is sometimes hanging. I'm using a hosted server,
    > so at the moment I'm not sure if its IIS or SQL2000 that is falling over
    > and re-starting itself.
    >
    > My main query at the moment is regarding database connections and if I'm
    > doing it correctly... here is a small example..
    >
    >
    > <WebMethod(Description:="Updates some data and return a string based on
    > result.", EnableSession:=True)> _
    > Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
    > String
    >
    > Dim cmd As New SqlCommand
    > Dim cn As New SqlConnection
    >
    > cn.ConnectionString = cnStr()
    > cmd.Connection = cn
    >
    > .... do some SQL stuff etc...
    >
    > cn.Close()
    > cmd.Dispose()
    > cn.Dispose()
    > End Function
    >
    > I have about 20 different functions all doing something different. Is
    > there an overhead by me declaring the Connection string and Command string
    > everytime the function is called, could I not just declare them once at
    > the top of my class? If so would it be thread safe?
    >
    > Is what I'm doing at the moment opening too many connections to the server
    > therefore causing it to crash when too many people are making requests?
    >
    > I hope this makes sense and any advice or best practises would be greatly
    > received
    >
    > Thanks.
    > Tim
    >
     
    sloan, Feb 26, 2009
    #3
  4. Timbo

    Timbo Guest

    Thanks for the response Erland, I do close everything explicitly including
    DataReader Objects, but most of the time I use Output Parameters against the
    Command Object in SQL which I consider to be the most efficient way of
    returning small amounts of data i.e., Single names or True/False results -
    Am I right in this assumption?

    TIA Tim



    "Erland Sommarskog" <> wrote in message
    news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
    > Timbo () writes:
    >><WebMethod(Description:="Updates some data and return a string based on
    >> result.", EnableSession:=True)> _
    >> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
    >> String
    >>
    >> Dim cmd As New SqlCommand
    >> Dim cn As New SqlConnection
    >>
    >> cn.ConnectionString = cnStr()
    >> cmd.Connection = cn
    >>
    >> .... do some SQL stuff etc...
    >>
    >> cn.Close()
    >> cmd.Dispose()
    >> cn.Dispose()
    >> End Function
    >>
    >> I have about 20 different functions all doing something different. Is
    >> there an overhead by me declaring the Connection string and Command
    >> string everytime the function is called, could I not just declare them
    >> once at the top of my class? If so would it be thread safe?

    >
    > Now, I am not the right person to talk about thread-safeness in ASP .Net
    > that I know next to nothing about, but I can't imagine that it would be
    > thread-safe for five seconds.
    >
    > You seem to be doing the right thing, but there are two things that need
    > qualification:
    >
    > o cnStr() should return the same result every time. If you are adding
    > something unqiue each time, you effectively kill connection pooling.
    >
    > o If you run ExecuteReader, I think you need to close it explicitly,
    > or else the command and connection will stay around, which again
    > causes problem.
    >
    >> Is what I'm doing at the moment opening too many connections to the
    >> server
    >> therefore causing it to crash when too many people are making requests?

    >
    > 50 simultaneous users on a web site is not a startling number.
    >
    > As I said, I don't have any web-programming experience (I'm an SQL guy),
    > but if I were you, I would try to have a local test environment, so you
    > can track down where the bottleneck is, IIS or SQL Server.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP,
    >
    > Books Online for SQL Server 2005 at
    > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
    > Books Online for SQL Server 2000 at
    > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
     
    Timbo, Feb 26, 2009
    #4
  5. Timbo

    sloan Guest

    output variables are faster than "single row" IDataReaders (or similar)

    Performance is better, maintenance is at a slightly higher cost. (Aka, when
    you need to add a column to the result, the signature changes using the
    output variables).



    "Timbo" <> wrote in message
    news:...
    > Thanks for the response Erland, I do close everything explicitly
    > including DataReader Objects, but most of the time I use Output Parameters
    > against the Command Object in SQL which I consider to be the most
    > efficient way of returning small amounts of data i.e., Single names or
    > True/False results - Am I right in this assumption?
    >
    > TIA Tim
    >
    >
    >
    > "Erland Sommarskog" <> wrote in message
    > news:Xns9BBEA4198CDD2Yazorman@127.0.0.1...
    >> Timbo () writes:
    >>><WebMethod(Description:="Updates some data and return a string based on
    >>> result.", EnableSession:=True)> _
    >>> Public Function SendDate(ByVal Str1 As String, ByVal Str2 As DataSet) As
    >>> String
    >>>
    >>> Dim cmd As New SqlCommand
    >>> Dim cn As New SqlConnection
    >>>
    >>> cn.ConnectionString = cnStr()
    >>> cmd.Connection = cn
    >>>
    >>> .... do some SQL stuff etc...
    >>>
    >>> cn.Close()
    >>> cmd.Dispose()
    >>> cn.Dispose()
    >>> End Function
    >>>
    >>> I have about 20 different functions all doing something different. Is
    >>> there an overhead by me declaring the Connection string and Command
    >>> string everytime the function is called, could I not just declare them
    >>> once at the top of my class? If so would it be thread safe?

    >>
    >> Now, I am not the right person to talk about thread-safeness in ASP .Net
    >> that I know next to nothing about, but I can't imagine that it would be
    >> thread-safe for five seconds.
    >>
    >> You seem to be doing the right thing, but there are two things that need
    >> qualification:
    >>
    >> o cnStr() should return the same result every time. If you are adding
    >> something unqiue each time, you effectively kill connection pooling.
    >>
    >> o If you run ExecuteReader, I think you need to close it explicitly,
    >> or else the command and connection will stay around, which again
    >> causes problem.
    >>
    >>> Is what I'm doing at the moment opening too many connections to the
    >>> server
    >>> therefore causing it to crash when too many people are making requests?

    >>
    >> 50 simultaneous users on a web site is not a startling number.
    >>
    >> As I said, I don't have any web-programming experience (I'm an SQL guy),
    >> but if I were you, I would try to have a local test environment, so you
    >> can track down where the bottleneck is, IIS or SQL Server.
    >>
    >>
    >> --
    >> Erland Sommarskog, SQL Server MVP,
    >>
    >> Books Online for SQL Server 2005 at
    >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
    >> Books Online for SQL Server 2000 at
    >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    >
    >
     
    sloan, Feb 26, 2009
    #5
    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. John T. Goodman

    Overhead of 4-port over 2-port SRAM

    John T. Goodman, Jan 25, 2005, in forum: VHDL
    Replies:
    0
    Views:
    656
    John T. Goodman
    Jan 25, 2005
  2. Showjumper

    Does Option Strict On add overhead?

    Showjumper, Jul 31, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    370
    John Saunders
    Jul 31, 2003
  3. Brett Robichaud

    Trace overhead

    Brett Robichaud, May 5, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    523
    Brett Robichaud
    May 6, 2004
  4. Dave Williamson
    Replies:
    2
    Views:
    465
    Rocky Moore
    Aug 15, 2004
  5. Mythran
    Replies:
    5
    Views:
    5,001
    Mythran
    Oct 5, 2005
Loading...

Share This Page