INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sql-Select Using Union

Sql-Select Using Union

(OP)
Hi All,

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

CODE -->

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

RE: Sql-Select Using Union

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

RE: Sql-Select Using Union

(OP)
Thanks Mike, let me check it.

Saif

RE: Sql-Select Using Union

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

RE: Sql-Select Using Union

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: How to optimize this query and didn't get an answer:

Quote (myself)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close