Гость
Войти | Профиль | Очистить
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос / 12 сообщений из 12, страница 1 из 1
29.07.2012, 14:51
    #37896818
YP977
YP977
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
Добрый день.

Есть база данных. Лежит тут http://www.ex.ua/get/629412770428/29673342 (2,2 мб)

Выполняю к ней запрос который на быстрой машине отрабатывает порядка 30-40 секунд, а на медленной и того больше.
И индексы есть, и используются, данных мало но тормоза нереальные.
План запроса не осилил :(
Подскажите что можно улучшить?

 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
SELECT 
  base_query.id_goods
, base_query.name_goods
, COALESCE (base_query.count_rest, 0) AS count_rest
, COALESCE (base_query.count_actual, 0)  AS count_actual
, COALESCE (base_query.rest_moion, 0) AS motion_rest
, COALESCE (showcase.count, 0) AS count_showcase
, base_query.price
, base_query.tabledoc_price
, base_query.been_edited
, base_query.number_edits
, base_query.free_price
, base_query.time_edit
, base_query.id_unit
, base_query.name_unit
, base_query.rate_unit
, base_query.type_unit
, bar.bar_code
, bar.ext_bar_code
, base_query.extern_service_id
, base_query.sort_order
FROM (
	SELECT
		goods.id_goods AS id_goods
		, goods.name_goods AS name_goods
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
		  ELSE
			COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS count_rest
		, SUM(tabledoc.count_actual) AS count_actual
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			 0
		  ELSE
			 COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS rest_moion
		, price.price / 100.0 AS price
		, tabledoc.price AS tabledoc_price
		, tabledoc.been_edited AS been_edited
		, SUM(tabledoc.number_edits) AS number_edits
		, goods.free_price AS free_price
		, tabledoc.time_edit AS time_edit
		, unit.id_unit AS id_unit
		, unit.name_unit AS name_unit
		, unit.rate AS rate_unit
		, unit.type_unit AS type_unit
		, goods.sort_order AS sort_order
		, goods.extern_service_id AS extern_service_id
	FROM
		  "front.goods" AS goods
		, "front.unit" AS unit
		, "front.price" AS price
		OUTER LEFT JOIN "spring.doc_inventory_table" AS tabledoc ON tabledoc.id_goods = goods.id_goods AND tabledoc.price = (price.price / 100.0) AND +tabledoc.guid = '90fbbd0a-27ce-40a4-b741-6851ee97788e'
	WHERE
		goods.active      		= 1
		AND  goods.id_goods 	= unit.id_goods
		AND  unit.isdefault  	= 1
		AND  unit.active        = 1
		AND  price.id_goods     = goods.id_goods
		AND  price.id_unit      = unit.id_unit
		AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 1)
	GROUP BY
		id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order
		 
	UNION
		 
	SELECT
		goods.id_goods AS id_goods
		, goods.name_goods AS name_goods
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
		  ELSE
			COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS count_rest
		, SUM(tabledoc.count_actual) AS count_actual
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			 0
		  ELSE
			 COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS rest_moion
		, price.min_price / 100.0 AS price
		, tabledoc.price AS tabledoc_price
		, tabledoc.been_edited AS been_edited
		, SUM(tabledoc.number_edits) AS number_edits
		, goods.free_price AS free_price
		, tabledoc.time_edit AS time_edit
		, unit.id_unit AS id_unit
		, unit.name_unit AS name_unit
		, unit.rate AS rate_unit
		, unit.type_unit AS type_unit
		, goods.sort_order AS sort_order
		, goods.extern_service_id AS extern_service_id
	FROM
		  "front.goods" AS goods
		, "front.unit" AS unit
		, "front.price" AS price
		OUTER LEFT JOIN "spring.doc_inventory_table" AS tabledoc ON tabledoc.id_goods = goods.id_goods AND tabledoc.price = (price.min_price / 100.0) AND +tabledoc.guid = '90fbbd0a-27ce-40a4-b741-6851ee97788e'
	WHERE
		goods.active      		= 1
		AND  goods.id_goods 	= unit.id_goods
		AND  unit.isdefault  	= 1
		AND  unit.active        = 1
		AND  price.id_goods     = goods.id_goods
		AND  price.id_unit      = unit.id_unit
		AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 4)
		AND  price.min_price != 0
		AND  goods.free_price = 1
	GROUP BY
		id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order
) AS base_query
OUTER LEFT JOIN "front.bar_codes" AS bar
	ON
	base_query.id_goods = bar.id_goods
	AND base_query.id_unit = bar.id_unit
	AND +bar.bar_code = (
		SELECT bar_int1.bar_code
		FROM "front.bar_codes" AS bar_int1
		WHERE base_query.id_goods       = bar_int1.id_goods
			AND  base_query.id_unit     = bar_int1.id_unit
			AND  +bar_int1.active 		= 1
		ORDER BY bar_code DESC
	LIMIT 1 )
	AND  +bar.ext_bar_code = (
		SELECT bar_int2.ext_bar_code
		FROM "front.bar_codes" AS bar_int2
		WHERE base_query.id_goods       = bar_int2.id_goods
			AND  base_query.id_unit     = bar_int2.id_unit
			AND  bar.bar_code           = bar_int2.bar_code
			AND  +bar_int2.active	    = 1
		ORDER BY ext_bar_code DESC 
	LIMIT 1 )
OUTER LEFT JOIN "spring.showcase" AS showcase 
	ON showcase.id_goods = base_query.id_goods AND showcase.id_outlet = 4
WHERE base_query.extern_service_id=0
ORDER BY base_query.sort_order asc, base_query.tabledoc_price asc




Суть запроса получение таблицы для отображения в документ Инвентаризация.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2012, 19:10
    #37896929
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
У тебя есть очень плохой индекс по полю active в таблице front.goods. Удаляем его - и вуаля: скорость повышается на порядки
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2012, 19:37
    #37896940
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
Да, и ещё. Пересчёт статистики индексов ещё немного ускорил выполнение запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2012, 11:10
    #37897381
YP977
YP977
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
ArtDen,

Это какая-то черная магия. Все летает.

Появились еще вопросы:
Где именно в плане запроса было видно, что этот индекс тормозит?
Ну и собственно, чем этот индекс так плох?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2012, 19:29
    #37898240
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
Да просто план запроса показал, что для этого индекса количество row было намного больше чем для остальных индексов. Вот я его методом научного тыка и удалил. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2012, 19:33
    #37898244
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
А индекс плох тем, что количество уникальных значений индексируемого поля крайне мало по сравнению с количеством записей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.07.2012, 21:59
    #37898342
YP977
YP977
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
ArtDenДа просто план запроса показал, что для этого индекса количество row было намного больше чем для остальных индексов. Вот я его методом научного тыка и удалил. :)
Вот брат первого запроса. Он тоже выполняется непозволительно долго.
Разница в запросах в том, что пердыдущий выбирает строки только из таблицы документа, а этот все имеющиеся.

Подскажи пожалуйста, что в этом запросе не так и где это видно в плане?
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
SELECT
  base_query.id_goods
, base_query.name_goods
, COALESCE (base_query.count_rest, 0) AS count_rest
, COALESCE (base_query.count_actual, 0) AS count_actual
, COALESCE (base_query.rest_moion, 0) AS motion_rest
, COALESCE (showcase.count, 0) AS count_showcase
, base_query.price
, base_query.tabledoc_price
, base_query.been_edited
, base_query.number_edits
, base_query.free_price
, base_query.time_edit
, base_query.id_unit
, base_query.name_unit
, base_query.rate_unit
, base_query.type_unit
, bar.bar_code
, bar.ext_bar_code
, base_query.extern_service_id
, base_query.sort_order
--  , base_query.goods_active
FROM (
  SELECT
    goods.id_goods AS id_goods
    , goods.name_goods AS name_goods
    , CASE WHEN tabledoc.time_edit IS NULL THEN
      COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
    ELSE
      COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
    END AS count_rest
    , SUM(tabledoc.count_actual) AS count_actual
    , CASE WHEN tabledoc.time_edit IS NULL THEN
      0
    ELSE
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
    END AS rest_moion
    , price.price / 100.0 AS price
    , tabledoc.price AS tabledoc_price
    , tabledoc.been_edited AS been_edited
    , SUM(tabledoc.number_edits) AS number_edits
    , goods.free_price AS free_price
    , tabledoc.time_edit AS time_edit
    , unit.id_unit AS id_unit
    , unit.name_unit AS name_unit
    , unit.rate AS rate_unit
    , unit.type_unit AS type_unit
    , goods.sort_order AS sort_order
    , goods.extern_service_id AS extern_service_id
  FROM
    "spring.doc_inventory_table" AS tabledoc
    , "front.goods" AS goods
    , "front.unit" AS unit
    , "front.price" AS price
  WHERE
    tabledoc.guid  = 'edbc8c07-f267-49c4-b8ab-b9d51dad58bd'
    AND  tabledoc.id_goods = goods.id_goods
    AND  goods.id_goods = unit.id_goods
    AND  unit.isdefault    = 1
    AND  unit.active       = 1
    AND  price.id_goods     = goods.id_goods
    AND  price.id_unit      = unit.id_unit
    AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 4)
  GROUP BY
    id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order, extern_service_id
  
  UNION 
  
  SELECT
    goods.id_goods AS id_goods
    , goods.name_goods AS name_goods
    , CASE WHEN tabledoc.time_edit IS NULL THEN 
      COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
     ELSE
      COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
      +
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
    END AS count_rest
    , SUM(tabledoc.count_actual) AS count_actual
    , CASE WHEN tabledoc.time_edit IS NULL THEN
      0
    ELSE
      COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
    END AS rest_moion
    , price.min_price / 100.0 AS price
    , tabledoc.price AS tabledoc_price
    , tabledoc.been_edited AS been_edited
    , SUM(tabledoc.number_edits) AS number_edits
    , goods.free_price AS free_price
    , tabledoc.time_edit AS time_edit
    , unit.id_unit AS id_unit
    , unit.name_unit AS name_unit
    , unit.rate AS rate_unit
    , unit.type_unit AS type_unit
    , goods.sort_order AS sort_order
    , goods.extern_service_id AS extern_service_id
  FROM
    "spring.doc_inventory_table" AS tabledoc
    , "front.goods" AS goods
    , "front.unit" AS unit
    , "front.price" AS price
  WHERE
    tabledoc.guid  = 'edbc8c07-f267-49c4-b8ab-b9d51dad58bd'
    AND  tabledoc.id_goods = goods.id_goods
    AND  goods.id_goods = unit.id_goods
    AND  unit.isdefault    = 1
    AND  unit.active       = 1
    AND  price.id_goods     = goods.id_goods
    AND  price.id_unit      = unit.id_unit
    AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 4)
    AND  price.min_price != 0
    AND  goods.free_price = 1
  GROUP BY
    id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order, extern_service_id
) AS base_query
OUTER LEFT JOIN "front.bar_codes" AS bar
  ON 
  base_query.id_goods = bar.id_goods
  AND base_query.id_unit = bar.id_unit
  AND +bar.bar_code = (
    SELECT bar_int1.bar_code
    FROM "front.bar_codes" AS bar_int1
    WHERE base_query.id_goods       = bar_int1.id_goods
      AND  base_query.id_unit     = bar_int1.id_unit
      AND  +bar_int1.active       = 1
  ORDER BY bar_code DESC
  LIMIT 1 )
  AND  +bar.ext_bar_code = (
    SELECT bar_int2.ext_bar_code
    FROM "front.bar_codes" AS bar_int2
    WHERE base_query.id_goods       = bar_int2.id_goods
      AND  base_query.id_unit     = bar_int2.id_unit
      AND  bar.bar_code           = bar_int2.bar_code
      AND  +bar_int2.active           = 1
    ORDER BY ext_bar_code DESC
  LIMIT 1 )
OUTER LEFT JOIN "spring.showcase" AS showcase 
      ON showcase.id_goods = base_query.id_goods AND showcase.id_outlet = 4
WHERE base_query.extern_service_id=0
ORDER BY base_query.sort_order asc, base_query.tabledoc_price asc


PS. На всякий случай планы во вложении.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.07.2012, 06:12
    #37898533
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
У меня последний запрос выполняется мгновенно и ничего не возвращает
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.07.2012, 15:25
    #37899240
YP977
YP977
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
ArtDen,

Проверял на другой базе.
Вот запрос
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
SELECT
  base_query.id_goods
, base_query.name_goods
, COALESCE (base_query.count_rest, 0) AS count_rest
, COALESCE (base_query.count_actual, 0) AS count_actual
, COALESCE (base_query.rest_moion, 0) AS motion_rest
, COALESCE (showcase.count, 0) AS count_showcase
, base_query.price
, base_query.tabledoc_price
, base_query.been_edited
, base_query.number_edits
, base_query.free_price
, base_query.time_edit
, base_query.id_unit
, base_query.name_unit
, base_query.rate_unit
, base_query.type_unit
, bar.bar_code
, bar.ext_bar_code
, base_query.extern_service_id
, base_query.sort_order
, base_query.goods_active
FROM (
	SELECT
		goods.id_goods AS id_goods
		, goods.name_goods AS name_goods
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
		  ELSE
			COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS count_rest
		, SUM(tabledoc.count_actual) AS count_actual
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			 0
		  ELSE
			 COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS rest_moion
		, price.price / 100.0 AS price
		, tabledoc.price AS tabledoc_price
		, tabledoc.been_edited AS been_edited
		, SUM(tabledoc.number_edits) AS number_edits
		, goods.free_price AS free_price
		, tabledoc.time_edit AS time_edit
		, unit.id_unit AS id_unit
		, unit.name_unit AS name_unit
		, unit.rate AS rate_unit
		, unit.type_unit AS type_unit
		, goods.sort_order AS sort_order
		, goods.extern_service_id AS extern_service_id
		, goods.active AS goods_active
	FROM
		  "spring.doc_inventory_table" AS tabledoc
		, "front.goods" AS goods
		, "front.unit" AS unit
		, "front.price" AS price
	WHERE
		tabledoc.guid  = '8b0a6235-3cf3-4219-83eb-fda36613feaa'
		AND  tabledoc.id_goods = goods.id_goods
		AND  goods.id_goods = unit.id_goods
		AND  unit.isdefault    = 1
		AND  unit.active       = 1
		AND  price.id_goods     = goods.id_goods
		AND  price.id_unit      = unit.id_unit
		AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 4)
	GROUP BY
		id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order, extern_service_id, goods_active
		 
	UNION
		 
	SELECT
		goods.id_goods AS id_goods
		, goods.name_goods AS name_goods
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			COALESCE ( (SELECT SUM(rest) FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
		  ELSE
			COALESCE ( (SELECT rest FROM "front.rest" AS rest WHERE goods.id_goods = rest.id_goods AND rest.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion < tabledoc.time_edit AND id_goods = goods.id_goods AND rest_motion.id_outlet = 4),0 )
			+
			COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion > tabledoc.time_edit AND id_goods = goods.id_goods AND comment != 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS count_rest
		, SUM(tabledoc.count_actual) AS count_actual
		, CASE WHEN tabledoc.time_edit IS NULL THEN
			 0
		  ELSE
			 COALESCE ( (SELECT SUM(quantity) AS quantity FROM "front.rest_motion" AS rest_motion WHERE time_motion >= tabledoc.time_edit AND id_goods = goods.id_goods AND comment = 'sales' AND rest_motion.id_outlet = 4),0 )
		  END AS rest_moion
		, price.min_price / 100.0 AS price
		, tabledoc.price AS tabledoc_price
		, tabledoc.been_edited AS been_edited
		, SUM(tabledoc.number_edits) AS number_edits
		, goods.free_price AS free_price
		, tabledoc.time_edit AS time_edit
		, unit.id_unit AS id_unit
		, unit.name_unit AS name_unit
		, unit.rate AS rate_unit
		, unit.type_unit AS type_unit
		, goods.sort_order AS sort_order
		, goods.extern_service_id AS extern_service_id
		, goods.active AS goods_active
	FROM
		  "spring.doc_inventory_table" AS tabledoc
		, "front.goods" AS goods
		, "front.unit" AS unit
		, "front.price" AS price
	WHERE
		tabledoc.guid  = '8b0a6235-3cf3-4219-83eb-fda36613feaa'
		AND  tabledoc.id_goods = goods.id_goods
		AND  goods.id_goods = unit.id_goods
		AND  unit.isdefault    = 1
		AND  unit.active       = 1
		AND  price.id_goods     = goods.id_goods
		AND  price.id_unit      = unit.id_unit
		AND  price.id_price_level = (SELECT id_price_level FROM "front.outlet" WHERE id_outlet = 4)
		AND  price.min_price != 0
		AND  goods.free_price = 1
	GROUP BY
		id_goods, name_goods, count_rest, rest_moion, price, tabledoc_price, been_edited, free_price, time_edit, id_unit, rate_unit, type_unit, sort_order, extern_service_id, goods_active
) AS base_query
OUTER LEFT JOIN "front.bar_codes" AS bar
	ON
	base_query.id_goods = bar.id_goods
	AND base_query.id_unit = bar.id_unit
	AND +bar.bar_code = (
		SELECT bar_int1.bar_code
		FROM "front.bar_codes" AS bar_int1
		WHERE base_query.id_goods       = bar_int1.id_goods
			AND  base_query.id_unit     = bar_int1.id_unit
			AND  +bar_int1.active 	    = 1
		ORDER BY bar_code DESC
	LIMIT 1 )
	AND  +bar.ext_bar_code = (
		SELECT bar_int2.ext_bar_code
		FROM "front.bar_codes" AS bar_int2
		WHERE base_query.id_goods       = bar_int2.id_goods
			AND  base_query.id_unit     = bar_int2.id_unit
			AND  bar.bar_code           = bar_int2.bar_code
			AND  +bar_int2.active		= 1
		ORDER BY ext_bar_code DESC
	LIMIT 1 )
OUTER LEFT JOIN "spring.showcase" AS showcase
ON showcase.id_goods = base_query.id_goods AND showcase.id_outlet = 4
WHERE base_query.goods_active = 1 
AND base_query.extern_service_id=0
ORDER BY base_query.sort_order asc, base_query.tabledoc_price asc



База лежит тут: http://www.ex.ua/load/629412770428/29827280 (3.1 мб)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.07.2012, 19:05
    #37899533
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
Следующий скрипт спасёт отца русской демократии :)
Код: sql
1.
2.
3.
4.
begin;
drop index IX_doc_inventory_table_guid;
analyze;
commit;


Это то, что удалось выведать из плана, пробежавшись по нему быстрым взглядом.

Чтобы ускорить ещё сильнее, надо изучать структуру базы, внимательно смотреть план запроса, делать составные индексы или возможно менять структуру базы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.07.2012, 20:46
    #37899589
YP977
YP977
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
ArtDenСледующий скрипт спасёт отца русской демократии :)

:) Гуд, обязательно проверю.
Все таки хотел бы получить ответ на вопрос - где именно видно что этот индекс больше всего жрет время?
Покажи плиз строку для примера. Нигде не могу найти доку в которой бы рассказали как читать план запроса sqlite. Может плохо искал?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.08.2012, 07:31
    #37899781
ArtDen
ArtDen
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос
YP977Все таки хотел бы получить ответ на вопрос - где именно видно что этот индекс больше всего жрет время?
Покажи плиз строку для примера.
Ну вот например. Первая же строка для explain query plan:
Код: plaintext
SEARCH TABLE spring.doc_inventory_table AS tabledoc USING INDEX IX_doc_inventory_table_guid (guid=?) (~492 rows)
Написано ~492 rows, хотя с твоих слов данных очень мало. Поэтому и выбросил этот индекс методом научного тыка :)

YP977Нигде не могу найти доку в которой бы рассказали как читать план запроса sqlite. Может плохо искал?
Ну как бы http://www.sqlite.org/eqp.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 140ms
Закрыть
start [/forum/topic.php?fid=54&mobile=1&tid=2008998]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 15ms
total:  140ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]