• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 877
  • Last Modified:

Select from remote client to database

Hello Guys,
I have a server with MSSQL 2005 Express and with my app. I save data every one sec from 5 rs232 using 5 comports. As you can imagine the number of records is extremly high. I`ve got also a remote client which I use to create reports by remote. The problem is that if I use select statement to filtrate the data let`s say from one week  the app hangs and I can`t do anything. If I filtrate data from one day it`s rather ok.
I have some questions:
1. Is there any way to speed up filtration.
2. What should I do to avoid app`s hang
3. I`m sure I`ve made some misteakes so if you can just point it.
Here is code of my remote app select statement<- I need the result to make a graph

procedure TForm1.Button3Click(Sender: TObject);
var
czas,czas1,temp1 : String;
temp : Double;
d1,d2,c1,c2 : String;
 
begin
  DBGrid1.DataSource:=DataSource2;
  czas := TimeToStr(DateTimePicker3.Time);
  czas1 := TimeToStr(DateTimePicker4.Time);
  raportodczas:=czas;
  raportdoczas:=czas1;
  raportoddata:= DateToStr(DateTimePicker1.Date);
  raportdodata:= DateToStr(DateTimePicker2.Date);
  if ComboBox1.Text = 'Wybierz wag...' then
      ComboBox1.Text := '2-8';
  if ComboBox2.Text ='Waga od...' then
      ComboBox2.Text:='-1000';
  if ComboBox3.Text ='Waga do...' then
      ComboBox3.Text:='100000';
  d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+'00:00:00';
  d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+'00:00:00';
  c1:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
  c2:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);
  waga_nr:= ComboBox1.Text;
     with ADOQuery1, SQL do
      begin
        Close;
        Clear;
        Add('SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) ');
        Parameters.ParamByName('masa_od').Value := StrToFloat(ComboBox2.Text);
        Parameters.ParamByName('masa_do').Value := StrToFloat(ComboBox3.Text);
        Parameters.ParamByName('waga').Value := ComboBox1.Text;
        Parameters.ParamByName('data_od').Value := d1;
        Parameters.ParamByName('data_do').Value := d2;
        Parameters.ParamByName('czas_od').Value := c1;
        Parameters.ParamByName('czas_do').Value := c2;
        Open;
      end;
end;

Open in new window

0
Vaalar
Asked:
Vaalar
  • 7
  • 4
  • 2
  • +2
1 Solution
 
JohnjcesCommented:
One of the things to consider is your database cursor's location.

If you have both your connection or dataset set for a local cursor, then all data is going to want to be dumped to your lcoal PC and that can cause a hang or freezeup.

Check your cursor locations and try your query with server connection and your dataset/query set for a server side cursor.

JOhn
0
 
mikelittlewoodCommented:
I notice you are not explicitly setting the data types of the parameters, that might help.

Also not sure but maybe using BETWEEN might help too
Add('SELECT ID, WAGA, MASA, RATE, DATA, CZAS, RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA BETWEEN :masa_od AND MASA <= :masa_do AND DATA BETWEEN Convert(DateTime, :data_od) AND Convert(DateTime, :data_do) '+ 'AND CZAS BETWEEN Convert(DateTime, :czas_od) AND Convert(DateTime, :czas_do) ');
0
 
mikelittlewoodCommented:
I've also just noticed your doing a lot of converting work of your dates before setting them as parameters and also converting in the query, thats a bit overkill

  d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+'00:00:00';
  d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+'00:00:00';
  c1:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
  c2:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);

make d1 a TDate and just truncate it, this will remove the time
d1 :;= trunc( dateTimePicker1.dateTime)

Also update the parameter info
Parameters.ParamByName('data_od').dataType = ftDateTime

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
VaalarAuthor Commented:
Ok Guys,
Thx for suggestions. I`ll test your solutions and inform about results.
0
 
aikimarkCommented:
1. move the records to a different (archive/warehouse) table on a regular basis, eliminating hard drive contention.
2. keep the number of records for your live rs232 data relatively small and optimized for inserts.  This should be a different disk drive than the archive table.  (not sure if SQL Server Express supports this)
3. To speed up the reporting, you will need indexes.
4. Force your parameters to be expected types, avoiding the CONVERT() functions.
5. Can we safely assume that the contents of the combobox1.text is numeric?
6. Try the Between clause to reduce the number of conditions.
7. Convert this SQL into a stored procedure with the same parameters, reducint the dynamic SQL parsing, validating, optimizing, etc.

Example in snippet has been reformatted for readability.
Add('SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT ' +
'FROM PRODUKCJA ' +
'WHERE WAGA = :waga ' +
'AND (MASA Between :masa_od AND :masa_do) ' +
'AND (DATA Between :data_od AND :data_do) ' +
'AND (CZAS Between :czas_od AND :czas_do) ');
        

Open in new window

0
 
Geert GruwezOracle dbaCommented:
2: app hangs ?
get the data in a thread using a different connection name
0
 
aikimarkCommented:
Geert

It is possible that the database is taking so long to return the data that it only appears that the app is hanging.  I agree that a separate thread will allow the user to continue with the main application's functions, but this doesn't solve the problem of getting the data.
0
 
Geert GruwezOracle dbaCommented:
this is my idea from reading your posts of what you want:
This is the backend:
Reading data from 5 commports
Saving this data
Making this data available through a database

This is the frontend:
Grid with filter showing the data
You seem to have a lot of records:
Does the user want to see all these records ?
Or can you use paging ?
If you can use paging, you could show 100 lines and then next page ...



0
 
VaalarAuthor Commented:
Hi again,
Geert I can use paging but if i want to create the graph with dbchart i need to have a whole data in one piece otherwise the graph wont show the real data
0
 
Geert GruwezOracle dbaCommented:
i don't quite follow your last comment ...
you can use paging, but if you do, it doesn't show the corrrect graph ???
and what do you mean with real data ?

is there a difference between real data and the graph data ?
0
 
Geert GruwezOracle dbaCommented:
am i guessing correctly that you are looking for the max and min values for the whole dataset
and using this to calibrate your graph ?

if so, do a query for these max and min values first, store these values in variables and
then starting the paging of the data to the graph
0
 
VaalarAuthor Commented:
ok i think i understand - you mean that the app hangs is cause by displaying the results, not becouse of select statement. If i don`t show the data in dbgrid everything should be ok.
I thought that I should select data from database by packages with 100 records and after this another 100 and so on...
In connection to your last comment - I have to display the production from specific range of time ( whole or at least every 60 record - it would show me production with 1 min breaks).
Secound case that I need to count the highest values becouse these are the numbers which show what was the production in the specified time but I`m doing it in another query.
0
 
Geert GruwezOracle dbaCommented:
application hang is usually caused by

a query taking time to return ( > 5 sec)
  --> this means running the query on the db server and
        copying the data over the connection to the dataset memory
a component (like DBGrid) filling up it's contents
  --> this means copying the dataset memory to the grid records
calibration or (max and min)
  --> finding calibration values in a lot of records is usually slower than launching a query with max, min and group by

solutions to problem 1:
lessen the query execution time by:
 - enhancing the query to perform faster (optimizing the execution plan)
 - getting less records
 - hacking the query into chunks and getting a number records at a time (like 100 records each time)
     this involves some coding to handle the different chucks each time

solutions to problem 2:
- Mike Lischke describes this problem very nicely
  -> only copy from the dataset what you see on the screen
- or in short use better components like TMS grid or DevExpress grid
- copy only the records you want to see from the dataset
    (this last is not allways obvious as the screen algorithm may be difficult)
- only show data in 1 way
    (sometimes there is a grid, a chart, a treeview which can show different views of the data
      -> only fill up the visible components)


0
 
Geert GruwezOracle dbaCommented:
my solution for reports was to start a form and let that form start a thread to get the data
(involved some classes and threading)

i couldn't post all units :)

but this should give a general idea about opening a secondary form
and let the form wait for the data.
When the data arrives from the query, the form grid is loaded and
the taskbar button is flashed 3 times to let the user know it is loaded

  TBackgroundQryFinished = procedure (Sender: TObject; ResultQry: TDacQuery; ErrorMessage: string) of   object;
 
  TBackGroundQuery = class(TThread)
  private
    fErrMessage: string;
    fQry: TDacQuery;
    fConnectionName: string;
    fprocFinished: TBackgroundQryFinished;
    class function NewBackGroundThreadId: integer;
    procedure Finished;
  protected
    procedure Execute; override;
  public
    constructor Create(Qry: TDacQuery; procFinished: TBackgroundQryFinished; FreeAfterTerminate: Boolean = True);
    destructor Destroy; override;
  end;
 
var
  fBackgroundThreadId: Integer = 0;
 
{ TBackGroundQuery }
 
constructor TBackGroundQuery.Create(Qry: TDacQuery;
  procFinished: TBackgroundQryFinished; FreeAfterTerminate: Boolean);
var I: Integer;
  List: TStrings;
  aConnectionString: string;
begin
  inherited Create(True);
  fErrMessage := '';
  CoInitializeEx(nil, 0);
  fprocFinished := procFinished;
  FreeOnTerminate := FreeAfterTerminate;
 
  List := TStringList.Create;
  try
    List.CommaText := Qry.Connector.ConnectionString;
    List.Values['NOREGISTRY'] := '1';
    aConnectionString := List.CommaText;
  finally
    FreeAndNil(List);
  end;
 
  fConnectionName := Qry.Connector.ConnectionName + '_THREAD_' + IntToStr(NewBackGroundThreadId);
 
  fQry := TDacQuery.Create(nil, fConnectionName, aConnectionString, False);
  fQry.SQL := Qry.SQL;
  for I := 0 to fQry.ParamCount-1 do
  begin
    fQry.Params.Items[I].DataType := Qry.Params.Items[I].DataType;
    case fQry.Params.Items[I].DataType of
      ftDate: fQry.Params.Items[I].AsDate := Qry.Params.Items[I].AsDate;
      ftDateTime: fQry.Params.Items[I].AsDateTime := Qry.Params.Items[I].AsDateTime;
      ftTime: fQry.Params.Items[I].AsTime := Qry.Params.Items[I].AsTime;
    else
      fQry.Params.Items[I].Value := Qry.Params.Items[I].Value;
    end;
  end;
end;
 
destructor TBackGroundQuery.Destroy;
begin
  FreeAndNil(fQry);
  CoUninitialize;
  try
    Connections.FindConnection(fConnectionName).Free;
  except
  end;
  inherited Destroy;
end;
 
procedure TBackGroundQuery.Execute;
begin
  try
    fQry.Open;
  except
    on E: Exception do // eat errors;
      fErrMessage := E.Message;
  end;
  Synchronize(Finished);
end;
 
procedure TBackGroundQuery.Finished;
begin
  if Assigned(fprocFinished) then
    fProcFinished(nil, fQry, fErrMessage);
end;
 
class function TBackGroundQuery.NewBackGroundThreadId: integer;
begin
  Inc(fBackgroundThreadId);
  Result := fBackgroundThreadId;
end;
 
unit UntPreview;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, StdCtrls, Buttons, ExtCtrls,
  cxGridCustomPopupMenu, cxGridPopupMenu, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, cxTextEdit, DBClient, MemDS, VirtualTable,
  Provider, cxGridExportLink, UntDacRoot, Menus;
 
type
  TfrmPreview = class(TForm)
    viewData: TcxGridDBTableView;
    levelSelection: TcxGridLevel;
    gridData: TcxGrid;
    popGrid: TcxGridPopupMenu;
    dsGrid: TDataSource;
    pnlOptions: TPanel;
    btnExportExcel: TBitBtn;
    btnClose: TBitBtn;
    vtData: TVirtualTable;
    viewSelection: TcxGridTableView;
    levelData: TcxGridLevel;
    colCriterium: TcxGridColumn;
    colValue: TcxGridColumn;
    pnlWaiting: TPanel;
    btnCancel: TButton;
    lblBackgroundData: TLabel;
    pnlErrorMessage: TPanel;
    lblErrMessage: TLabel;
    popGrafiek: TPopupMenu;
    Series1: TMenuItem;
    Datagroups1: TMenuItem;
    sdExport: TSaveDialog;
    btnExportHtml: TBitBtn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnCloseClick(Sender: TObject);
    procedure btnExportExcelClick(Sender: TObject);
  private
    fBThread: TThread;
    fBThreadTerminated: Boolean;
    procedure LoadData(ds: TDataset);
    procedure QueryFinished(Sender: TObject; aQry: TDacQuery; ErrorMessage: string);
    procedure FlashTaskbarButton;
  protected
    procedure CreateParams(var Params: TCreateParams); override;
  public
    constructor Create(AOwner: TComponent; ds: TDataset; aCaption, Comments: string); reintroduce;
    constructor CreateBackground(AOwner: TComponent; qry: TDacQuery; aCaption, Comments: string); virtual;
  end;
 
var
  frmPreview: TfrmPreview;
 
procedure ShowPreview(aDataset: TDataset; aCaption, Comments: string);
procedure ShowPreviewFromBackGround(aQry: TDacQuery; aCaption, Comments: string);
 
implementation
 
{$R *.dfm}
 
procedure ShowPreview(aDataset: TDataset; aCaption, Comments: string);
var frm: TfrmPreview;
begin
  frm := TfrmPreview.Create(nil, aDataset, aCaption, Comments);
  frm.Show;
  frm.Update;
end;
 
procedure ShowPreviewFromBackGround(aQry: TDacQuery; aCaption, Comments: string);
var frm: TfrmPreview;
begin
  frm := TfrmPreview.CreateBackground(nil, AQry, aCaption, Comments);
  frm.Show;
  frm.Update;
end;
 
{ TfrmPreview }
 
constructor TfrmPreview.Create(AOwner: TComponent; ds: TDataset; aCaption, Comments: String);
var I, r: Integer;
  List: TStrings;
begin
  inherited Create(AOwner);
  fBThread := nil;
  fBThreadTerminated := False;
  Caption := aCaption;
  if Comments <> '' then
  begin
    List := TStringList.Create;
    try
      List.Text := Comments;
      with viewSelection.DataController do
      begin
        BeginUpdate;
        try
          for I := 0 to List.Count-1 do
            List[I] := StringReplace(List[I], ' - ', '=', []);
          for I := 0 to List.Count-1 do
          begin
            r := AppendRecord;
            Values[r, colCriterium.Index] := List.Names[I];
            Values[r, colValue.Index] := List.ValueFromIndex[I];
          end;
        finally
          EndUpdate;
        end;
      end;
    finally
      FreeAndNil(List);
    end;
  end else
    gridData.RootLevelOptions.DetailTabsPosition := dtpNone;
  LoadData(ds);
end;
 
constructor TfrmPreview.CreateBackground(AOwner: TComponent; qry: TDacQuery; aCaption, Comments: string);
begin
  Create(AOwner, nil, aCaption, Comments);
  pnlWaiting.Visible := True;
  fBThread := TBackgroundQuery.Create(qry, QueryFinished);
  fBThread.Resume;
end;
 
procedure TfrmPreview.LoadData(ds: TDataset);
var I: Integer;
begin
  viewData.BeginUpdate;
  try
    if Assigned(ds) then
    begin
      vtData.Assign(ds);
      vtData.Active := True;
      viewData.DataController.CreateAllItems;
      for I := 0 to viewData.ColumnCount-1 do
      begin
        viewData.Columns[I].DataBinding.ValueTypeClass := TcxStringValueType;
        viewData.Columns[I].PropertiesClass := TcxTextEditProperties;
      end;
    end else
      viewData.ClearItems;
  finally
    viewData.EndUpdate;
  end;
  viewData.BeginUpdate;
  try
    viewData.ApplyBestFit;
  finally
    viewData.EndUpdate;
  end;
  FlashTaskbarButton;
end;
 
procedure TfrmPreview.FlashTaskbarButton;
var
  FWinfo: FlashWInfo;
begin
  FWinfo.cbSize := 20;
  FWinfo.hwnd := Handle; // Handle of Window to flash
  FWinfo.dwflags := FLASHW_ALL;
  FWinfo.ucount := 3; // number of times to flash
  FWinfo.dwtimeout := 0; // speed in ms, 0 default blink cursor rate
  FlashWindowEx(FWinfo); // make it flash!
end;
 
procedure TfrmPreview.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  if Assigned(fBThread) then
  begin
    fBThreadTerminated := True;
    TerminateThread(fBThread.Handle, 0);
  end;
  Action := caFree;
end;
 
procedure TfrmPreview.btnCloseClick(Sender: TObject);
begin
  Close;
end;
 
procedure TfrmPreview.btnExportExcelClick(Sender: TObject);
var aFileName: string;
begin
  aFileName := IncludeTrailingPathDelimiter(VarToStr(Dictionary.Read('report save path',
    ExtractFilePath(application.ExeName)+'Sources\', 'parameters'))) + Caption;
  if Sender = btnExportHtml then
  begin
    sdExport.DefaultExt := '.htm';
    sdExport.Filter := 'Htm files|*.htm';
    sdExport.FilterIndex := 1;
  end;
  aFileName := ChangeFileExt(aFileName, sdExport.DefaultExt);
  sdExport.FileName := aFileName;
  if sdExport.Execute then
  begin
    aFileName := sdExport.FileName;
    if SameText('.xls', ExtractFileExt(aFileName)) then
      ExportGridToExcel(aFileName, gridData)
    else if SameText('.htm', ExtractFileExt(aFileName)) then
      ExportGridToHTML(aFileName, gridData, True, True, 'htm')
    else if SameText('.xml', ExtractFileExt(aFileName)) then
      ExportGridToXML(aFileName, gridData)
    else if SameText('.txt', ExtractFileExt(aFileName)) then
      ExportGridToText(aFileName, gridData);
  end;
end;
 
procedure TfrmPreview.QueryFinished(Sender: TObject; aQry: TDacQuery; ErrorMessage: string);
begin
  pnlWaiting.Visible := False;
  if Assigned(aQry) and not fBThreadTerminated then
  begin
    if ErrorMessage <> '' then
    begin
      pnlErrorMessage.Visible := True;
      lblErrMessage.Caption := ErrorMessage;
    end else
      LoadData(aQry.Dataset);
  end;
end;
 
procedure TfrmPreview.CreateParams(var Params: TCreateParams);
begin
  inherited CreateParams(Params);
  Params.ExStyle := Params.ExStyle or WS_EX_APPWINDOW;
end;
 
end.

Open in new window

0
 
Geert GruwezOracle dbaCommented:
btw TDacQuery is my UniDav solution
it's basically a combination of 2 components
namely TOraSession and TOraQuery

but if you got the UniDac components you could adapt it to work more smoothly ...
0
 
VaalarAuthor Commented:
Geert rulez
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now