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

Откуда: Россия г.Оренбург
Сообщений: 349
1. Есть какое-то количество таблиц, содержащее какое-то одно ключевое символьное поле, назовём его userlast
2. В каждой из этих таблиц может быть много записей с данным значением
Необходимо одним запросом собрать все эти таблицы и выбрать количество записей по ключевому полю.

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

1) Выбрать наименование таблиц можно запросом:

SELECT t.name as tn, c.Name as cn, c.max_length as leng FROM sys.tables AS t INNER JOIN sys.columns AS c ON 
 t.object_id = c.object_id where c.name like '%user%' and t.name not in ('SprUser','Monitor','DogUser') ORDER BY t.name, c.Column_id


Данный запрос мне возвращает 39 записей, это значит что есть 39 таблиц ( в моём случае 38, так как одно поле под разными именами встречается дважды в одной из таблиц).

Есть таблица SprUser, где есть это ключевое поле, записей в неё на данный момент несколько сотен. Это есть пользователи.
Хотелось бы выбрать сколько сделал записей каждый пользователь в этих 38 таблицах, т.е. построить запрос в котором будет имя пользователя и далее 39 колонок с указанием количества записей.

Сейчас я сделал вот такой вариант (согласен что убого, но пока ничего больше на ум не пришло, поэтому и обращаюсь к вам)

select NaimFox, (select COUNT(*) from DogAtr where userlast=NaimFox) as DogAtr,
(select COUNT(*) from DogSch where userlast=NaimFox) as DogSch,
(select COUNT(*) from DopSogNed where userlast=NaimFox) as DopSogNed,
(select COUNT(*) from Energo where userlast=NaimFox) as Energo,
(select COUNT(*) from Energo2017 where userlast=NaimFox) as Energo2017,
(select COUNT(*) from EnergoOLD where userlast=NaimFox) as EnergoOLD,
(select COUNT(*) from EnergoOLD2017 where userlast=NaimFox) as EnergoOLD2017,
(select COUNT(*) from FiderKvt where userlast=NaimFox) as FiderKvt,
(select COUNT(*) from FiderKvt2010 where userlast=NaimFox) as FiderKvt2010,
(select COUNT(*) from FiderKvt2011 where userlast=NaimFox) as FiderKvt2011,
(select COUNT(*) from FiderKvt2012 where userlast=NaimFox) as FiderKvt2012,
(select COUNT(*) from FiderKvt2013 where userlast=NaimFox) as FiderKvt2013,
(select COUNT(*) from FiderKvt2014 where userlast=NaimFox) as FiderKvt2014,
(select COUNT(*) from FiderKvt2015 where userlast=NaimFox) as FiderKvt2015,
(select COUNT(*) from FiderKvt2016 where userlast=NaimFox) as FiderKvt2016,
(select COUNT(*) from FiderKvt2017 where userlast=NaimFox) as FiderKvt2017,
(select COUNT(*) from Grafik where userlast=NaimFox) as Grafik,
(select COUNT(*) from HisSprTp where userlast=NaimFox) as HisSprTp,
(select COUNT(*) from HistoryAll where userlast=NaimFox) as HistoryAll,
(select COUNT(*) from IntSverkaUL_Detail where userlast=NaimFox) as IntSverkaUL_Detail1,
(select COUNT(*) from IntSverkaUL_Detail where userlastPrim2=NaimFox) as IntSverkaUL_Detail2,
(select COUNT(*) from ProfAskue where userlast=NaimFox) as ProfAskue,
(select COUNT(*) from ProfSch where userlast=NaimFox) as ProfSch,
(select COUNT(*) from SchFider where userlast=NaimFox) as SchFider,
(select COUNT(*) from SprDogovor where userlast=NaimFox) as SprDogovor,
(select COUNT(*) from SprGR where userlast=NaimFox) as SprGR,
(select COUNT(*) from SprTN where userlast=NaimFox) as SprTN,
(select COUNT(*) from SprTp where userlast=NaimFox) as SprTp,
(select COUNT(*) from SprTT where userlast=NaimFox) as SprTT,
(select COUNT(*) from Ved where userlast=NaimFox) as Ved,
(select COUNT(*) from Ved2011 where userlast=NaimFox) as Ved2011,
(select COUNT(*) from Ved2012 where userlast=NaimFox) as Ved2012,
(select COUNT(*) from Ved2013 where userlast=NaimFox) as Ved2013,
(select COUNT(*) from Ved2014 where userlast=NaimFox) as Ved2014,
(select COUNT(*) from Ved2015 where userlast=NaimFox) as Ved2015,
(select COUNT(*) from Ved2016 where userlast=NaimFox) as Ved2016,
(select COUNT(*) from Ved2017 where userlast=NaimFox) as Ved2017,
(select COUNT(*) from VedProf where userlast=NaimFox) as VedProf,
(select COUNT(*) from VedTTP where userlast=NaimFox) as VedTTP  from SprUser order by NaimFox


Данный запрос работает, но я думаю, что можно сделать лучше.
22 май 17, 08:07    [20500165]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
SnakeBlg
Member

Откуда:
Сообщений: 37
На ум в первую очередь приходит динамический sql. Вам нужно динамически генерировать запрос на основе списка полученных таблиц. Тогда он будет универсальным и не придется допиливать его руками каждый раз когда появится новая таблица.
22 май 17, 10:10    [20500450]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
SnakeBlg, Можно попробовать динамически, но не могу сделать так как мне надо, вот пример, до которого я дошёл:

declare @query varchar(max) = '';
select @query = @query + case @query when '' then '' else ' union all ' end + ' select userlast, count(*) as kolvo from ' + tn+' group by userlast' 
from
(
SELECT t.name as tn FROM sys.tables AS t INNER JOIN sys.columns AS c ON 
 t.object_id = c.object_id where c.name='userlast' and t.name not in ('SprUser','Monitor','DogUser')
) s
exec (@query)


Сюда бы ещё как-нибудь имя таблицы и в принципе вариант был бы неплохим (хотя и не самым лучшим), но танцы с бубном никак не помогли
22 май 17, 10:20    [20500479]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
AngelOKES
В принципе я проблему решил, но считаю, что данный подход не очень-то оптимизированный,
По другому никак.

Оптимизация тут возможно только совершенствованием модели данных. Что за Ved2014, Ved2015, ... ???

Если вместо 38 таблиц будет 8, вот это будет оптимизация.

Ещё можно агрегировать заранее, поддерживать это в триггерах или через инедксированные представления...
22 май 17, 10:22    [20500490]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
AngelOKES
в принципе вариант был бы неплохим
Эээ, а он разве отличается от исходного? Такой же подсчёт во всех таблицах.
22 май 17, 10:24    [20500497]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
alexeyvg
AngelOKES
в принципе вариант был бы неплохим
Эээ, а он разве отличается от исходного? Такой же подсчёт во всех таблицах.


Результатом первого запроса будет количество записей = количеству пользователей + 39 столбцов, в которых будет указана количество записей, а результатом второго будет всего 2 столбца с указанием пользователя и количества записей по одной из таблиц, что в итоге мне даёт примерно около 10 тыс записей, вот поэтому я и пишу, что если бы была бы ещё поле с указанием имени талицы, то в принципе можно было использовать и такой вариант. Но в итоге конечно же те же яйца, только в профиль :)
22 май 17, 10:50    [20500605]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
alexeyvg
AngelOKES
В принципе я проблему решил, но считаю, что данный подход не очень-то оптимизированный,
По другому никак.

Оптимизация тут возможно только совершенствованием модели данных. Что за Ved2014, Ved2015, ... ???

Если вместо 38 таблиц будет 8, вот это будет оптимизация.

Ещё можно агрегировать заранее, поддерживать это в триггерах или через инедксированные представления...


Если вкратце, то это данные разделённые по годам и поверьте мне это самый оптимизированный вариант хранения данных (в моём конктретном случае).
На их чтение используются виды (объединение периодов), но это уже совсем другая история.
Так что нет, по количеству никак не уменьшишь, наоборот количество может только вырасти (не из-за деления на периоды).

Конечно у меня ещё есть вариант, всё это прописать программно, тем более как я понял других вариантов по ходу дела нет.

Ладно, всем спасибо, думаю, что тема закрыта, хотя если у кого будет интересный вариант решения данной задачи, то с удовольствием подискутируем!!!
22 май 17, 10:55    [20500629]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
AngelOKES, так пропиши там имя таблицы, и будет тебе Entity–Attribute–Value.

declare @query varchar(max) = '';
select @query = @query + case @query when '' then '' else ' union all ' end + 
               ' select userlast, 
               count(*) as kolvo, 
               ''' + tn + ''' as tabname 
               from ' + tn+' group by userlast' 
from
(
SELECT t.name as tn FROM sys.tables AS t INNER JOIN sys.columns AS c ON 
 t.object_id = c.object_id where c.name='userlast' and t.name not in ('SprUser','Monitor','DogUser')
) s
exec (@query)
22 май 17, 12:49    [20501072]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
Шыфл,
Спасибо! Уже что-то
22 май 17, 13:21    [20501154]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
AngelOKES
Если вкратце, то это данные разделённые по годам и поверьте мне это самый оптимизированный вариант хранения данных (в моём конктретном случае).
На их чтение используются виды (объединение периодов), но это уже совсем другая история.
Так что нет, по количеству никак не уменьшишь, наоборот количество может только вырасти (не из-за деления на периоды).
Что то не верится :-)
С правильными индексами чтение из одной таблицы будет в 10 раз быстрее, чем чтение из 10 таблиц.
А чтение из одной большой таблицы будет занимать то же время, что и чтение из одной маленькой.

Индексы для этого и придуманы. Внутри таблицы с помощью индексов делается "разделение" информации на маленькие порции. Разбиением вы только всё замедляете.
22 май 17, 13:28    [20501178]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
alexeyvg
С правильными индексами чтение из одной таблицы будет в 10 раз быстрее, чем чтение из 10 таблиц.
А чтение из одной большой таблицы будет занимать то же время, что и чтение из одной маленькой.

Индексы для этого и придуманы. Внутри таблицы с помощью индексов делается "разделение" информации на маленькие порции. Разбиением вы только всё замедляете.


Там смысл даже не в объеме данных (хотя каждая из этих таблиц содержит много данных) и с индексами там полный порядок, всё оптимизированно на максимум. Тут разделено скорее всего из целей безопасности, да и по скорости даст хорошего фора другой модели. Те данные которые уже закрыты (отчётный период тут не год, а месяц) складываются по годам, текущим будет лишь отчётный месяц и не более того, все остальные данные из основной таблицы переносятся и закрывается полностью на них доступ на любое редактирование (чего я не могу гарантировать при одной таблице). В итоге у пользователей всегда активен только один отчётный месяц, что думаю в любом случае будет быстрее, чем если 10 лет свалить в одну кучу (тут даже самые мега-крутые индексы уже не помогут), если взять все данные, то у меня примерно храниться около 100 отчётных периодов (около 100 млн записей), после я их уже переношу в архив (туда уже совсем другой доступ).
Даже доступ на чтение предыдущих периодов не так часто нужен, как к текущему отчётному периоду, так что это на данный момент самая оптимизированная структура. И по поводу индексов я в курсе как они работают и какуой прирост дают, в данном случае лучше иметь таблицу на 1 млн записей, чем на 100 млн... И конечно же про индексы никто не забывает и на все 100 млн и на 1 млн в отдельности!!!
22 май 17, 13:41    [20501224]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
Adx
Guest
AngelOKES
Там смысл даже не в объеме данных (хотя каждая из этих таблиц содержит много данных) и с индексами там полный порядок, всё оптимизированно на максимум. Тут разделено скорее всего из целей безопасности, да и по скорости даст хорошего фора другой модели. Те данные которые уже закрыты (отчётный период тут не год, а месяц) складываются по годам, текущим будет лишь отчётный месяц и не более того, все остальные данные из основной таблицы переносятся и закрывается полностью на них доступ на любое редактирование (чего я не могу гарантировать при одной таблице). В итоге у пользователей всегда активен только один отчётный месяц, что думаю в любом случае будет быстрее, чем если 10 лет свалить в одну кучу (тут даже самые мега-крутые индексы уже не помогут), если взять все данные, то у меня примерно храниться около 100 отчётных периодов (около 100 млн записей), после я их уже переношу в архив (туда уже совсем другой доступ).
Даже доступ на чтение предыдущих периодов не так часто нужен, как к текущему отчётному периоду, так что это на данный момент самая оптимизированная структура. И по поводу индексов я в курсе как они работают и какуой прирост дают, в данном случае лучше иметь таблицу на 1 млн записей, чем на 100 млн... И конечно же про индексы никто не забывает и на все 100 млн и на 1 млн в отдельности!!!


Обычно отделяется открытый период от закрытых.
Закрытые - только на чтение, все в одной таблице, все необходимые индексы. 100 млн на чтение - это совсем не много, если правильно организованы данные.
По открытым - другая логика.
22 май 17, 15:20    [20501551]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
Adx
Обычно отделяется открытый период от закрытых.
Закрытые - только на чтение, все в одной таблице, все необходимые индексы. 100 млн на чтение - это совсем не много, если правильно организованы данные.
По открытым - другая логика.


Именно и отделяю закрытые периоды и открытые.
А на счёт правильности организации тут говорить бессмысленно и речь даже не о 100 млн записей, пусть будет даже 10000 тыс и 100 записей, если и там и там одни и те же индексы, где будет работать быстрее???
вы хоть поняли что это одна и таже структура, один в один, байт в байт, индекс в индекс.
Чем вы хотите ускорить работу? Просто увеличив размер таблицы в 100 раз и недобавив каких-нибудь новых индексов, а по идее тут придёться добавлять (ну как минимум на период). Так что однозначно тормоза, а что в замен??? Одна таблица и что? Или вас просто раздражает наличие не одной а 10 одинаковых таблиц? У меня вообще есть как я его назвал "бесконечный алгоритм" в одном из решенений, где на каждый день создаётся своя таблица и пусть в неё будет всего пару тысяч строк, работает всё на ура, чем всё сваливать в одну кучу (хотя конечно в чём-то и тут есть свои плюсы, не надо будет соединять таблицы, но даже и тут есть решение статическое или линамическое, первое это виды, а второе это обычные запросы.
Вы лучшен задайте вопрос где самая большая нагрузка и ответ у меня будет в текущем отчётном периоде 99,99%.
Так что стоит ли из-за 0,01% что-то такое затевать и продумывать? Хотя опять же никто не отменял правильную организацию и индексы для текущего отчетного периода...
22 май 17, 15:31    [20501604]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31328
AngelOKES
Или вас просто раздражает наличие не одной а 10 одинаковых таблиц?
А вас просто раздражает наличие одной большой таблицы? :-)

Прочитал у вас кучу эмоций, но тема "почему с большой таблицей будет медленно" не раскрыта.
22 май 17, 17:13    [20502022]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Проще всего будет создать индексированные вью на таблицы, которые более не будут модифицироваться. По размеру минимум занимать буду и агрегаций делать сиквелу лишних не надо будет.
22 май 17, 17:51    [20502170]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
AlanDenton
Проще всего будет создать индексированные вью на таблицы, которые более не будут модифицироваться. По размеру минимум занимать буду и агрегаций делать сиквелу лишних не надо будет.


Именно так и сделано, но кому это интересно, тут главное показать, что ты крутой, знаешь, что такое индексы и конечно же искать проблемы там, где не нужно и давать советы, которые у них не просили. Хотя такое наверное на любом форуме, задашь один вопрос, а тебя начнут учить по всем правдам и не правдам, и код ты не красиво пишешь и нормализацию баз данных не довёл до пятой нормальнйо формы. Да и индексы не панацея, бывает, что на каждый чих не сделаеш индекс, особенно если полей под 100 штук, а запросы могут быть по всем этим 100 полям. Конечно можно отслеживать запросы как администратор баз данных или как программист самому подделывать индексы под свои запросы, но всё равно всё не предусмотришь. Моя же модель подразумевает во-первых защиту данных, а во-вторых объем меньше, что в любом случае будет легче, с индексами или без оных... Для каких-то гигантских отчётов как раз будет использоваться индексированый вью, который объединяет несколько лет и есть второй архивный, в котором храняться данные более 8 лет и в которые разве что раз в месяц кто обратиться и вот я не вижу ни одной причины сваливать всё в одну кучу и не дай бог, пройдет какой-то update несанкционнированный и пипец всем данным (если вовремя этого не заметить), а так все закрытые периоды храняться в отдельных закрытых таблицах, к которым никто не имеет доступа...
23 май 17, 09:09    [20503112]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
aleksrov
Member

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

