Help/Advise to optimize Excel - SQL data retrieval

[CuBixLoNneR]

Firstly, appreciate all the help I can get as I am not an expert in this field and am really struggling to resolve this for work.

Background, there is this excel macro that will extract data from sql. The most rows I have seen by far would be around 8k. I need to extract a month worth of data. Currently, my excel will hang or crash when I try to run the excel. I am able to generate the data if I choose a time range of 2 weeks.

I believe that my coding might be inefficient or that there could be certain settings I can change to improve this timing and successfully generate a month worth of data.

I have attached the codes below.

Thank you in advance.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[CuBixLoNneR]

Attachment 784370

Attached a sample image.

[cytop]

Just some thoughts - no idea if they will make any difference to your issue, but...

I'd use

Please Login or Register  to view this content.

Rather than the loops across the columns until EOF. I would also do the date check (< 2000-01-01) in the stored procedure rather than Excel.

Likewise, if you want to clear a range then

Please Login or Register  to view this content.

...rather than 100,000 loops clearing individual rows.

Finally, I'd close each recordset when finished with it, and just for completeness, set the variable to nothing at the end of each procedure. Would also consider explicitly closing the connection and destroying the variables as well. SQL in Excel can leak memory.

[Kyle123]

Nothing looks particularly wrong to me - are the stored procedures just slow? Have you tried running them in SSMS and seeing how long they take?

[mogul]

I don't use SQLOLEDB.1 to connect.

Please Login or Register  to view this content.

I use this to connect to Azure SQL Server

Please Login or Register  to view this content.

[CuBixLoNneR]

Nothing looks particularly wrong to me - are the stored procedures just slow? Have you tried running them in SSMS and seeing how long they take?

Hmm. Actually the database is about 15GB. Previously we were still able to load a full month in about 20 minutes. But after some security upgrades seems like query time out happens.

[CuBixLoNneR]

Just some thoughts - no idea if they will make any difference to your issue, but...

I'd use

Please Login or Register  to view this content.

Rather than the loops across the columns until EOF. I would also do the date check (< 2000-01-01) in the stored procedure rather than Excel.

Likewise, if you want to clear a range then

Please Login or Register  to view this content.

...rather than 100,000 loops clearing individual rows.

Finally, I'd close each recordset when finished with it, and just for completeness, set the variable to nothing at the end of each procedure. Would also consider explicitly closing the connection and destroying the variables as well. SQL in Excel can leak memory.

How should I use the "Cells(row_idx, 1).CopyFromRecordset". Sorry I'm not very well versed in VBA.

[CuBixLoNneR]

Further I have tried changing "Query options > Data Load > Background data > Never allow data previews to download in the background" from an old thread in this forum to optimize Excel to SQL data loading. Any other options that will help improve the loading speed.

[Kyle123]

Can’t you just create an agent job that runs the stored procedures and dump the output to a table, then query that in Excel?

[cytop]

How should I use the "Cells(row_idx, 1).CopyFromRecordset". Sorry I'm not very well versed in VBA.

CopyFromRecordset dumps a recordset object into a worksheet starting at whichever cell you specify - saves the 2 loops to first copy each field across the columns and then each record into rows

An example straight from the MS Help page for CopyFromRecordset. This writes the field names into cells in Row 1 (and sets the font to bold) and then copies the recordset starting into cell A2. No need to loop the fields and records.

Please Login or Register  to view this content.