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

Откуда: Киев
Сообщений: 173
Доброго дня всем!

Никак не могу вьехать, почему так получается. В таблице есть поле, которое содержит дату начала квартала. Например мне надо просуммировать некоторые столбцы в таблице, за весь период, тоесть мне не важна дата в принципе. Так вот, если я делаю приблизительно так:
Вариант№1
select sum(A),sum(B),Контрагент from(
select A,B,Контрагент from .....
where Контрагент="Петров") t
group by Контрагент

и вот так:

Вариант№2
select sum(A),sum(B),Контрагент from(
select A,B,Контрагент from .....
where Контрагент="Петров" and Дата = '01012007'
union all
select A,B,Контрагент from .....
where Контрагент="Петров" and Дата = '01042007'
union all
select A,B,Контрагент from .....
where Контрагент="Петров" and Дата = '01072007'
union all
select A,B,Контрагент from .....
where Контрагент="Петров" and Дата = '01102007'
......и так далее сотни раз за период 10 лет....
group by Контрагент


Так вот, второй вариант выполнится в десятки, а то и в сотни раз быстрее, чем первый. И в том и в другом случае нам нужны данные за все периоды какие есть. Так вот в первом случае, я не указал дату, что является логично, в принципе, а во втором случае я написал киллометровый запрос, указывая дату каждого квартала за период 10-15 лет и этот запрос выполнился в сотни раз быстрее, чем первый.
Меня, в принципе всё устраивало и всё отлично работало, пока в один момент SQL не ругнулся, что количество таблиц в запросе не может быть больше 256-ти.

Помогите, пожалуйста.
Спасибо.

p.s. SQL 2000
9 ноя 09, 20:34    [7904471]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
@@version?

Что с индексами на таблице?
Наверняка есть по дате и нет - по контрагенту...
9 ноя 09, 20:47    [7904513]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
RSRuslan,

план запроса приведите
9 ноя 09, 21:17    [7904622]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Esu
Member

Откуда: Киев
Сообщений: 154
> Меня, в принципе всё устраивало и всё отлично работало, пока в один момент SQL не ругнулся, что количество таблиц в запросе не может быть больше 256-ти.

Ну это легко обходится... Делайте временную таблицу и туда скидываете по одному
insert into #temp_table (A,B,Контрагент)
select sum(A),sum(B),Контрагент from .....
where Контрагент="Петров" and Дата = '01072007'
group by Контрагент


а потом просто select sum(A),sum(B),Контрагент from #temp_table
group by Контрагент


ну а так вообще план нужно смотреть
9 ноя 09, 21:19    [7904633]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
select @@version по максимуму какая там должна быть, обновил всё до sp4 уже давно.
Вариант с темповыми таблицами знаю, пробовал, но он мне не подходит там есть определённые сложности в приложении из которого идёт запрос.
Поле индексированное 100%. План пока предоставить не могу, т.к. не на работе :(
9 ноя 09, 21:51    [7904740]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Проблему удалось решить, чисто эксперементально вот таким способом:

select * from(
select 36 as Сч,sum(ДО) as ДО,sum(КО) as КО,
cast(left(max(Дата),8) as DateTime) as Дата,Номер as Номер,0 as СуммаДок from(
select ttl.obdt1+ttl.obdt2+ttl.obdt3 as ДО,ttl.obkt1+ttl.obkt2+ttl.obkt3 as КО,journ.date_time_iddoc as Дата,
journ.docno as Номер from _1sbkttl ttl (nolock) inner join _1sjourn journ (nolock) on substring(ttl.sc1,5,9)=
journ.iddoc where (ttl.accid='    C8   ') and (ttl.kind='1') and ((ttl.date='19940401') or (ttl.date='19940701') or (ttl.date='19941001') or (ttl.date='19950101') or 
(ttl.date='19950401') or (ttl.date='19950701') or (ttl.date='19951001') or (ttl.date='19960101') or 
(ttl.date='19960401') or (ttl.date='19960701') or (ttl.date='19961001') or (ttl.date='19970101') or 
(ttl.date='19970401') or (ttl.date='19970701') or (ttl.date='19971001') or (ttl.date='19980101') or 
(ttl.date='19980401') or (ttl.date='19980701') or (ttl.date='19981001') or (ttl.date='19990101') or 
(ttl.date='19990401') or (ttl.date='19990701') or (ttl.date='19991001') or (ttl.date='20000101') or 
(ttl.date='20000401') or (ttl.date='20000701') or (ttl.date='20001001') or (ttl.date='20010101') or 
(ttl.date='20010401') or (ttl.date='20010701') or (ttl.date='20011001') or (ttl.date='20020101') or 
(ttl.date='20020401') or (ttl.date='20020701') or (ttl.date='20021001') or (ttl.date='20030101') or 
(ttl.date='20030401') or (ttl.date='20030701') or (ttl.date='20031001') or (ttl.date='20040101') or 
(ttl.date='20040401') or (ttl.date='20040701') or (ttl.date='20041001') or (ttl.date='20050101') or 
(ttl.date='20050401') or (ttl.date='20050701') or (ttl.date='20051001') or (ttl.date='20060101') or 
(ttl.date='20060401') or (ttl.date='20060701') or (ttl.date='20061001') or (ttl.date='20070101') or 
(ttl.date='20070401') or (ttl.date='20070701') or (ttl.date='20071001') or (ttl.date='20080101') or 
(ttl.date='20080401') or (ttl.date='20080701') or (ttl.date='20081001') or (ttl.date='20090101') or 
(ttl.date='20090401') or (ttl.date='20090701') or (ttl.date='20091001') ) and (ttl.vsc0='450') and (ttl.vsc1='452') and (ttl.sc0=(select id from sc174 where code='   45'))) t1
group by Номер having sum(ДО)-sum(КО)<>0
union all
select 37 as Сч,sum(ДО) as ДО,sum(КО) as КО,cast(left(max(Дата),8) as DateTime) as Дата,
Номер as Номер,max(СуммаДок) as СуммаДок from(select ttl.obdt1+ttl.obdt2+ttl.obdt3 as ДО,ttl.obkt1+ttl.obkt2+ttl.obkt3 as КО,
journ.date_time_iddoc as Дата,journ.docno as Номер,DH.sp997 as СуммаДок from _1sbkttl ttl (nolock) inner join 
_1sjourn journ (nolock) on substring(ttl.sc1,5,9)=journ.iddoc inner join DH981 DH (nolock) on substring(ttl.sc1,5,9)=
DH.iddoc where (ttl.accid='    CK   ') and (ttl.kind='1') and ((ttl.date='19940401') or (ttl.date='19940701') or (ttl.date='19941001') or (ttl.date='19950101') or 
(ttl.date='19950401') or (ttl.date='19950701') or (ttl.date='19951001') or (ttl.date='19960101') or 
(ttl.date='19960401') or (ttl.date='19960701') or (ttl.date='19961001') or (ttl.date='19970101') or 
(ttl.date='19970401') or (ttl.date='19970701') or (ttl.date='19971001') or (ttl.date='19980101') or 
(ttl.date='19980401') or (ttl.date='19980701') or (ttl.date='19981001') or (ttl.date='19990101') or 
(ttl.date='19990401') or (ttl.date='19990701') or (ttl.date='19991001') or (ttl.date='20000101') or 
(ttl.date='20000401') or (ttl.date='20000701') or (ttl.date='20001001') or (ttl.date='20010101') or 
(ttl.date='20010401') or (ttl.date='20010701') or (ttl.date='20011001') or (ttl.date='20020101') or 
(ttl.date='20020401') or (ttl.date='20020701') or (ttl.date='20021001') or (ttl.date='20030101') or 
(ttl.date='20030401') or (ttl.date='20030701') or (ttl.date='20031001') or (ttl.date='20040101') or 
(ttl.date='20040401') or (ttl.date='20040701') or (ttl.date='20041001') or (ttl.date='20050101') or 
(ttl.date='20050401') or (ttl.date='20050701') or (ttl.date='20051001') or (ttl.date='20060101') or 
(ttl.date='20060401') or (ttl.date='20060701') or (ttl.date='20061001') or (ttl.date='20070101') or 
(ttl.date='20070401') or (ttl.date='20070701') or (ttl.date='20071001') or (ttl.date='20080101') or 
(ttl.date='20080401') or (ttl.date='20080701') or (ttl.date='20081001') or (ttl.date='20090101') or 
(ttl.date='20090401') or (ttl.date='20090701') or (ttl.date='20091001') ) and (ttl.vsc0='450') and (ttl.vsc1='452') and (ttl.sc0=(select id from sc174 where code='   45'))) table1
group by Номер having sum(ДО)-sum(КО)<>0) table2
order by Сч,Дата

Как видите, вместо "union all" я использую "или" "или" и т.д.. Работает охрененно быстро. В чём прикол так и не понял. Поля Контрагент, Дата да и многие другие индексированные (кроме вида субконто). Вот так вот :)
10 ноя 09, 10:19    [7905953]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
"поле индексированное" - еще ни о чем не говорит.
Нужно смотреть, какие поля и в каком порядке идут в составных индексах.

Параметры типа
ttl.sc0=(select id from sc174 where code='   45'))) 
нужно вычислять ДО запроса.
10 ноя 09, 11:47    [7906641]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
DeColo®es
"поле индексированное" - еще ни о чем не говорит.
Нужно смотреть, какие поля и в каком порядке идут в составных индексах.

Параметры типа
ttl.sc0=(select id from sc174 where code='   45'))) 
нужно вычислять ДО запроса.


Ну скажем, так: Что с вышеуказанной конструкцией, Что указав конкретно id=1234 - на скорость не влияет ни на милисекуну, как не странно. Что так, что эдак - будет выполнено одинаково.
10 ноя 09, 14:03    [7907783]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Esu
Member

Откуда: Киев
Сообщений: 154
(ttl.date='19950401') or (ttl.date='19950701') or (ttl.date='19951001') or (ttl.date='19960101') or
ОМГ

ttl.date in ('19950401', '19950701', ...)
10 ноя 09, 14:28    [7907971]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Esu
Member

Откуда: Киев
Сообщений: 154
on substring(ttl.sc1,5,9)=journ.iddoc inner join DH981 DH (nolock) on substring(ttl.sc1,5,9)=
DH.iddoc where (ttl.accid=' CK ') and (ttl.kind='1')

А вообще это конечно все мегажесть...
mssql определенно мощнейшая платформа... на mysql все бы сдохло уже давно наглухо и надолго )
10 ноя 09, 14:35    [7908020]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
А в чём мега жесть-то?? :)
Вообще-то это бухгалтерская база в 1С 7.7 SQL, за 15 лет, получаю бух итоги прямыми запросами, так быстрее в десятки раз, чем штатными средствами.
10 ноя 09, 18:09    [7909926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
RSRuslan
А в чём мега жесть-то?? :)
Вообще-то это бухгалтерская база в 1С 7.7 SQL, за 15 лет, получаю бух итоги прямыми запросами, так быстрее в десятки раз, чем штатными средствами.
Вы бы лучше описание индексов привели.
Без них ничем, кроме сочуствия помочь сложно, с телепатией пока проблемы.
10 ноя 09, 18:18    [7909983]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
DeColo®es
Вы бы лучше описание индексов привели.
Без них ничем, кроме сочуствия помочь сложно, с телепатией пока проблемы.


А где и что смотреть? Все параметры полей таблицы, как они заданы конструктором? Или что-то другое. Проблему-то я в принципе решил, тем способом, что описал выше, непонятно только почему вот такая горбатость :)
10 ноя 09, 19:24    [7910293]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
результат запроса
exec sp_help '>>>имя таблицы<<<'
10 ноя 09, 21:51    [7910700]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Сделал в QA. Получил много всяких таблиц. Как и в каком формате их сохранять или выложить сюда?
11 ноя 09, 10:11    [7911895]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Достаточно списка полей с типами данных и списка индексов.
Для оформления на форуме есть кнопка "CSV"
123123123
asdasdfasdf
11 ноя 09, 10:47    [7912162]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
DeColo®es
Достаточно списка полей с типами данных и списка индексов.
Для оформления на форуме есть кнопка "CSV"
123123123
asdasdfasdf


Список полей с типами данных:

DATEdatetimeno8 no(n/a)(n/a)NULL
ACCIDcharno9 nononoCyrillic_General_CI_AS
CURRIDcharno9 nononoCyrillic_General_CI_AS
KINDcharno1 nononoCyrillic_General_CI_AS
OBDT1numericno919 3 no(n/a)(n/a)NULL
OBKT1numericno919 3 no(n/a)(n/a)NULL
OBDT2numericno919 3 no(n/a)(n/a)NULL
OBKT2numericno919 3 no(n/a)(n/a)NULL
OBDT3numericno919 3 no(n/a)(n/a)NULL
OBKT3numericno919 3 no(n/a)(n/a)NULL
SDnumericno919 3 no(n/a)(n/a)NULL
FLAGStinyintno13 0 no(n/a)(n/a)NULL
SC0charno13 nononoCyrillic_General_CI_AS
VSC0intno410 0 no(n/a)(n/a)NULL
OSC0tinyintno13 0 no(n/a)(n/a)NULL
SC1charno13 nononoCyrillic_General_CI_AS
VSC1intno410 0 no(n/a)(n/a)NULL
OSC1tinyintno13 0 no(n/a)(n/a)NULL
SC2charno13 nononoCyrillic_General_CI_AS
VSC2intno410 0 no(n/a)(n/a)NULL
OSC2tinyintno13 0 no(n/a)(n/a)NULL


Список индексов:

PK__1SBKTTLclustered unique primary key located on PRIMARYDATE KIND ACCID SC0 SC1 SC2 CURRID


Вроде так :)
11 ноя 09, 11:53    [7912750]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Ну так все понятно - единственный имеющийся в таблице индекс начиается с даты.
Поэтому любой запрос, в котором нет поиска по конкретной дате может только сканировать таблицу.

