SQL Server Performance

Improve performance for simple SQL commands over WAN

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Blasterboy, Apr 30, 2010.

  1. Blasterboy New Member

    Is there a way to improve performance for a simple SQL command (e.g. UPDATE DATA or INSERT statement) over WAN.
    I am accessing a SQL server database in a remote location over WAN using the OLEDB Provider and the OLEDB consumer templates. A simple SQL command takes about 35ms for execution which is quite a lot compared to the time, that is needed to execute the same statement via the Jet driver in an Access database in the same location (about 2ms). Is this normal? Is there a possibility to improve the performance?
    I also tried to use the SQL Native Client driver which made no difference.
    I also tried to reduce the network packet size, but that seemed to have no effect at all to the performance? Is there a way to check which network packet size is used in the profiler or anywhere else?
    Any ideas are welcome.
  2. Luis Martin Moderator

    Welcome to the forum!!
    Did you check if you have any blocking when update or insert?
  3. Blasterboy New Member

    Since it is a test database and I am the only one using it, I don't think that there is any blocking and simple SELECT commands aren't any faster. However, how can I detect blocking?
    There seems to be a minimum processing time of about 35ms, which is independent of the complexity of the command. I first thought that this may come from the standard network packet size of 4kB (4kB over a 2Mbit network takes about 31ms), but changing the packet size in the connection does not make any difference.
  4. satya Moderator

    Welcome to the forums.
    How can you simply compare the driver's performance over WAN, when it is subject to changes of bandwidth, number of rows and amount of network packets that are generated.
    So it comes to the point that what kind of query you are passing over WAN to get a required performance.
  5. Adriaan New Member

    You might add a SET NOCOUNT ON command before the query itself. That may not be an option if your client app expects the rowcount to be returned.
  6. Blasterboy New Member

    [quote user="Adriaan"]
    You might add a SET NOCOUNT ON command before the query itself. That may not be an option if your client app expects the rowcount to be returned.
    [/quote]
    Thanks for the hint. I tried this, but unfortunately it did not make any difference, even in batch commands[*-)].
  7. Adriaan New Member

    How are you doing the OLEDB - over ADO? Make sure you do it as a server-side operation.
  8. Blasterboy New Member

    [quote user="Adriaan"]
    How are you doing the OLEDB - over ADO?
    [/quote]
    We use C++ OLEDB consumer templates: For example:
    CCommand <CNoAccessor, CNoRowset> cmd;
    HRESULT hRes = cmd.Open( session, _T("UPDATE DATA SET Locked = 1 WHERE DataID = 123));
    cmd.Close();

    [quote user="Adriaan"]Make sure you do it as a server-side operation.[/quote]
    How can I check this?
  9. Blasterboy New Member

    [quote user="satya"]
    Welcome to the forums.
    How can you simply compare the driver's performance over WAN, when it is subject to changes of bandwidth, number of rows and amount of network packets that are generated.
    So it comes to the point that what kind of query you are passing over WAN to get a required performance.
    [/quote]
    Since the connection is a dedicated link for our company, the bandwith is quite stable.
    The number of rows is 1. I'm just trying to improve performance for very simple SQL commands.
    I can compare the performance because I can profile my application either with an Access or an SQL database with identical content in the same location.

Share This Page