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

Откуда: Санкт-Петербург
Сообщений: 130
Помогите разобраться. Есть тестовая база, в ней две таблички (структура ниже).
Используются они для хранения ответов на анкеты. Подчиненность такая: есть проекты, в них есть интервью, для каждого интервью есть ответы на вопросы, указываемые кодом ответа (может быть несколько для одного вопроса), или ещё и кодом строки (тогда для каждой отвеченной строки есть обычные ответы). Также могут быть у каждого ответа открытые значения.

В рабоче базе сейчас порядка 50 млн. записей в таблице answers, и она постоянно пополняется.
Также из этой таблицы необходимо делать выборки, пересечения, условия для которых задают сами юзеры, т.е. надо считать - сколько интервью подходят под условие.
Эти условия могут быть простыми, аля:
Q15 == 4 and Q16 > 3

или куда сложнее:
(Q205(code == 1 and valueInt > 0) or Q7205(code == 1 and valueInt > 0)) and 
(Q5053(code >= 2 and code <= 4) or Q75053(code >= 2 and code <= 4)) and 
(Q507(code <= 13 or code == 19) or Q7507(code <= 13 or code == 19)) and Q999 == 9


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

select count(1) from interviews iv where project_id = @projectId and 
((exists(select 1 from answers where (interview_id = iv.id and question_number = 999) and (answer_code = 9)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 10000) and (answer_code = 9)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 10001) and (answer_code = 9))) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 4) and (answer_code = 2)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 6) and (answer_code = 3)) and
(exists(select 1 from answers where (interview_id = iv.id and question_number = 3) and (answer_code = 2)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 3) and (answer_code = 3)))))


Собственно проблема - правильно создать индексы или что-то ещё сделать, чтобы эти запросы выполнялись быстро.
Сейчас ситуация такая, что даже если включить в базе Forced параметризацию - это не помогает, т.к. запросы совершенно хаотичные, и в итоге получаем примерно 4...10 в секунду.
Понятно что если несколько раз выполнять одни и теже запросы (пусть даже с разными параметрами) - всё быстро, но в реальной жизни это не так...

При тестировании гонял запросы как спец.программкой, так и например просто создав скрипт и запуская его вручную.
Примеры 5-ти созданных скриптов тут: скачать
Выполнение первого скрипта 2 минуты 30 секунд. Повторно конечно моментально.
Второго и т.д. - тоже самое.
Если сбросить кэш - опять 2:30...

Беда. Для очистки совести сделал идентичную базу на PostgreSQL... стабильно 500-600 запросов в секунду, а например query1.sql из тех 5-ти запросов выполняется за 1200-1500 ms., т.е. за 1.5 секунды.

Куда крутить? Разница в производительности жуткая, ощущение что где-то галочка снята какая-то...
Всё это так только при разных запросах, постоянно разных.

Кстати запросы тестовые не придуманы, а взяты реально используемые, примерно 29 тыс.штук, по ним и гоняю.

Вот структура:
CREATE TABLE [dbo].[interviews] (
	[id] [int] IDENTITY(1,1) NOT NULL,
	[row_date] [datetime] NOT NULL,
	[respondent_name] [nvarchar](200) NOT NULL,
	[project_id] [int] NOT NULL,
CONSTRAINT [PK_interviews] PRIMARY KEY CLUSTERED ([id] ASC) WITH (...) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[answers] (
	[id] [int] IDENTITY(1,1) NOT NULL,
	[row_date] [datetime] NOT NULL,
	[interview_id] [int] NOT NULL,
	[question_number] [int] NOT NULL,
	[row_code] [int] NOT NULL,
	[answer_code] [int] NOT NULL,
	[value_num] [numeric](18, 2) NULL,
	[value_txt] [nvarchar](255) NULL,
 CONSTRAINT [PK_answers] PRIMARY KEY CLUSTERED ([id] ASC) WITH (...) ON [PRIMARY]
) ON [PRIMARY]


Связаны по ключу:
ALTER TABLE [dbo].[answers]  WITH CHECK ADD  CONSTRAINT [FK_answers_interviews] FOREIGN KEY([interview_id])
REFERENCES [dbo].[interviews] ([id])
ON DELETE CASCADE


В табличке interviews есть индекс по project_id, а в answers были попытки различные индексы создавать, как своим умом, так и после тюнинг адвизора, отдав ему большую пачку запросов.
Ну допустим сейчас такие:

CREATE NONCLUSTERED INDEX [ix0] ON [dbo].[answers] (
	[interview_id] ASC,
	[question_number] ASC,
	[answer_code] ASC
) INCLUDE ([row_code], [value_num]) 
WITH (...) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix1] ON [dbo].[answers] (
	[question_number] ASC,
	[answer_code] ASC
) INCLUDE ([interview_id]) 
WITH (...) ON [PRIMARY]
GO
24 ноя 12, 00:25    [13522324]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Ещё в дополнение тестовая заполненная база: тут.
В ней 2.7 млн. записей в answers и 52 тыс. записей в interviews, с разбивкой на 100 проектов (interviews.project_id)
24 ноя 12, 00:31    [13522333]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Забыл упомянуть - бэкап тестовой базы из Sql Server 2012, т.е. на более ранних не отресторится, как я понимаю.
24 ноя 12, 00:33    [13522335]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
План покажите, да.
24 ноя 12, 00:39    [13522345]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
"вопросов / примеров не читал" (ц), но OR в условиях - издавна первый враг..
особенно с конструкциями "[not] exists or [not] exists"
24 ноя 12, 11:23    [13523064]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
План приведенного выше запроса: тут в виде .sqlplan

  |--Compute Scalar(DEFINE:([Expr1030]=CONVERT_IMPLICIT(int,[Expr1037],0)))
       |--Stream Aggregate(DEFINE:([Expr1037]=Count(*)))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([iv].[id]))
                 |--Nested Loops(Left Semi Join, WHERE:([sstest].[dbo].[answers].[interview_id]=[sstest].[dbo].[interviews].[id] as [iv].[id]))
                 |    |--Nested Loops(Left Semi Join, WHERE:([sstest].[dbo].[answers].[interview_id]=[sstest].[dbo].[interviews].[id] as [iv].[id]))
                 |    |    |--Stream Aggregate(GROUP BY:([iv].[id]))
                 |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([sstest].[dbo].[answers].[interview_id]))
                 |    |    |         |--Sort(ORDER BY:([sstest].[dbo].[answers].[interview_id] ASC))
                 |    |    |         |    |--Concatenation
                 |    |    |         |         |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix1]), SEEK:([sstest].[dbo].[answers].[question_number]=(999) AND [sstest].[dbo].[answers].[answer_code]=(9)) ORDERED FORWARD)
                 |    |    |         |         |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix1]), SEEK:([sstest].[dbo].[answers].[question_number]=(10000) AND [sstest].[dbo].[answers].[answer_code]=(9)) ORDERED FORWARD)
                 |    |    |         |         |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix1]), SEEK:([sstest].[dbo].[answers].[question_number]=(10001) AND [sstest].[dbo].[answers].[answer_code]=(9)) ORDERED FORWARD)
                 |    |    |         |--Index Seek(OBJECT:([sstest].[dbo].[interviews].[IX_interviews] AS [iv]), SEEK:([iv].[project_id]=[@projectId] AND [iv].[id]=[sstest].[dbo].[answers].[interview_id]) ORDERED FORWARD)
                 |    |    |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix1]), SEEK:([sstest].[dbo].[answers].[question_number]=(4) AND [sstest].[dbo].[answers].[answer_code]=(2)) ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix1]), SEEK:([sstest].[dbo].[answers].[question_number]=(6) AND [sstest].[dbo].[answers].[answer_code]=(3)) ORDERED FORWARD)
                 |--Concatenation
                      |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix0]), SEEK:([sstest].[dbo].[answers].[interview_id]=[sstest].[dbo].[interviews].[id] as [iv].[id] AND [sstest].[dbo].[answers].[question_number]=(3) AND [sstest].[dbo].[answers].[answer_code]=(2)) ORDERED FORWARD)
                      |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix0]), SEEK:([sstest].[dbo].[answers].[interview_id]=[sstest].[dbo].[interviews].[id] as [iv].[id] AND [sstest].[dbo].[answers].[question_number]=(3) AND [sstest].[dbo].[answers].[answer_code]=(3)) ORDERED FORWARD)