Создайте еще один индекс по полям accid, kind, vsc0, vsc1, sc0 (в общем по тем, которые в where используются, как необходимые условия - через "and"), и не мучайте себя и сервер бесконечными union all и or.
11 ноя 09, 12:00    [7912821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
DeColo®es
Ну так все понятно - единственный имеющийся в таблице индекс начиается с даты.
Поэтому любой запрос, в котором нет поиска по конкретной дате может только сканировать таблицу.

Создайте еще один индекс по полям accid, kind, vsc0, vsc1, sc0 (в общем по тем, которые в where используются, как необходимые условия - через "and"), и не мучайте себя и сервер бесконечными union all и or.


Почёму один индекс??? Когда я смотрел таблицу в EM конструкторе, то там ключики против многих полей (а это в моём понимании и есть индексы). Ключики были не только напротив даты, но и напротив sc0 sc1 kind и т.д..
И ещё одно. БД сконструирована 1С 7.7, тоесть менять что-либо в ней я не могу, увы :(
Тоесть берите меня такую какая я есть или не берите вообще.
И ещё одно: неужели в SQL запросе нельзя как-то указать, что есть индексированное поле 'Date', используйте его при селектах и т.д. и т.п.. Мне козалось, что для SQL это само собой понятные вещи, про которые и не нужно вспоминать. Если брать 1С, то там я перед выборкой данных конкретно указываю, то есть вот такое вот индексированное поле, мол при выборке используй его, ну и дальше естественно всё работает быстрее. А в сиквеле разве такого нету?
11 ноя 09, 14:20    [7914165]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
RSRuslan
DeColo®es
Ну так все понятно - единственный имеющийся в таблице индекс начиается с даты.
Поэтому любой запрос, в котором нет поиска по конкретной дате может только сканировать таблицу.

Создайте еще один индекс по полям accid, kind, vsc0, vsc1, sc0 (в общем по тем, которые в where используются, как необходимые условия - через "and"), и не мучайте себя и сервер бесконечными union all и or.


Почёму один индекс??? Когда я смотрел таблицу в EM конструкторе, то там ключики против многих полей (а это в моём понимании и есть индексы). Ключики были не только напротив даты, но и напротив sc0 sc1 kind и т.д..

"Ключик" - это признак вхождения поля в ПК, а не признак наличия отдельного индекса по этому полю
11 ноя 09, 14:21    [7914179]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Glory
Member

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

И ещё одно: неужели в SQL запросе нельзя как-то указать, что есть индексированное поле 'Date', используйте его при селектах и т.д. и т.п.. Мне козалось, что для SQL это само собой понятные вещи, про которые и не нужно вспоминать.

Не думайте, что простое наличие индекса по какому-то полю сразу делает использование этого индекса выгодным во всех запросах
11 ноя 09, 14:24    [7914209]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Glory,

Многое стало понятно. Спасибо.
Не понятно только одно - мне выше написали, что "Мол типа всё понятно, у вас только одно поле с индексом - это Date". Почему одно?

Я выполнил команду

exec sp_help '_1sbkttl'

И что я вижу в определённой таблице, не поленюсь набрать:

index_name: PK__1SBKTTL
index_description: clustered, unique, primary key located on PRIMARY
index_keys: DATE, KIND, ACCID, SC0, SC1, SC2, CURRID

Разве это не индексированные поля?
11 ноя 09, 15:38    [7914955]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
RSRuslan
Glory,

Многое стало понятно. Спасибо.
Не понятно только одно - мне выше написали, что "Мол типа всё понятно, у вас только одно поле с индексом - это Date". Почему одно?

Я выполнил команду

exec sp_help '_1sbkttl'

И что я вижу в определённой таблице, не поленюсь набрать:

index_name: PK__1SBKTTL
index_description: clustered, unique, primary key located on PRIMARY
index_keys: DATE, KIND, ACCID, SC0, SC1, SC2, CURRID

Разве это не индексированные поля?

порядок полей в индексе имеет большое значение. этот индекс можно использовать ТОЛЬКО при заданном поле DATE, что вам сразу и сказали. И что вы собственно видите по своему запросу.
11 ноя 09, 15:41    [7914987]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
RSRuslan
И что я вижу в определённой таблице, не поленюсь набрать:

index_name: PK__1SBKTTL
index_description: clustered, unique, primary key located on PRIMARY
index_keys: DATE, KIND, ACCID, SC0, SC1, SC2, CURRID

Разве это не индексированные поля?

Это поля, входящие в индекс(пк). Это не тоже самое, что по каждому полю построить свой отдельный индекс
11 ноя 09, 15:46    [7915033]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться со скоростью выподнения запроса  [new]
RSRuslan
Member

Откуда: Киев
Сообщений: 173
Glory,

Понял. Большое спасибо за помощь!
Уже во всём разобрался.
11 ноя 09, 18:01    [7916045]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить