Как быстро вставить 300К записей в Excel файл через VBS?

Question

[KTG]

Так записывает неимоверно долго

' выводим содержимое таблиц и считаем записи
						recordsCount = 0
						RSSQL.CursorLocation = 3 ' клиентский курсор
						RSSQL.OPEN strSQL
						iFieldsCount = RSSQL.Fields.Count
							While Not RSSQL.EOF
								For J = 1 To iFieldsCount Step 1
									obj_WorkSheet.Cells(RSSQL.AbsolutePosition+3, j) = RSSQL(j-1)
								Next
							RSSQL.movenext
							Wend
						RSSQL.Close

Так вываливается по ошибке, что недостаточно памяти:

recordsCount = 0
						RSSQL.CursorLocation = 3 ' клиентский курсор
						RSSQL.OPEN strSQL
							obj_WorkSheet.Range("A4").CopyFromRecordset RSSQL
							recordsCount = RSSQL.RecordCount
						RSSQL.Close

Как взять из базы записи безболезненно и быстро?
Можно во 2-ом варианте поменять курсор на серверный, но тогда я не смогу получить количество записей.

Comments

[KTG]

Могу переформулировать вопрос, т.к. с серверным курсором все происходит меньше чем за 5 минут, то собственно как мне получить количество строк?

[KTG]

KTG, Cells.SpecialCells(11).Rows
Собственно последняя строка минус количество строк на заголовки.

[Константин Цветков]

В три притопа можете превысить максимальное количество строк.

Answer 1

[sim3x]

Импортируйте из БД в csv

Comments

[KTG]

1. В итоге мне всё равно нужно получить форматированный Excel файл.
2. Выгружается на клиенте, собственно без стороннего скрипта не обойтись, т.к. ни у сервера, ни у клиента, нет доступа к файловом системе друг друга.

[sim3x]

KTG,
1. Форматировать в более производительном ЯП (шарп)
2. Написать клиент на нормальном ЯП

300к строк слишком много для вменяемой работы в екселе на "офисной" машине

Answer 2

[akelsey]

Excel нативно умеет забирать данные из БД.

Answer 3

[Пычев Анатолий]

проверьте что тормозит Ваш код. участками запускайте и измеряйте скорость выполнения

Модуль класса для проверки скорости выполнения участка кода

'---------------------------------------------------------------------------------------
' Module    : CTimeCounter
' DateTime  : 25.12.2017 00:25
' Author    : Mike Woodhouse
' Purpose   : Класс таймера для подсчета времени работы кода
'
'            Sub test()
'               Dim tc As New CTimeCounter
'               tc.StartCounter
'                 Тестируемый код
'               Debug.Print tc.TimeElapsed
'            End Sub
'
'---------------------------------------------------------------------------------------
Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

#If VBA7 Then
   Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As LARGE_INTEGER) As Long
   Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As LARGE_INTEGER) As Long
#Else
   Private Declare Function getTickCount Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
   Private Declare Function getFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
#End If



Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#
'

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    getFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    getTickCount m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    getTickCount m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = (1000# * (crStop - crStart)) / m_crFrequency
End Property

2.

obj_WorkSheet.Cells()

Такое обращение в ячейке, даже без доступа к БД, при наличии 300к обращений, уже будет тормозить Ваш Excel. Собирайте все в 2х мерный массив и присваивайте одним присваиванием

Возможно будет уместным получить весь блок данных и обрабатывать его уже в Excel
А может разделить один большой запрос на несколько?

Comments

[KTG]

Как писал выше, сменил курсор на серверный.
Клиентский ставил, только что бы получить количество записей. Теперь их считаю по количеству заполненных строк.

Одним присваиванием не даёт - ошибка недостаточной памяти.

[Пычев Анатолий]

KTG,

Одним присваиванием не даёт - ошибка недостаточной памяти.

Создайте массив в 1000 записей и делайте 300 присваиваний. Будет прирост.

[KTG]

Пычев Анатолий, как тогда указывается с какой по какую запись выгружать?