Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
Здравствуйте все.
Не считал себя особо ламером в запросах SQL, хотя и не писал трехэтажных процедур.. Но когда встала задача - сделать быстрый запрос с большим объемом - тут возникла проблема.

Есть таблица
CREATE TABLE [db_owner].[KLADR_SEARCH](
	[ONENAME] [varchar](40) NOT NULL,
	[LVL] [smallint] NOT NULL,
	[code] [varchar](20) NOT NULL,
	[COUNTRY] [varchar](5) NOT NULL,
	[SS] [varchar](2) NULL,
	[RRR] [varchar](3) NULL,
	[GGG] [varchar](3) NULL,
	[PPP] [varchar](3) NULL,
	[UUUU] [varchar](4) NULL
) ON [PRIMARY]
около 1 100 000 записей

CREATE TABLE [db_owner].[KLADR_KLADR](
	[NAME] [varchar](40) NULL,
	[SOCR] [varchar](10) NULL,
	[code] [varchar](13) NULL,
	[INDEX] [varchar](6) NULL,
	[GNINMB] [varchar](4) NULL,
	[UNO] [varchar](4) NULL,
	[OCATD] [varchar](11) NULL,
	[STATUS] [char](1) NULL,
	[RRR] [varchar](3) NULL,
	[GGG] [varchar](3) NULL,
	[PPP] [varchar](3) NULL,
	[SS] [varchar](2) NULL,
	[lvl] [smallint] NULL
) ON [PRIMARY]
около 170 000 записей

CREATE TABLE [db_owner].[KLADR_STREET](
	[NAME] [varchar](40) NULL,
	[SOCR] [varchar](10) NULL,
	[code] [varchar](17) NULL,
	[INDEX] [varchar](6) NULL,
	[GNINMB] [varchar](4) NULL,
	[UNO] [varchar](4) NULL,
	[OCATD] [varchar](11) NULL,
	[SS] [varchar](2) NULL,
	[RRR] [varchar](3) NULL,
	[GGG] [varchar](3) NULL,
	[PPP] [varchar](3) NULL,
	[UUUU] [varchar](4) NULL,
	[DD] [varchar](2) NULL,
	[lvl] [smallint] NULL
) ON [PRIMARY]
около 760 000 записей

Стоит задача найти все записи в таблице KLADR_KLADR и KLADR_STREET, в пути адреса которых встречаются слова поиска.

Запрос я построил такой:
select top 10 * from (
			(
			select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Москва%' and 
				(
				(S.SS=KS.SS and S.LVL=1) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.LVL=2) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Москва%' and
				S.CODE=KS.CODE
			union
			select KK.CODE from KLADR_KLADR as KK, KLADR_SEARCH as S
			where S.ONENAME like 'Москва%' and 
				(
				(S.SS=KK.SS and S.LVL=1) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.LVL=2) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			) 
			INTERSECT
			(
			select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Кутузов%' and 
				(
				(S.SS=KS.SS and S.LVL=1) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.LVL=2) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Кутузов%' and
				S.CODE=KS.CODE
			union
			select KK.CODE from KLADR_KLADR as KK, KLADR_SEARCH as S
			where S.ONENAME like 'Кутузов%' and 
				(
				(S.SS=KK.SS and S.LVL=1) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.LVL=2) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
		) as W

На запросах, где количество записей в одной из выборок не велико - 2-5, а в другой около 3000 - работает около 2-3 секунд.. Если же вместо "Кутуз" ставлю "Новая", то выборка для него около 15 000, а для Москвы около 3000 - работа не заканчивается и за минуту..

Вопрос к уважаемым Гуру - может не так структуру построил, может не так запрос написал, может не те индексы построил??.. я не верю, что с таблицей в миллион сиквел не умеет работать быстро..

Конфигурация машины - Intel Core 2 Duo, 2Gb RAM, Win Web Server 2008, SQL 2005 Enterprise
8 ноя 09, 22:47    [7899090]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78,
вы план запроса смотрели? Вот такое вот
(S.SS=KK.SS and S.LVL=1) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.LVL=2) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
это почти наверняка сканирование таблицы, причем не простой, а декартова произведения, так чего вы удивляетесь?
8 ноя 09, 22:54    [7899102]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
как ускорить? структуру я и так улучшил, введя поля ss, rrr и т.п. до этого приходилось бы сканировать поле code и разбирать его по правилу..

Что касается плана запроса - смотрел, но не умею его читать правильно.. и тем более делать выводы и улучшать.. до этого не имел дел с таблицами с числом записей более нескольких тысяч.. и потому про оптимизацию знаю лишь понаслышке.. Может что дельного посоветуете?
8 ноя 09, 23:02    [7899121]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78,

для начала хотя бы это
S.SS=KK.SS
вынесете в условие ON, и вообще - отвыкайте через запятую таблицы писать.
8 ноя 09, 23:08    [7899130]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
По Вашему совету заметил на ON данную конструкцию (пока только в ней). Результат на Москва + Новая вычисляется за 42 секунды! Прогресс! )) Спасибо! ) Надеюсь, есть еще что улучшать.. )

Хотя для себя не могу объяснить, в чем существенная разница между INNER JOIN и явным указанием уловия отбора..
8 ноя 09, 23:26    [7899155]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
написал, а потом заметил.. менял я только в конструкции
select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Москва%' and
				S.CODE=KS.CODE
8 ноя 09, 23:28    [7899157]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78
По Вашему совету заметил на ON данную конструкцию (пока только в ней). Результат на Москва + Новая вычисляется за 42 секунды! Прогресс! )) Спасибо! ) Надеюсь, есть еще что улучшать.. )

Хотя для себя не могу объяснить, в чем существенная разница между INNER JOIN и явным указанием уловия отбора..

существенная - в наглядности. А в данном случае оптимизатор может не суметь вытащить это условие из OR, и поэтому индексы на таблице КК не используются. Вообще - вы план посмотрели? Гадать можно долго.
phoenix78
написал, а потом заметил.. менял я только в конструкции
select KS.CODE from KLADR_STREET AS KS, KLADR_SEARCH AS S
			where S.ONENAME like 'Москва%' and
				S.CODE=KS.CODE

и? поменяйте в остальных подзапросах.
9 ноя 09, 00:05    [7899221]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
добрый вечер всем. Продолим.. ))

Вот что получилось..

select top 10 * from (
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.CODE=KS.CODE
			where S.ONENAME like 'Москва%'
			union
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			) 
			INTERSECT
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.CODE=KS.CODE
			where S.ONENAME like 'Новая%'
			union
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
		) as W

запрос выполнялся 54 секунды. после установки индекса по полям RRR, SS, GGG, PPP, UUUU во всех таблицах скорость увеличилась до 44 секунд. Это предел? Есть еще пути ускорения?

Не могу понять, какие правильные индексы подобрать для такого запроса. Сделал их щтук по 5 для каждой таблицы (в GUI). Может их как-то надо особо привязать к таблице? Подскажите пожалуйста.

С планом запроса еще разбираюсь.. Как пойму, как выгрузить текст - отпишусь.
9 ноя 09, 21:58    [7904776]     Ответить | Цитировать Сообщить модератору
 план запроса  [new]
phoenix78
Member

Откуда:
Сообщений: 14
сохранил в файл план запроса. сам план не уместился - положил в архив

К сообщению приложен файл (plan.rar - 5Kb) cкачать
9 ноя 09, 22:12    [7904840]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78
добрый вечер всем. Продолим.. ))

Вот что получилось..
+

select top 10 * from (
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.CODE=KS.CODE
			where S.ONENAME like 'Москва%'
			union
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			) 
			INTERSECT
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.CODE=KS.CODE
			where S.ONENAME like 'Новая%'
			union
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
		) as W

запрос выполнялся 54 секунды. после установки индекса по полям RRR, SS, GGG, PPP, UUUU во всех таблицах скорость увеличилась до 44 секунд. Это предел? Есть еще пути ускорения?

