Страницы: 1 2 След.
RSS
Производительность итоговых функций SQL через ADODB.Recordset, или лучше циклами?
 
День добрый всем.
Вопрос скорее теоретический, поэтому пока без примера.

Недавно решал задачку на множественное приложение фильтров к набору данных. Excel 2003
По итогам работы нужно было получить суммы по отфильтрованным записям, при этом количество примененных фильтров - несколько тысяч (перебор различных границ интервалов). То есть, сами отфильтрованные записи не нужны, нужно получить лишь несколько чисел (суммы) для каждого из вариантов фильтров.

Для реализации выбрал следующую схему:
Данные на листе, делаем ADODB.Connection
Код
Set oBase = CreateObject("ADODB.Connection")
oBase.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
            ";Extended Properties='Excel 8.0;HDR=YES';"
            
Set oRS = CreateObject("ADODB.Recordset")

Запускаем циклы перебора параметров фильтров
В цикле делаем Recordset.Open cо сборной строкой constr типа:
Код
"Select COUNT([Поле 1]),SUM([Поле 2]),SUM([Поле 3]),SUM([Поле 4]) FROM [База$] Where HField & " >= " & t1 & " AND " & HField & " < " & t2 AND " & SField & " >= " & m1 & " AND " & SField & "< " & m2

где t1, t2, m1, m2 - это параметры фильтрации
Код
oRS.Open constr, oBase
... ' выгружаю результат в итоговый массив
oRS.Close
Next m2, m1, t2, t1 ' берем другие параметры фильтрации

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

Так как ADODB я только овладеваю, то пара вопросов:
1) Как можно ускорить работу макроса - у меня большие сомнения относительно целесообразности Open/Close для Recordset столько раз. Но строка запроса каждый раз отличается. Может быть, есть какие-то приемы для работы с ADODB, чтобы не заниматься "хлопанием дверями"?
2) не будет ли быстрее в данном случае гонять массив, проверяя условия на каждой записи? В принципе, оправдано ли применение ADODB для такой задачи?

Спасибки
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
То есть, на каждом шагу цикла открываем Recordset с итоговыми функциями в строке запроса, выгружаем результат, закрываем Recordset
Добрый вечер. Тут, вроде бы, похожая проблема. Может поможет. А зачем менять строку подключения?
Изменено: Smiley - 30.09.2015 17:27:52
Учусь программировать :)
 
Цитата
Максим Зеленский написал: То есть, сами отфильтрованные записи не нужны, нужно получить лишь несколько чисел (суммы) для каждого из вариантов фильтров.
я не уверена, но, думается мне, что в таких случаях стоит использовать не просто SELECT*FROM .. WHERE... но и GROUP BY (чтобы не бегать по каждой записи сопоставляя условию, а проверить на условие только нужную группу)... имхо (на правах предположения)... возможно, появится и более компетентное мнение  :)
p.s. насчёт "хлопанья дверями" - очень интересный вопрос... пока думаю, ВСЕ "выкрутасы" можно провернуть в самом sql-запросе до взятия в рекордсет - насколько быстрее (и будет ли быстрее - не знаю)... опять же личное имхо
Изменено: JeyCi - 30.09.2015 17:33:34
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
я не уверена, но, думается мне, что в таких случаях стоит использовать не просто SELECT*FROM .. WHERE... но и GROUP BY (чтобы не бегать по каждой записи сопоставляя условию, а проверить на условие только нужную группу
По-моему, SELECT всегда выполняется последним, то есть Вы добавляете работу запросу, заставляя его делать 4 действия вместо 3. Поправьте, если я не прав.
Учусь программировать :)
 
