Save Dataset as session variable or XML file?

[millerk]

I have an app that gets data from SQL Server and displays a crystal report. I'm filling a Dataset then using it as the datasource for the report. The SQL stored proc takes up to 60 seconds because it has to go get data from our SAP system. After the report is displayed in a CrystalReportViewer control, the user will have the ability to export the report to various file formats. I don't want to run the stored procedure again because it takes so long. And I don't want to maintain an instance of the report object due to concurrent user licensing for Crystal and not knowing how long a user will wait before exporting the report.

Right now I see two options.
1. Save the dataset as a Session variable.
2. Write the dataset to an XML file.

I know using Session variables takes a lot of server resources, but I'm not sure if that would be better than the disk access needed to write the XML file then read it when it's needed again.

Anyone have an opinion on which would be better? Or any other ideas?

Thanks,
Shanti

 

[Zarcom]

It would depend on a number of things. How many users are you expecting at a time? How long do your sessions last? How much memory does your server have?
For more information on this subject see thread:
thread855-310187

HTH That'l do donkey, that'l do

 

[millerk]

I've got the sessions set to 20 minutes. At first, it would probably be no more than a couple of people hitting the app at a time, but we plan on moving all of our current MS Access reports to this asp.net app. So the number could grow quite a bit, but probably not more than 10-20 concurrent users. I've already got it working with the XML file approach, but if that's not a good way to go, I can change it.

I read the other thread. I agree that hitting the db again for small amounts of data is not a bad idea. Unfortunately, the stored procedure I wrote goes out and gets data from our offsite SAP servers. And we have no way of speeding that process up. With what I'm currently doing, it can take as long as 60 seconds to get the data from SAP.

So if using the XML file approach or some other method is more scaleable, I would prefer to do it that way.

Thanks

 

[Zarcom]

I would likely shy away from the session datasets. As you may have noticed I am kind of anti session. I am not sure what would be better as far as scalable. You could keep doing the XML file thing, or I was thinking that you could use a database on the local machine to hold the data temporarily. You could use SQL Desktop to do it, which would allow for easy scalability. Like the other thread says, it's kind of an opinion judgement call on the developers side. That'l do donkey, that'l do

 

[Alcar]

How about exporting the report in all the formats once, and then let the user choose which one to view or download.
This will make you use your storedprocedure only once.
Something like:

MyReport cr = new MyReport();
cr.SetDataSource(DataSet.Tables["MyTable"]);

//
// pdf
//
strName = @"C:\InetPub\

http://wwwroot\myApplication\"

+ Session.SessionID.ToString() + ".pdf";
crDestination = new DiskFileDestinationOptions();
crDestination.DiskFileName = strName;
crExportOpt = cr.ExportOptions;
cr.ExportOptions.DestinationOptions = crDestination;
cr.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
cr.ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
cr.Export();

//
// doc
//
strName = @"C:\InetPub\

http://wwwroot\myApplication\"

+ Session.SessionID.ToString() + ".doc";
crDestination = new DiskFileDestinationOptions();
crDestination.DiskFileName = strName;
crExportOpt = cr.ExportOptions;
cr.ExportOptions.DestinationOptions = crDestination;
cr.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
cr.ExportOptions.ExportFormatType = ExportFormatType.WordForWindows;
cr.Export();

and so on...

hth

 

[millerk]

Alcar,
That might be a good idea. The only downsides are that I end up with more unneeded files on the web server. But the files I've seen so far have only been 30-50 KB and I will only need to do 3 or 4 formats. So maybe this would be a good way to go. It definitely simplifies my code.

Darn, I thought I was finally going to get to use XML for something so I could put it on my resume. LOL

Thanks for the ideas everyone.

 

[Alcar]

after they are finished using it, you can always delete it like this:

Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
Response.WriteFile(strName);
Response.Flush();
Response.Close();

System.IO.File.Delete(strName);

Good Luck!

 

[link9]

I'm just curious, Millerk... how big were the resulting xml files, and what was the speed like loading the data from them?

I'm using XML to hold data right now on a project, but the files are all relatively small -- speed to and from the files appears to be at least as fast as going to the database (sql server), but like I said, they're pretty small datasets (<100 records each).

Just wondering what the performance is like if you go big on the files, which it sounds like you have.

thanks,
paul

 

[millerk]

Actually, mine were pretty small too. The XML files were no more than 50K and the exported reports were 30-50K depending on the format. My datasets were usually no more than 200 records. The reason it takes so long is the data is not just coming from SQL Server. The stored procedure uses OLE automation to call a COM Dll that gets data from SAP. It then joins the SAP data with the SQL Server data to create the recordset that gets returned to the .aspx page. SAP is our bottleneck. The SAP servers are on the other side of the country at corporate headquarters and they are pretty slow. Some of the stuff we are doing with SAP takes 4-5 minutes to get a couple of thousand records back.

Reading and writng the XML files is very fast. It was only taking a couple 1 or 2 seconds to read the XML and export the report to a file on disk.

 

[Alcar]

So what did you decide to opt for?

 

[millerk]

I'm going with your idea of exporting them all at once. As of right now, I only need to export to pdf and xls. And that is probably all I will need. So there are really no disadvantages. I would have been writing an xml file and either a pdf or xls. Now I'm just writing the xml and the pdf and displaying the report in the web viewer along with links to the pdf and xls files.
Thanks for the idea.

 

[Alcar]

you are most welcome =)