Ошибка Timeout expired при выполнении MySQL-запроса

258

16 ноября 2017, 01:50

Есть запрос в БД (на нем и происходит ошибка, остальные обрабатываются нормально. Сам запрос рабочий (проверял в Workbench))

SELECT
  `contract`.`id` AS `#Number#`,
  `contract`.`date_create` AS `#Date#`,
  `contract`.`date_off` AS `#VozDate#`,
  `contract`.`days` AS `#Days#`,
  `contract`.`days_cred` AS `#DaysCred#`,
  `contract`.`summ_ocen` AS `#SummOcen#`,
  `contract`.`summ_ocen` AS `#SummOcenRub#`,
  `contract`.`summ_cred` AS `#SummCred#`,
  `contract`.`summ_cred` AS `#SummCredRub#`,
  `contract`.`proc_polz` AS `#ProcPolz#`,
  max(if(`object_class_method_value`.`object_class_method_id`=1,value,null)) AS `#AutoName#`,
  max(if(`object_class_method_value`.`object_class_method_id`=2,value,null)) AS `#AutoYear#`,
  max(if(`object_class_method_value`.`object_class_method_id`=4,value,null)) AS `#AutoGosNum#`,
  max(if(`object_class_method_value`.`object_class_method_id`=5,value,null)) AS `#AutoVIN#`,
  max(if(`object_class_method_value`.`object_class_method_id`=6,value,null)) AS `#AutoKuzNum#`,
  max(if(`object_class_method_value`.`object_class_method_id`=7,value,null)) AS `#AutoDvigNum#`,
  max(if(`object_class_method_value`.`object_class_method_id`=8,value,null)) AS `#AutoDvig#`,
  max(if(`object_class_method_value`.`object_class_method_id`=11,value,null)) AS `#AutoNumPTS#`,
  max(if(`object_class_method_value`.`object_class_method_id`=12,value,null)) AS `#AutoVydanPTS#`,
  max(if(`object_class_method_value`.`object_class_method_id`=13,value,null)) AS `#AutoNumSR#`,
  max(if(`object_class_method_value`.`object_class_method_id`=14,value,null)) AS `#AutoVydanSR#`,
  `clients`.`id`,
  `clients`.`fio` AS `#CliName#`,
  `clients`.`telephone` AS `#CliPhone#`,
  `clients`.`document_number` AS `#CliPasportNum#`,
  `clients`.`document_given` AS `#CliPasportVyd#`,
  `clients`.`document_given_date` AS `#CliPasportDate#`,
  `clients`.`registration` AS `#CliAddressProp#`,
  `clients`.`birth_date` AS `#CliBDate#`,
  `clients`.`address_living` AS `#CliAddress#`,
  CONCAT(`users`.`lastname`, ' ', `users`.`name`, ' ', `users`.`surname`) AS `#SotrFull#`,
  `users`.`authorization` AS `#SotrDover#`
FROM
  `contract`, `object_class_method_value`, `clients`, `users`
WHERE
  `contract`.`id` = 2396 AND
  `object_class_method_value`.`object_id` = `contract`.`object_id` AND
  `users`.`id` = `contract`.`user_id`
GROUP BY
  `object_class_method_value`.`object_id`;

Есть такая вот функция в программе(вырезка):

//Здесь открывается соединение
MySqlCommand command = new MySqlCommand(sqlQuery, Connect);
MySqlDataReader reader = command.ExecuteReader(); \\Вот здесь рушится
while (reader.Read())
{
//Обрабатывается и закрывается

С ошибкой

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Answer 1

Запрос жуткий. Зачем-то используется группировка. Записи из таблицы clients тупо перемножаются. Думаю, что перемножение записей основная проблема

Вот Вам правильный запрос

SELECT
  ct.`id` AS `#Number#`,
  ct.`date_create` AS `#Date#`,
  ct.`date_off` AS `#VozDate#`,
  ct.`days` AS `#Days#`,
  ct.`days_cred` AS `#DaysCred#`,
  ct.`summ_ocen` AS `#SummOcen#`,
  ct.`summ_ocen` AS `#SummOcenRub#`,
  ct.`summ_cred` AS `#SummCred#`,
  ct.`summ_cred` AS `#SummCredRub#`,
  ct.`proc_polz` AS `#ProcPolz#`,
  name.value,null AS `#AutoName#`,
  year.value AS `#AutoYear#`,
  ...........
  VydanSR.value AS `#AutoVydanSR#`,
  cl.`id`,
  cl.`fio` AS `#CliName#`,
  cl.`telephone` AS `#CliPhone#`,
  cl.`document_number` AS `#CliPasportNum#`,
  cl.`document_given` AS `#CliPasportVyd#`,
  cl.`document_given_date` AS `#CliPasportDate#`,
  cl.`registration` AS `#CliAddressProp#`,
  cl.`birth_date` AS `#CliBDate#`,
  cl.`address_living` AS `#CliAddress#`,
  CONCAT(usr.`lastname`, ' ', usr.`name`, ' ', usr.`surname`) AS `#SotrFull#`,
  usr.`authorization` AS `#SotrDover#`
FROM
  `contract` ct
  LEFT JOIN `object_class_method_value` name ON (
    name.`object_id` = ct.`object_id` AND
    name.`object_class_method_id` = 1
  )
  LEFT JOIN `object_class_method_value` year ON (
    year.`object_id` = ct.`object_id` AND
    year.`object_class_method_id` = 2
  )
  ......
  LEFT JOIN `object_class_method_value` VydanSR ON (
    VydanSR.`object_id` = ct.`object_id` AND
    VydanSR.`object_class_method_id` = 14
  )
  LEFT JOIN `clients` cl ON (
    ?????
  )
  LEFT JOIN `users` usr ON (
    usr.`id` = ct.`user_id`
  )
WHERE
  ct.`id` = 2396

Вместо ????? подставьте условие, по которому выбираются записи из таблицы clients и на поле object_class_method_value.object_class_method_id создайте индекс.

Ну и я надеюсь, что все поля xxx_id у Вас проиндексированы