Design question: Simultaneous connections

Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Design question: Simultaneous connections

  1. #1
    steve adamo Guest

    Default Design question: Simultaneous connections

    Quick question to see if its bad design policy to have 8-12 multiple database connections to an Access database at one time.<BR><BR>I have a mesage board that make several connections during its varous stages of validation, inserting, updating, etc. At the end of my code i close the connection to the database thereby closing the previous connections, but have always wondered if there were some way for me to close the connections inside my code before reaching the end.<BR><BR>Thanks for any advice!

  2. #2
    Join Date
    Dec 1969

    Default *ONE* connection per ASP page...

    There is *NEVER* a reason to make more than one connection per page, per database. I did *not* say per table. I said per *database*.<BR><BR>Every open connection eats up tons of resources, both in the ASP side *and* on the database side. So don&#039;t use one connection more than you absolutely need.<BR><BR>And 99.9% of the time that means one per ASP page. Period.<BR><BR>

  3. #3
    RDM Guest

    Default Hmmmm...

    I&#039;ve actually run some timing tests in Win2k IIS 5.0 and found something interesting. I found a considerable performance increase when I created the objects, ran the DB tasks (select,inserts, updates, etc...), closed the connection, and destroyed the objects for each and every DB tasks. This was faster than creating one connection object and using it for all db tasks in that same page then destroying the connection. <BR><BR>For testing purposes I create a loop to execute the same stored procedure 100 times. One test created the objects, established the connection, closed the connection, and destroyed the objects.<BR>Then performed the same test moving this outside the loop. I did this same test with several different db tasks and it all performed the same.<BR><BR>I argued against this practice with our webmaster as the overhead didn&#039;t seem to make sense. But, this test along with others proved me wrong...

  4. #4
    Join Date
    Dec 1969

    Default A guess...

    By closing the connection, you *told* the system that you didn&#039;t need any of the memory it was holding onto for not only that connection but also for the server-side records that were being cached.<BR><BR>Especially if you were retrieving large record sets, this would make sense.<BR><BR>But... you say "with several different db tasks" ... I am at a loss. <BR><BR>Well, if you were wrong and I am wrong, then there&#039;s a heluva lot of other people out there who are wrong!<BR><BR>

  5. #5
    RDM Guest

    Default Well...

    I don&#039;t know if I&#039;m "wrong" or not. But, I did see a performance improvement. I read over some of the Windows DNA information and how IIS processes ADO database access and it seemed to fall in line with destroying connections and objects as soon as possible. I don&#039;t profess to be an expert but these timed tests showed performance I certainly didn&#039;t expect. Perhaps my methods were faulty.<BR>If you get a chance to test it on a Win2k Advanced Server machine, I&#039;d be interested in your results.

  6. #6
    Join Date
    Dec 1969

    Default I was NOT doubting your word!

    Hey, 98% of the advice about performance that is *still* handed out today comes from the experience of a relative handful of users (and most of them MS employees) back in 1997 or so, when ASP was brand new and IIS was in its infancy, too, and the operating system...well, you get the idea!<BR><BR>I am *not* overly surprised by your results! Especially if, as I suspect, your site turns on connection pooling by default (because then a *lot* of the overhead of creating a new connection is minimized, so other factors become more significant).<BR><BR>As for me trying it on Win2K...<BR><BR>Ummm...look at the web site of the place where I work:<BR><BR>I&#039;d be much more likely to try it on Solaris 7.<BR><BR>

  7. #7
    RDM Guest

    Default Chuckle...

    Nah, I didn&#039;t take any offense to your comments. I&#039;ve read several of your comments at this forum and found them to be quite insightful...<BR><BR>Just thought it would be interesting to get some other people&#039;s opinions on stuff. I, like you, don&#039;t always take the MS bible at it&#039;s word. I usually gotta see it to believe it.<BR><BR>Well its time for bed. Have a good night...

  8. #8
    steve adamo Guest

    Default RE: *ONE* connection per ASP page...

    my apologies,.. what i meant to say was multiple requests,... not connections,...<BR><BR>i open the connection once,.. and set it to nothing, and close it at the end,..<BR><BR>inside my app i have several requests to the oen database... i was concerned and curious to see if i could "close" those requests,.. and i believe i can,.. that will free up some system resources,.. i belive,..<BR><BR>i am going to try it in the morning,..<BR><BR>thanks all for your responses,..

  9. #9
    Darren Neimke Guest

    Default Then... Woooops.

    Because I frequently create more than 1 connection object per ASP script.<BR><BR>Here&#039;s why. Let&#039;s say that I have a re-useable chunk-o-code which I use to dynamically populate a SELECT list from the database, it looks something like this:<BR> <BR> Function MakeGenericSelect ( tblName, fldValue, fldDescription, selectName )<BR> <BR>And I might use this code to call it...<BR> <BR> &lt;%= MakeGenericSelect ( "tblCarMakers", "fldID", "fldName", "selCarMakers" ) %&gt;<BR> <BR>Now I use that code on hundreds of pages, and I can&#039;t guarantee that there will be a pre-existing Open Connection Object on that page. Well maybe I can, but the other developers that use code from my library might not know it.<BR> <BR>In this case I create, open and close the Connection object within the function itself.<BR> <BR>That way it can operate more as a Black Box and isn&#039;t dependant on it&#039;s environment.<BR><BR>Cheers,<BR><BR>Darren<BR>[ ]

  10. #10
    The Hobbit Guest

    Default RE: Then... Woooops.

    What are you talking about?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts