用线程实现计时器_用线程计时查询

用线程实现计时器

介绍 (Introduction)

我在Delphi主题区域中看到了许多问题，这些问题是需要或建议进行线程查询的。 我知道遇到了类似的需求。

This article will address some of the concepts when dealing with a multithreaded delphi database application.

本文将介绍处理多线程delphi数据库应用程序时的一些概念。

Shared resource protection

共享资源保护

Query connection from thread

从线程查询连接

Displaying thread results

显示线程结果

Sketching the problem

勾画问题

Our only remaining oracle 9 production database on a 7 years old server repeatedly lost disk access.  The oracle db has been due for replacement for a few years, but vendor issues have prevented this. A other server was setup and I moved the db.  Off course the hardware wasn't exactly the same and soon people complained about performance.  

我们在7年历史的服务器上仅存的oracle 9生产数据库反复丢失了磁盘访问权限。 Oracle数据库已被替换了几年，但是供应商的问题阻止了这种情况。 设置了另一台服务器，然后我移动了数据库。 当然，硬件并不完全相同，很快人们就抱怨性能。

This was odd, taken into account the more  recent hardware.

考虑到最新的硬件，这很奇怪。

I won't go into detail about the oracle performance tuning.  After some searching we found some differences but most eye-catching was disk sorting.  A unsorted query took 500ms, but sorted it took 8 seconds.  Playing around with sort_area_size fixed this, but after some time other connection problems occurred.

我不会详细介绍oracle性能调整。 经过一些搜索，我们发现了一些差异，但最引人注目的是磁盘排序。 未排序的查询花费了500毫秒，但排序却花费了8秒。 使用sort_area_size进行修复可以解决此问题，但是一段时间后出现了其他连接问题。

I decided to play around with some database parameters to find out what the best settings would be.  I needed something to see impact on query execution time when changing parameters.  I'm not a guru at oracle performance tuning, so i setup a test system to play around with.

我决定使用一些数据库参数来找出最佳设置。 我需要一些东西来查看更改参数时对查询执行时间的影响。 我不是Oracle性能调优专家，所以我设置了一个测试系统来试用。

Specs for the Delphi test application

Delphi测试应用程序的规格

I wanted a app which would repeatedly time the execution of a query, catching and ignoring any errors and be able to connect to any of my oracle databases.

我想要一个可以重复计时查询执行时间，捕获并忽略任何错误并能够连接到我的任何Oracle数据库的应用程序。

I also wanted to optionally save test results.

我还想选择保存测试结果。

http://www.devart.com/odac/ http://www.devart.com/odac/

Creating the main form

创建主窗体

The goal for this app is to highlight the threading approach to this problem.  So the design for the main form must be kept simple to reduce overhead.

该应用程序的目标是突出解决此问题的线程方法。 因此，主要表单的设计必须保持简单以减少开销。

Based on the specs i needed following:

基于规格，我需要以下几点：

entry for a database connection

数据库连接条目

entry for a query

查询条目

display for query execution times

显示查询执行时间

option to start/stop a thread

启动/停止线程的选项

display for saved resultsets

显示保存的结果集

I used a listbox for the timing results, a memo for query and saved results and edit for the db connection. Some panels, splitters and labels were added for the look.

我使用了一个列表框作为计时结果，使用了一个备忘录来查询和保存结果，并为数据库连接进行编辑。 一些面板，分配器和标签被添加到外观。

Code for starting a thread

启动线程的代码

The "add thread" button should start a thread with a given query on a given db.

“添加线程”按钮应在给定数据库上以给定查询启动线程。

procedure TfrmQueryTiming.btnAddThreadClick(Sender: TObject);
var dbconn: string;
begin
  // cbDB is combobox for db connections, save all unique entries
  dbconn := cbDB.text;
  if cbDB.Items.IndexOf(dbconn) = -1 then
    cbDB.Items.Add(dbconn);
  // Start thread on database with query text
  AddThread(dbconn, memQuery.Text);
end;

The structure for the results

结果的结构

I need a structure to save the timing of the queries.  I don't know how many threads will be running simultaneously and it will never be a fixed number.  The structure needs to be dynamic.  Expanding together with the number of threads being created.  A descendant of TCollection fits these requirements. The timing will be done inside a thread and saved to this collection. For protecting access to the array i will use a TCriticalSection.  

我需要一种结构来保存查询的时间。 我不知道有多少个线程将同时运行，并且永远不会是一个固定的数目。 结构必须是动态的。 与正在创建的线程数一起扩展。 TCollection的后代符合这些要求。 计时将在线程内完成并保存到此集合中。 为了保护对阵列的访问，我将使用TCriticalSection。

In multithreaded applications it is vital to protect shared resources against simultaneous changes.  

在多线程应用程序中，保护共享资源免于同时更改至关重要。

I put the shared resources into a separate unit uResources.  This not a must but allows very tight control to the resources.

我将共享资源放入一个单独的uResources单元中。 这不是必须的，但是可以非常严格地控制资源。

共享资源 (The shared resource)

用于保存查询时间的结构是TCollectionItem后代。 可以将其添加到TCollection后代中。 这允许动态添加或删除项目。

type
  // item for holding results
  TQueryTime = class(TCollectionItem)
  public
    // ID to identify the item
    ThreadId: Integer;
    // Time for the query to open
    ExecutionTime: TDateTime;
    // Hold errors or other messages
    Msg: string;
    // Database connection user/pass@db_alias
    DBConn: string;
  end;

  // Structure for holding multiple item results
  TQueryTimes = class(TCollection)
  private
    // Return timing item for specific thread
    function GetQueryItem(ThreadId: integer): TQueryTime;
  public
    // Save timing info for a thread
    procedure ReportQueryTime(aThreadId: Integer; aExecutionTime: TDateTime; aMsg, aDBConn: string);
    // Timing item for thread
    property QueryItem[ThreadId: integer]: TQueryTime read GetQueryItem;
  end;

If i would want to monitor different queries, i would have to indicate which query the result is for too.  Here I'm not going to do that as I'm only interested in the same query on different databases.

如果我想监视不同的查询，我也必须指出结果也是针对哪个查询。 这里我不打算这样做，因为我只对不同数据库上的同一查询感兴趣。

The Msg: string is for holding any errors or other info

Msg：字符串用于保存任何错误或其他信息

保护共享资源 (Protecting the shared resource)

将数组变量放在单元的实现部分中会使其他单元无法访问。 一个函数将提供对数组变量的访问。

// Return threadlocked variable for results
function LockQueryTimes: TQueryTimes;

// Unlock variable for results
procedure UnlockQueryTimes;

implementation

uses SysUtils, SyncObjs;

var
  // varaible for thread results
  mQueryTimes: TQueryTimes;
  // protecttion vor thread results variable
  mCSTimes: TCriticalSection;

// Return threadlocked variable for results
function LockQueryTimes: TQueryTimes;
begin
  // Be ware of this when using the TQueryTimes
  // A lock may succeed but it doesn't