So what I have is an asp page that allows my user to hit a button and it will generate a dump of data from our sql server containing about 100k rows and 23 columns to an excel file. This takes about 10+ minuets. Should it be taking that long?
A:
If you have an average of 15 characters per cell, that's about 34 megs of data in that excel file.
Yes, that will take some time..but your query or table indexes may also not be optimized.
Ed B
2010-07-14 15:04:31
+1
A:
It isn't really clear if your bottleneck is the generation of the excel file itself; but if this is the case, I can highly recommend NativeExcel.Net for performing the conversion.
DanP
2010-07-14 15:18:22
Thanks I will take a look at it.
Breander
2010-07-14 17:24:06
A:
Here are the first steps you need to take to troubleshoot the problem:
- Measure how long it is taking to retrieve the data.
- Measure how long it is taking to create the excel file from that data.
- Measure how long it takes to transmit that file to your user.
If it is not then obvious if something is taking too long, post the results here.
Peter Recore
2010-07-14 15:20:06
Using the ants profiler its says about 72% of time was spent on the export function and about 25% of the time on fetching the data.
Breander
2010-07-14 15:25:35
@Breander: Given that this is the case, I'd recommend having a look at NativeExcel.Net; we evaluated quite a few libraries for exporting directly to excel and found this one to be the fastest by far.
DanP
2010-07-14 16:47:12