환경 : 윈도우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 SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 23408
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12412
8960 SQL FileTable 관련 질문 [2] 개발장 2015.10.06 2581
8959 쿼리 문의드립니다. [2] 에버존 2015.10.06 2615
8958 특정테이블 select 권한 주기 [3] 황금용이 2015.10.06 3104
8957 날짜 값 구하는 쿼리 도와주세요 ㅠ_ㅠ [1] khaluamilk 2015.10.05 2407
8956 한컬럼에 구분자가 2개 이상일경우 쿼리 문의 드립니다. [9] 만격 2015.10.05 3494
» 계정에 따라 프로시져 실행속도 차이 [23] 팔개 2015.10.05 4699
8954 고정된 시간 셋팅 방법 관련해서 문의 드립니다. [3] 나는짱이야 2015.10.05 3626
8953 쿼리 튜닝 부탁합니다. [3] 신현숙 2015.10.04 2600
8952 공백 기준으로 왼쪽 문자만 자르려고 하는데요.. [3] neiyan 2015.10.02 2538
8951 문자가 포함되어져 있는지 확인 [4] DOOLLY 2015.10.01 3020
8950 문자열에서 숫자만 뽑아서 표시하는 방법좀 알려주새요 [3] 한주댕이다 2015.10.01 8844
8949 로그시핑 복원시간과 관련된 문의 [5] 메칸더 2015.10.01 2792
8948 쿼리문 질문좀 할께요.. [2] khaluamilk 2015.10.01 2431
8947 여러 컬럼을 비교해서 높은 값을 가져오는건 어찌해야할까요 [3] 즈타 2015.09.30 8915
8946 top 관련의 건 [4] DOOLLY 2015.09.30 2635
8945 ms sql 기반 데이터베이스 배움터 3장. 테이블 생성 오류 [1] bigdream 2015.09.29 4703
8944 OUTPUT default value에 대해 질문 드립니다. [1] 나는짱이야 2015.09.26 2422
8943 shared라는건 어떤 기능의 옵션 인가요? [2] don12345 2015.09.25 2906
8942 개인정보 암호화 관련 문의입니다. 호짱 2015.09.24 3091
8941 대용량 테이블의 row 삭제에 관해 [1] 음마앙 2015.09.23 3974





XE Login