[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

For one of you guys in the top 10

I've got a form that does exactly what I need but I'd like to email some source to you to see if you see anything I'm missing as far as optimizing. I have some dynamic queries that take place as well as some run time indexing of a few CDS. I also have a few calculations that then determine both contents of a grid as well as color of rows in the grid which take place at startup.

I'll start with 190 points and up it for better input.
0
YodaMage
Asked:
YodaMage
1 Solution
 
CesarioCommented:
reading ( c_lababidi@hotmail.com)
0
 
kretzschmarCommented:
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 ;-)
0
 
YodaMageAuthor Commented:
Sent you both the code. Interbase 6.0.1.6, 2000 Network, TCP/IP...yadda yada yada....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
YodaMageAuthor Commented:
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;
*****************************************************************************************************************************************
0
 
kretzschmarCommented:
what is your question?
0
 
YodaMageAuthor Commented:
"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.
0
 
kretzschmarCommented:
did you tried my hint above?

0
 
jetballCommented:
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...
0
 
YodaMageAuthor Commented:
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.
0
 
YodaMageAuthor Commented:
Kretz- Problem is, the entire structure of the where clause changes, so passing params in a standardized SQL statement is not possible.
0
 
kretzschmarCommented:
? 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 ;-)
0
 
YodaMageAuthor Commented:
How would you handle " + GetDepts + " as a param?
0
 
kretzschmarCommented:
ooops :-)
0
 
kretzschmarCommented:
well, atleast,
maybe your problem is not your code,
maybe you must add some indexes on your database
and run the statistics there

meikl ;-)
0
 
YodaMageAuthor Commented:
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.


http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20321802
0
 
Computer101Commented:
Points refunded and placed in PAQ

Computer101
E-E Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now