Насчет:
Crimean
"вопросов / примеров не читал" (ц)

Какие все умные, а... не проще в том же кол-ве буковок подсказать, а не сотрясать воздух пустыми заявлениями?
Поверьте - не первый день за рулем этого пылесоса. И сюда пришел уже после общения с достаточно компетентными DBA по Sql Server.

Crimean
OR в условиях - издавна первый враг..
особенно с конструкциями "[not] exists or [not] exists"

И как с врагом бороться? Исключить из запросов - никак. Т.к. считаться результат должен ровно по написанным пользователям условиям, и как-то его в этом ограничивать - не дело. not и or это очень часто применяемые операторы в выборках, очень важные.

Проблема скорее в том, что каждый конкретный запрос - выполняется быстро, и план, на мой ущербный взгляд - достаточно оптимальный. Но если этих запросов скормить серверу очень много, и все они разные, т.е. планы от предыдущих не подходят... то всё, задумывается жутко. Возможно что-то не то с настройками сервера вообще? Т.к. размер тестовой базы таков, что её можно несколько раз полностью уместить в памяти тестовой машины, и вообще на диск не лезть (хотя оно и не лезет особо).
Либо всё время уходит на придумывание плана запроса... но почему так медленно?
24 ноя 12, 11:47    [13523117]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
qwerty112
Guest
Alexey Trizno
По этим условиям генерируются запросы. В тестовой базе они упрощены, но принцип примерно таков:
...
Собственно проблема - правильно создать индексы или что-то ещё сделать, чтобы эти запросы выполнялись быстро.

переписать запрос / "генерилку" - не вариант ?

вроде так (там что-то не правидьно со скобками в стартовом посте)
select count(*)
from

(select iv.id
from interviews iv inner join answers a
	on a.interview_id = iv.id
where project_id = @projectId 
group by iv.id
having 
	sum(case when question_number in (999, 10000, 10001) and answer_code = 9 then 1 end) > 0
	and
	sum(case when question_number = 4 and answer_code = 2 then 1 end) > 0
	and
	sum(case when question_number = 6 and answer_code = 3 then 1 end) > 0
	and
	sum(case when question_number = 3 and answer_code in (2,3) then 1 end) > 0
) b
24 ноя 12, 12:01    [13523150]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Alexey Trizno
И как с врагом бороться?


универсальное лекарство я знаю только одно - union. да, совершенно согласен, "в лоб" оно не поможет
извините, оперативно поучаствовать не могу - нужно ставить 12 сиквел, это сейчас небольшая проблема
если ваши предположения верны, можно попробовать (как бы глупо это не звучало) поставить "optimize for ad hoc workloads"
и я бы попробовал убрать (временно) ix0 на answers, оставил только ix1
"оно" запрос выполняет как бы почти правильно.. надо взять 4 группы answers и перемножить их - ровно это и происходит
так что к плану вопросы как бы снимаются. вопрос почему медленно компилит новый запрос. один вариант выше - все же попробуйте
второй вариант - как бы, опять, странно это не звучало - поиграйте в конекшен сеты. возможно, некая комбинация и даст улучшение
и независимо от того получится или нет - унифицируйте (если этого еще нет) на 100% (это важно) все конекшен сеты всех приложений, которые "конкурентно" работают с сервером (не с базой, с сервером). хотя бы временно, как версия

пока все, чем могу :)
24 ноя 12, 12:20    [13523189]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
qwerty112
переписать запрос / "генерилку" - не вариант


если я правильно понял и правильно прочитал те планы что уже показали вопрос не в скорости выполнения 1 запроса
а в том что план компилируется слишком долго каждый раз для каждого нового запроса
24 ноя 12, 12:47    [13523217]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
qwerty112
Guest
Crimean
qwerty112
переписать запрос / "генерилку" - не вариант


если я правильно понял и правильно прочитал те планы что уже показали вопрос не в скорости выполнения 1 запроса
а в том что план компилируется слишком долго каждый раз для каждого нового запроса

я в планах - "не очень",
но, имхо, в предложенном мной варианте, для всех запросов ТС (ниже), (если их переписать на having) - будет один и тот же план ... я не прав ?
+
set @cnt = (select count(1) from interviews iv where project_id = @projectId and 
(exists(select 1 from answers where (interview_id = iv.id and question_number = 5023) and (answer_code = 9)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 999) and (answer_code = 9))))
set @sum = @sum + @cnt

set @cnt = (select count(1) from interviews iv where project_id = @projectId and 
((exists(select 1 from answers where (interview_id = iv.id and question_number = 10011) and (answer_code = 4129)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 1009) and (value_num = 4))) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 999) and (answer_code = 9))))
set @sum = @sum + @cnt

set @cnt = (select count(1) from interviews iv where project_id = @projectId and 
(exists(select 1 from answers where (interview_id = iv.id and question_number = 1005) and (answer_code = 123)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 101) and (answer_code = 1)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 102) and (value_num >= 25 and value_num <= 34)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 23))))
set @sum = @sum + @cnt


зы
там бы ещё, при генерации запроса, стоило бы "отсечь" только анализируемые вопросы
...
where project_id = @projectId 
and question_number in (3, 4, 6, 999, 10000, 10001)
group by iv.id
having 
...
24 ноя 12, 13:02    [13523236]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
qwerty112
я в планах - "не очень"


сама задача, если ее реверснуть - надо лазить по answers ( question_number, answer_code ), выбирать по указанным параметрам коллекции interview_id и их или объединять или умножать. чаще - и то и другое
в плане все более чем прилично, невзирая на or и exists. сервер именно этим и занимается - берет подмножества и объединяет / умножает. переписать, конечно, можно, но математический смысл особо не изменится, как и объем данных, требуемых для решения. а лишнего там, вроде как, не поднимается, да и расчетов нет вообще. хотя, если запросы станут "компактнее" - будет только лучше, кто бы спорил

только вопрос, вроде как, стоит не "переписать эффективнее", а "почему тормозит компиляция". напоминаю:

Alexey Trizno
Выполнение первого скрипта 2 минуты 30 секунд. Повторно конечно моментально.
24 ноя 12, 13:20    [13523263]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Всех монстриков типа
select count(1) from interviews iv where project_id = @projectId and 
((exists(select 1 from answers where (interview_id = iv.id and question_number = 999) and (answer_code = 9)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 10000) and (answer_code = 9)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 10001) and (answer_code = 9))) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 4) and (answer_code = 2)) and
exists(select 1 from answers where (interview_id = iv.id and question_number = 6) and (answer_code = 3)) and
(exists(select 1 from answers where (interview_id = iv.id and question_number = 3) and (answer_code = 2)) or
exists(select 1 from answers where (interview_id = iv.id and question_number = 3) and (answer_code = 3)))))
универсализировать. Например так
declare @and_table table (i int identity, question_number int, answer_code int, primary key (question_number, answer_code));
declare @or_table table (question_number int, answer_code int, primary key (question_number, answer_code));

insert into @and_table
values
 (4, 2), (6, 3);
 
insert into @or_table
values
 (3, 2), (3, 3), (999, 9), (10000, 9), (10001, 9);
 
with cte as
(
 select
  a.iv_id
 from
  @and_table t join
  answers a on a.question_number = t.question_number and a.answer_code = t.answer_code
 group by
  a.iv_id
 having
  count(distinct t.i) = (select count(*) from @and_table) 
 
 intersect
 
 select
  a.iv_id
 from
  @or_table t join
  answers a on a.question_number = t.question_number
)
select
 count(*)
from
 cte t join
 intervews iv on iv.project_id = @project_id and iv.iv_id = t.iv_id;
Т.е., в конечном итоге, оформить как одну процедуру с табличными параметрами.
24 ноя 12, 13:36    [13523311]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
И так, попробовал предложенное. По порядку.

1) включение "optimize for ad hoc workloads" - никак не влияет на результат
2) а вот переписывание генерилки запросов, чтобы работало через having/sum, дает какой-то результат,
примерно в 3-5 раз ускорение, но, если опять же исполнять 1000 случайных запросов,
получаем первый запуск 30 секунд (было 2:30), а второй и последующие 15 секунд (с exists было моментально).
Стало интереснее, но не приемлемо всё равно.
3) играние с коннекшин-сеттингами в тестовом приложении исключаем, т.к. сейчас тестируем просто 1000 случайных
запросов собранных в один файл и выполняем из SQL Server Management Studio.
4) переписать запросы, как указал invm, как мне кажется совсем не просто, т.к. все они генерятся автоматом, и там может быть указано что угодно, не только and/or/not, а любые комбинации, любая вложенность проверок и условий.

Сейчас запросы получаются генерилкой в таком виде:
select coalesce(count(*), 0) from 
(select iv.id from interviews iv inner join answers a on (a.interview_id = iv.id) 
where project_id = @projectId 
group by iv.id 
having 
(sum(case when (question_number = 5 and (answer_code = 5)) then 1 else 0 end) > 0 and
sum(case when (question_number = 2 and (answer_code = 2)) then 1 else 0 end) > 0 and
sum(case when (question_number = 3 and (value_num >= 18 and value_num <= 24)) then 1 else 0 end) > 0 and
sum(case when (question_number = 999 and (answer_code = 9)) then 1 else 0 end) > 0)
) b

План запроса:
  |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1008] IS NOT NULL THEN [Expr1008] ELSE (0) END))
       |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1027],0)))
            |--Stream Aggregate(DEFINE:([Expr1027]=Count(*)))
                 |--Filter(WHERE:([Expr1004]>(0) AND [Expr1005]>(0) AND [Expr1006]>(0) AND [Expr1007]>(0)))
                      |--Stream Aggregate(GROUP BY:([iv].[id]) DEFINE:([Expr1004]=SUM([Expr1011]), [Expr1005]=SUM([Expr1012]), [Expr1006]=SUM([Expr1013]), [Expr1007]=SUM([Expr1014])))
                           |--Compute Scalar(DEFINE:([Expr1011]=CASE WHEN [sstest].[dbo].[answers].[question_number] as [a].[question_number]=(5) AND [sstest].[dbo].[answers].[answer_code] as [a].[answer_code]=(5) THEN (1) ELSE (0) END, [Expr1012]=CASE WHEN [sstest].[dbo].[answers].[question_number] as [a].[question_number]=(2) AND [sstest].[dbo].[answers].[answer_code] as [a].[answer_code]=(2) THEN (1) ELSE (0) END, [Expr1013]=CASE WHEN [sstest].[dbo].[answers].[question_number] as [a].[question_number]=(3) AND [sstest].[dbo].[answers].[value_num] as [a].[value_num]>=(18.00) AND [sstest].[dbo].[answers].[value_num] as [a].[value_num]<=(24.00) THEN (1) ELSE (0) END, [Expr1014]=CASE WHEN [sstest].[dbo].[answers].[question_number] as [a].[question_number]=(999) AND [sstest].[dbo].[answers].[answer_code] as [a].[answer_code]=(9) THEN (1) ELSE (0) END))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:([iv].[id], [Expr1026]) WITH UNORDERED PREFETCH)
                                     |--Index Seek(OBJECT:([sstest].[dbo].[interviews].[interviews_project_id] AS [iv]), SEEK:([iv].[project_id]=[@projectId]) ORDERED FORWARD)
                                     |--Index Seek(OBJECT:([sstest].[dbo].[answers].[ix0] AS [a]), SEEK:([a].[interview_id]=[sstest].[dbo].[interviews].[id] as [iv].[id]) ORDERED FORWARD)

Возможно есть смысл поиграться теперь с индексами по answers?
Для последней версии запросов какие оптимально сделать индексы?

Сейчас показатели скорости такие:
SQL Server...
MS: 100 (36,59/sec.)
MS: 200 (26,4/sec.)
MS: 300 (36,8/sec.)
MS: 400 (22,84/sec.)
MS: 500 (21,19/sec.)
MS: 600 (50,43/sec.)
MS: 700 (39,03/sec.)
MS: 800 (23,65/sec.)
MS: 900 (27,64/sec.)
MS: 1000 (26,21/sec.)
query execution: min 0ms., max 156 ms., avg 34 ms.
24 ноя 12, 14:38    [13523479]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Alexey Trizno,

а есть возможность собрать тестовую базку под 2008 (R2)?
чтобы не разворачивать 2012 инстанс? тогда плотнее смогу подключиться
если я правильно понимаю проблему - вопрос именно в скорости компиляции, скорость выполнения пока вторична
24 ноя 12, 14:44    [13523503]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
qwerty112
Guest
Alexey Trizno,

а вот это условие
qwerty112
зы
там бы ещё, при генерации запроса, стоило бы "отсечь" только анализируемые вопросы
...
where project_id = @projectId 
and question_number in (3, 4, 6, 999, 10000, 10001)
group by iv.id
having 
...

нет возможности при генерации запроса учесть ?
24 ноя 12, 14:47    [13523512]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Да, есть. Сейчас сделаю тестовую под R2.
Заполнение займет какое-то время.

Кстати, т.к. я сравниваю с PostgreSQL, всё пробую и на нем.
Запросы с having/sum там исполняются где-то 60-100 в секунду, против 30-50 на ms sql.
Но при этом старые с тупым exists четко по 500-600 в секунду... против <10 штук на ms.
Чудеса.
24 ноя 12, 14:49    [13523515]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
qwerty112
а вот это условие
нет возможности при генерации запроса учесть ?


Это уже сильно сложно, т.к. уже уходим в теорию компиляторов... :)
Построитель запросов достаточно простой, оптимизацию и анализ того что там получается - не делает.
24 ноя 12, 15:01    [13523529]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Crimean
а есть возможность собрать тестовую базку под 2008 (R2)?


Вот под 2008 R2: тут
Тут три sql-файлика с 1000 случайных запросов на новый лад: тырк
Ну а тут старые пять с exists: тырк
24 ноя 12, 15:24    [13523561]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Alexey Trizno,

может в аське / скайпе / гтолке будет удобнее?
и - это у вас "запросики" такие по полмега?
неудивительно, что они парсятся столько
24 ноя 12, 16:09    [13523640]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
ну, что можно сказать..
первое - индексы не очень. но это совсем мелочи. но я бы как-то так попробовал:

create index I_interviews_1 on dbo.interviews( project_id , id )

create unique clustered index I_answers_1 on dbo.answers( interview_id , id )
create index I_answers_2 on dbo.answers( question_number, value_num )
create index I_answers_3 on dbo.answers( question_number, answer_code )

это достаточно примерно - надо будет еще посмотреть что выгоднее. возможно, стоит оставить ( question_number, interview_id ) в ключе, а остальное - в include всунуть. но это, опять же, картины НЕ меняет

второе - ТАКОЙ батч (полмега) компилироваться действительно будет долго. да, можно "поэстэтствовать" и прописать схемы и т.д. но это мало влияет - даже элементарный ОДИН запрос из батча компилируется порядка 200 ms. подход в целом весь неверен.

