locked
Improve SQL transaction speed over WAN RRS feed

  • Question

  • Hi everyone,

      What is the best pratice for accelerate SQL transaction on a WAN connection ?

    I know that we can use WAN Accelerator like the one propose by Riverbed or Barracuda but, whitout theses appliances, is there a way to tune up SQL Server to accelerate transaction between the client and the server on a WAN connection ?

    Best regards,

    Francis
    Monday, August 31, 2009 12:01 AM

Answers

  • It depends on what is the prinicipal component of your queries run-time.  If it is, as you imply, WAN issues, then this is usually related to the throughput of your WAN connection vs. the amount of data that you are trying to return over the WAN.  This leads to two obvious ways to try to impove thier perofrmance over the WAN:

    1.  Reduce the amount of data requested in the query:
    In partiuclar this usually means being very selective about what columns you retunr in the query and never returning columns thatyou do not acutally need.

    2.  Cache unchanging values in the Client:
    Many values/columns are lookup values included from joins that are in fact typically invariant and could be cached on the client, rather than re-retrieved every time.  For instance a Customer's name and address rarely changes during execution, so rather than retirvieng that same information for the same customers every time, you could instead cache the customerid-name-address on the client and then just return the customerid to the client.


    however, if your performance problems are actually caused by something else other than the WAN, the this is all wasted effort.

    RBarryYoung
    • Proposed as answer by RBarryYoung Wednesday, September 2, 2009 1:27 PM
    • Marked as answer by Zongqing Li Friday, September 4, 2009 7:31 AM
    Monday, August 31, 2009 4:08 AM

All replies

  • use a stored procedure
    Monday, August 31, 2009 1:35 AM
  • It depends on what is the prinicipal component of your queries run-time.  If it is, as you imply, WAN issues, then this is usually related to the throughput of your WAN connection vs. the amount of data that you are trying to return over the WAN.  This leads to two obvious ways to try to impove thier perofrmance over the WAN:

    1.  Reduce the amount of data requested in the query:
    In partiuclar this usually means being very selective about what columns you retunr in the query and never returning columns thatyou do not acutally need.

    2.  Cache unchanging values in the Client:
    Many values/columns are lookup values included from joins that are in fact typically invariant and could be cached on the client, rather than re-retrieved every time.  For instance a Customer's name and address rarely changes during execution, so rather than retirvieng that same information for the same customers every time, you could instead cache the customerid-name-address on the client and then just return the customerid to the client.


    however, if your performance problems are actually caused by something else other than the WAN, the this is all wasted effort.

    RBarryYoung
    • Proposed as answer by RBarryYoung Wednesday, September 2, 2009 1:27 PM
    • Marked as answer by Zongqing Li Friday, September 4, 2009 7:31 AM
    Monday, August 31, 2009 4:08 AM
  • Hi,

      Well at this point, we are using stored procedure, we have also reduce the amount pf date requested in the query and also use cache but, it's still a hard game !

    I'll continue to work on this problem by try to use a software named NitroPDF.. im not sure it will work but I'll try it.

    All the applicance solution (Riverbed, Barracuda, Citrix, Cisco...) a pretty expansive for us. I'll keep posting if I found something useful !

    Thank you for your response and help.

    Francis

    Wednesday, September 2, 2009 1:14 PM