Цитата
Smiley написал: Поправьте, если я не прав.
выбирать из 10000 или из группы (например) в 100 записей?... даже если их надо сложить... что быстрее?..  8) да, группировка идёт до select... поэтому на этапе select уже пошустрее (если предварительная подготовка сделана - вся выборка разбита на группы)  
Изменено: JeyCi - 30.09.2015 17:47:31
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Я бы - просто провел "разведку боем", да и всё ☺
Мне непонятно, почему вызывает вопрос "открытие/закрытие" рекордсетов. Каждый раз Вы создаете новый набор условий для отбора строк, следовательно и рекордсет должен быть новый. Вы же коннект (oBase.Open) не открываете каждый раз заново.
Мне непонятно, что у вас считается в "Select COUNT([Поле 1]),SUM([Поле 2]),SUM([Поле 3]),SUM([Поле 4]) ...," и т.д.,  если Вы ничего не группируете :( В этом я солидарен с JeyCi, если правильно понял.
Если бы была какая-то закономерность в условиях отбора - например - во всех (или в какой-то их части) присутствует одно и то же условие, можно было бы создавать промежуточные таблицы, к которым уже применять по очереди оставшиеся условия отбора...
Все-таки я бы посоветовал задать вопрос например здесь http://hiprog.com/forum/ Там люди этим живут и дышат :)

Всё, отбирают комп, ушел ☺ ...
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
Михаил Лебедев написал:
Если бы была какая-то закономерность в условиях отбора - например - во всех (или в какой-то их части) присутствует одно и то же условие, можно было бы создавать промежуточные таблицы, к которым уже применять по очереди оставшиеся условия отбора...
О, вот это интересно. Но как сделать, пока не понимаю. У меня 4 вложенных цикла, соответственно в самом "глубоком" меняется только одна переменная. То есть, можно создать временные таблицы (уровнем цикла выше), и применять условия к ним, бат хау... где про эти временные таблицы почитать? Я же правильно понимаю, что это не дополнительные рекордсеты?
Цитата
Smiley написал:
Тут, вроде бы, похожая проблема. Может поможет. А зачем менять строку подключения?
Там (по ссылке) внутри цикла еще и соединение открывалось, я вроде догадался соединение один раз открывать, до цикла. Но за наводку спасибо. Строка подключения задумана так, что она сразу считает суммы по отфильтрованным полям, только условия фильтра каждый раз меняются. Поэтому меняем и строку
F1 творит чудеса
 
Цитата
JeyCi написал:
не просто SELECT*FROM .. WHERE... но и GROUP BY (чтобы не бегать по каждой записи сопоставляя условию, а проверить на условие только нужную группу)...
Так вроде у меня не "SELECT *", а "SELECT COUNT([Поле 1]),SUM([Поле 2]),SUM([Поле 3]),SUM([Поле 4]) FROM", то есть я не тащу сначала записи... или я неправильно понимаю?
С группировками не разобрался еще до конца, что мне там группировать? Только если я буду делать вложенные конструкции SELECT, но до этого я не дорос еще.
Или я не правильно понял?

SQL для меня как азбука пока, по слогам читаю, так что поясните плз, что вы имеете ввиду?
F1 творит чудеса
 
* я написала для общего случая (не расписывая дословно ваши поля) - просто потому что короче... но всякие count, sum и т.д. (т.н. "обобщающие") применяются к группам... в вашем случае, - [Поле1], [Поле2], [Поле3], [Поле4]... т .е.  применяется WHERE, затем GROUP BY, а в рамках группы вы производите потом вывод (отбор) полей суммы, количества или др ... не знаю, что вы считаете, что есть и что надо...
Цитата
Максим Зеленский написал: что мне там группировать? Только если я буду делать вложенные конструкции SELECT
- речь не о вложенных запросах, а о том где считаем...
Изменено: JeyCi - 30.09.2015 19:46:13
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Я сейчас внимательно посмотрел запрос... группировка тут не нужна, она дает абсолютно другое значение, так что спор ниочем.
Учусь программировать :)
 
Максим Зеленский, а можно попросить полный код посмотреть?
Учусь программировать :)
 
Цитата
Smiley написал:
группировка тут не нужна, она дает абсолютно другое значение
Тема интересная :) Если есть, что суммировать и считать, должна быть группировка :) Откройте access и воспользуйтесь им, как мастером для составления запросов. Если Вы хотите посчитать сумму соседних полей, то группирова не нужна.Если сумму записей по полю - группировка обязательна.
Цитата
Максим Зеленский написал:
О, вот это интересно. Но как сделать, пока не понимаю
Либо Вы должны создать новые временные таблицы, либо - да, доп. рекордсеты (если нужно сохранить >1 рекордсета). Я не знаю, есть ли такая конструкция в ADODB.Recordset, но в DAO.Recordset я пользуюсь вот такой конструкцией (в Access).
Код
Dim db as DAO.Database
Dim rst1 as DAO.Recordset
Dim rst2 as DAO.Recordset
Set db=CurrentDB()
Set rst1=db.Openrecordset("Select..........")
rst1.Filter="HField >= " & t1
Set rst2=rst1.Openrecordset

В итоге в rst1 будет первый набор записей, в rst2 - второй, отфильтрованный набор записей.

Но лучше, всё таки, пользоваться сохраненными таблицами. Чтобы не занимать память. Это будут запросы не на выборку, а на создание таблиц.
SELECT Таблица1.Код, Таблица1.ФИО, Таблица1.Телефон INTO Табл2
FROM Таблица1
WHERE Таблица1.ФИО Like "*Иван*";

Или на добавление записей в существующую таблицу
INSERT INTO Табл2 ( ФИО, Телефон ) SELECT Таблица1.ФИО, Таблица1.Телефон
FROM Таблица1
WHERE Таблица1.ФИО Like "*Иван*";

и т.д. всего не напишешь :)
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Поскольку я максимум работаю с акцесс и ексель, мне не понадобилась модель ADO, хватает DAO. Она была, когда ADO еще не запустили....
Откройте базу, нажмите правой мышкой на названии любого запроса. Конструктор. Затем в ленте выберете либо режим SQL, либо режим Конструктор же
Изменено: Михаил Лебедев - 30.09.2015 20:36:15
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
JeyCi написал:
...всякие count, sum и т.д. (т.н. "обобщающие") применяются к группам... в вашем случае, - [Поле1], [Поле2], [Поле3], [Поле4]... т .е.  применяется WHERE, затем GROUP BY, а в рамках группы вы производите потом вывод (отбор) полей суммы, количества или др ......
Да, вот под этим я бы тоже подписался ☺
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
Михаил Лебедев написал:
Тема интересная  Если есть, что суммировать и считать, должна быть группировка
да я же ведь не спорю совсем - это азы SQL-запросов. Я просто к тому, что запрос, который у Максима - это одно, а то, что предлагает JeyCi - другое. Учитывая код Максима, ему не нужна группировка, так как он получает 1 строку со значениями агрегатных функций (количество записей по "Поле 1" и сумму по каждому из остальных полей), а вариант JeyCi - выдаст несколько строк со значениями. Вот и все :)
Изменено: Smiley - 30.09.2015 21:01:29
Учусь программировать :)
 
:)
Изменено: Smiley - 30.09.2015 21:06:31
Учусь программировать :)
 
Цитата
Smiley написал:  - выдаст несколько строк со значениями.
1) не выдаст в таком запросе - т.к. нет поля (ключа) с этими различными строками - всё сгруппируется в одно
2) без данных, вообще, говорить сложно, что лучше... непонятно ещё есть ли там где null'и и как они будут себя вести... или появятся в будущем...
я пока пас думать без конкретных цифр - поскольку не-имею-теор.-базы... но азы=азбука (посему отметила - моё личное имхо)...  
Изменено: JeyCi - 30.09.2015 21:33:36
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
1) не выдаст в таком запросе - т.к. нет поля (ключа) с этими различными строками - всё сгруппируется в одно
Мой мозг устал, может я Вас не понимаю... То есть по-Вашему
Код
SELECT COUNT(Поле 1), SUM(Поле 2) 
FROM Таблица
эквивалентно
Код
SELECT COUNT(Поле 1), SUM(Поле 2) 
FROM Таблица
GROUP BY Поле 1, Поле 2
Изменено: Smiley - 30.09.2015 21:36:37
Учусь программировать :)
 
Цитата
Максим Зеленский написал:
По итогам работы нужно было получить суммы по отфильтрованным записям, при этом количество примененных фильтров - несколько тысяч (перебор различных границ интервалов).
Я бы сделал примерно так: получил бы SELECT-ом этот фильтр, внешним соединением (LEFT) соединил с основной базой по всем требуемым критериям,  далее уже группировки и SUM, COUNT
Код
SELECT dbf.f1, dbf.f2, dbf.f3, COUNT(dbf.[Поле 1]),SUM(dbf.[Поле 2]),SUM(dbf.[Поле 3]),SUM(dbf.[Поле 4])
FROM (SELECT DISTINCT --distinct нужен для того, чтобы отбросить пересечение данных по фильтрам
       h.f1, h.f2, h.f3, db.f1 AS [Поле 1], db.f2 AS [Поле 2], db.f3 AS [Поле 3], FROM <база-фильтр> AS h
       LEFT OUTER JOIN [База$] AS db ON db.f1=h.f1 AND db.f2=h.f2 AND db.f3=h.f3)
       WHERE Not db.f1 Is Null -- выбрасываем из базы все, что не соответствует фильтрам
      ) AS dbf
