Консультации Портала - Консультация #173882

Задать вопрос Консультации Пользователи Форум

Задать вопрос экспертам

Консультация № 173882

01.11.2009, 13:25

0.00 руб.

0 4 2

Компьютеры Программное обеспечение Базы данных

Всем привет!

Поделитесь, где лично вы используете временные таблицы MySQL.
Что-то я совсем не могу придумать, в каких областях они могут пригодиться.

Обсуждение

Неизвестный

01.11.2009, 15:37

общий

это ответ

Здравствуйте, Прим Палвер.

Вот несколько случаев, когда рекомендуется использовать временные таблицы в MySQL:

- Если имеется выражение ORDER BY и отличное от него выражение GROUP BY, или если выражения ORDER BY или GROUP BY содержат столбцы не только из первой таблицы в очереди на связывание, но и из других таблиц, то тогда создается временная таблица.

- Если используется параметр SQL_SMALL_RESULT, MySQL будет применять временную таблицу, которую разместит в памяти. Параметр SQL_SMALL_RESULT является опцией, специфической для MySQL. Данный параметр можно использовать с GROUP BY или DISTINCT, чтобы сообщить оптимизатору, что результирующий набор данных будет небольшим. В этом случае MySQL для хранения результирующей таблицы вместо сортировки будет использовать быстрые временные таблицы.

- Для DISTINCT в сочетании с ORDER BY, помимо этого, во многих случаях также требуется временная таблица.

- Если таблица подвергается многочисленным обновлениям, то команды SELECT будут ожидать, пока обновления не закончатся.
Чтобы обойти это в случае, когда для таблицы требуется выполнить много операций INSERT и SELECT, можно внести строки во временную таблицу и время от времени обновлять реальную таблицу записями из временной.

С уважением,

Спасибо за труд, но это немного не тот ответ. Вернее, не ответ на вопрос, который я задал. Какие практические примеры использования их? А это я и в учебнике прочитал. Дальше...

Неизвестный

01.11.2009, 18:55

общий

это ответ

Здравствуйте, Прим Палвер.
1. Временные таблицы по возможности создаются в памяти (Type=HEAP), поэтому с ними быстрее операции INSERT/UPDATE (при превышении определенного объема они автоматически пишутся на диск в формате MyISAM).
2. Временные таблицы автоматически удаляются при закрытии соединения; можно не заморачиваться с DROP TABLE. В этом основная прелесть, когда НЕ используется pconnect()
3. Временные таблицы видны только для текущего соединения - при insert/update можно не делать LOCK TABLE.
4. Поскольку mysql не поддерживает вложенные SELECT, можно разбить один сложный SELECT на несколько простых с использованием временных таблиц (CREATE TEMPORARY TABLE tmp ..., INSERT INTO tmp SELECT..., SELECT FROM tmp ...)
5. Для создания временных таблиц нужны соответствующие привилегии.

Неизвестный

01.11.2009, 19:49

общий

Прим Палвер:

Что-то я совсем не могу придумать, в каких областях они могут пригодиться.

Если не можите придумать, значит, они Вам сейчас не нужны - всё просто

А так, временные таблицы используются в запросах (чаще в процедурах и функциях) когда надо сформировать какой-то набор данных с "хитрым условием"..
Формируется такой набор данных (временная таблица) и потом, уже с этим набором данных ведется работа - выборка, курсоры и т.д.
Таким образом достигается следующее:
Мы значительно повышаем быстродействие нашей функции/процедуры/просмотра за счет 1 раза выборки и в последствии работа ведется только с этим набором.
Во вторых, таким образом мы можем получить набор данных, которых в природе не существует (т.е. данные есть, но не в таком виде и не в такой последовательности) и для этого нам нет нужды делать постоянную таблицу.
Иными словами, это своего рода переменная TEMP которую мы формируем под свои нужды в нашей транзакции..

Может Вам и это не совсем понятно.. Чтож, когда у Вас появятся запросы (процедуры/просмотры/функции) на несколько страниц, тогда Вы вероятно оцените все преимущество...

Для запросов в пару строк, конечно необходимости в формировании временных таблиц маловероятна, но вот для сложных - это довольно хорошее подспорье.

вот не большой пример (несмотря на то, что это скрипт для MS SQL, суть не меняется...):

Код:


USE [CTZ_works]
GO
/****** Object:  StoredProcedure [dbo].[app_GetReportUOGT1]    Script Date: 11/01/2009 21:47:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROC rpt.app_GetReportUOGT
-- =============================================
-- Author:		ПЫРЛИК В.А.
-- Create date: 23/05/2008
-- Description:	ВОЗВРАЩАЕТ СМЕННЫЙ РАПОРТ
-- =============================================
ALTER PROCEDURE [dbo].[app_GetReportUOGT1] 
(
   @oper int,
   @object int,
   @s_date datetime = null,
   @e_date datetime=null,
   @name_shift char(1)
)
AS
DECLARE @D1 DATETIME, @D2 DATETIME, @id_shift INT,
 @date DATETIME, @num_place INT, @num_oper INT, @shift CHAR(1),
 @operation_state int,@number_according_to_plan_output int,
 @id_oper INT,  -- ID операции
 @id_job  INT,  -- ID задания
 @id_group INT, -- сортность 1,2,3,4,5
 @num_par INT,  -- номер параметра 2 = длина  3=масса
 @partia char(10),@plavka char(16),
 @count_all INT, @massa_all FLOAT, @dlina_all FLOAT, -- всего
 @count_1 INT,   @massa_1 FLOAT, @dlina_1 FLOAT, -- первый сорт
 @count_2 INT,   @massa_2 FLOAT, @dlina_2 FLOAT, -- второй сорт
 @count_3 INT,   @massa_3 FLOAT, @dlina_3 FLOAT, -- попутных
 @count_4 INT,   @massa_4 FLOAT, @dlina_4 FLOAT, -- ремонт
 @count_5 INT,   @massa_5 FLOAT, @dlina_5 FLOAT -- брак
BEGIN
	SET NOCOUNT ON;
SET @D1 = PDB.GetDateNow(ISNULL(@S_DATE,GETDATE()))	
SET @D2 = PDB.GetDateNow(ISNULL(@E_DATE,@D1))
SET @id_shift = dbo.fn_GetShiftID(@name_shift) 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#rep') AND type in (N'U'))
DROP TABLE #rep
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#oper') AND type in (N'U'))
DROP TABLE #oper
CREATE TABLE #rep 
 (
 date DATETIME, /*num_place INT,num_oper INT,*/ shift CHAR(1),
 partia char(10),plavka char(16),/*operation_state int,*/number_according_to_plan_output int,
 count_all INT, massa_all FLOAT,dlina_all FLOAT, -- всего 
 count_1 INT,   massa_1 FLOAT, dlina_1 FLOAT, -- первый сорт
 count_2 INT,   massa_2 FLOAT, dlina_2 FLOAT, -- второй сорт
 count_3 INT,   massa_3 FLOAT, dlina_3 FLOAT, -- попутных
 count_4 INT,   massa_4 FLOAT, dlina_4 FLOAT, -- ремонт
 count_5 INT,   massa_5 FLOAT, dlina_5 FLOAT -- брак
 )
 
 
  SELECT 
  dbo.ViewOper.date_commencement,
  dbo.ViewOper.id_operationActualObjects,
  dbo.ViewOper.id_shift,
  dbo.ViewOper.id_jobNumber,
  dbo.ViewOper.startup_party,
  dbo.ViewOper.heat_ladle,
  dbo.ViewOper.id_techObject,
  dbo.ViewOper.id_operation,
  dbo.ViewOper.number_according_to_plan_output,
  dbo.ViewOper.id_conditionOperation as operation_state
  INTO #oper
  FROM
  dbo.ViewOper
  WHERE
  (dbo.ViewOper.date_commencement BETWEEN @D1 AND @D1)
 
 DECLARE sostav_obj CURSOR FAST_FORWARD FOR
 SELECT 
  #oper.date_commencement,
  #oper.id_shift,
  #oper.id_jobNumber,
  #oper.startup_party,
  #oper.heat_ladle,
  #oper.number_according_to_plan_output
  --#oper.operation_state
  FROM
  #oper
  WHERE
  (#oper.id_techObject = @object)
  AND (#oper.id_operation = @oper)
  AND (#oper.id_shift = @id_shift)
  GROUP BY 
  #oper.date_commencement,
  #oper.id_shift,
  #oper.id_jobNumber,
  #oper.startup_party,
  #oper.heat_ladle,
  #oper.number_according_to_plan_output
 -- #oper.operation_state
 

OPEN sostav_obj
  FETCH NEXT FROM sostav_obj INTO @date, @shift, @id_job,@partia,@plavka,@number_according_to_plan_output--,@operation_state
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
-- первый сорт
      SELECT  @count_1= ISNULL(COUNT(*),0),
               @dlina_1= ISNULL(SUM(dbo.ViewObjSort.dlina),0),
               @massa_1= ISNULL(SUM(dbo.ViewObjSort.massa),0)
      FROM  dbo.ViewObjSort
      WHERE dbo.ViewObjSort.id_groupProduct = 1
            AND dbo.ViewObjSort.id_operationActualObjects IN (SELECT #oper.id_operationActualObjects 
                                                                     FROM #oper WHERE  #oper.id_jobNumber = @id_job AND #oper.id_shift = @shift)
-- второй сорт
       SELECT  @count_2= ISNULL(COUNT(*),0),
               @dlina_2= ISNULL(SUM(dbo.ViewObjSort.dlina),0),
               @massa_2= ISNULL(SUM(dbo.ViewObjSort.massa),0)
      FROM  dbo.ViewObjSort
      WHERE dbo.ViewObjSort.id_groupProduct = 2
            AND dbo.ViewObjSort.id_operationActualObjects IN (SELECT #oper.id_operationActualObjects 
                                                              FROM #oper WHERE  #oper.id_jobNumber = @id_job AND #oper.id_shift = @shift)
-- попутных
       SELECT  @count_3= ISNULL(COUNT(*),0),
               @dlina_3= ISNULL(SUM(dbo.ViewObjSort.dlina),0),
               @massa_3= ISNULL(SUM(dbo.ViewObjSort.massa),0)
      FROM  dbo.ViewObjSort
      WHERE dbo.ViewObjSort.id_groupProduct = 3
            AND dbo.ViewObjSort.id_operationActualObjects IN (SELECT #oper.id_operationActualObjects 
                                                              FROM #oper WHERE  #oper.id_jobNumber = @id_job AND #oper.id_shift = @shift)
-- ремонт
       SELECT  @count_4= ISNULL(COUNT(*),0),
               @dlina_4= ISNULL(SUM(dbo.ViewObjSort.dlina),0),
               @massa_4= ISNULL(SUM(dbo.ViewObjSort.massa),0)
      FROM  dbo.ViewObjSort
      WHERE dbo.ViewObjSort.id_groupProduct = 4
            AND dbo.ViewObjSort.id_operationActualObjects IN (SELECT #oper.id_operationActualObjects 
                                                              FROM #oper WHERE  #oper.id_jobNumber = @id_job AND #oper.id_shift = @shift) 
-- брак
       SELECT  @count_5= ISNULL(COUNT(*),0),
               @dlina_5= ISNULL(SUM(dbo.ViewObjSort.dlina),0),
               @massa_5= ISNULL(SUM(dbo.ViewObjSort.massa),0)
      FROM  dbo.ViewObjSort
      WHERE dbo.ViewObjSort.id_groupProduct = 5
            AND dbo.ViewObjSort.id_operationActualObjects IN (SELECT #oper.id_operationActualObjects 
                                                              FROM #oper WHERE  #oper.id_jobNumber = @id_job AND #oper.id_shift = @shift)                                                                                                                                                                                                                                                                                   
      
       
       INSERT #rep VALUES( @date,@shift, @partia,@plavka,/* @operation_state,*/@number_according_to_plan_output,
                           @count_1+@count_2+@count_3+@count_4+@count_5,
                           @massa_1+@massa_2+@massa_3+@massa_4+@massa_5, 
                           @dlina_1+@dlina_2+@dlina_3+@dlina_4+@dlina_5, 
                           @count_1,   @massa_1, @dlina_1,
                           @count_2,   @massa_2, @dlina_2, 
                           @count_3,   @massa_3, @dlina_3, -- попутных
                           @count_4,   @massa_4, @dlina_4, 
                           @count_5,   @massa_5, @dlina_5 -- брак
                         )
       FETCH NEXT FROM sostav_obj INTO  @date, @shift, @id_job,@partia,@plavka,@number_according_to_plan_output--,@operation_state
  END
  CLOSE sostav_obj
  DEALLOCATE sostav_obj
  
  SELECT date as date_commencement, partia as startup_party,plavka as heat_ladle,
         /*operation_state,*/number_according_to_plan_output,
         count_all as quantity_input_s, massa_all as quantity_input_t,dlina_all as quantity_input_m, -- всего
         count_1 as quantity_first_sort_s,   massa_1 as quantity_first_sort_t, dlina_1 as quantity_first_sort_m, -- первый сорт
         count_2 as quantity_second_sort_s,   massa_2 as quantity_second_sort_t, dlina_2 as quantity_second_sort_m, -- второй сорт
         count_3 as quantity_repair_s,   massa_3 as quantity_repair_t , dlina_3 as quantity_repair_m, -- попутных
         count_4 as quantity_by_product_s,   massa_4 as quantity_by_product_t, dlina_4 as quantity_by_product_m, -- ремонт
         count_5 as quantity_rejects_s,   massa_5 as quantity_rejects_t, dlina_5 as quantity_rejects_m  -- брак
  FROM  #rep
  DROP TABLE  #rep
  DROP TABLE  #oper
  --DROP CURSOR sostav_obj
       
END

Неизвестный

02.11.2009, 13:03

общий

Victor Pyrlik:

Чтож, когда у Вас появятся запросы (процедуры/просмотры/функции) на несколько страниц, тогда Вы вероятно оцените все преимущество...

Уже успел оценить отсутствие временных таблиц в FireBird :)

Форма ответа

Отправка постов/ответов доступна только зарегистрированным и подтвержденным пользователям.

Если Вы уже зарегистрированы на Портале - войдите в систему, если Вы еще не регистрировались - пройдите простую процедуру регистрации.