powered by simpleCommunicator - 2.0.38     © 2025 Programmizd 02
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос
12 сообщений из 12, страница 1 из 1
Помогите оптимизировать запрос
    #37896818
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
Помогите оптимизировать запрос
    #37896929
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
У тебя есть очень плохой индекс по полю active в таблице front.goods. Удаляем его - и вуаля: скорость повышается на порядки
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37896940
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Да, и ещё. Пересчёт статистики индексов ещё немного ускорил выполнение запроса.
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37897381
YP977
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
ArtDen,

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

Появились еще вопросы:
Где именно в плане запроса было видно, что этот индекс тормозит?
Ну и собственно, чем этот индекс так плох?
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37898240
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Да просто план запроса показал, что для этого индекса количество row было намного больше чем для остальных индексов. Вот я его методом научного тыка и удалил. :)
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37898244
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
А индекс плох тем, что количество уникальных значений индексируемого поля крайне мало по сравнению с количеством записей.
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37898342
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
Помогите оптимизировать запрос
    #37898533
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
У меня последний запрос выполняется мгновенно и ничего не возвращает
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37899240
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
Помогите оптимизировать запрос
    #37899533
ArtDen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Следующий скрипт спасёт отца русской демократии :)
Код: sql
1.
2.
3.
4.
begin;
drop index IX_doc_inventory_table_guid;
analyze;
commit;


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

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

:) Гуд, обязательно проверю.
Все таки хотел бы получить ответ на вопрос - где именно видно что этот индекс больше всего жрет время?
Покажи плиз строку для примера. Нигде не могу найти доку в которой бы рассказали как читать план запроса sqlite. Может плохо искал?
...
Рейтинг: 0 / 0
Помогите оптимизировать запрос
    #37899781
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
12 сообщений из 12, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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