Хранимая процедура, занимающая целую вечность?

Question

DELIMITER $$

CREATE PROCEDURE Load_Fact_List()

BEGIN

  DECLARE Project_Number_Temp INT;
  DECLARE Panel_Id_Temp INT;
  DECLARE Employee_Id_Temp INT;
  DECLARE Zip_Temp VARCHAR(255);
  DECLARE Created_Date_Temp DATE;
  DECLARE Country_Temp VARCHAR(255);


  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;


  DECLARE   load_cur    CURSOR FOR
SELECT  Project_Id, Panel_Id, Employee_Id, Zip, Created_Date
    FROM  Fact_List;



  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;


  OPEN load_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  load_cur
    INTO   Project_Number_Temp, Panel_Id_Temp, Employee_Id_Temp, Zip_Temp, Created_Date_Temp;


    IF no_more_rows THEN
        CLOSE load_cur;
        LEAVE the_loop;
    END IF;

SET Country_Temp= (select Country from Zip where Zip= Zip_Temp);

INSERT INTO Test_Fact
(   
        Project_Key, 
        Campaign_Key, 
        Respondents_Key, 
        Event_Key, 
        Employee_Key, 
        Geography_Key, 
        Date_Key    
)

SELECT (SELECT Project_Key from Project_Dim where Project_Id= Project_Number_Temp AND Quota_Country= Country_Temp),0,(SELECT MAX(Respondents_Key) from Respondents_Dim WHERE Panel_Id= Panel_Id_Temp),1,(select MAX(Employee_Key) from Employee_Dim WHERE Employee_Id= Employee_Id_Temp),(Select Geography_Key from Geography_Dim where Zip= Zip_Temp), (Select Date_Key from Date_Dim where Full_Date= Created_Date_Temp);

    SET loop_cntr = loop_cntr + 1;
  END LOOP the_loop;


  select num_rows, loop_cntr;


END $$

Приведенный выше код работает правильно, но он чертовски медленен. За каждый 1 час загружается 1000 записей. У меня не хватает записей для загрузки в таблицу фактов. Кто-нибудь может предложить мне какую-либо оптимизацию?

Требование состоит в том, чтобы загрузить таблицу фактов, просматривая другую таблицу и собирая необходимые значения ключей из таблиц измерений.

Answer 1

Обычная процедура на самом деле так.

У вас построены измерения, и вы только что собрали данные, которые хотите вставить в таблицу фактов, во временную таблицу. Затем вы вставляете эти данные в другую временную таблицу, например:

INSERT INTO tmp_fact_table
(
fact_key,
dim1_key,
dim2_key,
...
fact1,
fact2
...
)
SELECT 
ISNULL (f.fact_key, 0),
ISNULL (d1.sid, 0) as whatever,
ISNULL (d2.sid, 0) as whatever2,
...
ISNULL (tt.fact1, 0),
ISNULL (tt.fact2, 0)
FROM
yourTempTable tt 
LEFT JOIN Dim1 d1 ON tt.identifying_column = d1.identifying_column
...
LEFT JOIN fact_table f ON 
f.dim1_key = d1.sid
AND f.dim2_key = d2.sid

где

• fact_key - это идентифицирующий столбец в вашей таблице фактов.
• dim1_key - это внешний ключ в вашей таблице фактов для измерений
• fact1 и т. д. - это факты, которые вы хотите найти в своей таблице фактов, ясно
• функция ISNULL() возвращает 0, если запись не найдена. 0 - идентификатор фиктивной строки в каждом измерении для неизвестных данных

Затем у вас будет таблица, в которой у вас есть идентификаторы ваших измерений, связанные с данными, которые вы хотите импортировать в таблицу фактов, с 0 в качестве ключа факта, когда запись в таблице фактов еще не существует, и идентификатор записи таблицы фактов. иначе.

Затем вы обновляете таблицу фактов, где tmp_fact_table.fact_key!= 0

Затем вы вставляете в таблицу фактов, где tmp_fact_table.fact_key = 0

Вот и все.

Я делаю это с миллионами строк, и это занимает около получаса. 300 000 строк - это арахис.