How to convert SQL SubQueries into a Crystal Report
How to convert SubQueries in Crystal Reports: I have a long SQL query that has several sub-queries that needs to be converted into a Crystal report using Tables. The query takes forever to run and we are hoping by converting to Crystal it will be easier to
1) allow others to use the query
2) allow for multiple, frequent additions and subtractions to the report.
3) This report is then pulled into a medical record system that is a complicated process, so by making it a table run query this simplifies the process.
Question: How do subqueries convert in Crystal? Do they become sub-reports?
Using Crystal Reports 2013 and SQL Server Mnagement Studio V17.4
1) allow others to use the query
2) allow for multiple, frequent additions and subtractions to the report.
3) This report is then pulled into a medical record system that is a complicated process, so by making it a table run query this simplifies the process.
Question: How do subqueries convert in Crystal? Do they become sub-reports?
Using Crystal Reports 2013 and SQL Server Mnagement Studio V17.4
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I really like both comments. They were both helpful. Here is my problem. My report is currently a command statement in a crystal report. However, Everytime I make the smallest update to the query, it takes up to two hours for the command statement to turn white again - so that I can click OK and try to run the report.
Any ideas? I understand it is because Crystal is running the query in full before it allows me to run it.
Any ideas? I understand it is because Crystal is running the query in full before it allows me to run it.
ASKER
And what do I do if some of the joins are on non-indexed columns?
>>And what do I do if some of the joins are on non-indexed columns?
The only thing you can do is work with the DBA to see if it makes sense to add indexes on the columns, generally on the to side of the join.
That is the table you are looking up in
>>Any ideas?
The only thing you can probably do is convert it to a stored procedure but it may have a similar effect when changes are made.
mlmcc
The only thing you can do is work with the DBA to see if it makes sense to add indexes on the columns, generally on the to side of the join.
That is the table you are looking up in
>>Any ideas?
The only thing you can probably do is convert it to a stored procedure but it may have a similar effect when changes are made.
mlmcc
As for as Crystal Reports development is concerned, about 80% of the work is related to query part, so that is where we need to ensure the query is with less performance prone because, once the same query is included in Crystal report, we may add additional groups, sub reports and formulae which will add additional performance issue.
So, once we develop the query, always run the explain plan or use any other performance review techniques you use to check the performance of the query.
As developers, sometimes we may not be knowing the techniques to improve the performance of a query, but DBA team can definitely help us in that regard and hence it's advisable to check the query with them and as mlmcc suggested, it might be something to do with the joins or there might be unwanted columns selected in the query or an introduction of an index might do the trick.
So, once we develop the query, always run the explain plan or use any other performance review techniques you use to check the performance of the query.
As developers, sometimes we may not be knowing the techniques to improve the performance of a query, but DBA team can definitely help us in that regard and hence it's advisable to check the query with them and as mlmcc suggested, it might be something to do with the joins or there might be unwanted columns selected in the query or an introduction of an index might do the trick.
ASKER
Thank you both! I now know to keep the command as a command and add it to the crystal report, after checking all the joins and filters for non-indexed columns. I will also see if we have a tool to check the query for performance issues or otherwise reach out to our DBA's for further assistance.
You need to analyze the query and look for place where the joins are on non-indexed column and filtering is done on non-indexed columns.
Also changing the order of the filters can speed a query tremendously.
mlmcc