SQL Server Performance

Client processing time; what is it, and how do I improve it?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by MennovdH, May 30, 2008.

  1. MennovdH New Member

    Hello all,
    I've been working on improving a number of stored procedures' performance. I started by adding some indexes (indices ?) on fields that where used for filtering and ordering a lot, and then tackled the query itself; an ugly thing with SELECT DISTINCTs on a bunch of UNIONs. I reduced it to the following (sorry about the dutch comments):
    DECLARE @f_user_key AS intDECLARE @currentdate AS datetimeSET @f_user_key = 891SET @currentdate = getdate()SELECTT_VEST.*FROMT_VEST WITH (nolock)LEFT OUTER JOIN T_PCODE ON T_VEST.f_pcode_key = T_PCODE.f_pcode_key,T_USER WITH (nolock), T_USER_AUTH AS AUTH_VEST WITH (nolock),T_USER_AUTH AS AUTH_INST WITH (nolock),T_USER_AUTH AS AUTH_SGR WITH (nolock),T_SGR WITH (nolock),T_INST WITH (nolock)WHERE /* Linken van tabellen aan elkaar */T_INST.f_sgr_key = T_SGR.f_sgr_key AND T_VEST.f_inst_key = T_INST.f_inst_key AND /* Security op Vest niveau */T_USER.f_user_key = AUTH_VEST.f_user_key AND ((AUTH_VEST.f_auth_key = T_VEST.f_vest_key) OR (AUTH_VEST.f_auth_key = -1)) AND AUTH_VEST.f_auth_tp_key = '9' AND /* Security op Inst niveau */T_USER.f_user_key = AUTH_INST.f_user_key AND ((AUTH_INST.f_auth_key = T_INST.f_inst_key) OR (AUTH_INST.f_auth_key = -1)) AND AUTH_INST.f_auth_tp_key = '9' AND /* Security op Sgr niveau */T_USER.f_user_key = AUTH_SGR.f_user_key AND ((AUTH_SGR.f_auth_key = T_SGR.f_sgr_key) OR (AUTH_SGR.f_auth_key = -1)) AND AUTH_SGR.f_auth_tp_key = '7' AND T_USER.f_user_key = @f_user_key AND T_INST.f_net_tp_key = 1 ANDT_PCODE.f_deleted = 0 AND T_PCODE.f_s_date < @currentdate AND T_PCODE.f_e_date > @currentdate ANDT_SGR.f_deleted = 0 AND T_SGR.f_s_date < @currentdate AND T_SGR.f_e_date > @currentdate ANDT_INST.f_deleted = 0 AND T_INST.f_s_date < @currentdate AND T_INST.f_e_date > @currentdate ANDT_VEST.f_deleted = 0 AND T_VEST.f_s_date < @currentdate AND T_VEST.f_e_date > @currentdate ANDT_USER.f_deleted = 0 AND T_USER.f_s_date < @currentdate AND T_USER.f_e_date > @currentdate
    And then I looked up the client statistics in the mgmt studio for original and new query:
    Original query:
    Client processing time109
    Total execution time390
    Wait time on server replies281New query:
    Client processing time781
    Total execution time859
    Wait time on server replies78The server replies a lot quicker, and I think that's good. But why is the client processing time so high? What exactly is my client doing, how can I find out, and how do I improve it? Does it even matter?
    Thanks
    Menno
  2. Luis Martin Moderator

    Welcome to the forum.
    Did you try to see both execution plan?
  3. riga New Member

    Very important topic.
    It's a pitty not so many responses so far....
    I'm having exactly the same problem.

    On PRODUCTION SQL Server 2000 server "client processing time" = 160 ms
    On Development SQL Server 2005 server "client processing time" = 7000 ms.
    And it's the same behavior for all the clients.
    We ran the same query on different users machines.
    We can't even start testing VB6 application functionality on DEV SQL box
    All windows freeze.
    And it's not CPU problem. CPU time is smaller than on PROD box.
    It's 3.1 GHz , 3.00 GB of RAM.
    I'm sure it's not a server/hardware problem because when I run the query with
    SET STATISTICS TIME ON
    the results is:
    CPU time = 16 ms, elapsed time = 6456 ms.
    All indicates it's "client processing time" that takes all the time.


    What exactly "client processing time" is?
    Where should I start the investigation?

Share This Page