How to optimize this query

[alisaif]

Hi All,

I have two tables:

1. hAccount.dbf ( Contains records from 01/01/2003 to 31/12/2016 }
2. Account.dbf ( Contains records from 01/01/2017 till todate )

I am running this query:

      Select hAccount.creference, hAccount.cdate, hAccount.ccode, Code.full_name,;
         hAccount.remarks, Code.chead, hAccount.debit, hAccount.credit;
         FROM ;
         village!hAccount ;
         INNER Join village!Code ;
         ON  hAccount.ccode = Code.ccode;
         WHERE Between(cdate,md1,md2) ;
         UNION ;
      Select Account.creference, Account.cdate, Account.ccode, Code.full_name,;
         Account.remarks, Code.chead, Account.debit, Account.credit;
         FROM ;
         village!Account ;
         INNER Join village!Code ;
         ON  Account.ccode = Code.ccode;
         WHERE Between(cdate,md1,md2) ;
         ORDER By cdate ;
         INTO Cursor TempAct Readwrite
      Index On ccode Tag ccode Additive

If the user is asking the records from 01/12/2016 to 31/10/2017. It is coming, but what about the opening balance (sum of debit and credit before {01/12/2016}).
Also, it is very very slow.

Please guide.

Thanks

Saif

 

[Olaf Doschke]

About the speed: Do you have any indexes on the two tables?
I stored data of 14 years in one dbf and unless you don't have a 2GB size problem, that works fast, when indexed on date columns, for example.
An index on cdate in both tables will optimize this query.

>What about the opening balance?

Well, what about it? I don't know your data, are you implicitly saying this would need to be computed from all data in haccount anyway - despite md1 an md2 lower and upper dates? Then that's what you have to do.

A database table is not an excel sheet, though, where you may split data into multiple sheets and have one row just having summary data or a carryover line to summary data of a previous sheet or can have such summary rows every 1000 data rows or such a thing. Even in cases that need no other data type, I'd not use a column (amount for example) of a table to hold the sum of previous records, because that makes the meaning of such a table field vague, some rows have a summary meaning, others are just current transaction value data. It could be specified by a special logical flag field or record type, but actually, such things are better stored separately. So store such summary in an extra table, eg yearly sums, quarterly sums could be computed and stored extra. This means redundancy you may not want to have or trust, so this is a case of a compromise for redundancy and normalized data vs performance. You can store a total with a checksum and verify when reading it back, such checksums could be stored in some vault only some users have access to and in the hardest, you could put some archive (haccount) read-only after having summarized it, so it can't be easily changed, there are several ways to handle this.

Bye, Olaf.

 

[Mike Lewis]

Saif,

You asked a similar question in thread184-1781895, and I advised you about using indexes to optimise the query. The same response will apply in this case. Did you try the indexes I suggested?

Regarding the opening balances, that would be difficult with a UNION. You would need to write a separate query using gouping (GROUP BY clause) and an aggregate function (the SUM() function). The problem is that you cannot then also select the other fields (dates, coded, name, etc). But you can't leave those out, because both sides of the UNION need to have the same fields in the result set.

It might be easier to write a separate query to get the opening balances, and then insert those into the main result set.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Hi,

Finally, I applied the following:

Do Case
   Case This.Parent.OptCumInd.OptCumulative.Value = 1
      Select hAccount.creference, hAccount.cdate, hAccount.ccode, Code.full_name,;
         hAccount.remarks, Code.chead, hAccount.debit, hAccount.credit;
         FROM ;
         village!hAccount ;
         INNER Join village!Code ;
         ON  hAccount.ccode = Code.ccode;
         WHERE Between(cdate,md1,md2) ;
         UNION ;
      Select Account.creference, Account.cdate, Account.ccode, Code.full_name,;
         Account.remarks, Code.chead, Account.debit, Account.credit;
         FROM ;
         village!Account ;
         INNER Join village!Code ;
         ON  Account.ccode = Code.ccode;
         WHERE Between(cdate,md1,md2) ;
         INTO Cursor TempAct Readwrite
      Index On ccode Tag ccode Additive
Endcase

SELECT ccode,SUM(debit) as debit,SUM(credit) as credit,SUM(debit-credit) As OpBal FROM hAccount ;
    WHERE cDate < md1 ;
GROUP BY ccode ;
    UNION ;
SeLECT ccode,SUM(debit) as debit,SUM(credit) as credit,SUM(debit-credit) As OpBal FROM Account ;
    WHERE cDate < md1 ;
GROUP BY ccode INTO CURSOR tmpOb

Sele ccode,;
 full_name,;
     cHead,;
   sum(debit) As debit, ;
   sum(credit) As credit, ;
   SUM(debit-credit) As TotMonth ;
   from TempAct ;
   group By ccode, full_name, cHead  ;
   into Curs Munthab Readwrite
Index On ccode Tag ccode Additive
Index On full_name Tag full_name Additive
Index On chead Tag chead Additive

SELECT Munthab
GO TOP 
DO WHILE ! EOF()
   mcode     = ccode
   mTotMonth = TotMonth
   SELECT tmpOb
   LOCATE FOR ccode = mcode
   mOpBal = OpBal + mTotMonth
   SELECT Munthab
   DO CASE 
   CASE mOpBal > 0
      REPLACE debit  WITH mOpBal
      REPLACE credit WITH 0
   CASE mOpBal < 0
      REPLACE debit WITH  0
      REPLACE credit WITH -(mOpbal)
   ENDCASE 
   SKIP 
ENDDO    
SUM debit,credit TO mTotDebit,mTotCredit

This.parent.ContList.contTotal.totalDebit.Value      = mTotDebit
This.Parent.ContList.ContTotal.TotalCredit.Value     = mTotCredit
This.Parent.ContList.ContTotal.TotalDifference.Value = mTotDebit - mTotCredit

Can I put the progressive bar in between (which I don't know how to do) in order to minimize the waiting time for the user.
If so, kindly guide me.

Thanks

Saif

 

[Olaf Doschke]

You're indexing your results. You must index your account and haccount tables permanently to make your initial queries fast.
What index tags do you have on these two tables? Is there an haccount.cdx and account.cdx?

Bye, Olaf.

 

[Mike Lewis]

Well, that seems to be a lot of complicated code just to do something that you could do fairly easily with two SELECTs. But never mind. If it works, that's fine.

By the way, your initial DO CASE / ENDCASE seems to be redundant, as it only contains one condition.

Regarding the progress bar, please start a new thread for this question. There are various of ways of doing progress bars, but they have nothing to do with optimising a query. (And, by the way, no progress bar can "minimize the waiting time for the user".)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

no progress bar can

minimize the waiting time for the user

Indeed, all a progress bar does is show the user, there is activity and progress.

Bye, Olaf.

 

[alisaif]

Thanks for the reply!

By the way, your initial DO CASE / ENDCASE seems to be redundant, as it only contains one condition.

Actually, I have two cases: Cumulative Records and Net Records

I will apply another case after finishing it.

Can I put the progressive bar in between (which I don't know how to do) in order to minimize the waiting time for the user.

I just wanted the user can judge how much time is left. I know progressive bar cannot minimize the time.

Thanks

Saif