For one of you guys in the top 10

reading ( c_lababidi@hotmail.com)

reading also,

first maybe hint
use parameters instead to build your sql-string allways new,
if there is a change only in a where-clause for some fields

depending on your database, will this raise the access performance on a new open of the query, because the statement must be not reparsed

reading further

meikl ;-)

Sent you both the code. Interbase 6.0.1.6, 2000 Network, TCP/IP...yadda yada yada....

Haven't heard anything....screw it, I post code here.

procedure TfrmTaskGrid.FormCreate(Sender: TObject);
begin
   PerformLocate := False;
   cbStatus.ItemIndex := 3;
   cbField.ItemIndex := 0;
   DetermineIndex;
   if dmGlobalData.cdsFormSec.Active then
   begin
      gb1.Visible := (dmGlobalData.cdsUser.FieldByName(gcACCESS).AsInteger > 6);
      btnAllTasks.Visible := (dmGlobalData.cdsUser.FieldByName(gcACCESS).AsInteger > 7);
      btnDelete.Visible := (dmGlobalData.cdsUser.FieldByName(gcACCESS).AsInteger > 7);
   end;
   PageControl1.ActivePageIndex := 0;
end;
*****************************************************************************************************************************************
procedure TfrmTaskGrid.DetermineIndex;
begin
   case cbField.ItemIndex of
      0 : begin
            cdscf.IndexName := 'loannum';
            ismain.SearchField := 'loanstr';
          end;
      1 : begin
            cdscf.IndexName := 'cinum';
            ismain.SearchField := 'courtndx';
          end;
      2 : begin
            cdscf.IndexName := 'fhlmc';
            ismain.SearchField := 'fhlmc_num';
          end;
      3 : begin
            cdscf.IndexName := 'fnma';
            ismain.SearchField := 'fannie_num';
          end;
      4 : begin
            cdscf.IndexName := 'addr';
            ismain.SearchField := 'addr1';
          end;
      5 : begin
         cdscf.IndexName := 'housenum';
            ismain.SearchField := 'addrnum';
        end;
   end;
end;
*****************************************************************************************************************************************
procedure TfrmTaskGrid.FormActivate(Sender: TObject);
begin
   if dmGlobalData.cdsUser.Active then
      MyTasks
   else
      taskgrid.Enabled := False;
   if not dmGlobalData.cdsCaseInfo.Active then
   begin
      frmMain.FileInfo1.Enabled := False;
      frmMain.Foreclosure1.Enabled := False;
      frmMain.Bankruptcy1.Enabled := False;
      frmMain.Notes1.Enabled := False;
      frmMain.Accounting1.Enabled := False;
   end;
   if (frmMain.MDIChildCount > 1) then
   begin
     frmMain.sbMain.Panels[1].Text := '';
     frmMain.sbMain.Panels[2].Text := '';
     frmMain.sbMain.Panels[3].Text := '';
   end;
   SqlByStatus;
   if ismain.Text <> '' then ismain.Clear;
end;
*****************************************************************************************************************************************
procedure TfrmTaskGrid.MyTasks;
begin
   if (dmGlobalData.cdsUser.FieldByName(gcACCESS).AsInteger < 7) then
   begin
      cdsTasks.Close;
      qryTasks.SQL.Clear;
      gFullText := 'select cf.casegen, cf.taskstep, cf.taskpriority, cf.taskassigned, cf.status, cf.taskdep, cf.loanstr, ' +
                'cf.taskdate, tl.taskgen, tl.taskname, tl.taskdept, tl.tasktype from casefile cf, tasklookup tl ' +
                'where (cf.taskstep = tl.taskgen) and (cf.status <> ' + QuotedStr('H') + ') and ' +
                '(cf.status <> ' + QuotedStr('C') + ') and (cf.deletedflag <> ' + QuotedStr(gcYES) + ') and (cf.taskassigned = ' +
                QuotedStr(dmGlobalData.cdsUser.FieldByName(gcUSER_GEN).AsString) + ') and (tl.tasktype <> ' + QuotedStr('C') + ') order by cf.taskpriority, cf.loanstr';
      qryTasks.SQL.Text := gFullText;
      cdsTasks.Open;
   end
   else //Supervisor
   begin
      cdsTasks.Close;
      qryTasks.SQL.Clear;
      gFullText := 'select cf.casegen, cf.taskstep, cf.taskpriority, cf.taskassigned, cf.status, cf.taskdep, cf.loanstr, ' +
                   'cf.taskdate, tl.taskgen, tl.taskname, tl.taskdept, tl.tasktype from casefile cf, tasklookup tl ' +
                   'where (cf.taskstep = tl.taskgen) and (cf.deletedflag <> ' + QuotedStr(gcYES) + ') and ' +
                   '(cf.status <> ' + QuotedStr('H') + ') and (cf.status <> ' + QuotedStr('C') + ') and ' +
                   '(((tl.tasktype <> ' + QuotedStr('C') + ') and (taskassigned = ' + QuotedStr(dmGlobalData.cdsUser.FieldByName(gcUSER_GEN).AsString) +
                   ')) or ((tl.tasktype = ' + QuotedStr('C') + ') and ' + GetDepts + ')) order by cf.taskpriority, cf.loanstr';
      qryTasks.SQL.Text := gFulltext;
      cdsTasks.Open;
   end;
end;
*****************************************************************************************************************************************
procedure TfrmTaskGrid.SqlByStatus;
var
 SelText, SelAppend, FullText, FullText2 : String;
begin
   SelAppend := '(cf.DeletedFlag <> ' + QuotedStr(gcYES) + ')';
   SelText := '';
   case cbStatus.ItemIndex of
      0 : SelText := '((cf.status = ' + QuotedStr('A') + ') or ' +
                     '(cf.status = ' + QuotedStr('E') + ') or ' +
                     '(cf.status = ' + QuotedStr('S') + ')) and ';
      1 : SelText := '(cf.status = ' + QuotedStr('B') + ') and ';
      2 : SelText := '(cf.status = ' + QuotedStr('H') + ') and ';
      3 : SelText := '(cf.status <> ' + QuotedStr('C') + ') and ';
      5 : SelAppend := '(cf.DeletedFlag = ' + QuotedStr(gcYES) + ')';
   end;
 
   FullText := 'Select cf.loanstr, cf.courtndx, cf.fhlmc_num, cf.fannie_num, cf.status, cf.deletedflag, ' +
               'cf.casegen, p.addr1, p.addrnum, cf.status, p.casegen ' +
               'from casefile cf left outer join propinfo p on cf.casegen = p.casegen ' +
               'where ' + SelText + SelAppend;
   FullText2 := 'select m.sortname, m.casegen, m.deletedflag, cf.status, cf.loanstr from mortgagor m, ' +
                'casefile cf where (m.casegen = cf.casegen) and ' + SelText + SelAppend;
   cdsCF.Close;
   cdsMortgagor.Close;
   qryCF.SQL.Clear;
   qryMortgagor.SQL.Clear;
   qryMortgagor.SQL.Text := Fulltext2;
   qryCF.Sql.Text := Fulltext;
   cdsCF.Open;
   cdsMortgagor.Open;
end;
*****************************************************************************************************************************************
procedure TfrmTaskGrid.taskgridCalcCellColors(Sender: TObject;
  Field: TField; State: TGridDrawState; Highlight: Boolean; AFont: TFont;
  ABrush: TBrush);
begin
   case cdsTasks.FieldByName(gcTASK_PRIORITY).AsInteger of
      2 : ABrush.Color := clYellow;
      3 : ABrush.Color := clLime;
      4, 6 : ABrush.Color := clWhite;
      5 : ABrush.Color := clAqua;
   else
      ABrush.Color := clRed;
   end;
 
   if Highlight then
      AFont.Color := clBlue;
end;
*****************************************************************************************************************************************
function TfrmTaskGrid.GetDepts : string;
var
   depts : String;
   count : smallint;
   fname : String[2];
   test : boolean;
begin
   count := 1;
   depts := '';
   test := False;
   while (count < 10) do
   begin
      fname := 'D' + IntToStr(count);
      if (dmGlobalData.cdsUser.FieldByName(fname).AsString = gcYES) then
      begin
         if ((depts = '') and not test) then
            depts := '(cf.taskdep = ' + QuotedStr(IntToStr(count)) + ')'
         else if ((depts <> '') and not test) then
         begin
            depts := '(' + depts + ' or ' + '(cf.taskdep = ' + QuotedStr(IntToStr(count)) + ')';
            test := True;
         end
         else
            depts := depts + ' or (cf.taskdep = ' + QuotedStr(IntToStr(count)) + ')'
      end;
      Inc(count);
   end;
   if test then
      depts := depts + ')';
   if (depts = '') then
      depts := '(cf.taskdep = ''0'')';
   result := depts;
end;
*****************************************************************************************************************************************

what is your question?

"to see if you see anything I'm missing as far as optimizing."

Essentially startup of this form takes about 12 seconds on average from relatively small (less than 50,000 records) tables.

did you tried my hint above?

You need to put a few time debug statements around your code... Find out which path is the slowest and work on that part...

First of all, you need to run the SQL on the database and time it. It might be that the SQL you wrote took a good 10 seconds. if you SQL returns in 100 ms then it is OK... always look at the database to see if indexes has been setup properly...

Secondly, where is the database? is it on the same machine? is it on the same subnet? can the machine handle the database?

Thirdly, think of how you can migrate the SQL into a stored procedure... much faster...

The main lag appears to be in procedure TfrmTaskGrid.SqlByStatus, on:
  cdsCF.Open;
  cdsMortgagor.Open;

Though the SQL looks like:

Prepare time = 10ms
Execute time = 10ms
Avg fetch time = 0.53 ms
Current memory = 17,990,608
Max memory = 18,116,664
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 16,937

When run alone.

The database is on its' own server currently, and all machines involved have more than enough horsepower to handle this. (Server is a P4 1.6 machine running W2K, with 1 Gig of Ram. Station is a P3 933 W2K with 256 RAM).

I really don't think that moving dynamically created SQL used to generate user specific datasets to drive data aware components into a Stored Proc is feasible.

Kretz- Problem is, the entire structure of the where clause changes, so passing params in a standardized SQL statement is not possible.

? i don't agree with
>the entire structure of the where clause changes

because this looks static


'select cf.casegen, cf.taskstep, cf.taskpriority, cf.taskassigned, cf.status, cf.taskdep, cf.loanstr, ' +
                  'cf.taskdate, tl.taskgen, tl.taskname, tl.taskdept, tl.tasktype from casefile cf, tasklookup tl ' +
                  'where (cf.taskstep = tl.taskgen) and (cf.deletedflag <> ' + QuotedStr(gcYES) + ') and ' +
                  '(cf.status <> ' + QuotedStr('H') + ') and (cf.status <> ' + QuotedStr('C') + ') and ' +
                  '(((tl.tasktype <> ' + QuotedStr('C') + ') and (taskassigned = ' + QuotedStr(dmGlobalData.cdsUser.FieldByName(gcUSER_GEN).AsString) +
                  ')) or ((tl.tasktype = ' + QuotedStr('C') + ') and ' + GetDepts + ')) order by cf.taskpriority, cf.loanstr';
     
only one parameter is needed

>:taskassigned

meikl ;-)

How would you handle " + GetDepts + " as a param?

ooops :-)

well, atleast,
maybe your problem is not your code,
maybe you must add some indexes on your database
and run the statistics there

meikl ;-)

Gonna delete this for lack of an answer. kretzschmar, go answer the question below so I can through you a bunch of points for at least taking some time to look and make some suggestions.


https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20321802