| Tertium |
| Отправлено: 12.08.2005, 03:25 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Есть запрос, он при различных условиях выполняется различное количество времени, может секунду, а может и час. Юзер априори туп. Поэтому ему надо показывать количество ожидания, на которое он себя обрекает. В средствах MSSQL есть чтонть типа пронозирования колва времени, затрачиваемого на выполнение запроса.
А то руками считать достаточно сложно. Запрос — фактически вызов ХП. В ней достаточно весомые операции, напр. связывание. Повторяемое в цикле. Медленная вещь, но быстрее сделать не могу пока.
Если интересно, вот она:
| CODE | alter proc get_ci (@begin datetime, @end datetime, @minute_period int)
with ENCRYPTION
as
if (@end<@begin)
begin
declare @tmp datetime
set @tmp = @end
set @end = @begin
set @begin = @tmp
end
DECLARE @first_time datetime
DECLARE @last_time datetime
select top 1 @first_time=last_query from stats where club_id in (1,2,3) order by last_query asc
select top 1 @last_time =last_query from stats where club_id in (1,2,3) order by last_query desc
if @begin<@first_time
set @begin=@first_time
if @end>@last_time
set @end=@last_time
DECLARE @last_datime datetime
DECLARE @last_IN bigint
DECLARE @last_OUT bigint
DECLARE @prev_last_IN bigint
DECLARE @prev_last_OUT bigint
DECLARE @actual_record_cnt int
SET @last_IN = 0
SET @last_OUT = 0
SET @prev_last_IN = 0
SET @prev_last_OUT = 0
SET @actual_record_cnt = 0
--не меньше часа, не больше високосного года
if @minute_period<1
SET @minute_period=1
if @minute_period>527040
SET @minute_period=527040
create table #ret_table
(
currentIN bigint,
currentOUT bigint,
last_query datetime,
PRIMARY KEY CLUSTERED
(
[last_query]
)
)
SET @last_datime=@begin
while @last_datime<@end
begin
SET @last_datime=DATEADD(minute,@minute_period,@last_datime)
if @last_datime>@end
set @last_datime=@end
---самый мат
--********************************************************************
Select @last_IN=sum(i1.currentIn), @last_OUT=sum(i1.currentOut) from
(select * from stats where club_id in (1,2,3)) as i1,
(Select club_id,place_no, max(last_query) as max_last_query
from stats where club_id in (1,2,3) and last_query<=@last_datime
Group by club_id,place_no) as t1
where i1.place_no=t1.place_no and i1.club_id=t1.club_id and i1.last_query=t1.max_last_query
--********************************************************************
if @last_IN IS NOT NULL and @last_OUT IS NOT NULL
begin --если не те же данные, что в прошлый раз
if @prev_last_IN!=@last_IN or @prev_last_OUT!=@last_OUT or @last_datime=@end or @last_datime=@begin
begin
insert into #ret_table(currentIN,currentOUT,last_query) values(@last_IN,@last_OUT,@last_datime)
set @actual_record_cnt = @actual_record_cnt + 1
SET @prev_last_IN = @last_IN
SET @prev_last_OUT = @last_OUT
end
end
else --если нет записей, то доход/расход нулевой
begin --если не те же нули, что в прошлый раз
if @prev_last_IN!=0 or @prev_last_OUT!=0 or @last_datime=@end or @last_datime=@begin
begin
insert into #ret_table(currentIN,currentOUT,last_query) values(0,0,@last_datime)
set @actual_record_cnt = @actual_record_cnt + 1
SET @prev_last_IN = 0
SET @prev_last_OUT = 0
end
end
end
if @actual_record_cnt=0
begin
insert into #ret_table(currentIN,currentOUT,last_query) values(0,0,@begin)
insert into #ret_table(currentIN,currentOUT,last_query) values(0,0,@end)
end
--первая строка — число записей
declare @records_number int
select @records_number=count(*) from #ret_table
insert into #ret_table(currentIN,currentOUT,last_query) values(@records_number,@records_number,DATEADD(Year,-1,@begin))
select * from #ret_table order by last_query
drop table #ret_table
|
Отредактировано Tertium — 12/08/2005, 03:35
|
|
| olegenty |
| Отправлено: 12.08.2005, 06:58 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
нет такой возможности. но пройдись по кускам планировщиком. написанный код почти всегда можно улучшить. понаблюдай за блокировками в БД. если дофига — перед началом выполнения основных действий скинь исходные данные во временные таблицы — станешь независим от блокировок реальных таблиц. на маленьких выборках, где в where и join используется только ключ, можешь попробовать табличные переменные.
|
|
| Tertium |
| Отправлено: 12.08.2005, 12:45 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
тут ситуация не совсем в оптимизации текущего кода. Планировщиком проходил, говорит что большая частьвремени уходит на связывание
[CODE]Select @last_IN=sum(i1.currentIn), @last_OUT=sum(i1.currentOut) from
(select * from stats where club_id in (1,2,3)) as i1,
(Select club_id,place_no, max(last_query) as max_last_query
from stats where club_id in (1,2,3) and last_query<=@last_datime
Group by club_id,place_no) as t1
where i1.place_no=t1.place_no and i1.club_id=t1.club_id and i1.last_query=t1.max_last_query [CODE]
и я ему верю. Блокировок нет пока вообще. Вообще интрисует сама методика просчёта времени выполнения нелинейного детерминистичного запроса. Может есть какие-то усреднённые методы, типа пргонки на тестовых картежах, построения графиков а потом апроксимация их функциями?...
|
|
| olegenty |
| Отправлено: 12.08.2005, 13:09 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
не-а, нету. прикинь, на блокировку попадёшь (а рано или поздно при работе с MSSQL — попадёшь), и все твои расчёты — псу в гузно.
так, а план-то какой. Table Scan, index Scan, Index Seek? должны быть только Index Seek... а из Join — Nested Loop — это хуже, Bookark Lookup — получше, Merge — ещё получше.... из чего у тебя текущий план этого места состоит? может индекс-другой накинешь, и резко наступит счастье?
|
|
| Tertium |
| Отправлено: 12.08.2005, 15:26 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Таблица
| CODE | IF NOT EXISTS (select * from sysobjects o where o.name='Stats')
CREATE TABLE [Stats] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[place_no] [tinyint] NOT NULL DEFAULT (0),
[club_id] [int] NOT NULL DEFAULT (0),
[currentIn] [bigint] NOT NULL DEFAULT (0),
[currentOut] [bigint] NOT NULL DEFAULT (0),
[last_query] [datetime] NOT NULL DEFAULT GETDATE(),
PRIMARY KEY CLUSTERED
(
[id] ASC
)
)
IF NOT EXISTS (select * from sysindexes o where o.name='my_unique')
create unique index my_unique on stats
(
[place_no] ASC,
[club_id] ASC,
[last_query] ASC )
with IGNORE_DUP_KEY
|
а план выполнения запроса:
| CODE |
Select sum(i1.currentIn), sum(i1.currentOut) from
(select * from stats where club_id in (1,2,3)) as i1,
(Select club_id,place_no, max(last_query) as max_last_query
from stats where club_id in (1,2,3) and last_query<='2006-08-11 19:12:26.000'
Group by club_id,place_no) as t1
where i1.place_no=t1.place_no and i1.club_id=t1.club_id and i1.last_query=t1.max_last_query
|
такой:
Clustered Index (который primary) Scan------------------¬
stats.my_unique(IndexScan)-- StreamAgregate -- HashMatch|InnerJoin (100%) -- StreamAgregate -- Compute Scalar -- Select
Короче всё кроме хашмача по нулям. Добавлял индекс некластеровый неуникальный по club_id,place_no — та же песня 
|
|
| olegenty |
| Отправлено: 12.08.2005, 15:52 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
вот отсюда и тормоза: clustered index scan монопенисуально table scan, потом создам себе такую табличку, посмотрю, что можно сделать
|
|
| Tertium |
| Отправлено: 12.08.2005, 16:10 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
>>clustered index scan монопенисуально table scan
да, ты знаешь, как сказать... убил ваще нахрен примари индекс — вот он заменил clustered index scan table scan'ом. Время запроса возросло более чем втрое
|
|
| olegenty |
| Отправлено: 13.08.2005, 11:54 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
я это к тому, что Scan — всегда плохо. а Seek — рулит.
|
|
| Tertium |
| Отправлено: 13.08.2005, 12:18 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
судя по смыслу, понятно. вот тока как сделать, чтобы был сик, а не скан?...
|
|
| olegenty |
| Отправлено: 13.08.2005, 12:28 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
у меня нет рецепта. я в таких случаях долго экспериментирую с индексами, чтобы при минимуме индексов получить максимум приятных планов.
|
|
| Tertium |
| Отправлено: 13.08.2005, 12:33 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
а концептуально, что нужно, чтоб был сик?
|
|
| olegenty |
| Отправлено: 13.08.2005, 13:36 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
наличие индекса, поля которого последовательно используются при выполнении запроса. при этом надо учитывать, что один индекс может охватывать как JOIN... ON условия, так и WHERE условия.
|
|
| Tertium |
| Отправлено: 13.08.2005, 14:18 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
ммм... для меня это пока тёмный лес
|
|
| olegenty |
| Отправлено: 15.08.2005, 15:09 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
а ты экспериментируй, тогда, с течением времени, хорошо представляя собственноручно спроектированные таблицы, легко будешь это делать. сложнее всего, это когда БД разрабатывал не сам, но когда "въедешь" в логику создателя БД, достаточно будет навешивать индексы. минус: если БД спроектирована плохо, быстро попадёшь на сиуацию перегрузки таблиц индексами.
|
|
|