третье - влияние параметризации для базы и/или "optimize for ad hoc" - несущественно, хотя, казалось бы..

четвертое - хинты типа "KEEP PLAN" нас тоже не спасают

что получилось "поймать":

если мы батч разбиваем на элементарные запросы
в каждом запросе переменные меняем на константы
в конец запроса дописываем "option( force order )"
получаем практически полное отсутствие затрат на компиляцию - 10-20 ms вместо 200 ms - на порядок точно
влияния параметризации и "optimize for ad hoc" на происходящее я не увидел
цена вопроса - разбивать полумегабайтный батч на элементарные запросы
24 ноя 12, 16:38    [13523687]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Конечно в реальной работе все эти запросы выполняются отдельно, поштучно.
Есть тестовая программа, гоняющая эти запросы поштучно, случайным образом, максимально приближено к реальной работе. Могу собрать.
От неё как раз вывод со скоростью приводил выше.

В один файл собрал просто чтобы исключить влияние самой программы.
24 ноя 12, 18:30    [13523875]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Alexey Trizno,

ну так добавьте указанный хинт. у меня от его добавления время компиляции от 200 до 20 ms упало. то есть в целом должно грубо в 10 раз ускориться выполнение. а при адекватном индексировании ваши запросы ничего не стоят сами по себе даже без переписывания

вариант с хранимкой в этом случае потенциально очень интересен так как уберет затраты на компиляцию вообще. но будет ли он эффективен с точки зрения вычиток - вопрос - использование @таблиц в отличие от #таблиц не дает оптимизатору возможности использовать статистики
24 ноя 12, 18:46    [13523908]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Crimean
ну так добавьте указанный хинт. у меня от его добавления время компиляции от 200 до 20 ms упало. то есть в целом должно грубо в 10 раз ускориться выполнение. а при адекватном индексировании ваши запросы ничего не стоят сами по себе даже без переписывания


Разницы от force order практически нет:
обычные запросы:

SQL Server...
MS: 100 (26,69/sec.)
MS: 200 (24,19/sec.)
MS: 300 (26,34/sec.)
MS: 400 (36,4/sec.)
MS: 500 (42,01/sec.)
query execution: min 0ms., max 63 ms., avg 33 ms.

добавлена option (force order):

SQL Server...
MS: 100 (69,68/sec.)
MS: 200 (32,83/sec.)
MS: 300 (29,8/sec.)
MS: 400 (52,33/sec.)
MS: 500 (66,93/sec.)
query execution: min 0ms., max 62 ms., avg 22 ms.
24 ноя 12, 20:20    [13524144]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
Crimean
первое - индексы не очень. но это совсем мелочи. но я бы как-то так попробовал:

create index I_interviews_1 on dbo.interviews( project_id , id )

create unique clustered index I_answers_1 on dbo.answers( interview_id , id )
create index I_answers_2 on dbo.answers( question_number, value_num )
create index I_answers_3 on dbo.answers( question_number, answer_code )


Такие индексы только заметно ухудшили ситуацию:
SQL Server...
MS: 100 (7,46/sec.)
MS: 200 (7,56/sec.)
MS: 300 (7,7/sec.)
MS: 400 (7,62/sec.)
MS: 500 (7,6/sec.)
query execution: min 16ms., max 172 ms., avg 131 ms.
24 ноя 12, 20:29    [13524160]     Ответить | Цитировать Сообщить модератору
 Re: Запросы по случайным where... что не так?  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 130
В тоже время на PostgreSQL по прежнему:
PostgreSQL...
PG: 100 (325,71/sec.)
PG: 200 (442,45/sec.)
PG: 300 (327,85/sec.)
PG: 400 (303,01/sec.)
PG: 500 (357,12/sec.)
query execution: min 0ms., max 16 ms., avg 2 ms.


Это с exists и одним простым индексом в answers по interview_id, question_number, answer_code
24 ноя 12, 20:36    [13524178]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить