Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск в дереве с 20 млн записей  [new]
Priest1234567
Guest
Добрый вечер. Есть у меня дерево территориальных объектов вида

CREATE TABLE [dbo].[TerritoryObject](
	[OID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[TerritoryObjectType] [bigint] NOT NULL,
	[CodeKLADR] [nvarchar](30) NULL,
	[Index] [int] NULL,
	[OKATO] [nvarchar](20) NULL,
	[Name] [nvarchar](200) NULL,
	[Owner] [bigint] NULL,
	[OptimisticLockField] [int] NULL,
	[GCRecord] [int] NULL,
	[TerritoryMunicipalUnion] [bigint] NULL,
	[CodeAutonomy] [nvarchar](10) NULL,
	[CodeRegion] [nvarchar](10) NULL,
	[CodeCity] [nvarchar](10) NULL,
	[CodeIntraCityDistrict] [nvarchar](10) NULL,
	[CodeLocality] [nvarchar](10) NULL,
	[CodeStreet] [nvarchar](10) NULL,
	[CodeAdditionElement] [nvarchar](10) NULL,
	[CodeChildAdditionElement] [nvarchar](10) NULL,
	[CodeArea] [nvarchar](10) NULL,
	[FullName] [nvarchar](300) NULL,
	[TypeCaption] [nvarchar](100) NULL,
	[TerritoryObjectSubType] [bigint] NULL,
 CONSTRAINT [PK_TerritoryObject] PRIMARY KEY CLUSTERED 
(
	[OID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



И вот нужно ускорить клиентский запрос вида
exec sp_executesql N'select N0."OID",N0."TerritoryObjectType",N0."CodeKLADR",N0."OKATO",N0."TerritoryMunicipalUnion",N0."CodeAutonomy",N0."CodeArea",N0."CodeCity",N0."CodeIntraCityDistrict",N0."CodeLocality",N0."CodeStreet",N0."CodeAdditionElement",N0."CodeChildAdditionElement",N0."CodeRegion",N0."Index",N0."FullName",N0."Name",N0."TerritoryObjectSubType",N0."Owner",N0."OptimisticLockField",N0."GCRecord" from "dbo"."TerritoryObject" N0
where (N0."GCRecord" is null and (isnull(CharIndex(@p0, N0."Name"), 0) > 0) and (isnull(CharIndex(@p1, N0."Name"), 0) > 0))
',N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'страна',@p1=N'россия'


Запрос генерится программно и изменить его нельзя. Т.е. нужно крутить на уровне БД
26 дек 12, 23:44    [13692230]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9687
С предикатами вида isnull(CharIndex(@p0, N0."Name"), 0) > 0, на уровне БД крутить нечего.
26 дек 12, 23:48    [13692242]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4538
Priest1234567
Запрос генерится программно и изменить его нельзя

Стоит обратиться к разработчикам этого чуда... Возможно, существует какойнить workaround, позволяющий изменить запрос отправляемый в БД...
26 дек 12, 23:54    [13692265]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Владимир Затуливетер
Member

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

а вот таких записей сколько?
select  count(*)
from    dbo.TerritoryObject
where   GCRecord is null    
27 дек 12, 00:13    [13692323]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
Priest1234567
Запрос генерится программно и изменить его нельзя. Т.е. нужно крутить на уровне БД
Не получится, только более мощный сервер.

А почему не получится изменить запрос? Это же не серийный продукт, могут и подкрутить.

Или просто запостить баг разработчику, пусть исправляет.
27 дек 12, 00:36    [13692442]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
aleks2
Guest
alexeyvg
Priest1234567
Запрос генерится программно и изменить его нельзя. Т.е. нужно крутить на уровне БД
Не получится, только более мощный сервер.

А почему не получится изменить запрос? Это же не серийный продукт, могут и подкрутить.

Или просто запостить баг разработчику, пусть исправляет.


А чо, слабо sp_executesql подменить?
27 дек 12, 08:36    [13692919]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
aleks2
alexeyvg
А почему не получится изменить запрос? Это же не серийный продукт, могут и подкрутить.

Или просто запостить баг разработчику, пусть исправляет.


А чо, слабо sp_executesql подменить?
Ну, как то это слишком радикально и сложно (в логике) :-)

Проще написать разработчику.

Хотя, может быть, если такой проблемный запрос один...
27 дек 12, 08:58    [13692973]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
aleks2
alexeyvg
пропущено...
Не получится, только более мощный сервер.

А почему не получится изменить запрос? Это же не серийный продукт, могут и подкрутить.

Или просто запостить баг разработчику, пусть исправляет.


А чо, слабо sp_executesql подменить?


да, и как же?

судя по тексту запроса видимо выдран он из профайлера, потому если менять, то сам текст запроса, по живому
hex редактором
27 дек 12, 22:06    [13697591]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
Winnipuh
aleks2
пропущено...


А чо, слабо sp_executesql подменить?


да, и как же?

судя по тексту запроса видимо выдран он из профайлера, потому если менять, то сам текст запроса, по живому
hex редактором
Каким ещё hex редактором?

Не, технически это вполне осуществимо.

Делаем свою процедуру sp_executesql. В ней по тексту переданного запроса анализируем, этот запрос или какой то другой.

Если другой, то вызываем старую процедуру sp_executesql, если этот, то вызываем совершенно другой запрос, который будет работать с другой структурой данных (с самодельным полнотекстовым инедксом для индексированного поиска по фрагментам слов).

Но это конечно совсем сумашедшая идея, с гемморойной реализацией и последующей поддержкой, и работать будет только с определёнными запросами.

Проще заставить переделать прогу.
27 дек 12, 23:18    [13697880]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
alexeyvg
Winnipuh
пропущено...


да, и как же?

судя по тексту запроса видимо выдран он из профайлера, потому если менять, то сам текст запроса, по живому
hex редактором
Каким ещё hex редактором?

Не, технически это вполне осуществимо.

Делаем свою процедуру sp_executesql. В ней по тексту переданного запроса анализируем, этот запрос или какой то другой.

Если другой, то вызываем старую процедуру sp_executesql, если этот, то вызываем совершенно другой запрос, который будет работать с другой структурой данных (с самодельным полнотекстовым инедксом для индексированного поиска по фрагментам слов).

Но это конечно совсем сумашедшая идея, с гемморойной реализацией и последующей поддержкой, и работать будет только с определёнными запросами.

Проще заставить переделать прогу.


идея понятна.
Хекс редактор имелся в виду - если клиентское приложение ехе, то в нем и исправить запрос
27 дек 12, 23:19    [13697884]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
Winnipuh
Хекс редактор имелся в виду - если клиентское приложение ехе, то в нем и исправить запрос
Не, это ещё экстремальнее :-)
27 дек 12, 23:42    [13697945]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17285
Function Based Index прикрутить?
28 дек 12, 00:59    [13698103]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31823
ScareCrow
Function Based Index прикрутить?
А что это такое, можно ссылку?
28 дек 12, 08:14    [13698358]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Priest1234567
Guest
Откуда: Земля > Марс > Земля
Сообщений: 222


Priest1234567,

а вот таких записей сколько?
select count(*)
from dbo.TerritoryObject
where GCRecord is null



Этот запрос вернет мне 99,9% записей

А вообще я как понимаю основная проблема с (isnull(CharIndex(@p0, N0."Name"), 0) > 0)? Т.е. мне как то надо вклиниться в генератор кода и поменять на N0."Name" like '%' + @p0 + '%'
Т.е. получится унифицированный код, который везде будет править такие проблемы? Я верно понимаю?
28 дек 12, 11:20    [13699393]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Priest1234567
Guest
alexeyvg
ScareCrow
Function Based Index прикрутить?
А что это такое, можно ссылку?



Я так понимаю это актуально для Оракла. А у меня MS Sql Server. Да и CharIndex это встроенная в субд функция
28 дек 12, 11:23    [13699416]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Priest1234567
А вообще я как понимаю основная проблема с (isnull(CharIndex(@p0, N0."Name"), 0) > 0)? Т.е. мне как то надо вклиниться в генератор кода и поменять на N0."Name" like '%' + @p0 + '%'
Т.е. получится унифицированный код, который везде будет править такие проблемы? Я верно понимаю?

Проблему такое исправление не решит. Т.к. проблема заключается в сканировании индекса (кластерного либо такого, который включает в себя столбец Name). Чтобы SQL Server мог использовать быстрый index seek, запросы должны быть вида N0.Name like @p0 + '%'.

Можете ещё посмотреть в сторону полнотекстового поиска.
28 дек 12, 12:11    [13699738]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Подменить вполне возможно, хотя и не просто. Мы например свой провайдер разработали на уровне подмены netlibe, oledb(осталось с найтивом разобраться). Сложный, быстрый парсинг и корректную подмену делать - задача тоже не тривиальная. Но в данном случае все значительно проще. Хотя опять таки в вашем случае не совсем понятно нужен ли поиск на вхождение по всей подстроке(%блабла%) или по первым символам(блабла%). Если нужен поиск по всей подстроке - ничего не подалешь. Однозначно скан всего множества.
28 дек 12, 12:23    [13699820]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg
технически это вполне осуществимо.

Делаем свою процедуру sp_executesql. В ней по тексту переданного запроса анализируем, этот запрос или какой то другой.

Если другой, то вызываем старую процедуру sp_executesql, если этот, то вызываем совершенно другой запрос, который будет работать с другой структурой данных (с самодельным полнотекстовым инедксом для индексированного поиска по фрагментам слов).

Это теория, или так можно сделать в реальности? У меня вот не получается:

create procedure sp_executesql @statement nvarchar(max)
as print 'Фиг вам, а не динамический SQL!';
go
exec sp_executesql N'print 1234';
go
drop procedure sp_executesql;
go

Результат:
Messages
1234
28 дек 12, 12:38    [13699941]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17285
alexeyvg
ScareCrow
Function Based Index прикрутить?
А что это такое, можно ссылку?

http://msdn.microsoft.com/en-us/library/ms188783.aspx

автор
Computed Columns

Indexes can be created on computed columns. In addition, computed columns can have the property PERSISTED. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.
28 дек 12, 15:47    [13701364]     Ответить | Цитировать Сообщить модератору
 Re: Поиск в дереве с 20 млн записей  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17285
Indexes on Computed Columns
28 дек 12, 15:48    [13701373]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить