Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Location
    Hamburg, Germany
    Posts
    5

    Unanswered: SQL-Server very slow via VPN (WAN)???

    Hello,

    I've written a program, which communicates via ODBC with multipe database platforms. In a local network it seems to be everthing OK, but when I connect via VPN (2MBit/s S-DSL) to the MSSQL (2000 SP3) the connection is not only very slow, it seems that the MSSQL only uses 1 % of the bandwith. I don't think that 0,25 KByte/s is quite normal speed. A query takes about 5 - 10 minutes. (And I do a lot of queries...)

    If I connect to an Oracle-DB the full bandwith will used (125 KByte/s).

    Is there a problem with the SQL2000? How can I solve this behaviour?

    so long

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not sure this is a sql problem. DSL has significantly slower upload speed than it's download speed. I do not think cable high speed has the same issue.

    Do you have a sql query analyzer on this remote machine? How long do queries take in the QA.

    In addition you might want to change you ODBC connection to a DSN'less ODBC connection
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Location
    Hamburg, Germany
    Posts
    5
    Thanks for your tips but the problem still exists.

    In our local 100 MBit-network I had a network load of only 3 - 5 % while accessing the SQL Server via ODBC. I excessive wrote data in a table. So via VPN the network load never grows about 5 %. This is very very slow. Is there an explanation for this behaviour?
    Can I accelerate the speed?

    DSNless connections were at the same speed and my SDSL network has a bandwith of 2 MBit/s up- and downstream.

    I become desperate...

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Did you try the query in the query analyzer over the VPN?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2005
    Location
    Hamburg, Germany
    Posts
    5
    Query Analyzer seems to be slightly faster and has a net load of 20 %...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    On any given piece of hardware/software, using ODBC connectivity (simply switching DSNs), MS-SQL always outperforms Oracle by 30-50% in raw throughput for us. If you are seeing Oracle running at 125 Kb/second and MS-SQL running at 0.25 Kb/second, something is seriously wrong with your setup.

    My first guess would be that your SQL connection is using something other than TCP/IP (NetBios or nwlink would be my first guesses). If it is using TCP/IP, then I'd suspect some kind of router/link problems, possibly filtering UDP port 1433.

    I can't fully diagnose this with the information provided, but assuming that your observations are correct, then something is seriously interfering with the MS-SQL communications path.

    -PatP

  7. #7
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Have you tried a simple trace route from client to server to measure your hop times? VPN's introduce a lot of lag but you also need to see what your overall performance is outside the application.
    Fred Prose

  8. #8
    Join Date
    Jan 2005
    Location
    Hamburg, Germany
    Posts
    5
    I verified the speed of Oracle and it is as slow as the MSSQL. I only tested it in our local network and via VPN from my home. I configured the MSSQL of several ways, but nothing improved the speed. I only connect to the servers via TCP/IP. All other methods are disabled.

    I programmed a little tool today, which connects to a database in two different ways. First via ODBC and it needed 13 seconds, second via native programming (in C) and it needed 14 secondsfor writing 1000 rows into the DB.

    Our network is well configured and I can not explain why the connections are so slow.

    I tried my tool on two servers:

    1.

    Dual PII-400 MHz
    786 MB RAM
    40 GB SCSI HD

    2.

    Dual Xeon P4@2,66 GHz
    2,5 GB RAM
    90 GB SCSI RAID5

    The results are indentical...

    I'm going crazy...

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you're never going to get the same speed out of VPN as you are a LAN connection.

    If you do let me know.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2005
    Location
    Hamburg, Germany
    Posts
    5
    But why is the speed to the 2 databases slow in my local network?

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I was'nt reading carefully enough. The reasons for this could be numerous. Bad database design. Bad query writing. Network bottlenecks. What is your front end? Client server apps can do all kinds of things. If you are using a web front end, there can be problems on the web server.

    If your query is slow in the QA on the LAN it probably has something to with your database or query design.

    Execute the query in the QA on your database server (not from a client). If the query takes too long being run locally on your server you most likely have a design issue.

    Apply indexes. Updates statistics. Reindex. Denormalize. Create a reporting datamart etc.............
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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