Влияние Collate на запрос / Microsoft SQL Server / Sql.ru

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Microsoft SQL Server

Влияние Collate на запрос [new]
relief Member Откуда: Сообщений: 1197	Привет. К примеру есть простой запрос SELECT * FROM People p JOIN Citizens c ON p.LastName = c.LastName К примеру изменится collation у поля LastName у одной из таблиц. И соотв-но надо будет в джойне писать COLLATE .... Вопрос, как повлияет COLLATE в джойне на запрос? Будет ли использован индекс по полю LastName, если он есть? Как повлияет на производительность это?
19 мар 13, 11:00 [14065984] Ответить \| Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615

relief

Привет.

К примеру есть простой запрос

SELECT * FROM People p 
  JOIN Citizens c 
   ON p.LastName = c.LastName

К примеру изменится collation у поля LastName у одной из таблиц.
И соотв-но надо будет в джойне писать COLLATE ....

Вопрос, как повлияет COLLATE в джойне на запрос?
Будет ли использован индекс по полю LastName, если он есть?
Как повлияет на производительность это?

там где будешь писать COLLATE индекс использоваться не будет.

19 мар 13, 11:03 [14065997] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

relief
Member

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

Мистер Хенки
там где будешь писать COLLATE индекс использоваться не будет.

смотрите. вот простой скрипт. Почему срабатывает индекс на OldCities?

