Q&A

  • 여러분들은 ado와 bde의 속도차이 어떻게 해결하셨습니까?
현재 ADO 쪽 조건은 이렇습니다.

client의 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                                
이 게시물을
1  COMMENTS
  • Profile
    박진수 • 2003.11.14 21:35
    원인을 찾았습니다.

    바로 TADOQuery.SQL.Add 메소드에 그 원인이 있었습니다..

    혹시나 하는 의구심이 들어서  Open 메소드를 사용하기 직전에
    showmessage('오픈실행');을 두고 컴파일후 실행한 결과

    shoemessage 창이 나올때 까지 무려 22초가 흘러버리더군요..

    또 TAdoQuery를 TQuery로 대체한 결과 (동일 메소드 수행) 약 1~2초

    그래서 쿼리부분의에 사용했던  SQL.Add 메소드를 제거하구
    각각의 문자열 끝에는 cr값을 더해서 1개의 문자열로 만든후에
    SQL.Text에 넣고 Open을 실행하니 거의 1초 안에 읽어 오더군요..

    아무래도 잘은 모르겠으나, TAdoQuery 의 SQL.Add()  메소드가 문제가 있는거
    같습니다.  TQuery 의 SQL.Add()  메소드와  처리속도 차이가 너무 나니..

    물론 TAdoQuery의 SQL.Append() 도 무척 느리기는 마찬가지 입니다..

    TADOQuery사용시  SQL.Add() 메소드의 사용은 피하는것이 좋을둣 합니다.

    대신 SQL.Text 프로퍼티에 직접  sql 스트링을 넣는것이 좋을듯 하네요.

    일단 TADOQuery.SQL.Add() 메소드가 왜 이렇게 느린지는 저도 원인을 찾고 있는 중 입니다.
←First Page
542 543 544 545 546 547 548 549 550 551
Last Page→
한델 로그인 하기