    25  

       / 

            
   /,    
Delphi.      :  
 Delphi,       . 
        ;   
     .     
      ,   
  ,      
  . 

      
             
.         
/       15-18. 

      " " 

    ,      , -   
.  ,       
   ,       . 
   ,      
,  ,     " 
  ".        
  . 

       

    ,   ,  ,    
   .    ,    
  . , ,   
,    Delphi,    ..   
          
  Delphi  /. 

       

            
 .        
   .      .  
            
.         

     25.    / 477 

     .        
    .      
     ,    
   . 

       

    ,         
.  ,        
?  ,    ? 
    ,   
 . 
          (TPS  transactions per second).  
    .    
   ,       , 
      . 
    Q      (query responce time).   
       . 
        . 
    Q        (batch job execution 
time).   ,      
      .   
   ,      .. 
  ,        
 . 
    U     (application responsive ness).   
        . 
          
  . 
         (concurrency).     
        
. , 1000       
 .   ,       
         
 . 
           
.    -     
 .         
. ,       
      .   
       ,  
  .     ,  
    . ,    
     ,    
 ,      . 

       

            
/,    .   
   ,   ,  
             
 . 

         

             
,   .     
,        .  
     . 

    478  IV.    

     TDatabase 

             
   TDatabase   .   
   . 
    1.      TDatabase    . 
    2.         AliasName  
TDatabase    BDE,  . 
    3.     DatabaseName  ,   
     . 
    4.       DataSet (, TTable, TQuery  
TStoredProc),   DatabaseName   DatabaseName 
 TDatabase,    BDE,    . 
       ,   ,    
   ,       
  TDatabase. 
            BDE  
 TDatabase,        Delphi 
  TDataSet,    ,   
 . 
    Q  ,   TDatabase,       
 . 
    Q      DataSet (   
Active   Object Inspector  True),   TDatabase, 
    .   
KeepConnection  TDatabase    True,  
   DataSet      . 
         Delphi  
     .     
   ,   TDatabase  
    . 
      ,   KeeoConnection  
TDatabase  False;        
         
   DataSet     . 

    SQLPASSTHRU MODE 

             
    SQLPASSTHRU MODE   . 
           
.          
   ,  ,     
.  SQLPASSTHRU MODE    : NOT 
SHARED, SHARED AUTOCOMMIT  SHARED NOAUTOCOMMIT.     
     SHARED,    
,      SQLPASSTHRU MODE  
BDE    ,  ,  
 . 

      

      ,      
  ,       ,   
  .   '     
 ,        .  
      ,    
 . 
     -     ,    
        . 
   ,    .   
     . 
    1.       Delphi   Project Options. 
    2.     ,   ,   Auto-create forms  
    Available forms. 

     25.    /    479 

    3.     ,      
,    OnClose   Action   := caFree;. 
    4.                                 
           
    Application.CreateForm   (TForml,   Forml),  TForml    
, a Forml     . 
    5.             
showModal  . 
    6.        ,   ,  . 

       SQL 

            
     SQL.      
       "  
"     /. 

       

          ,  
,    .     
 SQL.    TQuery   SQL- 
Delphi    ,   ,    
.  ,     SQL,   
      .  
        , 
      ,  . 
      ,       
.      .., ,  
  INSERT, UPDATE  DELETE.     
      ,     
     .        
         
  . 
       ,      :  
         
  /,  Delphi.   Delphi 
       
  TUpdateSQL.   TUpdateSQL  
   ,        
       SQL.    
 ,     , , 
  .       
   SQL.       ,  
   . 
      ,   TUpdateSQL    
.        
   Delphi   ,   
 TUpdateSQL     .	
             
 TUpdateSQL        
Delphi.       ,  
 Delphi   ,     
,          
          . 
      ,       
     ,  Delphi,  
    -  .     
          
     API . 
          ,     
    .   
     ,    - 
   DELETE  - .    
       
,         
 .        
,   GRANT  REVOKE,    , 
   . 

    480	 IV.    

      
       Sybase, Oracle  Microsoft SQL Server,  InterBase 
         ,  
