Results 1 to 3 of 3

Thread: SQL slow to connect after sitting for a bit

  1. #1

    Thread Starter
    PowerPoster Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    SQL slow to connect after sitting for a bit

    I have the vb.net program and the MS SQL Server on the same machine (its a POS program)
    If the POS sits for a little while, 10, 20 minutes.. the first sale to go through takes a good 20 seconds to save the info into the database.. any orders done right after are fast
    when the "finish button" is clicked, it inserts or updates the customer info, then inserts the order info, then the order details.
    3 different tables.
    It then opens an rdlc to print....
    using
    Code:
    Public conn As New SqlConnection(My.Settings.POSDataConnectionString)
    the Settings is this (the Min Pool Size was just added.. Not sure if it will help?)
    Code:
    Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\POSData\POSData.mdf;Integrated Security=True;Min Pool Size=1;
    I always open the connection (which if it already open) it doesnt open another
    Code:
    If conn.State <> ConnectionState.Open Then
       conn.Open()
    End If
    and then close it after..

    is there something I can do? can I keep it open? just call the open each time to make sure its open.. and only close it when the app closes? Im also wondering if the rdlc (report viewer report) could be the delay.
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,957

    Re: SQL slow to connect after sitting for a bit

    You shouldn't need to hold the connection open. Connection Pooling will handle that for you.

    I think the RDLC is more likely to be the problem as it's served by IIS. I don't understand the exact mechanism but it does do some sort of caching of recent activities so that it can serve them up faster. I'm guessing you're leaving it long enough between requests to have it fall out of that cache.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    PowerPoster Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: SQL slow to connect after sitting for a bit

    yeah digging around last night seem to point to that....

    Anyone have any ideas about that?
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

Posting Permissions

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



Click Here to Expand Forum to Full Width