Не могу понять, какие правильные индексы подобрать для такого запроса. Сделал их щтук по 5 для каждой таблицы (в GUI). Может их как-то надо особо привязать к таблице? Подскажите пожалуйста.

попробуйте union заменить на union all, а в основном select поставить distinct.

select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.CODE=KS.CODE
			where S.ONENAME like 'Новая%'
если KLADDR_SEARCH.CODE всегда имеет соответствие в KLADDR_STREET, таблицу KLADDR_STREET из запроса можно выкинуть.

и еще - у вас по ONENAME индекс есть?
phoenix78

С планом запроса еще разбираюсь.. Как пойму, как выгрузить текст - отпишусь.

SET SHOWPLAN_TEXT
SET STATISTICS PROFILE
9 ноя 09, 22:13    [7904845]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
phoenix78
Надеюсь, есть еще что улучшать.. )
Слейте в одно древо весь КЛАДР не разделяя на улицы и итемы классификатора. У Вас одной таблицей станет меньше.
9 ноя 09, 22:23    [7904887]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
Так, вот что вышло:

select top 10 * from (
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union all
			select S.CODE from KLADR_SEARCH AS S
			where S.ONENAME like 'Москва%' AND S.LVL=5
			union all
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
			INTERSECT
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.LVL=3) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP and S.LVL=4)
				)
			union all
			select S.CODE from KLADR_SEARCH AS S
			where S.ONENAME like 'Новая%' AND S.LVL=5
			union all
			select KK.CODE from KLADR_KLADR as KK INNER JOIN KLADR_SEARCH as S ON S.SS=KK.SS
			where S.ONENAME like 'Новая%' and 
				(
				(S.LVL=1) or
				(S.RRR=KK.RRR and S.LVL=2) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
		) as W

Индекс по ONENAME в таблице KLADR_SEARCH сделал (если я правильно понял, то только по этому полю индекс). Скорость выборки данных 45 секунд.

План выполнения запроса следующий:

StmtText
-----------------------
SET SHOWPLAN_TEXT ON

(строк обработано: 1)

StmtText
----------------------------
SET STATISTICS PROFILE ON

(строк обработано: 1)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select top 10 * from (
			(
			select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S ON S.SS=KS.SS
			where S.ONENAME like 'Москва%' and 
				(
				(S.LVL=1) or
				(S.RRR=KS.RRR and S.LVL=2) or
				(S.RRR=KS.RRR and S.GGG=KS.GGG and S

(строк обработано: 1)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((10)))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Union1015]))
            |--Hash Match(Flow Distinct, HASH:([Union1015]), RESIDUAL:([Union1015] = [Union1015]))
            |    |--Concatenation
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1034]) OPTIMIZED WITH UNORDERED PREFETCH)
            |         |    |--Compute Scalar(DEFINE:([Expr1033]=BmkToPage([Bmk1000])))
            |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([S].[LVL], [S].[SS], [S].[RRR], [S].[GGG], [S].[PPP]) OPTIMIZED)
            |         |    |         |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[LVL2] AS [S]), SEEK:([S].[ONENAME] >= 'Москва' AND [S].[ONENAME] < 'МосквБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[ONENAME] as [S].[ONENAME] like 'М
            |         |    |         |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_STREET].[ADDR] AS [KS]), SEEK:([KS].[SS]=[citytrans].[db_owner].[KLADR_SEARCH].[SS] as [S].[SS]),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(1) 
            |         |    |--RID Lookup(OBJECT:([citytrans].[db_owner].[KLADR_STREET] AS [KS]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1006]) OPTIMIZED)
            |         |    |--Compute Scalar(DEFINE:([Expr1035]=BmkToPage([Bmk1006])))
            |         |    |    |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[LVL] AS [S]), SEEK:(([S].[ONENAME], [S].[LVL]) >= ('Москва', (5)) AND [S].[ONENAME] < 'МосквБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(
            |         |    |--RID Lookup(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH] AS [S]), SEEK:([Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)
            |         |--Nested Loops(Inner Join, WHERE:([citytrans].[db_owner].[KLADR_KLADR].[SS] as [KK].[SS]=[citytrans].[db_owner].[KLADR_SEARCH].[SS] as [S].[SS] AND ([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(1) OR [citytrans].[db_owne
            |              |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[LVL2] AS [S]), SEEK:([S].[ONENAME] >= 'Москва' AND [S].[ONENAME] < 'МосквБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[ONENAME] as [S].[ONENAME] like 'Москва%' AN
            |              |--Table Scan(OBJECT:([citytrans].[db_owner].[KLADR_KLADR] AS [KK]))
            |--Concatenation
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1022]) OPTIMIZED)
                 |    |--Compute Scalar(DEFINE:([Expr1036]=BmkToPage([Bmk1022])))
                 |    |    |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[KS_CODENAME] AS [S]), SEEK:([S].[code]=[Union1015] AND [S].[ONENAME] >= 'Новая' AND [S].[ONENAME] < 'НовБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[ONENAME] as 
                 |    |--RID Lookup(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH] AS [S]), SEEK:([Bmk1022]=[Bmk1022]),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(5)) LOOKUP ORDERED FORWARD)
                 |--Nested Loops(Inner Join, WHERE:([citytrans].[db_owner].[KLADR_STREET].[SS] as [KS].[SS]=[citytrans].[db_owner].[KLADR_SEARCH].[SS] as [S].[SS] AND ([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(1) OR [citytrans].[db_owner].[
                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1016]))
                 |    |    |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_STREET].[CODE_STREET] AS [KS]), SEEK:([KS].[code]=[Union1015]) ORDERED FORWARD)
                 |    |    |--RID Lookup(OBJECT:([citytrans].[db_owner].[KLADR_STREET] AS [KS]), SEEK:([Bmk1016]=[Bmk1016]) LOOKUP ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[LVL2] AS [S]), SEEK:([S].[ONENAME] >= 'Новая' AND [S].[ONENAME] < 'НовБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[ONENAME] as [S].[ONENAME] like 'Новая%' AND ([cityt
                 |--Nested Loops(Inner Join, WHERE:([citytrans].[db_owner].[KLADR_KLADR].[SS] as [KK].[SS]=[citytrans].[db_owner].[KLADR_SEARCH].[SS] as [S].[SS] AND ([citytrans].[db_owner].[KLADR_SEARCH].[LVL] as [S].[LVL]=(1) OR [citytrans].[db_owner].[K
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1025]))
                      |    |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_KLADR].[KLADR_CODE] AS [KK]), SEEK:([KK].[code]=[Union1015]) ORDERED FORWARD)
                      |    |--RID Lookup(OBJECT:([citytrans].[db_owner].[KLADR_KLADR] AS [KK]), SEEK:([Bmk1025]=[Bmk1025]) LOOKUP ORDERED FORWARD)
                      |--Index Seek(OBJECT:([citytrans].[db_owner].[KLADR_SEARCH].[LVL2] AS [S]), SEEK:([S].[ONENAME] >= 'Новая' AND [S].[ONENAME] < 'НовБ'),  WHERE:([citytrans].[db_owner].[KLADR_SEARCH].[ONENAME] as [S].[ONENAME] like 'Новая%' AND ([cityt

(строк обработано: 32)

Почему-то режет текст.. сохранил даже в файл - все равно режет.
9 ноя 09, 22:38    [7904955]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
насчет слияния - совет хороший, но не думаю, что увеличение скорости будет существенным - в разы.. Поиск по таблице KLADR_KLADR существенно меньше (там и записей меньше). Может отработка UNION отнимает существенное время, конечно.. Вобщем, надо будет обдумать Вашу мысль.. А пока выжать все, что можно из существующей структуры..
9 ноя 09, 22:42    [7904974]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
провел эксперимент - закоментил union с выборкой из KLADR_KLADR. Результат - скорость выборки 44 секунды, т.е. без изменений (хотя для меня это странно - почему не расходуется время на поиск в этой таблице)..
9 ноя 09, 22:51    [7905035]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
Еще было замечено без отношения к проблеме.. )

пока игрался - выключил часть по слову "Москва" (она легче). Выборка top 10 прошла менее, чем за секунду!!!! но решив посчитать записи - уже 18 секунд на выборку )) хитрит SQL ) я думал, что top всегда строится после выборки ВСЕХ записей..

