Solved

For one of you guys in the top 10

Posted on 2002-06-26
16
196 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
Comment Utility
reading ( c_lababidi@hotmail.com)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
Sent you both the code. Interbase 6.0.1.6, 2000 Network, TCP/IP...yadda yada yada....
0
 
LVL 4

Author Comment

by:YodaMage
Comment Utility
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
Comment Utility
what is your question?
0
 
LVL 4

Author Comment

by:YodaMage
Comment Utility
"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
Comment Utility
did you tried my hint above?

0
 
LVL 1

Expert Comment

by:jetball
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Author Comment

by:YodaMage
Comment Utility
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
Comment Utility
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
Comment Utility
? 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
Comment Utility
How would you handle " + GetDepts + " as a param?
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
ooops :-)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
Points refunded and placed in PAQ

Computer101
E-E Moderator
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now