SQL запрос в Excel на GO, оптимизация

111
16 октября 2019, 19:50

Мне нужно сформировать отчет Excel и скачать его. Данные берутся из SQL ( примерно 200 тысяч строк на 70 столбиков). Количество столбцов заранее не известно (иначе просто запихнула бы всё в структуру и норм), поскольку для нормального вывода используется pivot, без него будет примерно 20 столбцов на 1400 тысяч строк. Сам запрос идет примерно две минуты - плохо, но сойдет. Далее идет запись в каждую ячейку Excel отдельно, ибо другого способа всё это перенести я не нашла, вот он (используются "github.com/360EntSecGroup-Skylar/excelize" и _ "github.com/denisenkom/go-mssqldb" // s):

rows, err := db.Query("[Proc]", sql.Named("param", param))
        if err != nil {
            log.Fatal(err)
        }
        defer rows.Close()
 (
        colNames, err := rows.Columns()
        if err != nil {
            fmt.Println("error fetching column names\n", err)
        }
        length := len(colNames)
        // Create a interface slice filled with pointers to interface{}'s
        pointers := make([]interface{}, length)
        container := make([]interface{}, length)
        for i := range pointers {
            pointers[i] = &container[i]
        }

        rowcol := 9
        col := 1
        for i := 0; i < len(colNames); i++ {
            xxxfile.SetCellValue(sheetName, getExcelColumnName(col)+"8", colNames[i])
            col++
        }
        // Process sql rows
        for rows.Next() {
            column := 1
            // Scan the sql rows into the interface{} slice
            err = rows.Scan(pointers...)
            if err != nil {
                fmt.Println("error scanning sql row\n", err)
            }
            // Here we range over our container and look at each column
            // and set some different options depending on the column type.
            for _, v := range container {
                xxxfile.SetCellValue(sheetName, getExcelColumnName(column)+strconv.Itoa(rowcol), v)
                column++
            }
            rowcol++
        }

После записи каждая строка обрабатывается по циклу (в строке есть признак группы, в соответствии с которым она форматируется и назначается стиль) Это дело затягивается на несколько часов, что не подходит совсем.

Как это оптимизировать? Нужно свести к нескольким минутам

Answer 1
Анализ

Рассмотрим проблему "Как оптизировать создание "20 столбцов на 1400 тысяч строк в excel" внимательнее:

  1. БД - MSSQL 2017
  2. Источник данных - хранимая процедура [Proc]. Доступ к исходным таблицам используемым в [Proc] - есть. Время исполнения: ~2 минуты
  3. Используется библиотека excelize
  4. В программе организуется цикл с обходом 20 колонок и 200 тысяч строк из исходного набора данных
  5. В цикле происходит манипуляция с getCellValue, setCellValue (не совсем ясна роль pointers и container
  6. Само преобразование xxxfile.SetCellValue(sheetName, getExcelColumnName(column)+strconv.Itoa(rowcol), v) не совсем ясно, но ясно, что не содержит каких-либо рокет-сайенс алгоритмов обработки

Как оптимизировать for rows.Next(){ ... } ?

Никак. Самая быстрая операция - та, которую не исполняешь.

По исходным данным, полученным от ОПа - можно рекомендовать следующий путь:

  1. Берем процедуру [Proc]
  2. Либо изменяем ее, либо записываем вывод из нее в таблицу insert into .. exec [Proc], либо делаем view/function - то, что посчитаем наиболее подходящим на уровне MSSQL
  3. Создаем запрос в SQL выполняющий преобразование, аналогичное getExcelColumnName(column)+strconv.Itoa(rowcol), v). Если возникают проблемы - создаем вопрос c тагами mssql, t-sql и ждем помощи коммьюнити (я лично готов помочь, я убежден, что там что-то простое)
  4. Изменяем программу на голанге, оставляем в ней только db.Query к составленному запросу и запись в эксель файл без какой-либо обработки
  5. Замеряем время исполнения, обнаруживаем, что оно стало меньше минуты.

Готов помочь в случае затруднений

Answer 2

Проблема была, как я считаю, в том, что с библиотекой excelize я до каждой ячейки дотягивалась отдельно, т.е. нужно сделать запись в ячейку C10254, начинаем с A1 и долго ищем нужную, затем следующую так же, это и занимает кучу времени. Поняла это когда поставила логи на каждые 10000 строк, с каждой десяткой время увеличивалось.

Решение - мне помогла другая библиотека tealeg/xlsx, в которой создается строка, в ней создаются ячейки, т.е. записываем всегда в ячейку рядом и далеко не ходим.

Пример

READ ALSO
Как обновлять дату

Как обновлять дату

Форма изначально невидима, открывается при нажатии на кнопкуВ форме есть поля, одно из которых input в который нужно вставить дату (каждый раз...

141
JavaScript classList IE9

JavaScript classList IE9

IE9 не поддерживает classList в связи с этим написал небольшую функцию для удаления классов, есть два варианта :

122
Открыть ссылку в новом ОКНЕ браузера

Открыть ссылку в новом ОКНЕ браузера

Реализую виджет "поделиться"

152
Код ES6 map() не работает в IE 11

Код ES6 map() не работает в IE 11

Обнаружил, что код ES6 map() и `` не работают в IE 11 Как это участок кода переписать кроссбраузерно или какой использовать можно polyfill для этого?

136