Ну, план помог чем-нить? Есть мысли по оптимизации скорости выборки? наверное конечно можно весь путь адреса запихать в KLADR_SEARCH по словам отдельно - но этот путь я оставлю на крайний случай.. Такая таблица будет просто огромная.. )
9 ноя 09, 22:59    [7905076]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
phoenix78
насчет слияния - совет хороший, но не думаю, что увеличение скорости будет существенным - в разы.. Поиск по таблице KLADR_KLADR существенно меньше (там и записей меньше). Может отработка UNION отнимает существенное время, конечно.. Вобщем, надо будет обдумать Вашу мысль.. А пока выжать все, что можно из существующей структуры..
Я и не обещал увеличения производительности. Просто поделился опытом. А еще выскажу свое мнение. Судя по невнятным названиеям столбцов в Ваших таблицах, занимаетесь фигней (я не спорю, все эти столбцы - [SS], [RRR], [GGG] и т.п. - очень нужны ).
9 ноя 09, 23:07    [7905106]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78,
попробуйте запросы
select KK.CODE from KLADR_STREET as KK, KLADR_SEARCH as S
			where S.ONENAME like 'Москва%' and 
				(
				(S.SS=KK.SS and S.LVL=1) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.LVL=2) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.LVL=3) or
				(S.SS=KK.SS and S.RRR=KK.RRR and S.GGG=KK.GGG and S.PPP=KK.PPP and S.LVL=4)
				)
			)
переписать так (для KLADR_STREET и KLADR_KLADR одинаково, и кстати правда подумайте про объединение этих таблиц)
select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S
 ON S.ONENAME like 'Москва%' and S.LVL=1 and S.SS=KS.SS
	union all
select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S
 ON S.ONENAME like 'Москва%' and S.LVL=2 and S.SS=KS.SS and S.RRR=KS.RRR
	union all
select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S
 ON S.ONENAME like 'Москва%' and S.LVL=3 and S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG
	union all
select KS.CODE from KLADR_STREET AS KS INNER JOIN KLADR_SEARCH AS S
 ON S.ONENAME like 'Москва%' and S.LVL=4 and S.SS=KS.SS and S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP
и создать такие индексы:
create clustered /*unique?*/ index IX_KLADR_SEARCH  on KLADR_SEARCH
(
	[LVL],	[ONENAME]
)

create index IX_KLADR_KLADR on KLADR_KLADR -- для KLADDR_STREET такой же
([SS],[RRR],[GGG],[PPP])
include
(
[code]
)
9 ноя 09, 23:08    [7905112]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
Спасибо за советы! завтра попробую обязательно!

Что касается внятности или невнятности названий полей - это вещь субъективная. Для знакомых со структурой кода КЛАДРа - название этих полей никаких вопросов не вызвало бы ;) Не в обиду.. Я ценю ВСЕ Ваши советы.. :) просто за живое задело ))

Про объединение таблиц подумаю..
9 ноя 09, 23:36    [7905200]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
aleks2
Guest
Маразм. Через временную таблицу можно в 5-10 сек уложиться.

declare @k table(code [varchar](20) NOT NULL primary key clustered)
declare @s table(SS, LVL, RRR, GGG, PPP, CODE, unique clustered(LVL, SS, RRR, GGG, PPP))

insert @s(SS, LVL, RRR, GGG, PPP, CODE)
select SS, LVL, RRR, GGG, PPP, CODE
FROM KLADR_SEARCH
where S.ONENAME like 'Москва%'

insert @k(code)
select KS.CODE from KLADR_STREET AS KS INNER JOIN @s S
ON S.SS=KS.SS
where (S.LVL=1)

insert @k(code)
select KS.CODE from KLADR_STREET AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR
where S.LVL=2

