SetDataSource running slowly the first time

[Former Member]

Hi,

I have a report that has a main dataset and then 4 datasets that fill subreports. When I fill the main dataset using SetDataSource it takes around 20 seconds but each subsequent SetDataSource runs quicker (when filling the subreports). The first SetDataSource fills more fields (around 35), but it's only 1 record whereas the other datasets fill less fields but have multiple records. If I run the same report again after the first try then sometimes it's quicker and others it's not. I'm not sure what might be causing this and can't find any answers online. The report has been made in Crystal Reports 2008. Below is what the code leading up to the SetDataSource looks like.

Report = New BOL_Final

p_sql = "SQL CODE HERE"

' get_cr_rs uses an OleDbDataAdapter Fill function to pull the data in from the database (SQL Server 2005)
' p_sql has the SQL Code, "tbl_bol" is the name of the dataset inside the .xsd file
p_sys_dataset(0) = get_cr_rs(p_sql, "tbl_bol")

Report.SetDataSource(p_sys_dataset(0))

If any more information is needed just let me know and thanks for any help!

Tim

[former_member183750 · Accepted solution]

Thread moved to the .NET - SAP Crystal Reports forum.

I do not believe the issue is the 1st dataset. Rather it is the load of the report. E.g.; most of the time you see being chewed up is due to the application needing to load the CR runtime. For ideas on how to get faster load of the report, see KB #1439745 - [How to improve performance of a Crystal Report in VB. NET application on load of first report|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333433333339333733343335%7D.do].

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

Thanks.

I'll give this a shot and let you know if it works.

[Former Member · Accepted solution]

This didn't work. It still takes the same amount of time. Maybe more detail will help...

I have Crystal 2008 installed on a server. The server also has a web service installed that takes all the calls from a client to then create the report on the server and export it to pdf and send the pdf back to the client. Basically it's a server that's dedicated to making reports and send them back to the user.

Sometimes when the report in question is run there are multiple instances of the report requested so it might be 6 different reports being processed sequentially with the next not starting until the previous is finished and the pdf is sent back to the client. The first time (of 6) the report is run it takes about 15-20 seconds then sometimes the next 5 all finish in about 10 seconds total. Other times the first time will still take about 15-20 seconds but a random 1 or 2 of the others will take 15-20 seconds as well. I put code in to submit the time before and after a lot of the steps that I though would take longer to process and it's always the first SetDataSource that takes time with everything else not even taking a second.

Does anyone have any other ideas?

Thanks!

[Former Member · Accepted solution]

Did you ever find a solution to this?

I have a winform application, where the first report takes over 2 minutes to load. Subsequent reports are loaded much faster, within seconds. I am using a dataset as my datasource.

[Former Member · Accepted solution]

No it still loads slowly the first time. I even have a different report that fails on the SetDataSource every 20 or so times the report runs, but if the same report is called again it works fine. All the error says is "Object Reference not set to an instance of an Object"

The only other thing I can think of at this point is to update Crystal 2008 to SP4 instead of SP2 but it's not high on the priority list at this point.

[former_member208657 · Accepted solution]

It's likely occuring because you are using the ADO.NET database driver. It is our slowest driver because all data processing is being done in the application. It gets particularly slow if you have a multitable DataSet because we have to index all the tables to process the report.

Our other drivers, OLE DB, ODBC, etc. are faster because they run the query on the server and we get one flat table of data back. It is much easier for Crystal Reports to handle. This is why we don't recommend using disconnected tables in Crystal Reports.

If you can, flatten your DataSet into one DataTable to see if it makes a difference. Hopefully you can find a way to do it.

PS: No software updates will make this faster. None of our updates are done to improve speed.

[Former Member · Accepted solution]

I do have it using OLE at this point (darn, I was so excited when I thought this might be the problem!). I'm pulling in around 40-50 fields but only from 1 table so would it actually be quicker to use ADO in this case since a bunch of tables don't need to be indexed? Or does a large number of fields also slow ADO down?

The only reason I thought that SP4 would help is if there were bugs in the setDataSource that were fixed. I am glad to rule that out though.

Thanks for your help.

[former_member208657 · Accepted solution]

You can't use OLE DB and SetDataSource at the same time. When you use SetDataSource we swap the database driver in the backend to crdb_adoplus.dll (ADO.NET XML).

I've always though that using OLE DB to design the report, then shoving data at the report using SetDataSource is a bit of a cheat. It often leads to problems at runtime because the schema might not match, and you have unrealistic expectations with report performance. The OLE DB driver is faster than ADO.NET (XML).

If you have a direct connect to a database - use it.

[Former Member · Accepted solution]

I'm not sure what you mean by using OLE DB and SetDataSource at the same time. If you look at my code above it shows that I'm getting a dataset using OLE DB and then passing that to SetDataSource. Is this the same thing? What should I be doing differently?

Also, anytime I change the .xsd file I make corresponding changes to the SQL code so I'm not sure how the schema might not match at runtime?

I guess a lot of my problem is from having never seen a way of creating a report differently then I currently do. If you know of any documentation that goes into further detail about any of this or directly connecting to the database then that may help me understand further.

Thanks again for your help.

[former_member208657 · Accepted solution]

The way you are passing data into a report is the most difficult way to manage your reports. Most people design their report on OLE DB or ODBC, or another driver and rely on Crystal Reports to pull the data from the database. In those scenarios, you simply need to supply the username and password of the database to allow Crystal Reports to connect.

As for how to do this, you can read the help guide on how to design a report. I doubt you'll need to do that though because there is only one step different when designing the report. When you are in the database expert you'll choose OLE DB, then connect to the database - that's it.