Оптимизация вставки/обновления данных

Модераторы: kdv, CyberMax

Ответить
Дмитрий Б.
Сообщения: 56
Зарегистрирован: 05 дек 2007, 18:09

Оптимизация вставки/обновления данных

Сообщение Дмитрий Б. » 16 янв 2012, 18:36

Здраствуйте.

ПО:
Embarcadero RAD Studio XE2
Interbase 7.0 (WI-V7.0.0.206)

Есть БД: ZKZ.GDB
Таблицы: TB_POST - поставщики, - TB_ZKZ заказы, TB_OST - остатки

В таблице TB_OST несколько полей: NUMB, NAME ... Основное поле NUMB - номер детали.
В таблице TB_POST есть поле POST - поставщик.
В таблице TB_ZKZ также присутствует поле NUMB и POST с их помощью произвожу связку (программно - без каких-либо первичных и вторичных ключей, т.к. не требуется целостность данных) двух таблиц TB_OST и TB_ZKZ.

Т.о. при выборе в DBGridOstat любой записи в DBGridZKZ отобразятся записи с номером который соответствует номеру в поле NUMB таблицы TB_OST.

Например

DBGridOST
---------------------------------
NUMB NAME ...
...
2565 Деталь 111
...
---------------------------------

DBGridZKZ
-------------------------------------------------
NUMB POST ZK1 DATA
2565 #1 2 25.01.2000
2565 #2 5 25.10.2001
2565 #10 20 10.12.2002
-------------------------------------------------

Т.е. выбираем номер детали и видим количество штук заказанные у разных поставщиков и когда сделан заказ.

Вот суть работы программы в кратце пояснил.
Теперь перейду к проблеме

Допустим у каждого поставщика есть свой прайс в Excel со своим количеством и ценой и различной номенклатурой (например есть позиции которые не нужны нам или с такими позициями мы не работаем) соответственно они не попадут в БД.

Теперь непосредственно алгоритм подгрузки данных поставщиков.

Код: Выделить всё

При помощи ADO отрываю Excel файл и считываю первую строку получаю номер детали (например из первой ячейки) - формирую запрос для таблицы TB_OST по полю NUMB - 
ЕСЛИ (записей не обнаружено)
{
     пропускаю эту строку и перехожу к следующей.
}
ИНАЧЕ
{
    формирую запрос к таблице TB_ZKZ по полю NUMB
    ЕСЛИ (нет записей)
    {
          то произвожу добавление данны в таблицу TB_ZKZ (IBSQL "insert ...") через параметры.
    }
   ИНАЧЕ
    {
          произвожу обновление данных  в таблицу TB_ZKZ (IBSQL "update ...") через параметры.
    }
}
Исходник описанного метода приведу ниже

Все работает, но есть одно большое НО - Excel-файл на 14000 строк добавляется очень долго (не хватает терпения чтобы дождаться окончания) - предположительно из-за времени затрачиваемое на обработку дополнительных запросов ("select * from TableName where Numb=...") по проверке присутствия/отсутствия номера в таблицах.
Т.е. получается на вставку 14000 записей дополнительно формируется 2*14000 = 28000 проверочных запросов "select"/ В итоге 42000 запросов с учетом insert/update.
Если убрать первую проверку в таблице TB_OST - это конечно сократит количество запросов на 14000, но при этом в таблицу TB_ZKZ будет попадать мусор - номера которые не используються в номенклатуре, а их может быть очень много.
Если-же просто импортировать данные из Excel в базу без каких-либо проверок эти 14000 добавляються меньше минуты.

P.S. чтение Excel файла произвожу посредством ADO.

Подскажите пожалуйста Как можно ускорить процесс ввода/обновления данных, может есть какие-нибудь приемы, а то уже голову сломал?

Спасибо большое. С Уважением, Дмитрий.

Исходник

Код: Выделить всё

void __fastcall TFormLoad::Load(TObject *Sender, AnsiString FileName,
                                AnsiString TableName, AnsiString CompName,
                                AnsiString NUMB_Col, AnsiString LOAD_Col, AnsiString ZAK_Col)
{
  int error_cnt=0;
  DM->ADOConn->Provider=WideString("Microsoft.Jet.OLEDB.4.0");
  DM->ADOConn->ConnectionString=WideString("Data Source="+LoadFileXLS+";Extended Properties='Excel 8.0;HDR=No;IMEX=1'");
  DM->ADOConn->LoginPrompt=false;
  static_cast<TCustomConnection*>(DM->ADOConn)->Open();
  DM->ADOQuery1->Connection=DM->ADOConn;
  DM->ADOQuery1->Close();
  DM->ADOQuery1->SQL->Text="select * from ["+ TableName +"]";
  DM->ADOQuery1->Open();
  int row_count = 0;
  for(DM->ADOQuery1->First(); !DM->ADOQuery1->Eof; DM->ADOQuery1->Next())
  {
     row_count++; // определяем количество строк на листе
  }
  if(row_count < 10)
  {  // если условие выполняется - можно предположить, что лист пустой
     DM->ADOQuery1->Close(); // закрываем запрос
     return;                 // пропускаем лист
  }
  int err_count = 0;
  DM->ADOQuery1->SQL->Text="select * from ["+ TableName +"]"; // формирование запроса
    DM->ADOQuery1->Open();  // открываем запрос
    for(DM->ADOQuery1->First(); !DM->ADOQuery1->Eof; DM->ADOQuery1->Next())
    {
       double Pay;
     int tmp;
     AnsiString Nm;
      bool err_is = false;
  // ZAK_Col содержит строку следующего формата <ЦИФРА><СТРОКА>
  // Например     0 Цена,    1 Количество,   2 Еще что-то
  // Строковая составляющая требуется для идентификации пользователем выбираемой позиции, а циферная часть для передачи ее в switch
      switch(StrToInt(ZAK_Col.SubString(1,1))) 
      {
         case 0: 
         { //Цена поставщика
           try
           {
               Pay = (DM->ADOQuery1->FieldByName(("F"+LOAD_Col))->AsFloat);
               Nm = (DM->ADOQuery1->FieldByName(("F"+NUMB_Col))->AsString);
               err_is = false;
           }
           catch(...)
           {
               error_cnt++;
               Memo2->Lines->Add("Ошибка №" + IntToStr(error_cnt) + "\t\t"+
                                 DM->ADOQuery1->FieldByName(("F"+LOAD_Col))->AsString);
               err_is = true;
           }
         } // case close
         case 1: case 2: case 3: case 4: case 5: case 6: case 7: case 8: case 9:
         { //Остальные позиции
           try
           {
               Pay = (DM->ADOQuery1->FieldByName(("F"+LOAD_Col))->AsFloat);
               Nm = (DM->ADOQuery1->FieldByName(("F"+NUMB_Col))->AsString);
               err_is = false;
           }
           catch(...)
           {
               error_cnt++;
               Memo2->Lines->Add("Ошибка №" + IntToStr(error_cnt) + "\t\t"+
                                 DM->ADOQuery1->FieldByName(("F"+LOAD_Col))->AsString);
               err_is = true;
           }
         }// case close
      } // switch close

      if(!err_is)
      switch(StrToInt(ZAK_Col.SubString(1,1)))
      {
         case 0:
         {
            DM->IBSQL1->Close();
            DM->IBSQL1->SQL->Clear();
            DM->IBSQL1->SQL->Add("select * from TB_MAIN where NUMB_CAT like'%" + NUMB_Col + "%'");
            DM->IBSQL1->ExecQuery();
            if(DM->IBSQL1->RecordCount > 0)
            {  // Если запись с таким номером найдена в основной таблице
               DM->IBSQL1->Close();
               DM->IBSQL1->SQL->Clear();
               DM->IBSQL1->SQL->Add("select * from TB_ZAKAZ where (NUMB_CAT='"+Nm+"' and POST='"+CompName+"')");
               DM->IBSQL1->ExecQuery();
               if(DM->IBSQL1->RecordCount > 0)
               { // Если запись с таким номером найдена в таблице заказов
                 DM->IBSQL1->Close();
                 DM->IBSQL1->SQL->Clear();
                 Memo2->Lines->Add("Update " + Nm);
                 DM->IBSQL1->SQL->Add("update TB_ZAKAZ set PAY_POST=:PAY_POST where (POST = :POST and NUMB_CAT = :NUMB_CAT)");
                 DM->IBSQL1->ParamByName("POST")->AsString = CompName;
                 DM->IBSQL1->ParamByName("NUMB_CAT")->AsString = Nm;
                 DM->IBSQL1->ParamByName("PAY_POST")->AsFloat = Pay;
                 
                 DM->IBSQL1->ExecQuery();
               }
               else
               { // Если запись с таким номером НЕ найдена в таблице заказов
                 DM->IBSQL1->Close();
                 DM->IBSQL1->SQL->Clear();
                 DM->IBSQL1->SQL->Add("insert into TB_ZAKAZ "
                 "(POST, NUMB_CAT, PAY_POST, "
                 "ZK_CURR_1, DT_CURR_1, ZK_CURR_2, DT_CURR_2, ZK_CURR_3, DT_CURR_3, "
                 "ZK_WORK_1, DT_WORK_1, ZK_WORK_2, DT_WORK_2, ZK_WORK_3, DT_WORK_3, "
                 "ZK_FIN_1, DT_FIN_1, ZK_FIN_2, DT_FIN_2, ZK_FIN_3, DT_FIN_3) "
                 "values "
                 "(:POST, :NUMB_CAT, :PAY_POST, "
                 ":ZK_CURR_1, :DT_CURR_1, :ZK_CURR_2, :DT_CURR_2, :ZK_CURR_3, :DT_CURR_3, "
                 ":ZK_WORK_1, :DT_WORK_1, :ZK_WORK_2, :DT_WORK_2, :ZK_WORK_3, :DT_WORK_3, "
                 ":ZK_FIN_1, :DT_FIN_1, :ZK_FIN_2, :DT_FIN_2, :ZK_FIN_3, :DT_FIN_3)");
                 DM->IBSQL1->ParamByName("POST")->AsString = CompName;
                 DM->IBSQL1->ParamByName("NUMB_CAT")->AsString = Nm;
                 DM->IBSQL1->ParamByName("PAY_POST")->AsFloat = Pay;
                 DM->IBSQL1->ParamByName("ZK_CURR_1")->AsInteger = -1;
                 DM->IBSQL1->ParamByName("DT_CURR_1")->AsDate = Now();
                 DM->IBSQL1->ExecQuery();
               }
            } // Close if(!IBDSet_NOM_TMP->IsEmpty())

         } break;  // Close case:
         case 1:
         {
            IBDSet_NOM_TMP->Close();
            IBDSet_NOM_TMP->SelectSQL->Clear();
            IBDSet_NOM_TMP->SelectSQL->Add("select * from TB_MAIN where NUMB_CAT like'%" + NUMB_Col + "%'");
            IBDSet_NOM_TMP->Open();
            if(!IBDSet_NOM_TMP->IsEmpty())
            {  // Если запись с таким номером найдена в основной таблице
               IBDSet_ZAK_TMP->Close();
               IBDSet_ZAK_TMP->SelectSQL->Clear();
               IBDSet_ZAK_TMP->SelectSQL->Add("select * from TB_ZAKAZ where (NUMB_CAT='"+Nm+"' and POST='"+CompName+"')");
               IBDSet_ZAK_TMP->Open();
               if(!IBDSet_ZAK_TMP->IsEmpty())
               { // Если запись с таким номером НЕ найдена в таблице заказов
                 DM->IBSQL1->Close();
                 DM->IBSQL1->SQL->Clear();
                 DM->IBSQL1->SQL->Add("update TB_ZAKAZ set PAY_POST=:PAY_POST where (POST = :POST and NUMB_CAT = :NUMB_CAT)");
                 DM->IBSQL1->ParamByName("POST")->AsString = CompName;
                 DM->IBSQL1->ParamByName("NUMB_CAT")->AsString = Nm;
                 DM->IBSQL1->ParamByName("PAY_POST")->AsFloat = Pay;                  
                 DM->IBSQL1->ExecQuery();
               }
               else
               { // Если запись с таким номером наййдена в таблице заказов
                 DM->IBSQL1->Close();
                 DM->IBSQL1->SQL->Clear();
                 DM->IBSQL1->SQL->Add("insert into TB_ZAKAZ "
                 "(POST, NUMB_CAT, PAY_POST, "
                 "ZK_CURR_1, DT_CURR_1, ZK_CURR_2, DT_CURR_2, ZK_CURR_3, DT_CURR_3, "
                 "ZK_WORK_1, DT_WORK_1, ZK_WORK_2, DT_WORK_2, ZK_WORK_3, DT_WORK_3, "
                 "ZK_FIN_1, DT_FIN_1, ZK_FIN_2, DT_FIN_2, ZK_FIN_3, DT_FIN_3) "
                 "values "
                 "(:POST, :NUMB_CAT, :PAY_POST, "
                 ":ZK_CURR_1, :DT_CURR_1, :ZK_CURR_2, :DT_CURR_2, :ZK_CURR_3, :DT_CURR_3, "
                 ":ZK_WORK_1, :DT_WORK_1, :ZK_WORK_2, :DT_WORK_2, :ZK_WORK_3, :DT_WORK_3, "
                 ":ZK_FIN_1, :DT_FIN_1, :ZK_FIN_2, :DT_FIN_2, :ZK_FIN_3, :DT_FIN_3)");
                 DM->IBSQL1->ParamByName("POST")->AsString = CompName;
                 DM->IBSQL1->ParamByName("NUMB_CAT")->AsString = Nm;
                 DM->IBSQL1->ParamByName("ZK_CURR_1")->AsInteger = Pay;
                 DM->IBSQL1->ParamByName("DT_CURR_1")->AsDate = Now();
                 DM->IBSQL1->ExecQuery();
               }
            } // Close if(!IBDSet_NOM_TMP->IsEmpty())
         } break;  // Close case:
      } // Close SWITCH
      FormLoad->Update();
    }
  DM->ADOQuery1->Close();
  DM->ADOConn->Close();
}
//---------------------------------------------------------------------------

Дмитрий Б.
Сообщения: 56
Зарегистрирован: 05 дек 2007, 18:09

Re: Оптимизация вставки/обновления данных

Сообщение Дмитрий Б. » 28 янв 2012, 02:01

Нашел выход из положения следующим образом:

Предварительно в таблице TB_ZKZ установил составной первичный ключ на NUMB + POST

Код: Выделить всё

При помощи ADO отрываю Excel файл и считываю первую строку получаю номер детали (например из первой ячейки) - формирую запрос для таблицы TB_OST по полю NUMB - 
ЕСЛИ (записей не обнаружено)
{
     пропускаю эту строку и перехожу к следующей.
}
ИНАЧЕ
{
    try
    {
          то произвожу добавление данны в таблицу TB_ZKZ (IBSQL "insert ...") через параметры.
    }
    catch(...) // предположим, что здесь будет вылетать в основном по ошибке существования первичного/уникального ключа
    {
        try
        {   
             произвожу обновление данных  в таблицу TB_ZKZ (IBSQL "update ...") через параметры.
        }
        catch(Exception *err) // на всякий случай, мало-ли какая ошибка выскочит
        {
             ShowMessage("Error   " + err->Message);
        }  
    }
}
При этом исключил запрос на проверку существования номера в TB_ZKZ, что на 1/3 сократило количество выполняемых запросов и дало значительное снижение времени на подгрузку данных из файла в БД.

Sla
Сообщения: 1
Зарегистрирован: 29 фев 2012, 07:25

Re: Оптимизация вставки/обновления данных

Сообщение Sla » 29 фев 2012, 07:30

Насколько большая таблица TB_OST?
Можно всю таблицу считать в память "select * from TB_OST" и проверку проводить непосредственно в клиенте. Скорость возрастает на порядки.

Код: Выделить всё

        TStrings *errors_error;
        TStrings *errors_id;
        TStrings *errors_cause;
        TStrings *errors_comment;

        errors_error->Clear();
        errors_id->Clear();
        errors_cause->Clear();
        errors_comment->Clear();

        Query->SQL->Clear();
        Query->SQL->Add("select * from "+tabname+"_ERROR");
        Query->Open();

        while(!Query->Eof)
        {
               errors_error->Add(Query->FieldByName("ERROR")->AsString);
               errors_id->Add(Query->FieldByName("ID")->AsString);
               errors_cause->Add(Query->FieldByName("CAUSE")->AsString);
               errors_comment->Add(Query->FieldByName("COMMENT")->AsString);

               Query->Next();
        }
        Transaction->Commit();

       ...
       
       // Узнаём error_id
       id=0;
       int imax=errors_error->Count;
       for(int i=0;i<imax;i++)
       {
                        if(Error==errors_error->Strings[i])
                        {
                                id=StrToInt(errors_id->Strings[i]);
                                cause=StrToInt(errors_cause->Strings[i]);
                                comment=errors_comment->Strings[i];
                                break;
                       }
       }

Ответить