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
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?