0

I have 3 tables boy girl and relationship, where boy and girl r parent tables where thr primary key is boy_id and girl_id(both are guids). their mapping is done in relationship table. where each mapping set is composite primary key.

now I want to copy all these tables to another data base(where table names are man woman and affair and their structure is similar to boy ,girl and relationship), table ids will be converted to some other guid.

What I have done is- I created a IDmapper table, and before doing anything I stored old guid and thier respective new guid in a table. Then I copied data from boy to man and girl to woman with their new guid which is stored in IDmapper table. later I used following queries (1) and (2) to form the affair table.

Boy,Man,Woman,Girl tables are of following structure – 

 (col1 varchar(50) not null,col2 varchar(50) not null,primary key(col1));

Relationship, Affair and IDMapper(temporary) table are of follwoing structure

(col1 varchar(50) not null,col2 varchar(50) not null,primary key(col1,col2));

Following are the queries for copy from Boy(opt),Girl(opt),Realtionship(jon) to Man(opt), Woman(opt),Affair(jon) respectively

Insert into IDMapper Select Boy_ID,Boy_ID+'XY' from Boy; --Generate new guid and store mapping into IDMApper
Insert into Man Select New_ID,BoyName from Boy join IDMapper on Old_ID=Boy_ID; -- Copy table data

Insert into IDMapper Select Girl_ID,Girl_ID+'XX' from Girl; --Generate new guid and store mapping into IDMApper
Insert into Woman Select Old_ID,GirlName from Girl join IDMapper on Old_ID=Girl_ID; -- Copy table data

(1) 

Insert into Affair Select M.New_ID,F.New_ID from Relationship
  join IDMapper M 
     on Boy_ID=M.OLD_ID 
  join IDMapper F 
     on Girl_ID=F.Old_ID;

(2) 

Insert into Affair Select 
   (Select New_ID from IDMapper where Old_ID=R.Boy_ID),
   (Select New_ID from IDMapper where Old_ID=R.GIRL_ID) from Relationship R;

among query (1) and (2), which one is faster and what is their time complexity??

I have to assume such situations where relationship table will hold the mapping information of 3-4 tables.

Please explain which query is better and why?

Bopsi
  • 2,090
  • 5
  • 36
  • 58
  • Do you have a problem with the query, or are you just curious? You also may try to let your server analyze the query execution path to get some information. – Uwe Allner Mar 24 '15 at 08:32
  • Both the query worked, and due to the small amount of dataset the execution time shown in both the query is almost same. The prominent difference can be seen only I have large number of dataset. and what if the relationship table holds mapping of more than 3 table , I am curious ? – Bopsi Mar 24 '15 at 09:32
  • And also I am looking for a query with minimal time complexity – Bopsi Mar 24 '15 at 09:33
  • Normally joins are faster than inner selects, except when you have to make left outer joins; then it is about the same. But you should only optimize when you run into performance problems. – Uwe Allner Mar 24 '15 at 11:08
  • Check if I am right about this- Query (1) time complexity O(n^c) Query (2) time complexity O(c*n^2) where c= number of tables mapped in IDmapper or number of parent tables and n= number of rows in IDmapper am I calculating it ri8? – Bopsi Mar 25 '15 at 03:53
  • If you leave out the effects of indexes and query optimizers, this may be right. With an index on OldID in IDMapper you can make access constant (n=1), so that both methods have the same complexity. – Uwe Allner Mar 25 '15 at 08:15

0 Answers0