|
현재 ADO 쪽 조건은 이렇습니다.
Cient MDAC 버전은 2.8 최신버전 이구요.
TAdoConnection <- TAdoQuery <- TDataSource <- TDbgride
이렇게 연결했구요.
Connection String 값은
'Provider=SQLOLEDB.1;Password=BEST;Persist Security Info=True;User ID=BEST;Initial Catalog=BESTLRM;Data Source=210.91.xx.xx'
TAdoConnection과 TAdo쿼리의 커서로케이션 값은: 클라이언트 로 맞춰놨고
TAdoConnection과 TAdo쿼리의 커서타입 값은: ctStatic 로 맞춰놨고
TADOQuery의 cash size는 200으로 했습니다.
BDE 쪽은
MS-SQL 클라이언트 드라이버를 통해 다이렉트로 BDE에 연결했습니다.
-------------------------------------------------------------------
서버쪽 시스템 사양은
NT서버 4.0 서비스팩 6a
SQL서버 버전은 MS-SQL서버 7.0 서비스팩4
펜3 500 듀얼 메모리 1GB
HDD ULTRA 160 wide scsi 18기가에 NTFS 파일 시스템으로 동작되고 있습니다.
클라이언트쪽 시스템 사양은
펜4 1.4G 256RDRAM Win98se
델파이 7
인데요..
아래의 SQL문을 실행시
ADO로 하면 22초 BDE로 하면 거의 1초 입니다.
이 속도 문제의 해결방법은 어떻게 하면 BDE 속도에 근접시킬수 있을까요?
ADO 는 SQL서버OLEDB 로 연결했습니다.
참그리고 더 재밌는 현상은 아래의 쿼리를
디자인 타임시에 TADOQuery의 sql스트링 프로퍼티에 넣어주고
TADOQuery의 ConnectionString 프로퍼티의 값을 직접 TAdoConnection의
TAdoConnection의 ConnectionString 프로퍼티 값과 동일한 값을 준후에
TADOQuery의 Active 프로퍼티를 True로 셋 하여 직접 dbgrid와 연결된
상태를 디자인 타임시에 볼때는 BDE와 동일한 속도처럼 읽어 온다는
사실 입니다.
LAN은 100BaseT 환경이고 현재 BLRM_TAXM 테이블에 저장된 ROW는 TEST 중이어서
단 1~2개 정도 저장되어 있습니다.
그런데 더 희안한것은 쿼리문의 라인수가 많아질수록 더 느려지내요.
BDE 쪽은 거의 변화가 없는데..,
MS에서 제공하는 쿼리분석기로 실행해도 단 1초 안에 결과가 리턴되는데
왜 델파이에 ADO를 연결하여 "컴파일한 실행화일"에서는 이모양으로 느린지..
디자인타임시에도 1초내로 읽어오는데..,
혹시 주제 넘는 생각인지 모르겠지만 델파이가 ADO를 제어함에 있어서
어떤 문제가 있는 없는것은 아닌지 도저히 감을 못잡겠네요.
--------------------------------------------------------------------------
SELECT case AA.MM
when '035' then '1/4분기'
when '065' then '2/4분기'
when '095' then '3/4분기'
when '125' then '4/4분기'
when '999' then '합 계'
else substring(AA.MM, 1, 2)+'월'
end MM,
sum(AA.TAX_AMT11) AMT11,
sum(AA.TAX_TAX11) TAX11,
sum(AA.TAX_TOT11) TOT11,
sum(AA.TAX_AMT12) AMT12,
sum(AA.TAX_TAX12) TAX12,
sum(AA.TAX_TOT12) TOT12,
sum(AA.TAX_AMT21) AMT21,
sum(AA.TAX_TAX21) TAX21,
sum(AA.TAX_TOT21) TOT21,
sum(AA.TAX_AMT22) AMT22,
sum(AA.TAX_TAX22) TAX22,
sum(AA.TAX_TOT22) TOT22
FROM ( SELECT substring(TAXM_CDATE, 5, 2)+'0' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE LIKE '2003%'
GROUP BY substring(TAXM_CDATE, 5, 2)+'0'
UNION ALL
SELECT '035' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE BETWEEN '20030101'
AND '20030399'
UNION ALL
SELECT '065' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE BETWEEN '20030401'
AND '20030699'
UNION ALL
SELECT '095' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE BETWEEN '20030701'
AND '20030999'
UNION ALL
SELECT '125' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE BETWEEN '20031001'
AND '20031299'
UNION ALL
SELECT '999' MM,
/*------- 과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX11,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT11,
/*------- 과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX12,
sum(case TAXM_DUTYFREE
when 0 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT12,
/*------- 비과세 매출 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX21,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '0' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT21,
/*------- 비과세 매입 ----------*/
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TAMOUNT
else 0
end
else 0
end) TAX_AMT22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TTAX
else 0
end
else 0
end) TAX_TAX22,
sum(case TAXM_DUTYFREE
when 1 then
case TAXM_GBN
when '1' then TAXM_TOTAL
else 0
end
else 0
end) TAX_TOT22
FROM BLRM_TAXM
WHERE TAXM_CDATE LIKE '2003%'
UNION ALL
SELECT '010' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '020' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '030' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '035' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '040' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '050' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '060' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '065' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '070' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '080' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '090' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '095' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '100' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '110' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '120' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
UNION ALL
SELECT '125' MM,
0 TAX_AMT11, 0 TAX_TAX11, 0 TAX_TOT11, 0 TAX_AMT12, 0 TAX_TAX12, 0 TAX_TOT12,
0 TAX_AMT21, 0 TAX_TAX21, 0 TAX_TOT21, 0 TAX_AMT22, 0 TAX_TAX22, 0 TAX_TOT22
) AA
GROUP BY AA.MM
|
