1

I am trying to determine a set of id's that that exist in a set of tables, but the size of the tables are > 10 million records, the database is denormalized, normalization is not an option and the query either takes to long to complete or never completes.

I'm not sure how to partition this sort of job so that it can be run in parallel so that it takes less time and so if a step/subsection fails it can continue from where it left off.

A simplified example would be: find the customer in 3 tables whose Id matches in all tables.

The initial idea was to start looking at how sql engine inner LOOP JOIN actually works to try get ideas on how to partition it so that parts could be run in parallel. But without knowing anything about the various data science algorithms, I suspect there is some standard way to do this.

The rather cryptic original requirement was:

Compare party_id column in the three tables to identify the customer available in three table i.e. it is AND operation between three.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID AND SAMPLE3.PARTY_ID

If the operation is OR then pick all the customers available in the three tables.

SAMPLE1.PARTY_ID OR SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

AND / OR are used between tables then performed the comparison as required.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

I set up some 4 test tables each with with this definition

CREATE TABLE `TABLE1` (
  `CREATED` datetime DEFAULT NULL,
  `PARTY_ID` varchar(45) NOT NULL,
  `GROUP_ID` varchar(45) NOT NULL,
  `SEQUENCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`SEQUENCE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=978536 DEFAULT CHARSET=latin1;

Then added 1,000,000 records to each just random numbers in a range that should result in joins.

I used the following test query

SELECT `TABLE1`.`PARTY_ID` AS `pi1`, `TABLE2`.`PARTY_ID` AS `pi2`, `TABLE3`.`PARTY_ID` AS `pi3`, `TABLE4`.`PARTY_ID` AS `pi4` FROM `devt1`.`TABLE2` AS `TABLE2`, `devt1`.`TABLE1` AS `TABLE1`, `devt1`.`TABLE3` AS `TABLE3`, `devt1`.`TABLE4` AS `TABLE4` WHERE `TABLE2`.`PARTY_ID` = `TABLE1`.`PARTY_ID` AND `TABLE3`.`PARTY_ID` = `TABLE2`.`PARTY_ID` AND `TABLE4`.`PARTY_ID` = `TABLE3`.`PARTY_ID`

It's supposed to complete in under 10 min and for table sizes 10x larger. My test query still hasn't completed and it has been running for 15 min

migrated from datascience.stackexchange.com Oct 19 '15 at 19:28

This question came from our site for Data science professionals, Machine Learning specialists, and those interested in learning more about the field.

2

This belongs on dba.stackexchange.com but I don't have the rep to comment
(and the reason I don't have the points is I gave out a 100 point bounty)

You don't give the sql an algorithm. You give it indexes and a good query (some times with query hints). SQL will parallelize on its own.

PartyID would be better as an int and then have another table for the text
Int or VarChar put and index on it

select table1.partyID 
  from table1 
  join table2 
    on table2.partyID = table1.partyID 
  join table3 
    on table3.partyID = table1.partyID

for an or just do do a union

select table1.partyID 
  from table1
union
select table2.partyID 
  from table2
...
1

Are you able to create indices in the database? If so, create an index on the columns you need for the join in each table. It does not have to be a unique or a clustered index. For example, on Table1:

CREATE NONCLUSTERED INDEX Table1_PartyId ON Table1(PartyId);

Then for the AND solution inner join to SELECT DISTINCT subqueries.

SELECT T1.PartyID
FROM (SELECT DISTINCT PartyId FROM Table1) AS T1
INNER JOIN (SELECT DISTINCT PartyId FROM Table2) AS T2
INNER JOIN (SELECT DISTINCT PartyId FROM Tale3) AS T3;

The other conditions are just different joins and a WHERE statement to test NULLs for the specific condition.

A few million records should be trivial to any reputable database system so I suspect that proper indexing of the records is your issue. If you can examine your query plan I'm betting that it is doing full table scans per row. Performance doom.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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