.          
       
 . , ,      
  . 
         /  
 ,        
    .    ,  
Delphi,      ,        
    Delphi   
.    ,  ,   
     ,    . 

      Prepare 

     Prepare  TQuery    .   
  SQL        
 .   Prepare     , 
    ,      
   ,     .   Prepare 
"  " ,      
  .   ,     
           
     .      
   . 

     UpdateMode 

     UpdateMode    ,   TQuery.  
      SQL WHERE,  
  .      UpWhereAll,   
BDE       WHERE    
.     ,    . 
,  ,      
UpWhereChanged,    ,     
  .      . 
    ,  Delphi     LastName 
 CUSTOMER.    SQL,     
 UpWhereAll (     WHERE). 

    UPDATE CUSTOMER 
    SET LastName='newlastname' 
    WHERE CustomerNumber=l 
    AND LastName='Doe' 
    AND FirstName='John' 
    AND StreetAddress='123 SunnyLane' 
    AND City='Anywhere' 
    AND State='OK' 
    AND Zip='73115' 

        ,    UpWhereChanged. 

    UPDATE CUSTOMER SET LastName='newlastname' WHERE CustomerNumber=l AND LastName='Doe' 

       ,    ,    , 
           
LastName,   ,      WHERE, 
    .   , , 
          
  ,   UPDATE,   
 UpWhereChanged,      .    
 ,     UpWhereAll.    
         

     25.    / 
    16  Delphi 3   / 481 

                  
LastName.       UPDATE  
      ,  .    . 
      , UpWhereKeyOnly,   ,     
 .        
,          
      .     
 ,    . 
         , 
            
.   UpWhereKeyOnly      
,     . 
      UpWhereKeyOnly    ,  
      .    
,      WHERE, , ,  
 ,       
       
 . 

      TQuery 

     ,   TQuery  ,  
   .     . -, 
   TQuery   
    SQL       
 (   Borland Database Engine),    .  
   /       
         .  
       SQL,    
,          
,    . 

      

        SQL,   ,  
        Delphi 
  .   (cashed update) 
   ,        .    
    .       
  ,      . 
     Delphi  . 
    1.      Object Inspector   CachedUpdates  
True   _ DataSet,     . 
    2.      UpdateRecordTypes     
DataSet       .  
    : 

    rtModified, rtlnserted, rtDeleted  rtUnmodifled. 

    3.      OnUpdateError     
    ApplyUpdates. 
    4.      DataSet  . 
    5.      ApplyUpdates      
CancelUpdates   . 

      SQL Monitor 

     Delphi   SQL Monitor     
 SQL,  .   , , 
   ,    .   
   ,    SQL  
     .   , 
        , 
   

    482	 IV.    

       .     ,   
"" ,    SQL Monitor,   
.  SQL Monitor    Database   Delphi. 

      

     BDE     (schema cashing)  
       .  
      ,  
          . 
      . 
          BDE Administration.   
  ,     .   
 . 25.1. 

     25.1.   BDE,     

       
    ENABLE SCHEMA CACHE	/   ( 
 ) 
    SCHEMA CACHE SIZE	     
    SCHEMA CACHE TIME	     ( ) 
    SCHEMA CACHE DIR 

          (  ) 

     SCHEMA CACHE TIME    -1,     
   -     .  
      1  2147483647 . 
           
    .  ,   
     () 
  , -         
.  ,       
 . 
                .  
       NULL /NOT NULL. 
           ,   
  ,    . 
    	Unknown Column ( ). 
    Q	Invalid Bind Type (  ). 
    Q	Invalid Type ( ). 
    	Invalid Type  Conversion (  ). 
    Q	Column Not  a Blob (  ,   BLOB). 

     

      Delphi       
   .     DataSet 
   OnFilterRecord    
,     ,      
    .     
     ,    
   ,   .    
    Delphi  . 
    1.       OnFilterRecord   
DataSet          Accept. 
    2.      Filtered  DataSet  True. 
    3.        DataSet     
,      ,  
 . 

     25.    / 483 

      
              
 OnFilterRecord, a   Filter,    
 Filter   .  , 
,     .   
    Table,   ,   
  
      ,   . 

     TField 

       FieldByName   Fields  
DataSet      TField.  
 TField  ,       
  ,       BDE.    
,        
  .   ,  FieldByName   
Fields          
.      .   TField  
   ,         
 . 

       

    ,   ,     -  
  .    , ,   
  -   .   
-     Delphi    . 
    -     
    DataSet   TField.   
-    ,     , 
     . 

    DBText       

         ,    DBEdit 
    DBText.  DBText     
   .      
Label   ,        . 
     ,   
 DBText.      Caption  TLabel  
  OnShow     ,   
  ,   ,  DBText. 
        
  ,  ,     
        . 

        

         32-  Windows  
     .  Delphi 
       TThread. 
  (multi-threading)      
.         
 .  -    ,  
         . 
           
. , ,         
     SQL,        
,         . 
           
.   25.1-25.4   , 
           . 

    484	 IV.    

      
           Delphi 
IBLOCAL.    ,   InterBase   
     IBLOCAL   . 

     25.1.        
 thrdex 

    program thrdex; 
    uses Forms, 
    thrdexOO in 'thrdexOO.pas ' {Forml}, thrdexOl in 'thrdexOl.pas ' ; 
    {?R *.RES} 
    begin 
    Application. Initializer-Application. CreateForm(TForml, Forml); Application.Run; 
    end. 

     25.2.    thrdexOO. pas,     
  thrdex 

    unit thrdexOO; interface 
    uses 
    Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, 
StdCtrls, DB, Grids, DBGrids, DBTables, ThrdexOl, ExtCtrls; 
    type 
    TForml = class(TForm) 
    Queryl: TQuery; 
    DataSourcel: TDataSource; 
    DBGridl: TDBGrid; 
    Buttonl: TButton; 
    DBGrid2: TDBGrid; 
    Query2: TQuery; 
    DataSource2: TDataSource; 
    Databasel: TDatabase; 
    Sessionl: TSession; 
    Session2: TSession; 
    Database2: TDatabase; 
    Query3: TQuery; 
    DataSourceS: TDataSource; 
    DBGridS: TDBGrid; 
    Button2: TButton; 
    Databases: TDatabase; 
    procedure ButtonlClick(Sender: TObject); 
    procedure FormClose(Sender: TObject; var Action: TCloseAction); 
    procedure Button2Click(Sender: TObject); 
    procedure DatabaselLogin(Database: TDatabase; LoginParams: TStrings); private 
    { Private declarations } public 
    { Public declarations } end; 

     25.    / 485 

    Forml: TForml; 
    implementation var 
    QueryThreadl, QueryThread2 : TQueryThread; 
    {$R *.DFM} 
    procedure TForml.ButtonlClick(Sender: TObject); begin 
    Databasel.Open; 
    Database2.Open; 
    QueryThreadl:=TQueryThread.Create(Queryl) ; 
    QueryThread2:=TQueryThread.Create(Query2); 
    Buttonl.Enabled:=False ; end; 
    procedure TForml.FormClose(Sender: TObject; var Action: TCloseAction); begin 
    QueryThreadl.Terminate; 
    QueryThread2.Terminate; end; 
    procedure TForml.Button2Click(Sender: TObject); begin 
    with queryS do begin if active then close/open; end; end; 
    procedure TForml.DatabaselLogin(Database: TDatabase; 
    LoginParams: TStrings); begin 
    LoginParams.Values['USER NAME'] := 'SYSDBA'; 
    LoginParams.Values['PASSWORD'] := 'masterkey'; end; 
    end. 

     25.3.   DFM   thrdexOO. pas,    
   thrdex                                    
                                                                               
                    
    object Forml: TForml Left = 26 Top = 9 Width = 595 Height = 434 Caption = 
'Forml' Font.Charset = DEFAULT_CHARSET Font.Color = clWindowText Font.Height = 
-11 Font.Name = 'MS Sans Serif Font.Style = [] OnClose = FormClose 
PixelsPerlnch = 96 TextHeight = 13 object DBGridl: TDBGrid 
    Left = 72   
    Top = 16 
    Width = 497 
    Height = 113 
    DataSource = DataSourcel 
    TabOrder = 0 

    486	 IV.    

     
    TitleFont.Charset = DEFAULT_CHARSET 
    TitleFont.Color = clWindowText 
    TitleFont.Height = -11 
    TitleFont.Name = 'MS Sans Serif 
    TitleFont.Style = [] end object Buttonl: TButton 
    Left = 72 
    Top = 374 
    Width = 121 
    Height = 25 
    Caption = 'Start Query Threads' 
    TabOrder = 󙗗 - 
    OnClick = ButtonlClick end object DBGrid2: TDBGrid 
    Left = 72 
    Top = 132 
    Width =497 
    Height = 113 
    DataSource = DataSource2 
    TabOrder = 2 
    TitleFont.Charset = DEFAULT_CHARSET 
    TitleFont.Color = clWindowText 
    TitleFont.Height = -11 
    TitleFont.Name = 'MS Sans Serif 
    TitleFont.Style = [] end object DBGridS: TDBGrid 
    Left = 72 
    Top = 248 
    Width = 497 
    Height = 113 
    DataSource = DataSource3 
    TabOrder = 3 
    TitleFont.Charset = DEFAULT_CHARSET 
    TitleFont.Color = clWindowText 
    TitleFont.Height = -11 
    TitleFont.Name = 'MS Sans Serif 
    TitleFont.Style = [] end object Button2: TButton 
    Left = 240 
    Top = 374 
    Width = 145 
    Height = 25 
    Caption = 'Open Query in Main Thread' 
    TabOrder = 4 
    OnClick = Button2Click end object Queryl: TQuery 
    DatabaseName = 'dbthreadl' 
    SessiqnName = 'Sesl' 
    SQLTstrings = ( 
    'select * from EMPLOYEE') 
    Left = 16 
    Top = 8 end object DataSourcel: TDataSource 
    DataSet = Queryl 
    Left = 16 
    Top =40 end object Query2: TQuery 
    DatabaseName = 'dbthread2' 

     25.    / 487 

    SessionName = 'Ses2' SQL.Strings = ( 
    'select * from SALES') 
    Left = 16 
    Top =80 end object DataSource2: TDataSource 
    DataSet = Query2 
    Left = 16 
    Top = 120 end object Databasel: TDatabase 
    AliasName = 'IBLOCAL' 
    DatabaseName = 'dbthreadl' 
    LoginPrompt = False 
    Params.Strings = ( 'USER NAME=SYSDBA' 1PASSWORD=masterkey') 
    SessionName = 'Sesl' 
    OnLogin = DatabaselLogin 
    Left = 16 
    Top = 152 end object Sessionl: TSession 
    Active = True 
    SessionName = 'Sesl' 
    Left = 16 
    Top = 224 end object Session2: TSession 
    Active = True 
    SessionName = 'Ses2' 
    Left = 16 
    Top = 256 end object Database2: TDatabase 
    AliasName = 'IBLOCAL'-.. 
    DatabaseName = 'dbthread2' 
    LoginPrompt = False 
    Params.Strings = ( 'USER NAME=SYSDBA' 'PASSWORD=masterkey') 
    SessionName = 'Ses2' 
    OnLogin = DatabaselLogin 
    Left = 16 
    Top = 192 end object Query3: TQuery 
    DatabaseName = 'dbdefaultthread' 
    SQL.Strings = ( 
    'SELECT * FROM CUSTOMER') 
    Left = 16 
    Top = 288 end object DataSourceS: TDataSource 
    DataSet = QueryS 
    Left = 16 
    Top = 320 end object Databases: TDatabase 
    AliasName = 'IBLOCAL' 
    DatabaseName = 'dbdefaultthread' 
    LoginPrompt = False 
    Params.Strings = ( 'USER NAME=SYSDBA' 

    488  IV.    

    'PASSWORD=masterkey') SessionName = 'Default' OnLogin = DatabaselLogin 
Left = 16 Top = 352 end end 

     25.4.    thrdexOl. pas,     
  thrdex 

    unit thrdexOl; interface 
    uses 
    Classes, Forms, DBTables; 
    type 
    TQueryThread = class(TThread) private 
    { Private declarations } 
    FQuery : TQuery; protected 
    procedure Execute; override; 
    procedure OpenQuery; 
    procedure DoOpenQuery; public 
    constructor Create(Query: TQuery); 
    procedure OnTerm(Sender: TObject); end; 
    implementation 

    { :     VCL     
   Synchronize, , 

    Synchronize(UpdateCaption) ;  UpdateCaption     
    procedure TQueryThread.UpdateCaption; begin 
    Forml.Caption := 'Updated in a thread'; end; } 
    { TQueryThread } 
    constructor TQueryThread.Create(Query: TQuery); begin 
    inherited Create(False); 
    FQuery := Query; end; 
    procedure TQueryThread.OpenQuery; begin 
    FQuery.Open; 
    With FQuery do begin 
    With Owner.Owner as TApplication do ProcessMessages; While not EOF do 
Next; Close; 
    With Owner.Owner as TApplication do ProcessMessages; end; end; 

     25.    /	489 

    procedure TQueryThread.DoOpenQuery; begin 
    Synchronize(OpenQuery); end; 
    procedure TQueryThread.Execute; 
    var 
    Counter : Integer; 
    begin 
    {   ,      . 
} For Counter:=0 to 100 do begin DoOpenQuery; 
    If Terminated then exit/end; 
    end; 
    procedure  TQueryThread.OnTerm(Sender:   TObject); begin 
    Application.MessageBox(PChar(',        '+ 
    FQuery.Name+'   .',   PChar(FQuery.Name),IDOK) ; end; end. 

           TSession  
  ,       100 
.        .  
      (   
 -)  ,       
         .  
          
Open,       .   Delphi 
 /      
   .  ,   ,  
 . 

       

           SQL, 
 ,     .. ,   
     ,  ,   
 ,    Delphi. 

       

         . , 
  ,  ,      
 .      
 . 

     

      , ,     , 
    ,   ,  
         .  
         
.     :     
    ,     . 

     

            
     .        
 ,       , 
   . 
          .  Sybase, 
  Microsoft SQL Server  -   : - 
  - .  -     
   -.      

    490	 IV.    

     -   .    
, ,       
   - .      
   .     
  - . 

     

            
  . ,  Microsoft SQL Server 
    (affinity mask),  , 
    .   Sybase   
  max online engines,    
.  Oracle          
      .  
,         
        .,  
.   ?       
^   ,    -.     
 -  ,    
   , ,  . 

     - 

           -. 
,      ,  
.      -,  
      .   
          
 RAID.   -    
    . 

      

            
 .     ,     
  .       , 
    -  ,  
      . ,   
Microsoft  Sybase SQL Server    trunc. log on chkpt  
          
.        
 ,    ,  
    . 
         :    
 .       
 (incremental backup),       
 .      
   .   ,    
 ;   ,      . 

      

            (query 
optimizer). ,  ,  ,       
  .        
,    .    SQL 
       . , 
    ,    .  
   ,  ,  
   (query execution plan).     
    ,   ,   
,    ,    
    ..   ,  
,     ,    
  . 

     25.    / 491 

      

            
    .   (index 
selectivity)      .   
  ,      
       . 
      InterBase       . 

    SET STATISTICS INDEX INVOICES03 

     INVOICES 03    . 
     Sybase  Microsoft   . 

    UPDATE STATISTICS INVOICES.INVOICES03 

     INVOICES   ,    , a INVOICES03  
  .    SQL Server    
,    ,      . 
  ,    ,      
   . 

    UPDATE STATISTICS INVOICES 

      Oracle     ANALYZE. 
      ,   
.        . 

    ANALYZE   INDEX  CUSTOMER03   COMPUTE   STATISTICS; 

            . 

    ANALYZE   TABLE   CUSTOMER  COMPUTE   STATISTICS   CASCADE; 

              
(   ). 

    ANALYZE  CLUSTER  acctrecv  COMPUTE   STATISTICS   CASCADE; 


        

         SQL-    
 .   ,   InterBase, 
  Display Query Plan    Basic ISQL Set Options, 
      Session  WISQL (. 25.1). 
        SET PLAN ON. 
            
    SET STATS ON.       
   SET STATISTICS,     
  . 
      Sybase  Microsoft SQL Server     
   SET SHOWPLAN ON.   SET NOEXEC, 
    ,      . 
     ,    ,   
    SQL Server SET STATISTICS.   SET 
STATISTICS 10 ON   -    
.   SET STATISTICS TIME ON    
    . 
          Oracle   
EXPLAIN PLAN PL/SQL.   EXPLAIN PLAN     
,   ,    Oracle  
     .      
,       . 
       ,  ,  
        .  . 25.2 
,      ,     
     (      
):       NATURAL. 

    . 25.1.   Basic ISQL Set Options 492 

     IV.    

       ,    .  . 25.3 
     ,     InterBase. 
      ,   ,     
 .   ,      
  . 

    . 25.2.      
    
    . 25.3.      

       

      InterBase    SELECT,   
        
,    .    : 

    SELECT  LastName,   FirstName FROM  CUSTOMER 
    PLAN    (CUSTOMER  ORDER  CUSTOMER03) ORDER BY  LastName 

         PLAN.     
,      ,    
    (    ). 
   ,     
           CUSTOMER03. 
          ,    
 ,      . 
      SQL Server         
.        
  . 

    SELECT LastName, FirstName FROM CUSTOMER (3) ORDER BY LastName 

            .   
    ,    
CUSTOMER. (          
 .        .) 
       0,     ,   
 1      ,  
 . 

     SQL Server      , : 
    SELECT LastName, FirstName 
    FROM CUSTOMER (INDEX = CUSTOMER03) 
    ORDER BY LastName 

     25.    / 493 

          SQL Server  
SET FORCEPLAN      ,   
   FROM.      
 ,     .    
  ,   ,    
 FORCEPLAN         
 FROM. 

       

             
  /    .  ,  
        
  . 

      
       " "     
        ,  56 /. 
      -1   
 1/7     Ethernet (10/),  
          . 
           
    . 
    Q  ,    ,     
 /,      (, 
   100 /). 
    U           
,       .   
 ,        . 
               
( , ,   Novell Large Internet Packet),  
         
 .        
 ,  . 
    Q       ,   
.          
  .        
 ,   .  ,     
  ,    ,   . 
    Q            
     DBGrid  DBCtrlGrid.   
     ,      
 ,     . -    
     . 
             "-".  
            
  .        
    . 
    Q          
 Label    Lookup/Locate,    DBText. 
    DBText   ,  
      .   
 ,    . 
       KeepConnection  TDatabase   
 False.      -   
    ,    
      .  
     .    
        , 
   .       
(KeepConnections:=False)     
  . 

    494	 IV.    

    Q          
   ,       . 
 ,          
   ,   . 
    Q          
,      ,      . 
    Q       BDE   
       .    
  ENABLE SCHEMA CACHE   BDE Configuration  True. 
    Q     Delphi      
 .          . 
    Q    Delphi BatchMove     
  .     . 
    Q      TCP/IP   ,   
 .       ,  
-    .     
       .  
    .      
      ,  
   . ,  Sybase SQL Server  
System 11    sp_conf igure,   
  TCP.   System 10     
  (1610)   :    
TCP_NODELAY       -1610. 
    Q        
 TCP/IP.         UNIX: 

    ndd  -set   /dev/tcp  tcp_rexmit_interval_max   
 ndd  -set   /dev/tcp  tcp_conn_req_max  
 ndd -set   /dev/tcp  tcp_close_wait_interval   
 ndd  -set  /dev/tcp tcp_keep_alive_interval  
 

           
 TCP/IP.         
   TCP/IP,       
    . 

     

      Delphi  /    
 :     .   , 
      SQL  
 .        
Delphi      ,      
. ,       
         . 

     ... 

      26, "Borland Database Engine",     
 ,     Delphi.  
       Delphi  ,   
   . 

     25.    / 495 