Без Check огр. он будет их делать.

AngelOKES
успокоились? :)
Видите ли, прежде чем затевать такой тип организации данных, надо думать как потом с этим работать. Вы сейчас хотите ускорить то, что нельзя ускорить из за вашей модели, о чем вам и говорят.
Вы хоть написали бы какая у вас версия для начала.
И повторю вопрос alexeyvg, зачем 14, 15 и т.д. сли вас так бесит большая основная таблица, ну сделайте тогда Table_Old и залейте туда все за все прошлые года, и у вас будет 2 таблицы, а не 100+ и кол-во только растет.
23 май 17, 09:37    [20503155]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
AngelOKES
Member

Откуда: Россия г.Оренбург
Сообщений: 349
aleksrov
Видите ли, прежде чем затевать такой тип организации данных, надо думать как потом с этим работать. Вы сейчас хотите ускорить то, что нельзя ускорить из за вашей модели, о чем вам и говорят.
Вы хоть написали бы какая у вас версия для начала.
И повторю вопрос alexeyvg, зачем 14, 15 и т.д. сли вас так бесит большая основная таблица, ну сделайте тогда Table_Old и залейте туда все за все прошлые года, и у вас будет 2 таблицы, а не 100+ и кол-во только растет.


В том-то и дело, что я не просил мне советовать как организовывать хранение данных, я лишь просил упростить запрос как таковой, возможно ли сделать его более интересным что ли, есть ли другой путь и тем более данная функция нужна лишь как административная и скорость тут меня вообще нисколько не заботит.
Как вот уже было показано ниже, подстановка наименвоаний таблиц и так далее, оптимизация самого запроса, а не нормализация баз данных, по сути это разовая проблема, которую я в принципе уже решил и она мне уже больше и не нужна, но хотел бы узнать есть ли другой, более изящный путь, а мне ту только про индексы уже раз 15 сказали...

Админ закрывай тему, тут всё равно уже наверное один срач сейчас пойдет не по теме, а у кого пиписька больше и как круто можно всё сделать с индексами и свалить всё в одну макротаблицу :)
23 май 17, 10:38    [20503357]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это одним оптимизированным запросом  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27676
AngelOKES
Adx
Обычно отделяется открытый период от закрытых.
Закрытые - только на чтение, все в одной таблице, все необходимые индексы. 100 млн на чтение - это совсем не много, если правильно организованы данные.
По открытым - другая логика.


Именно и отделяю закрытые периоды и открытые.
А на счёт правильности организации тут говорить бессмысленно и речь даже не о 100 млн записей, пусть будет даже 10000 тыс и 100 записей, если и там и там одни и те же индексы, где будет работать быстрее???
вы хоть поняли что это одна и таже структура, один в один, байт в байт, индекс в индекс.
Чем вы хотите ускорить работу? Просто увеличив размер таблицы в 100 раз и недобавив каких-нибудь новых индексов, а по идее тут придёться добавлять (ну как минимум на период). Так что однозначно тормоза, а что в замен??? Одна таблица и что? Или вас просто раздражает наличие не одной а 10 одинаковых таблиц? У меня вообще есть как я его назвал "бесконечный алгоритм" в одном из решенений, где на каждый день создаётся своя таблица и пусть в неё будет всего пару тысяч строк, работает всё на ура, чем всё сваливать в одну кучу (хотя конечно в чём-то и тут есть свои плюсы, не надо будет соединять таблицы, но даже и тут есть решение статическое или линамическое, первое это виды, а второе это обычные запросы.
Вы лучшен задайте вопрос где самая большая нагрузка и ответ у меня будет в текущем отчётном периоде 99,99%.
Так что стоит ли из-за 0,01% что-то такое затевать и продумывать? Хотя опять же никто не отменял правильную организацию и индексы для текущего отчетного периода...

Забавно.

log(100) - 2
log(10000) - 4
log(1000000) - 6
log(100000000) - 8

Намёк понятен? :)
23 май 17, 11:11    [20503456]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить