Multiple users requesting 30,000 lines of information simultaneously
I have a test bench app available for 3 users.
If all users run the application at the same time, the stored procedure starts, and all three will have result sets that currently use ADO.NET Datatable
about 30,000 records, which must then be transferred to an Excel template for each one.
A template is a file xlsm
containing several VBA
that should be run after importing data. This template is saved in the solution.
I will try to move data from DataTable
to worksheet
withExcel-Interop
Does anyone have any experience with moving this amount of data from Datatable to Excel?
@slugster suggested "set up datasource from Excel and just run query using dirty read" ... is it possible to set Excel
datasource to a non-materialized datatable?
Will loop through 30,000 rows by 10 columns through xl interop
run into problems?
source to share
Does anyone have any experience with moving this amount of data from Datatable to Excel?
Not from a DataTable object, but this amount of data using Excel's built-in data import capability, yes.
@slugster suggested "set up a datasource from Excel and just run a query using dirty read:" ... is it possible to create a datasource in Excel associated with a non-materialized datatable?
I would suggest this too. To go further, I suggest creating a stored procedure and then calling it. You should see better performance using the stored procedure. The procedure can collect and prepare data and then return it to Excel. Alternatively, you can create a caching mechanism within the procedure. For example, if your data only changes daily, you only restore the data in the original table once a day, so only the first user requesting the data gets the initial performance hit. Also, depending on what type of mailing you are doing in Excel in VBA, it might also be possible to handle this in a procedure. This procedure will also help reduce the likelihood of blocking problems.if you add the SET TRANSACTION UNCOMMITTED ISOLATION DEVICE to the beginning of the procedure, or use the (NOLOCK) hint on tables that you are ready to resolve with dirty reads.
Here is a good article on using stored procedures in Excel: http://codebyjoshua.blogspot.com/2012/01/get-data-from-sql-server-stored.html
Will loop through a table of 30,000 rows by 10 columns through xl interop facing problems?
It depends on your definition of "problem". I could see the possible performance implications, however, if you process as much as you can in a stored procedure, you should be fine. In the data world, it's really tiny tiny.
source to share