12

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations.

The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and end May 2014 (result = 450K lines). The 2nd data set has the unique StoreKey/ProductKey combinations, for sales begin June 2014, until today (result = 190K lines).

I'm looking to find the StoreKey/ProductKey combinations that are in the 2nd set, but not in the 1st set - i.e. new products sold from the beginning of June.

Up until now, I've dumped the two data sets into temp tables, created indexes for both tables on both keys, and used the EXCEPT statement to find unique items.

What is the most efficient way of comparing such large data sets? Is there a more efficient way of doing this type of large comparison?

Improve this question
0

2 Answers 2

Reset to default
10

Using EXCEPT is in my opinion the way to go here, but you might want to reconsider the use of the temporary table. By doing so you are effectively duplicating your data in memory, which will slow you down. If the indexes you need exist on the source tables (as I suspect), just compare the appropriate SELECTS:

SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date1 AND date2
EXCEPT
SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date3 AND date4
Improve this answer
2
  • 1
    Correct, the table does have indexes, but it's a clustered index on the two required fields, plus a field named TransactionDateKey. Would a big difference be seen if I implement either: a.) A clustered index on StoreKey and ProductKey b.) Two separate non-clustered indexes on StoreKey and ProductKey respectively?
    – Pierre Pretorius
    Commented Jun 18, 2014 at 8:36
  • 1
    I assume TransactionDateKey is the column used to filter the time period. In that case the clustered index on TransactionDateKey, StoreKey and ProductKey is perfect.
    – Twinkles
    Commented Jun 18, 2014 at 9:21
1

If you're familiar with algorithms (Big-O complexity), performing this comparison is at best O(n log(n)). The most efficient algorithm will sort both data sets, then make a merged run down them in parallel to find matching (or unmatched) keys. Most RDBMS optimizers will do this automatically for you when you are using EXCEPT or MINUS. Your explain plan will confirm or disconfirm. If you see nested loops, you're doing O(n^2), not as efficient.

Improve this answer
2
  • Thanks Josua. Not familiar with Big-O complexity, but will certainly have a look at it.
    – Pierre Pretorius
    Commented Jun 18, 2014 at 8:40
  • Links to learn more about Complexity Analysis, which some people refer to colloquially as Big-O. It's not as tough as it might look at first. When people say a task will run in linear time or polynomial time, this is what they are referring to. Database backup in general is linear, meaning 2x database size takes 2x time to backup. Sorting a data set it not linear though. A file 2x as big takes more than 2x the time to sort. bigocheatsheet.com, In the wiki en.wikipedia.org/wiki/Time_complexity it mentions the fastest possible comparison sort is "linearithmic time" = n log(n).
    – Joshua Huber
    Commented Jun 18, 2014 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.