환경 : 윈도우2003, SQL2000

내용

관리자 : SA

사용자 : USER

쿼리분석기에서 SA로 프로시져를 실행할 경우 3초정도 걸리던 쿼리문이 USER로 실행할 경우 7초정도 소요가 됩니다.

응용프로그램에서는 USER로 접속할 경우 런타임오류가 발생하는 경우도 있습니다.

서버에는 DB명이 두 개 있습니다.

DB1, DB2

쿼리문은 아래와 같습니다

아래와 같이 쿼리문을 만들고 sa실행 하였을 때와, user로 실행하였을 때 속도차이가 두배로 나타납니다.

프로시져명 : Usp_statics_List

프로시져 실행은 DB2에서 하였습니다.

       SELECT

             or_company,

             or_ID,

             deDay,

             SUM(saleCost) AS saleCost,

             SUM(taxSum) AS taxSum,

             SUM(vatSum) AS vatSum,

             SUM(exemptionSum) AS exemptionSum,

             SUM(totSum) AS totSum,

             SUM(account) AS account,

             SUM(remainderPay) AS remainderPay

       FROM

       (

                    SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

                           or_company,

                           or_ID,

                           DATEPART(@DPart, or_deDay) AS deDay,

                           0 AS saleCost,

                           SUM(CASE WHEN ca_tax = 't' THEN CONVERT(MONEY, (ca_saleCost * ca_Ea)) ELSE 0 END) AS taxSum,

                           ISNULL(SUM(CASE WHEN ca_tax = 't' THEN ca_VAT * ca_Ea ELSE 0 END), 0) AS vatSum,

                           SUM(CASE WHEN ca_tax = 'b' THEN CONVERT(MONEY, (ca_saleCost + ca_vat) * ca_Ea) ELSE 0 END) AS exemptionSum,

                           ISNULL(SUM(CONVERT(MONEY, (ca_saleCost + ca_vat) * ca_Ea)), 0) AS totSum,                                                                                                                                                                                                                                       

                           SUM(or_account) AS account,

                           remainderPay = (SELECT SUM(or_totpay - or_account) FROM B2BDB.dbo.B2BOrder_TB WHERE or_id = Main.or_ID AND DATEPART(@DPart, or_deDay) <= DATEPART(@DPart, Main.or_deDay) AND or_deDay LIKE @YY%)

                    FROM DB2.dbo.B2BOrder_TB Main

                    LEFT JOIN DB2.dbo.B2BCart_TB ON ca_Session=or_Session

                    WHERE (ca_goodsCode > 'K3010030' OR ca_goodsCode < 'K3010030' OR ca_goodsCode IS NULL) AND or_buyNsaletag = 'b' AND or_balDay = 'Y' AND REPLACE(or_Company, ' ', '') LIKE '%@Company%' AND or_deDay LIKE @YY%

                    GROUP BY or_company, or_ID, DATEPART(@DPart, or_deDay)

                    UNION ALL

                   SELECT go_info, go_id, deDay, SUM(saleCost), taxsum, vatsum, exemptionSum, totSum, account, remainderPay

                    FROM

                    (

                           SELECT

                                 go_info,

                                 go_id,

                                 DATEPART(@DPart, or_deDay) AS deDay,

                                 SUM((ca_salecost + ca_vat) * ca_ea) AS saleCost,

                                 0 AS taxsum,

                                 0 AS vatsum,

                                 0 AS exemptionSum,

                                 0 AS totSum,

                                 0 AS account,

                                 0 AS remainderPay

                           FROM dbo.B2BCart_TB => 이 부분을 DB2.dbo.B2BCart_TB

                           JOIN DB1.dbo.Goods_TB ON go_goodscode = ca_goodscode

                           JOIN dbo.B2BOrder_TB => 이 부분을 DB2.dbo.B2BOrder_TB ON or_session = ca_session

                           JOIN DB1.dbo.Company_TB ON co_ID = or_ID

                           WHERE co_CompanyChk IN('A','D')

                           AND or_deDay LIKE @YY%

                          AND or_buyNsaletag = 's'

                           AND go_info LIKE %@Company%

                           GROUP BY go_info, go_id, DATEPART(@DPart, or_deDay)

                           UNION ALL

                           SELECT

                                 go_info,

                                 go_id,

                                 DATEPART(@DPart, or_date) AS deDay,

                                 SUM(ca_cost * ca_ea),

                                 0,

                                 0,

                                  0,

                                 0,

                                 0,

                                 0

                           FROM DB1.dbo.Cart_TB

                           JOIN DB1.dbo.Goods_TB ON go_goodscode = ca_goodscode

                           JOIN DB1.dbo.Order_TB ON or_session = ca_session

                           WHERE or_date LIKE @YY%

                           AND go_info LIKE %@Company%

                           GROUP BY go_info, go_id, DATEPART(@DPart, or_date)

                    ) SaleTable

                    GROUP BY go_info, go_id, deDay, taxsum, vatsum, exemptionSum, totSum, account, remainderPay

       ) X

       GROUP BY or_company, or_ID, deDay

       ORDER BY deDay

       OPTION (LOOP JOIN)

빨간색 부분을 수정 해 주니 user로 접속하여 쿼리를 하여도 sa접속한 것과 같이 동일한 속도로 실행이 되었습니다.

어떤 이유에서 이런 현상이 나타나는지?

원격접속에서만 차이가 나고,

서버에 접속하여 실행하였을 때에는 동일한 속도가 나왔습니다.