CREATE TABLE [dbo].[OldCities](
	[CityId] [int] NOT NULL,
	[CityName] [nvarchar](50)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[NewCities](
	[CityId] [int] NOT NULL,
	[CityName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

GO

/****** Object:  Index [ix_newcities]    Script Date: 03/19/2013 11:41:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ix_newcities] ON [dbo].[NewCities] 
(
	[CityName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [ix_newcities]    Script Date: 03/19/2013 11:41:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ix_oldcities] ON [dbo].[OldCities] 
(
	[CityName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

insert [dbo].[OldCities] select 1, 'Moscow'
insert [dbo].[NewCities] select 1, 'Moscow'
go

select * from [dbo].[OldCities] o 
 join [dbo].[NewCities] n 
  on o.[CityName] = n.[CityName] collate SQL_Latin1_General_CP1_CI_AS

19 мар 13, 11:43 [14066264] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Гость333
Member

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

relief
select * from [dbo].[OldCities] o join [dbo].[NewCities] n on o.[CityName] = n.[CityName] collate SQL_Latin1_General_CP1_CI_AS

Этот запрос, условно говоря, аналогичен следующему:

select * from dbo.OldCities o 
 join dbo.NewCities n 
  on o.CityName = F(n.CityName)

где F — функция, приводящая n.CityName к сортировке SQL_Latin1_General_CP1_CI_AS.

Если заменить сортировку на Latin1_General_CI_AS — будет использоваться индекс по n.[CityName], т.к. запрос будет аналогичен такому:

select * from dbo.OldCities o 
 join dbo.NewCities n 
  on G(o.CityName) = n.CityName

где G — функция приведения строки к сортировке Latin1_General_CI_AS

Если изменить сортировку на какую-то третью (например, Latin1_General_CI_AI), то индексы не будут использованы, т.к. в запросе

select * from dbo.OldCities o 
 join dbo.NewCities n 
  on H(o.CityName) = H(n.CityName)

об использовании индексов уже речи не идёт.

19 мар 13, 12:14 [14066480] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]
Мистер Хенки Member Откуда: канализация Сообщений: 6615	Гость333, точно и развернуто.
19 мар 13, 12:17 [14066497] Ответить \| Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

relief
Member

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

Гость333
...

теорию я понял. но не понял, почему Мистер Хенки сказал

автор
там где будешь писать COLLATE индекс использоваться не будет

а он использовался

19 мар 13, 12:32 [14066583] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

dalex1973
Member

Откуда: Польша
Сообщений: 287

relief

Гость333
...

теорию я понял. но не понял, почему Мистер Хенки сказал

автор
там где будешь писать COLLATE индекс использоваться не будет

а он использовался

Перепишем запрос

select * from dbo.OldCities o 
 join dbo.NewCities n 
  on o.CityName = F(n.CityName)

как

select * from dbo.OldCities o,dbo.NewCities n 
  WHERE o.CityName = F(n.CityName)

теперь вопросов быть не должно.

19 мар 13, 13:15 [14066929] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615

relief,

relief

Гость333
...

теорию я понял. но не понял, почему Мистер Хенки сказал

автор
там где будешь писать COLLATE индекс использоваться не будет

а он использовался

Преобразование collate это функция,которая не позволяет использовать индекс на том поле к которому эта функция применяется. в примере функция применяется к полю n.[CityName], поменяй в запросе на COLLATE SQL_Latin1_General_CP1_CI_AS, то как обьяснил Гость333 и получится функция на o.[CityName] и уже поучится, что индекс по o.[CityName] не используется. Если знаешь заранее порядок соединения таблиц, то внешнюю таблицу можно присоединить используя индекс, если преобразовывать вывод из внутреннего соединения к соответствующему collation. Но это если принудительно задаешь порядок соединения или уверен в нем, а иначе оптимизатор может сам определить порядок и поменять внешнее и внутренние множества местами, тут уж поиск по индексу не получится . Так что разные collation добавят сложностей в оптимизации запросов .

19 мар 13, 13:23 [14066980] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

relief
Member

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

dalex1973

relief
пропущено... теорию я понял. но не понял, почему Мистер Хенки сказал пропущено... а он использовался

Перепишем запрос

select * from dbo.OldCities o 
 join dbo.NewCities n 
  on o.CityName = F(n.CityName)

как

select * from dbo.OldCities o,dbo.NewCities n 
  WHERE o.CityName = F(n.CityName)

теперь вопросов быть не должно.

если честно, непонятно
я хочу понять, почему при указании COLLATE всё равно используется индекс одной из таблиц

19 мар 13, 13:39 [14067132] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Glory
Member

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

relief
если честно, непонятно я хочу понять, почему при указании COLLATE всё равно используется индекс одной из таблиц

Потому что только одно из полей приводится к другой COLLATE

19 мар 13, 13:49 [14067212] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

dalex1973
Member

Откуда: Польша
Сообщений: 287

relief
если честно, непонятно я хочу понять, почему при указании COLLATE всё равно используется индекс одной из таблиц

На пальцах: потому что o.CityName=X - это SARG а F(n.CityName)=X - не SARG. Хотя в случае с JOIN-ом оптимизатор работает по-другому.

19 мар 13, 13:50 [14067218] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

relief
Member

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

Glory

relief
если честно, непонятно я хочу понять, почему при указании COLLATE всё равно используется индекс одной из таблиц

Потому что только одно из полей приводится к другой COLLATE

а т.е. если бы не указывал COLLATE, то использовались бы оба индекса?

19 мар 13, 16:27 [14068417] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Glory
Member

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

relief
а т.е. если бы не указывал COLLATE, то использовались бы оба индекса?

Нет. Было бы сообщение об ошибке.
Разве так трудно проверить ?

19 мар 13, 16:29 [14068441] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

Гость333
Member

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

relief
а т.е. если бы не указывал COLLATE, то использовались бы оба индекса?

Если не указать collate, то вообще ни одного индекса не используется :-)


Msg 468, Level 16, State 9, Line 3 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

19 мар 13, 16:30 [14068448] Ответить | Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]
Cygapb-007 Member Откуда: Сообщений: 1677	Но в случае одинаковых COLLATE - да, использовались бы оба индекса :)
19 мар 13, 16:52 [14068614] Ответить \| Цитировать Сообщить модератору

Re: Влияние Collate на запрос [new]

iap
Member

Откуда: Москва
Сообщений: 47052

Cygapb-007
Но в случае одинаковых COLLATE - да, использовались бы оба индекса :)

Необязательно

19 мар 13, 17:20 [14068807] Ответить | Цитировать Сообщить модератору

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)