GROUP BY dbf.f1, dbf.f2, dbf.f3

В GROUP BY указывается по каким критериям надо выполнять sum, count и т.д. Если GROUP BY выбросить, то будет суммирован/посчитан весь массив! а результат запроса - одна запись.
Изменено: TheBestOfTheBest - 30.09.2015 21:49:43
Неизлечимых болезней нет, есть неизлечимые люди.
 
Доброе время суток коллеги
Правильный вопрос уже как бы прозвучал, а как группировать, если потенциально HField и SField могут быть уникальными значениями? Варианты
1. Если интервалы идут последовательно с равным шагом, то можно группировать по номеру интервала (вычисляемому полую). Для HField, например,
Код
Int((HField - HFieldMin) / HFieldStep)
- где HFieldMin - минимальное значение поля, а HFieldStep шаг интервала, аналогично для SField. Вот по этим вычисляемым полям и выполнять группировку. Используя запрос на группировку как подзапрос, получать из него требуемые записи COUNT([Поле 1]),SUM([Поле 2]),SUM([Поле 3]),SUM([Поле 4]) и номера интревалов (по этим номерам обратным предобразованием можно легко восстановить диапазоны). Или, возможно, укладывающиеся тоже, в свою очередь, в допустимый диапазон.
2. Создать таблицу интервалов фильтра (Номер_Фильтра; HField_From; HField_To; SField_From; SField_To), собственно, то, что предлагает уважаемый TheBestOfTheBest. Только вот соединение выполнять по
Код
(HField Between HField_From And HField_To) And (SField Between SField_From And SField_To)
, то есть отбирать HField SField записи для Номер_фильтра попадающие в соответствующий диапазон, группировать же, естественно, по Номер_фильтра. Ну, а дальше использовать запрос-группировку как подзапрос как 1 пункте.
3. Если ни 1, ни 2 не возможно, тогда ставить PostgreSQL, MySQL, MS SQL Express, Oracle XE, заливать туда таблицу, проиндексировать по полям фильтрации и наслаждаться "поеданием кактусов", всё же будет быстрее от 5 до 10 раз, - что то другое, при данной постановке вопроса, на мой взгляд, придумать сложно.
Изменено: Андрей VG - 01.10.2015 04:33:28
 
Не очень понял зачем здесь интервалы. Да  и, честно говоря, не ковырялся в условиях ТС, для понимания условий надо смотреть на данные.
Но то, что в #20 под п.1 - это присвоение уникального номера каждой записи, ИМХО, это здесь не требуется, ведь мы не используем в запросе что-либо, что требует упорядочивания записей в результирующем запросе.
Цитата
нужно ... получить суммы по отфильтрованным записям, при этом количество примененных фильтров - несколько тысяч (перебор различных границ интервалов).
..., нужно получить лишь несколько чисел (суммы) для каждого из вариантов фильтров.
Изменено: TheBestOfTheBest - 01.10.2015 08:08:03
Неизлечимых болезней нет, есть неизлечимые люди.
 
TheBestOfTheBest, тогда подскажите, пожалуйста, в исходном SQL у ТС для каждого цикла задаётся следующий фильтр,
Код
Where HField & " >= " & t1 & " AND " & HField & " < " & t2 AND " & SField & " >= " & m1 & " AND " & SField & "< " & m2

разве это не есть задание интервала, для которого выполняется суммирование и подсчёт количества? Иначе какой тогда смысл?
Цитата
TheBestOfTheBest написал:
п.1 - это присвоение уникального номера каждой записи
Серьёзно?
Пусть по полю HField имеем ряд значений: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
По выражению Int((HField - 0) / 5) [0 - минимум, 5 - шаг] будем иметь, что для чисел 0, 1, 2, 3, 4 - значение 0, а для 5, 6, 7, 8, 8 - соответственно, 1. Где же тут присвоение уникального номера? Уникального номера интервала фильтра по HField - да, но к записи исходной таблице это не имеет никакого отношения.
Изменено: Андрей VG - 01.10.2015 09:44:23
 
Коллеги, спасибо за бурное обсуждение. Во второй половине дня (сейчас не у компьютера) смогу выложить код с примером данных, чтобы было понятнее, а то неудобно получается.
Пока могу добавить к обсуждению:
1. Границы фильтра (t1,t2,m1,m2) представлены одним упорядоченным списком, с неравномерными интервалами, типа 1,2,4,7,8,10
2. Об уникальности значений в полях HField и SField заранее не известно, предположим, что они могут быть неуникальными
F1 творит чудеса
 
Согласен, это уникальный номер только в случае, если интервал = 1 (для примера из #22). Он имеет отношение не к исходной таблице(я собственно этого не утверждал), а к результату. Только опять же вопрос: где в условиях допущение "интервалы идут последовательно с равным шагом"? Ведь m2, m1, t2, t1 могут быть какими угодно, в т.ч. и неупорядоченными с определенным шагом. Без этого п.1 из #20 не работает,Только, как вариант, в копилку идей, что тоже неплохо.
По поводу цикла. У ТС есть упоминание про некий цикл для каждого набора условий, но нет этого цикла в тексте. Поэтому я бы не брался утверждать, что этот цикл дает только разные интервалы или только одинаковые интервалы. Вполне возможно, что результатом "нескольких фильтров" из цикла могут оказаться одинаковые записи (пересечение результатов) в результате неупорядоченных условий. И это может стать проблемой если уйти от цикла в коде к SQL-запросу, заменяющему данный цикл (а идея была именно в этом). Конечно если в результате требуется учесть и это пересечение, то DISTINCT следует убрать.

Было бы полезно, если бы автор показал реальные данные.
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
TheBestOfTheBest написал:  где в условиях допущение "интервалы идут последовательно с равным шагом"?
Цитата
TheBestOfTheBest написал: Без этого п.1 из #20 не работает
TheBestOfTheBest именно для этого и написал условие в п. 1
Цитата
Андрей VG написал:
1. Если интервалы идут последовательно с равным шагом, то можно
Или это не условие применения? Тогда, подскажите, пожалуйста, как в тексте сообщения задавать условия? Чем вам, всё же, так п.1 не угодил? Из-за Between? :sceptic:
Цитата
TheBestOfTheBest написал: Вполне возможно, что результатом "нескольких фильтров" из цикла могут оказаться одинаковые записи (пересечение результатов) в результате неупорядоченных условий.
Вот с этим всецело согласен. ТС обещался представить пример данных и диапазонов фильтра. Тогда и продолжим ;)
 
Максим Зеленский, если вы сможет предоставить диапазон фильтрации в виде подобной таблицы, то будет всё по проще
 
В связи с #23 становится понятно, что решение #20 п.1 не подходит (. Видимо этим мне этот пункт и не понравился. В противном случае зачем делать таблицу условий, если алгоритм формирования условий известен !?
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
TheBestOfTheBest написал:
В GROUP BY указывается по каким критериям надо выполнять sum, count и т.д. Если GROUP BY выбросить, то будет суммирован/посчитан весь массив
Не совсем понял... то есть, моя конструкция без GROUP BY сначала считает суммы по всем записям, а затем только фильтрует? О_о
Но как-то это странно, я думал, она сначала фильтрует, и уже к отфильтрованному набору применяется функция.
Другое дело, что я на каждом этапе цикла прилагаю фильтр сразу по 4 критериям, в то время как можно предварительно (как я пока понял из обсуждения) на первом уровне вложенности цикла создать Recordset с фильтром по t1, на втором уровне из него выбрать Recordset с фильтром по t2, ..., на последнем уровне цикла менять последний фильтр и там же суммировать/считать. Тогда у меня получится ускорение фильтрации, я не буду каждый раз фильтровать весь массив
Но надо еще подумать.
Спасибо всем за комментарии. Всё-таки SQL пока полутемный лес еще для меня
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
В связи с #23 становится понятно, что решение #20 п.1 не подходит
Да, теперь согласен, но п. 1 был один из вариантов, применение которого было под вопросом из-за скудности информации ТС.
Цитата
Максим Зеленский написал:
то есть, моя конструкция без GROUP BY сначала считает суммы по всем записям, а затем только фильтрует?
Почему же. Сначала фильтрует, а потом считает по отобранным записям суммы. Просто, можно заставить "движок" баз данных делать цикл для каждого набора фильтров, если, учитывая их нерегулярный интервал, свести их в таблицу, и, связав с таблицей данных, получить таблицу поинтервальных сумм.
Изменено: Андрей VG - 01.10.2015 10:13:28
 
2ТС: И еще, надо понять как поступать с дублями?
Неизлечимых болезней нет, есть неизлечимые люди.
Страницы: 1 2 След.
Наверх