Sql-Select Using Union

[alisaif]

Hi All,

I want to add two tables into one using SQL-Select with "Union" which is as follows:

      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;
         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

The thing is, it is very slow and not filtering the records in given dates.

Please note that hAccount is history file which contains 916,350 records and Account is the current file which as around 200,000 records.
Code is the master file contains the account heads and name.
The records in history file are from 01/01/2003 to 31/12/2016.
The records in current file are from 01/01/2017 till to-date.

dt1 = {01/01/2016}
dt2 = {31/10/2017}

Thanks

Saif

 

[Mike Lewis]

These are two separate problems here.

The reason for the slowness is probably related to your indexes - or lack of them. At the very least, you should have indexes on the various Ccode fields and on your Cdate. That alone would probably speed up the query considerably.

But keep in mind that, with large tables such as these, the indexes might slow down the updating of the tables. This is somehting you will have to judge for yourself.

The other problem is the filtering. It's hard to see the reason for this off hand. But are you aware that the WHERE clause will only apply to the second SELECT? So you will get all the history records, and those current records in 2017, none in 2016.

If that doesn't help, I suggest you try running each of the two SELECTs separately in order to see where the incorrect filtering takes place.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks Mike, let me check it.

Saif

 

[mjcmkrsr]

Hi,

In addition to what Mike said please consider this:

...
By default, a UNION includes only DISTINCT records—just like including the DISTINCT clause in a single query, UNION automatically culls out exact duplicates. There are two reasons this can be a problem. First, UNION doesn't bother to check whether the duplicates originated in the same table—they're still eliminated. Second, culling duplicates this way is slow. Our recommendation is to use UNION ALL except when you know you want duplicates removed.
...
Because ORDER BY and TO/INTO are post-processing clauses, a UNIONed query should contain only one of each of these. The other clauses can be applied separately to each query in the UNION.
...

From "Hacker's Guide to VFP 7.0"

hth

MarK

 

[Olaf Doschke]

You can also analyze what optimizations are done with SYS(3054). So what is displayed on screen, when you execute SYS(3054,12) before executing your query?
Besides I already asked you within thread184-1781999 and didn't get an answer:

What index tags do you have on these two tables? Is there an haccount.cdx and account.cdx?

Open the tables hAccount, then later Account in the table designer and see into the tab "Indexes", perhaps make a screenshot.

Bye, Olaf.