Crystal Performance - Stored Procedures vs Datasets
We have a web application that produces PDF formated reports.
Currently we use .NET datasets to pass the data into Crystal reports in real time.
These can be large and some reports take sveral hours to run (1500+ records) where there are multiple subreports embedded. At time it crashes the server altogether, chewing up resources.
We are trying to come up with a way to delay the report running to a time where server usage is low eg; (9pm)
If I use stored procedures as the datasources in my reports, and pass the parameters out of a database (where they are stored until time allows the report to be run) does this create performance issues. One report may access 5 stored procedures for each record... therefore 7500 calls on a large report.
I want to know if this is faster, slower...or a stupid way of addressing our issue.
Any input is appreciated...though please dont give generic links to examples.
Currently we use .NET datasets to pass the data into Crystal reports in real time.
These can be large and some reports take sveral hours to run (1500+ records) where there are multiple subreports embedded. At time it crashes the server altogether, chewing up resources.
We are trying to come up with a way to delay the report running to a time where server usage is low eg; (9pm)
If I use stored procedures as the datasources in my reports, and pass the parameters out of a database (where they are stored until time allows the report to be run) does this create performance issues. One report may access 5 stored procedures for each record... therefore 7500 calls on a large report.
I want to know if this is faster, slower...or a stupid way of addressing our issue.
Any input is appreciated...though please dont give generic links to examples.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's difficult to say the 'best' approach w/out being in your shoes. One thing I would consider doing is to make all of the applicant's details a single subreport. Create a stored procedure that returns for every employee a single record structured like this:
Qualification1, Qualification2, Qualification3, Qualification4, Qualification5, PrevEmploy1, PrevEmploy2, ... etc.
Have your SP return empty strings or even a single space character for any field w/out data so you're always getting back the same fields, even if nothing is in them. You can then layout your subreport with a very large details section and arrange all of these fields however you want to see them. This gives you all of the same data but only 1 subreport per employee - as long as the SP is efficient this will be a tremendous improvement.
You might also consider making this subreport "on-demand" so it only runs if you're interested in this person and click on their name. I don't know though if that makes sense for your situation.
HTH
frodoman
Qualification1, Qualification2, Qualification3, Qualification4, Qualification5, PrevEmploy1, PrevEmploy2, ... etc.
Have your SP return empty strings or even a single space character for any field w/out data so you're always getting back the same fields, even if nothing is in them. You can then layout your subreport with a very large details section and arrange all of these fields however you want to see them. This gives you all of the same data but only 1 subreport per employee - as long as the SP is efficient this will be a tremendous improvement.
You might also consider making this subreport "on-demand" so it only runs if you're interested in this person and click on their name. I don't know though if that makes sense for your situation.
HTH
frodoman
Glad i could help
mlmcc
mlmcc
Glad to help.
Spykair
Spykair
ASKER
If it helps the report produces a list of applicants for a particular hospital for various positions.
So the report is grouped by 'Position' and inside each applicant's details is subreports for
1.List of qualifications (0...5 records)
2. List of previous employment (0...10 records)
3. List of preferences for positions (1-5 records)
4. List of referees (3 records)
5. List of referee replies (3 records)
These are also accompanied by up to 100 other fields eg : surname, DOB, etc
What would be the recommended approach, based on the fact there may be 1500+ applicants.