환경 : 윈도우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접속한 것과 같이 동일한 속도로 실행이 되었습니다.


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


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

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


No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 602
8972 문자를 자를수 없나요? [4] 문상연 2015.10.14 2696
8971 SQL문을 어떻게 만들어야 할지 모르겠어요 ㅠㅠ [2] pistis1992 2015.10.13 3123
8970 대용량 테이블명 변경 관련 문의 [2] 쵸보 2015.10.13 2221
8969 메모리좀 올려 주세요 (2) [3] 홍훈아 2015.10.12 2711
8968 DATE 합계 관련 해서 문의 좀 하려고 하는데요. [4] ssunsori 2015.10.12 2423
8967 표현법 질문드립니다 [1] 코스믹아울 2015.10.10 2077
8966 업체별 통계 조회 [3] 냥냥 2015.10.10 2572
8965 SQL 관계도 급여항목 만들기 오리올리브 2015.10.08 2574
8964 2014버전의 Compute 절 [3] dorhi73 2015.10.08 3089
8963 간단한 질문 드립니다. [2] ppoway 2015.10.07 2447
8962 sp_lock 관련 질문 [5] 순작 2015.10.07 2929
8961 다음 테이블의 쿼리 조회는 조인 말고는 다른 방법이 없을까요? [7] Gusto 2015.10.07 2741
8960 SQL FileTable 관련 질문 [2] 개발장 2015.10.06 2574
8959 쿼리 문의드립니다. [2] 에버존 2015.10.06 2608
8958 특정테이블 select 권한 주기 [3] 황금용이 2015.10.06 3092
8957 날짜 값 구하는 쿼리 도와주세요 ㅠ_ㅠ [1] khaluamilk 2015.10.05 2391
8956 한컬럼에 구분자가 2개 이상일경우 쿼리 문의 드립니다. [9] 만격 2015.10.05 3463
» 계정에 따라 프로시져 실행속도 차이 [23] 팔개 2015.10.05 4498
8954 고정된 시간 셋팅 방법 관련해서 문의 드립니다. [3] 나는짱이야 2015.10.05 3541
8953 쿼리 튜닝 부탁합니다. [3] 신현숙 2015.10.04 2577





XE Login