Results 1 to 16 of 16

Thread: Horrible Database Performance over VPN

  1. #1

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Horrible Database Performance over VPN

    Ok, I have a strange situation and I don't even know how to debug it. We have a site-to-site VPN set up between two locations with 50/50mbps fiber links.
    I can transfer a 10Mb file in about 2-3 seconds from a fileshare on a computer on this end, to a fileshare on a computer at the other end.

    I have a .NET program. When it launches, it pulls the contents of three, roughly 1000 row tables into itself from an SQL server using ADO.NET. It does this with three lines of SQL that it sends to the database server. The total data-size of all of this is about 600kb.

    When I run it on the same site as the database, it's almost instant.
    When I run it on a machine on the other end of the VPN from the server, it takes almost 2 minutes.

    I've read a LOT of people with bad database performance over VPNs in hunting about this, but most were odd scenerios, or blaming the programmer for sloppy database programming like opening and closing 1000 database calls, or latency (1ms to 70ms in my case) but things aren't adding up. I know my program. I know the exact amount of data it's pulling. I know how it's pulling that data. I know my connection speeds. I know my latency. I can live with the program taking 10 seconds to load it's data, but not "launch it and walk away and in 2-3 minutes, you can use it" slow.

    Does anyone know anything about this? Has anyone else experienced it? It would be faster if I wrote a backend server and passed text-files with SQL requests and return data back and forth at this point.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Horrible Database Performance over VPN

    This makes zero sense. I've got decades of VPN experience with connecting to my client sites to do work.

    Take those same three SQL statements and instead of executing them from code over the VPN, execute from a query window in SSMS - still over the VPN - using your local machine the same as your app used your local machine to call those queries.

    How long does the query window attempt take??

    [edit]

    running SSMS with network credentials

    runas /netonly /user:domain\szlamany "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe -S server.name.here -d acctfiles"

    [/edit]

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    That's why I'm pulling my hair out. Ok, I reduced it down to one table call that's UNIONing from two different databases.
    With my VB.NET code which is essentially:
    Code:
    Dim dt As New DataTable
    Using con As SqlConnection = OpenAndReturnNewOpenConnection()
      Dim sql As String = "SELECT A, B, C, D FROM DatabaseA.Table1 UNION SELECT A, B, C, D FROM DatabaseB.Table1"
      Dim da As New SqlDataAdapter(sql, con)
      da.Fill(dt)
    End Using
    From my workstation with debug flag set:
    Time <1 second (it's too fast for me to even start my stopwatch before the "loading" message dissappears)

    From a Release build on a computer at the other end of the VPN:
    Time: 47 seconds

    From the Query window:
    On my workstation:
    Time: Instant

    On the same machine on the other end of the VPN:
    Time: 5 seconds

    I'm seriously at a loss for words. This simply should not be this slow.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Horrible Database Performance over VPN

    Try something a whole lot simpler like this type of code to get the data

    Code:
                    Using dcn As New SqlConnection(strSQL)
                        Using cmd2 As New SqlCommand
                            cmd2.CommandType = CommandType.StoredProcedure
                            cmd2.CommandText = "dbo.GetRptEle_P"
                            cmd2.Connection = dcn
                            cmd2.CommandTimeout = 0
                            cmd2.Parameters.AddWithValue("@StoredProc", strSP)
                            dcn.Open()
                            Using rsReport As SqlDataReader = cmd2.ExecuteReader
                                While rsReport.Read
    .
    .
    .
                                End While
                            End Using
                        End Using
                    End Using
    Of course you will have CommandType.Text instead of .StoredProcedure and put your straight select statement within.

    I'm guessing the SQLDataAdapter sucks under the hood. Or you need to trick it up to work differently in your use case.

    Is this data "readonly" in your app life cycle?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Horrible Database Performance over VPN

    i'd suggest the following to track it down:
    split the Connection.open from the Adapter.fill and measure Timing for both. the way you use it shown in post #3 the .fill does the cn.open and the .fill in one call so you cannot tell if establishing the Connection takes the Long time (which i suspect) or if it is the query and data Transfer.

    i have observed very Long connect times in cases where security was set up in a wicked way.

  6. #6

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    Yes, the data is most all read only.

    Ok, good plan. I'm going to make a little "test program" that times each step of the process with your suggestions. The current program is too mature for me to start hacking into it with a bunch of test routines, but I can easily copy the form-load and background thread pre-load routines into their own thing.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Horrible Database Performance over VPN

    If your data is readonly then you should be able to use the basic code I've shown to OPEN THE CONNECTION ONLY ONCE - and use a simple SQL object to grab the data and load into simple structures for use (arrays for instance).

    The code I showed is typical of what I use in back end web services to return JSON strings with thousands of rows (as response to AJAX calls). I use StringBuilder object in the loop to keep the "time-to-build" as small as possible.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    Ok, here's some numbers prior to me doing any tweaking. I'm loading the original 3 tables mentioned in my first post, as well as anything else prior to that load that might affect it. All numbers in milliseconds using System.Diagnostics.Stopwatch. Both programs compiled for release and run a few times to cache the binaries.

    My PC at the home office on the same local network as the databases:
    Name:  home.png
Views: 3769
Size:  10.8 KB

    The server at the branch office on the other end of the VPN:
    Name:  cali.png
Views: 3723
Size:  10.9 KB

    Wow. Looking at these, maybe the problem is more general than I thought.
    Hitting the internal webserver to get a 2kb XML file had a difference of about 14:1
    The data fill differences were 39:1, 96:1 and 75:1 respectively.
    Creating connections was only a difference of 3:1, and seem to be the least of my problems.
    As I said, we have a 50/50Mbps fiber pipe at each location as our primary uplink. VPN is handled via Cisco-Meraki's site-to-site.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  9. #9

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    First big change - Instead of doing DataAdapter.Fill(DataTable), I'm doing DataReader = SQLCommand.ExecuteReader, DataTable.Load(DataReader):

    My PC at the home office on the same local network as the databases:
    Name:  home2.png
Views: 3781
Size:  13.3 KB

    The server at the branch office on the other end of the VPN:
    Name:  cali2.png
Views: 3747
Size:  17.1 KB

    Looks pretty negligible to me.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Horrible Database Performance over VPN

    Is your PC also connecting via VPN? If not, is there a way you can, and see what kind of throughput you get? I'm wondering if it isn't either end, but something in the middle between the two ends that causes the hangup. That's a sharp difference between 801 and 38964

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    No. The VPN is handled via the Cisco-Meraki gateways. Everything inside this building on the internal network. The gateways have two VPN setups, one for site-to-site VPN which connects gateways together and handles NAT traversal, and client-to-site VPN which handles individual PCs connecting into the network from home for example. Both VPN setups use L2TP as it's underlying base. The site-to-site is a little bit "auto-magical" and has few options. You basically tell it how you want the architecture to work, what VLANs you want forwarded over it, and any VPN specific firewall rules.

    I'm going to give Meraki a call next week and see if there's anything they can do. Granted, this is between Ohio and California, but I have a hard time believing the performance is this bad. Another thing would be to start looking at packets for fragmentation / out-of-order. It's been a LONG time since I last used Wireshark though, and the latest version doesn't look anything like what I messed with years ago, so if anyone has some Wireshark advice or a good "how to" I could read, I'd be most appreciative.

    I'm really starting to think this is more of a VPN thing, but database calls seem to be affected the hardest.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Horrible Database Performance over VPN

    Do you have IIS running on the same machine that the SQL instance is on??

    If it was turned on you should be able to put a PDF, for instance, in the wwwroot folder and see how long it takes to "display" in a browser window on your machine (that is if your machine is looking through the VPN to get to that destination - I'm a little confused about what that was all about and I've not had enough time to read all the posts yet).

    Make it a really big PDF - like one that you get by scanning 10 pages on your copier

    If you saw the same behavior you would at least have duplicated it in a much simpler fashion that the VPN folk should be able to address.

    Plus IIS has all kinds of logs about when and where and the timing of requests and delivery - and the browser debuggers do as well.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Horrible Database Performance over VPN

    [edit] Ignore the prior post - I read your posts and this does seem to be a network/VPN issue - maybe a credential trip to AD? [/edit]

    Actually - have you done a TRACERT at a CMD prompt to see any obvious bottleneck?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: Horrible Database Performance over VPN

    Not so much a traceroute. Mostly pings. Here's the traceroute:

    Name:  trace.png
Views: 3659
Size:  13.5 KB

    This is between a fileserver I've been using as my test machine and the ERP server here at the home office.
    The first-hop is the Cisco-Meraki gateway on the remote end. Last hop is the ERP server.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  15. #15
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Horrible Database Performance over VPN

    Can't you just activate the IIS (or a different WebServer) on your target-machine (which is acting as the DB-Host) -
    and then use that as an intermediate Layer which then handles the Selects (the Resultsets) locally -
    then transferring these Resultsets serialized (and compressed if you want) to your Client-Machine(s)?

    Below is an example which does not require a VPN (but is using SSL per https-Requests instead) -
    serving JSON-serialized Resultsets from a NorthWind-DB (working at the WebServer-side against SQLite):
    https://vbRichClient.com/JSONQueryDemo.html

    To test the biggest View (returning 2048 records on 26 Columns, you could change the Default-SQL
    [Select * From Orders] to: [Select * From Invoices] ... response- (transfer-)time for the entire resultset
    (there is no "delayed loading" activated in the OpenUI5-Grid) would be about 200msec for Invoices -
    (the transferred JSON-data in this case having a Volume of about 750KB or so, handled with serverside VB6-libs -
    and this could be tuned to about a third of the transfer-time per GZ-compression which is currently not activated on the IIS).

    Olaf

  16. #16
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Horrible Database Performance over VPN

    I don't know if it would help but I had a serious issue with a vb program loading SQL data from another location.
    I endup up using SET ARITHABORT ON in my vb.net code query.
    But I am not sure it would help in that case.
    Anyhow, just an idea.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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