单线程和多线程访问数据库性能比较二实现代码部分(oracle 数据库)

单线程和多线程访问数据库性能比较(oracle 数据库,C++Builder实现,odac4)

说明：本文主要说明单线程和多线程访问数据库性能的性能比较，关键词在
数据库(DataBase)，线程(Thread),单线程(single Thread),多线程(multi-thread)，数据库缓存(catch),事务(Transation)，等,与具体实现方式相关不大。基本个人爱好，采用C++builder和odac vcl数据库连接组件及c++builder封装的TThread线程类实现具体测试,数据库采用oracel，基于 sqlserver,mysql等数据库的测试以后有机会再做。

//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿
下面描述一下线程的的实现方法：
偶采用一个队列来保存这些任务线程5个，然后建立一个主调用线程完成10000次操作，依次队列中取得一个任务线程执行任务，任务线程执行完任务后suspend自己，并且重新加入自己到队列中。
主要程序如下：
单线程部分：
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit1.h"
#include "dmUnit2.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "cxContainer"
#pragma link "cxControls"
#pragma link "cxEdit"
#pragma link "cxMaskEdit"
#pragma link "cxSpinEdit"
#pragma link "cxTextEdit"
#pragma resource "*.dfm"
TForm1 *Form1;
//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿

//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
    : TForm(Owner)
{
}
//---------------------------------------------------------------------------
/*
drop sequence S_TEST1;
create sequence S_TEST1
minvalue 1000
maxvalue 9999999999
start with 1000
increment by 1
cache 20;
commit;
*/
void __fastcall TForm1::Button1Click(TObject *Sender)
{
int is = GetTickCount();
DataModule2->OraSession1->Connected = true;
int xx=0;
while (xx < cxSpinEdit1->Value)
{
//DataModule2->GetID("select s_Test1.Nextval from dual ");

DataModule2->ExecSQL("insert into test1  (pk,nm,tt)values  (s_Test1.Nextval,'nmmmmm',sysdate) ");
xx++   ;

}
int ie = GetTickCount();
DataModule2->OraSession1->Connected = false;
this->Edit1->Text = IntToStr(ie -is);

}
//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)
{
DataModule2->OraSession1->Connected = true;
try{
DataModule2->OraScript1->SQL->Clear();
DataModule2->OraScript1->SQL->Add("drop sequence S_TEST1 ") ;
DataModule2->OraScript1->Execute();
}catch(...){
}
try{
DataModule2->OraScript1->SQL->Clear();
DataModule2->OraScript1->SQL->Add("create sequence S_TEST1 minvalue 1000 maxvalue 9999999999 start with 1000 increment by 1 nocache  ")  ;
DataModule2->OraScript1->Execute();
}catch(...){
}

DataModule2->OraSession1->Connected = false;   
}
//---------------------------------------------------------------------------

数据库操作部分：
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "dmUnit2.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "DBAccess"
#pragma link "Ora"
#pragma link "MemDS"
#pragma link "OraSmart"
#pragma link "OraScript"
#pragma resource "*.dfm"
TDataModule2 *DataModule2;
//---------------------------------------------------------------------------
__fastcall TDataModule2::TDataModule2(TComponent* Owner)
    : TDataModule(Owner)
{
//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿

}
//---------------------------------------------------------------------------
AnsiString   __fastcall TDataModule2::GetID(const AnsiString &aSql)
{
      AnsiString  result("0");
      try{
          try
             {
              qryTemp->Active=false;
              qryTemp->SQL->Clear();
              qryTemp->SQL->Add(aSql);
              qryTemp->Active=true;
              qryTemp->First();
              if(!qryTemp->Eof)
              {
                result=qryTemp->Fields->Fields[0]->AsString;
              }
            }
            catch(...)
            {
              throw Exception("访问数据库出错，请检查数据库连接情况！");
            }
         }
      __finally
      {
        qryTemp->Active=false;
        qryTemp->SQL->Clear();
      }
      return result;
}
//------------------
AnsiString   __fastcall TDataModule2::ExecSQL(const AnsiString &aSql){
      AnsiString  result("0");
      try{
          try
             {
              qryTemp->Active=false;
              qryTemp->SQL->Clear();
              qryTemp->SQL->Add(aSql);
              qryTemp->ExecSQL();
            }
            catch(...)
            {
              throw Exception("访问数据库出错，请检查数据库连接情况！");
            }
         }
      __finally
      {
        qryTemp->Active=false;
        qryTemp->SQL->Clear();
      }
      return result;
}
多线程部分：
主程序：
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit1.h"
#include "Unit3.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "cxContainer"
#pragma link "cxControls"
#pragma link "cxEdit"
#pragma link "cxMaskEdit"
#pragma link "cxSpinEdit"
#pragma link "cxTextEdit"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
    : TForm(Owner)
{
qu1 = new TQueue();
m2 = new Threadm2(true,0,qu1);
//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿

}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
// 清除已有任务线程
while(qu1->AtLeast(1)){
    ThreadTest * tt = (ThreadTest *) qu1->Pop();
    tt->Terminate();
    tt->Resume();
}
// 创建任务线程
for (int i = 0;i < cxSpinEdit1->Value; i++){
    ThreadTest * tt = new  ThreadTest(true,qu1);
    tt->iNo = i;
    qu1->Push(tt);
}
m2->Resume();
}
//---------------------------------------------------------------------------

void __fastcall TForm1::FormClose(TObject *Sender, TCloseAction &Action)
{

while(qu1->AtLeast(1)){
    ThreadTest * tt = (ThreadTest *) qu1->Pop();
    tt->Terminate();
    tt->Resume();
}
/*
for (int i = 0;i < cxSpinEdit1->Value; i++){
    if (qu1->AtLeast(1)) {
        ThreadTest * tt = (ThreadTest *)(qu1->Pop());
        tt->Resume();
    }
}
*/
delete qu1;
m2->Terminate();
m2->Resume();
//delete m2;
Sleep(100);
}
//---------------------------------------------------------------------------

主线程:
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit4.h"
#include "Unit3.h"
#include "Unit1.h"
#pragma package(smart_init)

__fastcall Threadm2::Threadm2(bool CreateSuspended)
    : TThread(CreateSuspended)
{
//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿

}
//---------------------------------------------------------------------------

__fastcall Threadm2::Threadm2(bool CreateSuspended,int count,TQueue * qu11)
    : TThread(CreateSuspended)
{
    iThreadCount = count;
    qu1 = qu11;
}
//---------------------------------------------------------------------------
void __fastcall Threadm2::Execute()
{
    //---- Place thread code here ----
while(!this->Terminated) {

    Form1->RichEdit1->Lines->Clear();
    int i = 0;
    int j = 0;
    bool flag = true;
    int is = GetTickCount();
    //操作开始,启动事务
    for (int i = 0;i < Form1->cxSpinEdit1->Value; i++){
        ThreadTest * tt = (ThreadTest *)(qu1->Pop());
        tt->dd->OraSession1->StartTransaction();
        qu1->Push(tt);
        //tt->Terminate();
    }
    while(flag){
        j++;
        if (qu1->AtLeast(1)){
            if ( i > (Form1->cxSpinEdit2->Value - 1) ){
              //ShowMessage(IntToStr(GetTickCount() -is));
              if( qu1->Count() == Form1->cxSpinEdit1->Value  ){
                flag = false;
                //操作结束，线程回收完毕，提交数据
                for (int i = 0;i < Form1->cxSpinEdit1->Value; i++){
                    ThreadTest * tt = (ThreadTest *)(qu1->Pop());
                    tt->dd->OraSession1->Commit();
                    qu1->Push(tt);
                    //tt->Terminate();
                }
              }
            }else{
              ThreadTest * tt = (ThreadTest *)(qu1->Pop());
  //            Form1->RichEdit1->Lines->Add(IntToStr(i) + " in Main Accept: iNo=" + IntToStr(tt->iNo) );
              tt->Resume();
              i++;
            }
        }else{
//            Form1->RichEdit1->Lines->Add(IntToStr(j) + "  jjj : " + IntToStr( qu1->Count()));
            Sleep(10);
        }
    }
    int ie = GetTickCount();
    Form1->Edit1->Text = IntToStr(ie- is)   ;
//    ShowMessage(IntToStr(ie -is));
//    Application->ProcessMessages() ;
//    iii = ie -is;
//    Synchronize(aaa);
    Suspend();
}
}
//---------------------------------------------------------------------------
void __fastcall Threadm2::aaa(void)

{
  ShowMessage(IntToStr(iii));
}

任务线程：
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit3.h"

#include "Unit1.h"
#pragma package(smart_init)

__fastcall ThreadTest::ThreadTest(bool CreateSuspended)
    : TThread(CreateSuspended)
{
//本人原创runnerrunning@163.com转载请保留本信息
//http://blog.csdn.net/runnerrunning
//大家看了别忘了点右边的广告阿支持一下runnerrunning@163.com阿

}
//---------------------------------------------------------------------------
__fastcall ThreadTest::ThreadTest(bool CreateSuspended,TQueue * qu11)
    : TThread(CreateSuspended)
{
    qu1 = qu11;
    dd = new TDataModule2(Application);
    dd->OraSession1->Connected = true;
}
//---------------------------------------------------------------------------
void __fastcall ThreadTest::Execute()
{
    //---- Place thread code here ----
    int xx=0;
    while(!Terminated){
        try{
          try{
          //xx=dd->GetID("select s_Test1.Nextval from dual ").ToInt();
          dd->ExecSQL("insert into test1  (pk,nm,tt)values  (s_Test1.Nextval,'nmmmmm',sysdate) ");
      //    Form1->RichEdit1->Lines->Add("in Task over iNo=" + IntToStr(this->iNo) + "  " + IntToStr( qu1->Count()));
          }catch(...){
              //异常处理
          }
        }__finally{
          qu1->Push(this) ;
          Suspend();
        }
    }
    dd->OraSession1->Connected = false;
    delete dd;
}
//---------------------------------------------------------------------------