Добро пожаловать в форум, 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

Откуда: Москва
Сообщений: 47198
Cygapb-007
Но в случае одинаковых COLLATE - да, использовались бы оба индекса :)
Необязательно
19 мар 13, 17:20    [14068807]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить