Что использовать для простой работы с большими таблицами?
Здравствуйте!
Есть исторически накопленная информация в 10+ эксельных файлах 100 000+ строк каждый. Одно товарное направление - один файл. Файлы обновляются ультраредко - раз в год.
Задача в том чтобы из каждого файла периодически (раз в неделю) добывать информацию об отгрузках по одному из 100 филиалов и склеивать в одну таблицу-отчёт. Даже работа с каждым из файлов по отдельности происходит медленно. Скрутить файлы вместе или сделать сводник не получается - Excel валится (Excel 32bit, 64 недоступен).
Посоветуйте чем бы воспользоваться чтобы все 10+ файлов свернуть в одну базу и из неё раз в неделю вынимать требуемое? MS Access? Не хочется городить что-то серъёзное да и не умею я пока с СУБД работать... По сути хочется Excel, который может переварить 1млн строк и несрочно (например, за пару минут) выдать требуемый отчёт, который можно копипастнуть в отдельный файл и с ним ковыряться.
Роман Мирр: в тексте вопроса ни слова не написано о том, что у автора есть купленная лицензия 1С и есть типовые конфигурации. Из типовых конфигураций хорошая работа со множеством Excel файлов и формирования на их базе сводных отчетов реализована в Консолидации - v8.1c.ru/consolid . По сути именно то, что нужно автору вопроса и всего за 777 600 руб.
Роман, в таблице только данные, без формул.
Аксесс не пробовал, хотя наверное этим и кончится.
Над данными работаю я один.
Генерирую отчёты из каждого файла простой фильтрацией таблицы по названию филиала, потом склеиваю копипастом в новый файл. Этот файл в итоге является отчетом по одному филиалу, который чернз сводник используется для аналитики.
Понял почему не хочу использовать Access. MS Access это всё-таки СУБД. А у меня нет баз данных. У меня есть одна база - статичная двумерная таблица данных. Просто размером немного больше обычного. Поэтому не хочется из пушки по воробьям, а хочется научиться пихать данные в одну табличку (для этого нужно снять ограничение в 1 млн строк Excel) и научиться из него вынимать отчёт фильтром по одному столбцу.
если надо делать быстро - акцесс тут тоже не очень подойдет. Ничего серьезного в этой задаче нет, конечно.
я бы поставил бесплатную версию sql сервера, загнал все в одну таблицу и дал бы признак Department
и в него ваши филиалы от 1 до 100.
А дальше простой селект. Должно летать и ничего сложного не будет.
И неженственный. Мое решение - работает. Именно поэтому я эксперт в этой теме. Вот что не работает _ваше_ решение по предложенной мной идее - в это охотно верю.
Предлагаю пари. Ваши $500 против моей $1000. Я беру базу с нормально залитыми данными, нормальное ТЗ и выкатываю результат максимум через 3 часа.
Ну? Чистый заработок, деньги просто валяются на земле, вам остается просто их подобрать!
Артём, добрый день! Извините за реакцию. Вспылил, приношу извинения.
Поясните, пожалуйста, что такое Признак? Поиск по мсдн мне не помог, поэтому я его не добавил.
Запрос из Excel сделан Select column1, column2, ..., columnN FROM db.table WHERE date >= {d'xxxx-xx-xx'}
Да6 поиск идёт тупо перебором всей кучи несмотря на фильтры. Я начал подозревать необходимость создания индексов, но а) тема проектирования индекса тонкая и б) как я понял Excel всё равно будет перебирать 2,7 млн строк индекса и ему не хватит регистра в любом случае. Или это не так?
Что, собственно, и требовалось доказать. Вся куча данных валится на эксел, от чего ему и плохеет. А я как сказал?
Сделайте локальный сервер, не нужен никакой Azure.
Залейте туда ваши данные.
Пронумеруйте ваши филиалы от 1 до 100
Назовите это поле в таблице как Id_Department, например.
Сделайте индекс по нему и дате, можно - включающий необходимые данные.
Далее пишется select, в идеале - процедура. В нее передается Id_Department и дата.
И тогда: данные выбираются и обрабатываются на стороне сервера! Серверу миллион записей - раз плюнуть, он на миллиарды способен.
А вот на эксел будет тянуться только результат обработки.
Я правильно понял что некластерный составной индекс по трём-четырём основным полям (дата/филиал/товар) и выборка селектом именно по ним передаст работу на строну сервера? И Excel будет уже работать только с 1/100500 частью данных, приехавших с сервера результате запроса?
А количество столбцов, попавших в селект влияет на производительность? Имеет смысл экономить на столбцах (а это возможно, ибо в БД масса избыточных данных) или вся нагрузка только в строчках?
:) Вообще любой запрос на стороне сервера, (а процедура - это стопроцентная сторона сервера), все передаст именно на него.
Количество столбцов, разумеется, тоже влияет на производительность. И еще на производительность выборки влияет то, покрывается ли она индексом.
Я бы сделал индекс, покрывающий конкретно вашу задачу. Т.е. поля - Id_department, Date_action, а вот остальные поля, необходимые в выборке, пошли бы у меня в INCLUDE.
Вот и мне казалось что любой селект выполняется на той стороне. Но вот этот счётчик (prnt.sc/e5ic27), что крутится после создания (обновления) запроса в коннекторе Excel'я - он гад крутится до 2х млн и просит дать ему 64хразрядный офис или памяти поболе :(
Ок, пошёл строить индексы, изучать процедуры и экспериментировать. Две недели назад я вообще не представлял что такое реляционные СБУД и как работает SQL. Так что есть ещё эйриа фор импрувмент
Глеб, Вот я говорю: select * from tbl_cooltable. Где выполняется запрос? На стороне сервера. Куда идут данные? Вызывателю. Сколько их пойдет? Все. Миллион.
Теперь я говорю: select * from tbl_cooltable where Id_department = 79 and date_action > '20161101' (год-месяц-день) Где выполнится запрос? На стороне сервера. Куда пойдут данные? Вызывателю.
Но пойдут они уже по условию. А значит, их будет не миллион, а несколько тысяч. Это первый момент.
Второе - если добавить индекс по этим двум полям, то выборка ускорится, потому что оба этих поля будут INT. Если же в этот индекс добавить еще и нужные вам поля - то будет счастье еще больше.
Третье: если оформить это еще и процедурой - то будет еще и зараннее откомпилированный запрос, что еще больше ускорит эту выборку.
Работы тут реально на три часа, из которых два с половиной уйдут на нормализацию и индексирование ваших данных.
строго говоря, они ускорятся не потому, что будут интовые, а потому, что будут искаться не в таблице, а в индексе. Упоминание про INT здесь для того, чтобы вы именно этот тип данных выбрали.
Извинения приняты, а вот ответ как ответ - так и не помечен.
Вот удивительно как раз то, что несмотря на (..."select * from tbl_cooltable where Id_department = 79 and date_action > '20161101' (год-месяц-день) Где выполнится запрос? На стороне сервера. Куда пойдут данные? Вызывателю. Но пойдут они уже по условию. А значит, их будет не миллион, а несколько тысяч. ") при ровно таком запросе (без индексов) Excel крутит этот чортов счётчик до двух миллионов пока не сглючит по переполнению (обнулению). Хотя по идее выдача должна быть не больше 100 строк. Нормально отрабатывает только если сделать TOP 1000. Я подозреваю, что может я драйвер (провайдер) в Excele неправильный выбрал SQL Server Data. Хотя пробовал уже и ODBC 13 и Native Client 11 - результат один ((
Ответ обязательно помечу как только допилю и допишу всю телегу чтобы если у кого возникнет задача был готовый ответ в деталях.
В итоге решение оказалось более сложным: на бесплатной версии SQL server'a не поместились из-за ограничения в 10Гб, поэтому использован Azure с оплатой по факту потребления ресурса. В чистом виде из одной объединённой таблицы (2,7 млн строк) Select в Excel x32 не работает. Нужно Нормализовать таблицу (БД) по значимым столбцам, для каждого создавать индексы и связки для замены длинных текстовых полей короткими INT числами.
Потом выкачивать отдельные таблицы в Excel и подтягивать vlookup'ом нужные текстовые поля. Решение, но не элегантное. Особенно в связи с тем, что после добавления строк в основную таблицу всю эту работу по нормализации нужно проделывать снова.
Хорошее, годное решение - полная переработка БД и написание вьюшек и процедур. А это много работы для профессионала. Своими руками такого бегемота не вытащить.
Иван, спасибо за идею. Я пробовал QlikView Desktop, но он заточен не под отчёт а под визуализацию (как я понял). Может порекомендовать какой продукт из их линейки подойдёт лучше? Ну и потом, в Qlik надо скармливать готовые таблицы, а их мне в Excel'e в одну не скрутить. А работать с несколькими одинаковыми по ключам как-то непонятно
Простой
Средний
Простой
