Free Support Forum - aspose.com

Issues with SQLDatareader

Aspose.Cells Product Family
#1

When using a data reader as a data source for smart markers the computer memory usage spikes while processing the Excel template with a large amount of data. The point of a data reader is reader small amounts of data at a time so as to not use memory on the PC. Please help!!

#2

Hi,


Please download and try our latest fix/version: Aspose.Cells for .NET v7.4.2.4 and let us know if it works fine.

If you still find the issue, kindly create a sample console runnable application (please use dynamic data table(s) or MS Access database file), zip it and post it here with all the files, we will check your issue soon.

Thank you.


#3

What .Net framework are the attached DLL built for?

#4
Hi,

We by default, provide .NET 2.0 compiled version of the fixes that are targeted for any CPU (32 bit and 64 bit) and also that works fine on any normal .NET framework version greater than or equal to 2.0 (e.g. 2. x, 3. x, 4.0, 4.5 etc.).

If you need .NET framework client profile version, we may also provide it as well but you may try my attached fix on any of your .net project other than client profile framework.

thank you.

#5

Can you please provide a verison built for net3.5_ClientProfile?

Sadi

#6

Hi,


Yes, but probably on Monday/Tuesday (next week). By the way, if you are not using our official latest release Aspose.Cells for .NET v7.4.2 (client profile version), then we recommend you to kindly download and try this.

Thank you.
#7

Yes I am using that release now. Please foward me the 3.5 build when you get next week

#8

Hi,


There would be not much difference regarding importing data feature of the product b/w v7.4.2 (official release) and v7.4.2.4(latest fix), so you may create a sample runnable application as requested earlier and provide us there to reproduce the issue on our end. We will check your issue soon.

Thank you.


#9

It's hard to create a sample program since we are using SQLDatareader with a SQL Server DB. The issue is when using a SQLdatareader when the data is 65k+ rows.

#10

Hi,


As you are importing data more than 65K+ records, so please make sure that your template file is XLSX instead of XLS, the reason is simple XLS file format can only save 65536 rows in a single worksheet, so you should use XLSX file format. Also if you are importing that kind of huge data in a single worksheet, it will surely demand lots of memory, so make sure you have sufficient amount of memory for the big operation, may be you may try to use a good 64 bit processor with sufficient amount of RAM which may resolve your issue.

Anyways, unless we receive your sample project, we cannot evaluate your issue, so kindly create a sample console runnable application (please use dynamic data table(s) or MS Access database file and don’t use SQL Server database to make it runnable, you may use OleDb DataReader if possible), zip it and post it here with all the files, we will check your issue soon.

Thank you.
#11

What is the maximum size of a file i can upload

#12

Hi,


If you are talking about what is the maximum size of the file that Aspose.Cells can load or import a data source or export to data source, there is no limit involved, it totally depends on your resources (RAM, CPU etc.) and file format type that you are using.

If you are talking about the file (with a specific file size) to be uploaded in the forums/thread, I am afraid, there is a limit here (it might be up to 30MB or so.). You may zip the file prior attaching here if it may help a bit.

Thank you.

#13

Here is a sample application with MDB file. It uses 650 mb of ram while processing the excel. Other componets use only 88mb of ram. The whole point of a datareader is to read chunks of the data in memory instead of reading all the data into memory.

#14

Hi,


Well, I tested your project with your template file and it works fine with v7.4.3 (latest version).
I generated the output file with 13MB in size without taking much time on my good x64 bit machine with good RAM. Also, MS Excel does take some time to open the output file as it has a long list of records.
The point is as you have lots of records in your source table (122301) with a certain amount of columns, so the process would demand more memory for sure.

Moreover, in your code, you have specified more number of rows than actual, so Aspose.Cells has to create many extra blank rows which also takes resources, so you may try to change the line of code i.e.

designer.SetDataSource(“LabelPrev”, oReader, 400000);

to:

designer.SetDataSource(“LabelPrev”, oReader, 122301);

Moreover, we recommend you to use DataTable instead of DataReader if it enhances the performance on a normal system with a minimal amount of RAM. For example, you may fill the datatable from the back end and then specify that datatable while setting data source for the markers.

In short, there are measures to be adopted as mentioned above, but if you say, for a huge list of data (as you have), Aspose.Cells would take very minimal/little amount of RAM and resources to process the big process, I am afraid, this is not possible.

Thank you.