insert @k(code)
select KS.CODE from KLADR_STREET AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR and S.GGG=KS.GGG
where S.LVL=3

insert @k(code)
select KS.CODE from KLADR_STREET AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP
where S.LVL=4

insert @k(code)
select CODE from  @s S
where S.LVL=5

insert @k(code)
select KS.CODE from KLADR_KLADR AS KS INNER JOIN @s S
ON S.SS=KS.SS
where (S.LVL=1)

insert @k(code)
select KS.CODE from KLADR_KLADR AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR
where S.LVL=2

insert @k(code)
select KS.CODE from KLADR_KLADR AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR and S.GGG=KS.GGG
where S.LVL=3

insert @k(code)
select KS.CODE from KLADR_KLADR AS KS INNER JOIN @s S
ON S.SS=KS.SS AND S.RRR=KS.RRR and S.GGG=KS.GGG and S.PPP=KS.PPP
where S.LVL=4
-- далее мне неохота писать... ты уж сам INTERSECT заколбась.
10 ноя 09, 09:33    [7905702]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
Добрый вечер всем.

Спасибо за новые советы. Попробую и их в действии. А пока о своих успехах ))
Переписал запрос и изменил структуру таблицы, добавив новую. Сначала о запросе:

select top 10 W.CODE from
(
	(
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL1=KG.CODELVL1 and S.LVL=1 and S.ONENAME like 'Москва%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL2=KG.CODELVL2 and S.LVL=2 and S.ONENAME like 'Москва%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL3=KG.CODELVL3 and S.LVL=3 and S.ONENAME like 'Москва%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL4=KG.CODELVL4 and S.LVL=4 and S.ONENAME like 'Москва%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL5=KG.CODELVL5 and S.LVL=5 and S.ONENAME like 'Москва%'
	)
	INTERSECT
	(
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL1=KG.CODELVL1 and S.LVL=1 and S.ONENAME like 'Новая%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL2=KG.CODELVL2 and S.LVL=2 and S.ONENAME like 'Новая%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL3=KG.CODELVL3 and S.LVL=3 and S.ONENAME like 'Новая%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL4=KG.CODELVL4 and S.LVL=4 and S.ONENAME like 'Новая%'
	union all
	select KG.CODE, KG.LVL from KLADR_GENERAL as KG INNER JOIN KLADR_SEARCH AS S
		ON S.CODELVL5=KG.CODELVL5 and S.LVL=5 and S.ONENAME like 'Новая%'
	)
	/*
	select KG.CODE from KLADR_GENERAL AS KG, KLADR_SEARCH as S
	where S.ONENAME like 'Новая%' and 
		(
		(S.CODELVL1=KG.CODELVL1 and S.LVL=1) or
		(S.CODELVL2=KG.CODELVL2 and S.LVL=2) or
		(S.CODELVL3=KG.CODELVL3 and S.LVL=3) or
		(S.CODELVL4=KG.CODELVL4 and S.LVL=4) or
		(S.CODELVL5=KG.CODELVL5 and S.LVL=5)
		)*/
) AS W ORDER BY LVL ASC
Выборка работает за 4!!!!! секунды ))) YES!
Сразу скажу - существенный перелом внесло разделение выборки с OR на несколько с объединением union.
Далее - для упрощения запросов была введена таблица KLADR_GENERAL, куда я слил по совету выше данные из KLADR и STREET - не полностью, а только нужные для выборки данные.
Ну и еще один момент уже пришел в голову сегодня - ввести доп. поля CODELVL1..CODELVL5, убрав сравнения с ключами SS, RRR и т.п.

Про индексы.
Создал для SEARCH и GENERAL по 5 индексов. В каждый индекс включил NAME, LVL, CODELVL[1-5]. Результат выборки наилучший в такой конфигурации. Пробовал создавать кластерный индекс (первый для каждой из таблиц) - ухудшение результата по скорости в 2-3 раза..
В принципе, меня уже устраивает результат, хотя он и на грани по скорости.. С индексами можете что-то посоветовать? Те ли индексы (оптимальные) я сделал или может еще надо как-то докрутить?
Читал, что кластерный индекс быстрее, чем некластерный, но почему у меня результаты противоположные?

В любом случае, благодарю всех участников дискуссии за Ваши советы! )
10 ноя 09, 22:59    [7910891]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
iljy
Member

Откуда:
Сообщений: 8711
phoenix78

Про индексы.
Создал для SEARCH и GENERAL по 5 индексов. В каждый индекс включил NAME, LVL, CODELVL[1-5].

какие именно индексы? Какой порядок полей? тут нужны индексы
для SEARCH

(LVL, Name) include (codelvl1,codelvl2,codelvl3,codelvl4,codelvl5)

для GENERAL

(codelvl1) include (code,lvl)
(codelvl2) include (code,lvl)
(codelvl3) include (code,lvl)
(codelvl4) include (code,lvl)
(codelvl5) include (code,lvl)
10 ноя 09, 23:26    [7910984]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
valerytin
Member [заблокирован]

Откуда: Moscow
Сообщений: 146
А если улицы не должно быть в адресе? ("Москва, Зеленоград, корп.1661" или "N-ская обл, Кукуйкинский р-н, д.Фигуйкино", д.1) :-)
11 ноя 09, 09:57    [7911810]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
phoenix78
Что касается внятности или невнятности названий полей - это вещь субъективная. Для знакомых со структурой кода КЛАДРа - название этих полей никаких вопросов не вызвало бы ;)
Я хорошо знаком со структурой КЛАДРа. И докУменты под названием METODIKA.DOC и OPISKLAD.DOC мне разве что во страшных снах не снились :) Но таких полей там нет.
11 ноя 09, 10:09    [7911884]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
автор
Я хорошо знаком со структурой КЛАДРа. И докУменты под названием METODIKA.DOC и OPISKLAD.DOC мне разве что во страшных снах не снились :) Но таких полей там нет.


Да, там нет таких полей.. Но что мешает вводить свои поля, если это помогает поднять скорость выборки? Если у Вас оптимально работает оригинальная структура, замечательно. Мне не удалось в силу своих небольших знаний SQL сделать это не вводя доп. полей. Такой ответ Вас устраивает? :)

автор
А если улицы не должно быть в адресе? ("Москва, Зеленоград, корп.1661" или "N-ская обл, Кукуйкинский р-н, д.Фигуйкино", д.1) :-)


Не совсем понял Ваш вопрос. Моя задача найти адрес, где встречается одно или несколько искомых слов.

По поводу новых индексов - сейчас буду пробовать ))
11 ноя 09, 21:02    [7916877]     Ответить | Цитировать Сообщить модератору
 Re: кладр - сложные запросы  [new]
phoenix78
Member

Откуда:
Сообщений: 14
iljy
phoenix78

Про индексы.
Создал для SEARCH и GENERAL по 5 индексов. В каждый индекс включил NAME, LVL, CODELVL[1-5].

какие именно индексы? Какой порядок полей? тут нужны индексы
для SEARCH

(LVL, Name) include (codelvl1,codelvl2,codelvl3,codelvl4,codelvl5)

для GENERAL

(codelvl1) include (code,lvl)
(codelvl2) include (code,lvl)
(codelvl3) include (code,lvl)
(codelvl4) include (code,lvl)
(codelvl5) include (code,lvl)


Ваш вариант индексов сработал просто великолепно! Большое спасибо! Результат на "Новой Москве" 3 секунды! )

Правда, для сравнения попробовал еще раз свои индексы. Убрал Ваши, добавил свои.. далеко не 4 секунды получилось - 54!!! Не понимаю я, как работают индексы... вчера работало за 4, а сегодня за 54! )) В итоге вернулся к Вашему варианту - стабильно быстро )

Вобщем, надо будет почитать про индексы..

Спасибо всем еще раз!
Прошу прощения, если кого-то задел своей реакцией на ответы.. )))
11 ноя 09, 21:41    [7916978]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить