Solved

For one of you guys in the top 10

Posted on 2002-06-26
16
202 Views
Last Modified: 2010-04-04
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
Comment
Question by:YodaMage
16 Comments
 
LVL 8

Expert Comment

by:Cesario
ID: 7111580
reading ( c_lababidi@hotmail.com)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7111913
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
 
LVL 4

Author Comment

by:YodaMage
ID: 7111927
Sent you both the code. Interbase 6.0.1.6, 2000 Network, TCP/IP...yadda yada yada....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:YodaMage
ID: 7116928
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7117293
what is your question?
0
 
LVL 4

Author Comment

by:YodaMage
ID: 7121705
"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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7144343
did you tried my hint above?

0
 
LVL 1

Expert Comment

by:jetball
ID: 7144858
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
 
LVL 4

Author Comment

by:YodaMage
ID: 7146077
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
 
LVL 4

Author Comment

by:YodaMage
ID: 7146080
Kretz- Problem is, the entire structure of the where clause changes, so passing params in a standardized SQL statement is not possible.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7146122
? 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
 
LVL 4

Author Comment

by:YodaMage
ID: 7146206
How would you handle " + GetDepts + " as a param?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7146284
ooops :-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7146296
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
 
LVL 4

Author Comment

by:YodaMage
ID: 7197202
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 7197248
Points refunded and placed in PAQ

Computer101
E-E Moderator
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Reconfigure Delphi Install? 2 66
Base1 Encode/Decode 3 90
Dev express lookupcombo 3 47
How to make Sign in, using Clientdataset? 1 33
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question