We have some massive key-value tables that ofcourse has some performance issues. A few months ago, I could finally have convinsted the developer to leave this key-value based model and move to a column based model.
Today implentation is ready and now we have to move te data to the column_based table. To reduce down-time, what is the best query to do the migration? I was thinking of using insert into cb_tbl (select objectid, t1.parameter1, t2.parameter, ...,t50.parameter from kv1_tbl t1, kv1_tbl t2, kv2_tbl t3...) but this is a massive join. What is the best query to fill up the cb_tbl?
Some facts :
Current key-value tables
---------------------
kv1_tbl(objectid, key, value, semanticid) #string values
kv2_tbl(objectid, key, value, semanticid) #data values
kv3_tbl(objectid, key, value, semanticid) #integer values
new column based table
--------------
cb_tbl(objectid,parameter1,parameter2, ... , parameter50)
So each objectid has around 50 key_values devided over 3 key-value tables.
Current migration script contains 50 merge statements:
Merge into cb_tbl using (select objectid, value from kv1_tbl where semantidid=1) x on ( cb_tbl.objectid = x.objectid) when matched then update set parameter1 = x.value;
Merge into cb_tbl using (select objectid, value from kv2_tbl where semantidid=102) x on ( cb_tbl.objectid = x.objectid) when matched then update set parameter2 = x.value;
Merge into cb_tbl using (select objectid, value from kv1_tbl where semantidid=54) x on ( cb_tbl.objectid = x.objectid) when matched then update set parameter3 = x.value;
...
Merge into cb_tbl using (select objectid, value from kv3_tbl where semantidid=50) x on ( cb_tbl.objectid = x.objectid) when matched then update set parameter50 = x.value;