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

Откуда:
Сообщений: 694
1. На интервью мне задали следующий вопрос.

Есть таблица "TableA" с полями "col1" INT и "col2" VARCHAR(512).
Нужно сделать запрос
SELECT * FROM TableA
WHERE col2 = '<полный текст значения поля>'

По мнению интервьюера делать индекс на поле "col2" нецелесообразно из-за его длины

Вопрос интервьюера: как можно убыстрить выполнение указанного запроса, не строя индекс на поле "col2" ?

Я ответить затруднился, может кто-то знает соответствующие методы ?


2. Ещё был вопрос: когда целесообразно использоват temporary table, а когда table variable ?

На этот вопрос я ответил своё мнение, но хотелось бы прочитать мнение коллег.
28 июн 13, 11:54    [14496503]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Бока
1. На интервью мне задали следующий вопрос.

Есть таблица "TableA" с полями "col1" INT и "col2" VARCHAR(512).
Нужно сделать запрос
SELECT * FROM TableA
WHERE col2 = '<полный текст значения поля>'

По мнению интервьюера делать индекс на поле "col2" нецелесообразно из-за его длины

Вопрос интервьюера: как можно убыстрить выполнение указанного запроса, не строя индекс на поле "col2" ?

Я ответить затруднился, может кто-то знает соответствующие методы ?
CHECKSUM (Transact-SQL)
28 июн 13, 12:11    [14496607]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Бока
Вопрос интервьюера: как можно убыстрить выполнение указанного запроса, не строя индекс на поле "col2" ?
+ Пример
use tempdb;
go

create table dbo.TableA
(
 col1 int identity primary key,
 col2 varchar(512)
);
go

insert into dbo.TableA
values
 ('a'),
 ('aa'),
 ('aa'),
 ('aaa'),
 ('aaaa'),
 ('aaaa'),
 ('aaaa'),
 ('b'),
 ('bb'),
 ('bbb'),
 ('bbbb'),
 ('c'),
 ('cc'),
 ('ccc'),
 ('cccc'),
 ('c'),
 ('cccc'),
 ('ccccc');
go 10

insert into dbo.TableA
values
 ('x');
go 10000

set statistics io, xml on;
select col1, col2 from dbo.TableA where col2 = 'ccc';
set statistics io, xml off;
go

alter table dbo.TableA add col3 as checksum(col2);
create index IX_TableA__col3 on dbo.TableA (col3);
go

set statistics io, xml on;
select col1, col2 from dbo.TableA where col3 = checksum('ccc') and col2 = 'ccc';
set statistics io, xml off;
go

drop table dbo.TableA;
go
28 июн 13, 12:21    [14496660]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
OffTop

Бока, а если бы было такое условие
Бока
Есть таблица "TableA" с полями "col1" INT и "col2" VARCHAR(max)
Вы бы ответили?
Задача сферического коня. Просто есть такие адепты которых такое вгоняет в ступор, как эпилептиков.
28 июн 13, 13:01    [14496928]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 635
Бока,
собеседование в Вайлдбериз? :) В моем случае длина поля была 1234, что исключало принципиальную возможность индекса.
Решение: вычисляемый столбец CHECKSUM, и индекс по нему с INCLUDE изначального столбца.
28 июн 13, 15:56    [14498122]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Oblom
Бока,
собеседование в Вайлдбериз? :) В моем случае длина поля была 1234, что исключало принципиальную возможность индекса.
Решение: вычисляемый столбец CHECKSUM, и индекс по нему с INCLUDE изначального столбца.
Надо было длину 901 назначить! :))
28 июн 13, 16:02    [14498170]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iap
Надо было длину 901 назначить! :))
Всё равно искать что-то по простыне текста - странная задача, мягко говоря.
Другое дело дубли ...

А с другой стороны:
автор
Вопрос интервьюера: как можно убыстрить выполнение указанного запроса
Если придираться то CheckSum не решит эту проблему.

Скорее что-то типа:
CREATE INDEX FX_TableA_col2 ON TableA (col2) WHERE col2 = '<полный текст значения поля>'
28 июн 13, 16:47    [14498419]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
Бока
2. Ещё был вопрос: когда целесообразно использоват temporary table, а когда table variable ?
Интересная статья по теме (на английском).

P.S. Меня на нее навел этот пост.
28 июн 13, 17:17    [14498589]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
baracs
Бока
2. Ещё был вопрос: когда целесообразно использоват temporary table, а когда table variable ?
Интересная статья по теме (на английском).

P.S. Меня на нее навел этот пост.

Что то Paul White толком и не раскрыл когда и как использовать временные таблицы и табличные переменные. Доподлинно известно что первые вызывают перекомпиляции, но дают возможность использовать статистику, а вторые перекомпиляцию не вызывают, но и для оптимизатора выступают как таблицы с 1 записью + в табличные переменные вставка меньше стоит из-за особенностей логирования. Вот как я понимаю отличия - тут и думай что и когда лучше.
28 июн 13, 17:32    [14498654]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Мистер Хенки
в табличные переменные вставка меньше стоит из-за особенностей логирования.
Ох уж эти мифы про нелогируемые табличные переменные. Все там честно логируется, просто данные в них не откатываются.

Проверяется просто:

use tempdb
go
if object_id( 'tempdb..#t' ) is not null
    drop table #t
go    
create table #t ( id int )
go
declare @t table ( id int )
checkpoint

insert #t( id ) select 666 union all select 13
select * from sys.fn_dblog(null, null)

checkpoint

insert @t( id ) select 666 union all select 13
select * from sys.fn_dblog(null, null)
go


В резултате все в логе есть, в том числе и вставка двух записей:

К сообщению приложен файл. Размер - 96Kb
28 июн 13, 17:45    [14498685]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
baracs
Member

Откуда: Москва
Сообщений: 7217
Мистер Хенки
baracs
пропущено...
Интересная статья по теме (на английском).

P.S. Меня на нее навел этот пост.

Что то Paul White толком и не раскрыл когда и как использовать временные таблицы и табличные переменные. Доподлинно известно что первые вызывают перекомпиляции, но дают возможность использовать статистику, а вторые перекомпиляцию не вызывают, но и для оптимизатора выступают как таблицы с 1 записью + в табличные переменные вставка меньше стоит из-за особенностей логирования. Вот как я понимаю отличия - тут и думай что и когда лучше.
Так, однозначного ответа, скорее всего, и нет.
А пищу для размышлений статья дает.
28 июн 13, 17:46    [14498686]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Гавриленко Сергей Алексеевич
Ох уж эти мифы про нелогируемые табличные переменные. Все там честно логируется, просто данные в них не откатываются.
Осталось MS написать, зачем так.
28 июн 13, 18:01    [14498743]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Мистер Хенки
в табличные переменные вставка меньше стоит из-за особенностей логирования
Добавлю к вышесказанному: каждая модификация данных в табличной переменной порождает "автономную" транзакцию с автокоммитом, поименованную TVQuery. Так что в некоторых случаях нагрузка на журнал может больше, по сравнению с временной таблицей.
28 июн 13, 18:02    [14498746]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Mnior
Гавриленко Сергей Алексеевич
Ох уж эти мифы про нелогируемые табличные переменные. Все там честно логируется, просто данные в них не откатываются.
Осталось MS написать, зачем так.
Видимо, времянки и переменные - одно и то же с разным синтаксисом, за исчключением фичи "неотката" (это умозрительно сделать довольно просто), чтобы был хотя бы один человеческий способ достать данные, сформированные в откатившейся транзакции (особенно во времена 8.0).
28 июн 13, 18:07    [14498758]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mnior
Осталось MS написать, зачем так.
Потому что они фиксируются. Это самый простой и очевидный способ реализовать "нетранзакционность" табличных переменных.
+ Пример
use tempdb;
go

create table dbo.TestTable (i int);
create table #tr (tid varchar(30), tn sysname);

checkpoint;
go

begin tran TestTransaction;

declare @t table (i int);

insert into dbo.TestTable
select top (10)
 a.object_id
from
 sys.objects a cross join
 sys.objects b

insert into @t
select
 i
from
 dbo.TestTable;

delete top (50) percent from @t; 

commit;
--rollback;
go

insert into #tr
select
 [Transaction ID], [Transaction Name]
from
 fn_dblog(null, null)
where
 SPID = @@spid and
 Operation = 'LOP_BEGIN_XACT' and
 [Transaction Name] in ('TVQuery', 'TestTransaction');
 
select
 t.tn, l.*
from
 #tr t join
 fn_dblog(null, null) l on l.[Transaction ID] = t.tid
order by
 [Current LSN];
go
 
drop table #tr, dbo.TestTable;
go
Обратите внимание на значения в столбце Transaction ID.
28 июн 13, 18:13    [14498776]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Бока
Member

Откуда:
Сообщений: 694
invm
Бока
Вопрос интервьюера: как можно убыстрить выполнение указанного запроса, не строя индекс на поле "col2" ?
+ Пример
use tempdb;
go

create table dbo.TableA
(
 col1 int identity primary key,
 col2 varchar(512)
);
go

insert into dbo.TableA
values
 ('a'),
 ('aa'),
 ('aa'),
 ('aaa'),
 ('aaaa'),
 ('aaaa'),
 ('aaaa'),
 ('b'),
 ('bb'),
 ('bbb'),
 ('bbbb'),
 ('c'),
 ('cc'),
 ('ccc'),
 ('cccc'),
 ('c'),
 ('cccc'),
 ('ccccc');
go 10

insert into dbo.TableA
values
 ('x');
go 10000

set statistics io, xml on;
select col1, col2 from dbo.TableA where col2 = 'ccc';
set statistics io, xml off;
go

alter table dbo.TableA add col3 as checksum(col2);
create index IX_TableA__col3 on dbo.TableA (col3);
go

set statistics io, xml on;
select col1, col2 from dbo.TableA where col3 = checksum('ccc') and col2 = 'ccc';
set statistics io, xml off;
go

drop table dbo.TableA;
go
Спасибо,
а почему
select col1, col2 from dbo.TableA where col3 = checksum('ccc') and col2 = 'ccc';

разве
select col1, col2 from dbo.TableA where col3 = checksum('ccc');
недостаточно ?
28 июн 13, 19:08    [14498901]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Бока
Member

Откуда:
Сообщений: 694
Я считал, что данные в table variable находятся в оперативной памяти, в отличие от temporary table.
Кроме того, table variable имеют restrictions для использованя в dynamic sql (по сравнению с temporary table).
28 июн 13, 19:23    [14498946]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Бока
Я считал, что данные в table variable находятся в оперативной памяти, в отличие от temporary table.
Это тоже миф.
28 июн 13, 19:28    [14498958]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Бока
разве
select col1, col2 from dbo.TableA where col3 = checksum('ccc');
недостаточно ?
Для 40кк уникальных строковых ключей сгенерилось 340к дублирующихся чексум. Т.е. почти каждая сотая запись будет иметь повторяющуюся чексумму.
28 июн 13, 19:40    [14498981]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Это самый простой и очевидный способ реализовать "нетранзакционность" табличных переменных


Чё бы не потролить ...? ;)
28 июн 13, 19:47    [14498992]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
invm
Member

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

Что вас так сильно удивило?
28 июн 13, 20:08    [14499030]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm,

Ну как что. Очевидный способ, как и все ожидают, это отсутствие какого либо логирования вааще.
А tempdb это никакое не db, должно быть, а так - raw data. Чуть ли не файлы подкачки.

Разве tempdb участвует в восстановлении сервера? Для каких механизмов?

Ок, обычные временные таблы "транзакционны", но на них также не распространяется надёжность. Вот ввели в 2005 версионность и inserted/deleted через него работает. Ну что нельзя было по аналогии? Теже механизмы использовать.
Лог, в его стандартном полном понимании тут тоже не нужен.

Более того, идея стримовой записи, как оптимистическая стратегия тут возможно вообще не катит. А если посмотреть не возможное, а типичное использование времянок, так вообще не катит.
Вон хеши(join) заранее просчитываются оптимизатором (скидывать/не скидывать на диск), притом там стрим. А тут тоже надо думать - стоит/не стоит. На подобии своеобразных стратегий гарбидж коллекторов.

Вы меня конечно извините но текущее решение никак в "очевидность" не лезет.
А вот, как обычно "сдать проект по быстрому" и "работает не трогай" - вполне. Стандартное накопление программного долга.

С точки зрения клиента и рядового пользователя (проггера) - продукт очень даже хорош.
С точки зрения разраба баз данных (в вакууме, диванного), имхо выглядит говном.

Так что жду очередных помидоров в мою сторону. Где что я там неучёл/недопонял.

invm
Так что в некоторых случаях нагрузка на журнал может больше, по сравнению с временной таблицей.
Подставил времянку в ваш тест и далеко не увидел меньше логов. Наоборот, раза так под два (@28 vs #45). Ошибся в тесте? Он не всё показывает?
29 июн 13, 02:58    [14500067]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
up

Хотя бы на счёт вопроса "нагрузка на журнал" разных типов времянок.
1 июл 13, 12:35    [14504470]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mnior
Ну как что. Очевидный способ, как и все ожидают, это отсутствие какого либо логирования вааще.
А tempdb это никакое не db, должно быть, а так - raw data. Чуть ли не файлы подкачки.
Т.е. вы считаете, что нужно иметь две Storage Engine: ту, что есть сейчас и специализированную для табличных переменных? Не перебор?
Mnior
Хотя бы на счёт вопроса "нагрузка на журнал" разных типов времянок.
Использование в пользовательской транзакции табличной переменной вместо обычной временной таблицы приведет к появлению в журнале записей LOP_BEGIN_XACT и LOP_BEGIN_XACT/LOP_ABORT_XACT на каждый чих с табличной переменной, т.к. этот самый чих физически будет выполняться в автономной транзакции. А COMMIT приводит к сбросу буфера журнала на диск.
Насчет последнего, в разрезе tempdb, не уверен, но пока что опровержения не нашел.
1 июл 13, 13:29    [14504873]     Ответить | Цитировать Сообщить модератору
 Re: Эффективный поиск по длинному тексту и переменная типа table  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
invm
COMMIT приводит к сбросу буфера журнала на диск.
Насчет последнего, в разрезе tempdb, не уверен, но пока что опровержения не нашел.


"...the commit operations and commited log records are not flushed to disk synchronously in tempdb, as they are in other databases."
Microsoft® SQL Server® 2008 Internals
Авторы: Kalen Delaney,Paul Randal,Kimberly Tripp,Conor Cunningham
2 июл 13, 00:20    [14507798]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить