Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query/Display performance lag using TIBOQuery and TcxGridDBTableView

Posted on 2008-11-04
8
Medium Priority
?
1,110 Views
Last Modified: 2013-12-09
I have a form with a DevExpress cxGridDBTableView whose DataController is connected to a TIBOQuery. When returning large resultsets, >2000 records, we're noticing a significant time lag for the data to display in the grid. When the user clicks a button to refresh, we get a similar lag. This particular query is going against a single table in Firebird, a table that can theoretically grow to 10k records or more. Is there any way to speed up the process of returning and displaying the resultset?
The code that calls the query within the form...
************************************************************
procedure TfCatalogItemEntry.GetConstList( eQM : TeConstQM );
begin
  SvSetCursor( crHourglass );
  dmItems.iboqItemNcConstituents.DisableControls;
  case eQM of
    ecqIndiv:
      dmItems.QueryConstsIndividually;
    ecqAsEntered:
      dmItems.QueryConstsAsEntered;
  end; // case
  dmItems.iboqItemNcConstituents.EnableControls;
  RevertCursor;
  if dmItems.iboqItemNcConstituents.RecordCount > 100 then
   bLargeConstList := True;
  OldQM := eQM;
end;
************************************************
 
The code that builds the SQL and runs the query
*********************************************************
procedure TdmItems.QueryConstsAsEntered;
begin
  try
    with iboqItemNcConstituents do
    begin
      close;
      sql.clear;
      sql.add('select constituentID,Inactive_YN,Deceased_YN,CT_Code,CoupleID,Primary_PartnerID,Secondary_PartnerID,Display_Name from constituents');
      sql.add('where coupleid is NULL');
//      sql.add('(select constituentID from constituents where coupleID > 0)');
      sql.add('and Inactive_YN<>'+#39+'*'+#39+' ');
      sql.add('and Deceased_YN<>'+#39+'*'+#39+' ');
      sql.add('order by display_name');
      open;
    end;
  except
  on e:exception do
    messagedlg('An error occurred in procedure TfNonCatalogItemEntry.QueryConstsAsEntered.'+#13+
               'Error: '+e.Message,mterror,[mbok],0);
  end;//try
end;
 
procedure TdmItems.QueryConstsIndividually;
begin
  try
    with iboqItemNcConstituents do
    begin
      close;
      sql.clear;
      sql.add('select constituentID,Inactive_YN,Deceased_YN,CT_Code,CoupleID,Primary_PartnerID,Secondary_PartnerID,Display_Name from constituents');
      sql.add('where ct_code<>2');
      sql.add('and Inactive_YN<>'+#39+'*'+#39+' ');
      sql.add('order by display_name');
      open;
    end;
  except
  on e:exception do
    messagedlg('An error occurred in procedure TfNonCatalogItemEntry.QueryConstsIndividually.'+#13+
               'Error: '+e.Message,mterror,[mbok],0);
  end;//try
end;

Open in new window

0
Comment
Question by:OlGreyFox
  • 4
  • 3
8 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 22883694
please show the database table concerned with any indexes, constraints, etc
0
 

Author Comment

by:OlGreyFox
ID: 22886911
Here's the DDL for our CONSTITUENTS table
CREATE TABLE CONSTITUENTS
(
  CONSTITUENTID                         INTEGER         NOT NULL,
  CT_CODE                              SMALLINT,
  INACTIVE_YN                              CHAR(     1)  COLLATE NONE,
  DECEASED_YN                              CHAR(     1)  COLLATE NONE,
  COUPLEID                              INTEGER,
  PRIMARY_PARTNERID                     INTEGER,
  SECONDARY_PARTNERID                   INTEGER,
  DISPLAY_NAME                          VARCHAR(    50)  COLLATE NONE,
  BUSINESS_NAME                         VARCHAR(    50)  COLLATE NONE,
  LAST_NAME                             VARCHAR(    20)  COLLATE NONE,
  FIRST_NAME                            VARCHAR(    20)  COLLATE NONE,
  MI                                       CHAR(     1)  COLLATE NONE,
  COUPLES_TITLE                         VARCHAR(    35)  COLLATE NONE,
  EMPLOYERID                            INTEGER,
  DONORID                               VARCHAR(    30)  COLLATE NONE,
  TITLE                                 VARCHAR(    20)  COLLATE NONE,
  SUFFIX                                VARCHAR(    20)  COLLATE NONE,
  MAIL_YN                                  CHAR(     1)  COLLATE NONE,
  SORT_NAME                             VARCHAR(    50)  COLLATE NONE,
  SOUNDEX                               VARCHAR(    10)  COLLATE NONE,
  FORMAL_ADDRESS_NAME                   VARCHAR(    50)  COLLATE NONE,
  INFORMAL_ADDRESS_NAME                 VARCHAR(    50)  COLLATE NONE,
  FORMAL_SALUTATION                     VARCHAR(    50)  COLLATE NONE,
  INFORMAL_SALUTATION                   VARCHAR(    50)  COLLATE NONE,
  REFERRED_BY                           VARCHAR(    50)  COLLATE NONE,
  BUSINESS_PRIMARY_CONTACT              VARCHAR(    50)  COLLATE NONE,
  DELETED_YN                               CHAR(     1)  COLLATE NONE,
  DATE_DELETED                             DATE,
  DELETED_BY                            VARCHAR(    30)  COLLATE NONE,
  REASON_DELETED                        VARCHAR(    50)  COLLATE NONE,
  AFFILIATION                           VARCHAR(    30)  COLLATE NONE,
  COMMENT                               VARCHAR(   200)  COLLATE NONE,
  USE_EMPLOYER_ADDRESS                  VARCHAR(     3)  COLLATE NONE,
  NOTES                                    BLOB SUB_TYPE 1 SEGMENT SIZE 80,
  JOB_TITLE                             VARCHAR(    40)  COLLATE NONE,
 PRIMARY KEY (CONSTITUENTID)
);
update rdb$relation_fields set rdb$description = 'AT6/58, added to table 4/13' where rdb$relation_name = 'CONSTITUENTS' and rdb$field_name = 'AFFILIATION';
CREATE ASC INDEX SORT_NAME ON CONSTITUENTS (SORT_NAME);
SET TERM  ^^ ;
CREATE TRIGGER CONSTITUENTS_INS FOR CONSTITUENTS ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
  new.constituentid =gen_id(gen_constituents , 1);
  new.inactive_yn = '';
  new.mail_yn = 'Y';
  new.deceased_yn = '';
  new.use_employer_address = 'N';
  new.deleted_yn = 'N';
  new.employerid = 0;
END
 ^^
SET TERM ;  ^^

Open in new window

0
 
LVL 19

Assisted Solution

by:NickUpson
NickUpson earned 1050 total points
ID: 22889941
it looks like you have no idexes on the fields in the where clause, unfortunately you don't seem to have any good candidates either unless "coupleid is NULL" is true for less than 10%, do you really need to retrieve 2000 rows in one go, could you use

"select first 100 ....
 order by ..."
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 19

Expert Comment

by:NickUpson
ID: 22892944
you could run the sql using the isql tool that comes with firebird, that would tell you how much time is firebird and how much delphi
0
 

Author Comment

by:OlGreyFox
ID: 22896632
Nick: I didn't know Firebird supported the "Select first 100..." construct. (I think I once encountered it working with Oracle some years back). Could you elaborate (to refresh my memory) the general "Select first ... select next..." scenario to refresh me? I got some work backlog to hit ... will be back at this Monday(???) Thanks...
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 1050 total points
ID: 22899690
select first x skip y field1, field2 from memory is the syntax, needs to be at least firebird 1.5

if your grid holds 20 rows, you just get 20 at a time, changing the numbers as the users scrolls
0
 
LVL 4

Assisted Solution

by:JDSkinner
JDSkinner earned 450 total points
ID: 22975492
Hi,
Try amending the indexing to include the fields mentioned in the two where clauses, >> coupleid + ct_code <<, that should improve the speed.
procedure TdmItems.QueryConstsAsEntered;
begin
  try
    with iboqItemNcConstituents do
    begin
      close;
      sql.clear;
      sql.add('select constituentID,Inactive_YN,Deceased_YN,CT_Code,CoupleID,Primary_PartnerID,Secondary_PartnerID,Display_Name from constituents');
      sql.add('where coupleid is NULL');
      //  sql.add('(select constituentID from constituents where coupleID > 0)');
      sql.add('and Inactive_YN<>'+#39+'*'+#39+' ');
      sql.add('and Deceased_YN<>'+#39+'*'+#39+' ');
      // LINE CHANGED: sql.add('order by display_name');
      // CHANGE TO ..............................
      sql.add('order by coupleid, display_name');
      // ........................................
      open;
    end;
  except
  on e:exception do
    messagedlg('An error occurred in procedure TfNonCatalogItemEntry.QueryConstsAsEntered.'+#13+
               'Error: '+e.Message,mterror,[mbok],0);
  end;//try
end;
 
procedure TdmItems.QueryConstsIndividually;
begin
  try
    with iboqItemNcConstituents do
    begin
      close;
      sql.clear;
      sql.add('select constituentID,Inactive_YN,Deceased_YN,CT_Code,CoupleID,Primary_PartnerID,Secondary_PartnerID,Display_Name from constituents');
      sql.add('where ct_code<>2');
      sql.add('and Inactive_YN<>'+#39+'*'+#39+' ');
      // LINE CHANGED: sql.add('order by display_name');
      // CHANGE TO .............................      
      sql.add('order by ct_code, display_name');
      // .......................................
      open;
    end;
  except
  on e:exception do
    messagedlg('An error occurred in procedure TfNonCatalogItemEntry.QueryConstsIndividually.'+#13+
               'Error: '+e.Message,mterror,[mbok],0);
  end;//try
end;

Open in new window

0
 

Author Closing Comment

by:OlGreyFox
ID: 31513374
I thank you for your answers, as they taught me some things (or reinforced some things I should have known), and guided me in my search for a solution. Subsequent testing and querying has shown me that the SQL wasn't my biggest problem; it was the LOADING mode of the DevEx grid control; i.e., the query was taking maybe 1/4 the total lag time, the loading into the control's buffers was taking the rest. Again, thanks for your time, consideration & help.
For those with further questions, I am using DevEx Express Quantum Grid Suite, v6.35 (which we still like for its power and configurability).  In the DevEx knowledge base, there are several items/articles on the loading performance issue and workarounds thereto.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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