Passing parameters to SQL Server 3

[olympian]

I need to allow the user to enter e.g. Start Date and End Date to filter on a large table. Setting up the parameters seems no problem, and Database|Show Query seems to require entry of these parameters before in fact showing the query - but then no WHERE clause with the parameters i.e. SQL processes the whole table and then Cystal has a lot of work and ends up printing say 2 records out of over 200,000 !
I know stored procedures will help - but why won't Crystal send the WHERE clause from these parameters anyway/ (ver. CRW 7.) [sig][/sig]

 

[FowlerConsulting]

- but then no WHERE clause with the parameters i.e. SQL processes the whole table and then Cystal has a lot of work and ends up printing say 2 records out of over 200,000 !

This tells me that you have a &quot;join&quot; issue. Check your DB schema and insure that your report table joins are correct. [sig]<p>Steven Fowler<br><a href=mailto:info@fowlerconsulting.com>info@fowlerconsulting.com</a><br><a href=

http://www.fowlerconsulting.com>Fowler

Consulting</a><br>[/sig]

 

[olympian]

Thanks for having a look at this; however

Create a report to simply list all the records of a table in SQL Server which has a date field. (My table has 230,000 records). Then Insert|Parameters to accept a @StartDate and an @EndDate. Run the report. Yes, you will get the results back that you expect BUT it'll probably come as some surprise to you that SQL actually RETURNED the full table and it was only SCR that then used these date parameters to filter the set. MY question is why? And yes my query has got a heap of joins but if you really grasp the simple example above, you'll see why CRW appears to grind to a halt.

Thanks again! I know parameters to a stored procedure will cure the above example but there are other reasons why I MUST BE ABLE to pass user variables to SQL Server via SCR. [sig][/sig]

 

[FowlerConsulting]

“BUT it'll probably come as some surprise to you that SQL actually RETURNED the full table and it was only SCR that then used these date parameters to filter the set.”

It does not; this is how the product works.
1) You add tables to the report from the DB schema you have as your data source.
2) To join the tables, thus making relationships
3) You start to add fields you want outputted to the report.
4) Last, you filter the records with parameters, and a select formula.

“… you'll see why CRW appears to grind to a halt.”
This issue is not so much CR as it is your DB schema.
I bet that you are reporting of an operational database that is the backend to an application.
I bet that the tables are highly normalized (bad thing for OLAP)
I bet that the index strategies for the tables are tuned for application use and not OLAP

If you can not author up a OLAP version of your operational database tuned and constructed for report and analysis, the only way that your reports are going to be timely is to use stored procedures - pushing some the record filtration to the backend server. [sig]<p>Steven Fowler<br><a href=mailto:info@fowlerconsulting.com>info@fowlerconsulting.com</a><br><a href=

http://www.fowlerconsulting.com>Fowler

Consulting</a><br>[/sig]

 

[MalcolmW]

I disagree with Steven. First of all, the problem is not the joins (which Crystal handles quite nicely by passing that work to the server) but the WHERE clause. Crystal is very limited in its ability to parse a record selection formula into a WHERE statement.
Because of this, Crystal often will do full table reads, and then pass this junk across your network for your client machine to throw away. Needless to say, this is not an effective approach.
To demonstrate this, take a version of your report and use the SQL Query tool to create the datasource for it, complete with the parameters. The parameter values get plugged into the query you have written, and thus get passed to the server, and the (much smaller) query result is returned promptly.
The problem has nothing to do with the database being possibly highly normalized, because the table joins are done on the server in both cases.
To deal with the limited parsing capability available to you in Crystal Reports, avoid:
OR statements
IN statements (which are a form of an OR statement)
BETWEEN (which I find can be parsed incorrectly)
If you do have to use these, put them at the end of the record selection formula, so at least Crystal will parse up to the first OR statement.
[sig][/sig]

 

[kenhamady]

I think Malcolm is heading in the right direction. The key is what is getting into the SQL statement.

One other issue that causes things to drop out of the SQL is date time conversion. I believe that using the &quot;Convert Date-Time to Date&quot; option may keep the dates from moving into the SQL statement.

You may want to experiment with several different ways to write the formula and see if one gives you a more optimized SQL statement in your environment. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Taining by Ken Hamady</a><br>[/sig]

 

[olympian]

Thank you all for your attention to this matter. I can agree with Steven's synopsis on first hearing about the problem - most problems of this nature are indeed all of the things listed - BUT just take a simple step back for a moment - the issue IS that the parameter field (OK so there may be special considerations with date/time fields) IS NOT getting into the Show SQL Query !!!

Let us suppose that a simple report just wants to return say invoices >$100 (not hardcoded - user value entry) - then I would expect (demand) that the SQL Query had something along the lines of WHERE Amount>100. It does not.
So, SQL Server send every record back to SCR and OK CR then filters to only show the records we want.

I can think of many ways around this problem - but I still want to know why the SQL does not contain something like WHERE Amount>{?Amount} - thus allowing users to type in a value at runtime in a parameter box AND have SQL Server take advantage of that parameter not just SCR.

Beginning to think I am missing a simple check box or option but I really have tried it all! [sig][/sig]

 

[FowlerConsulting]

>“I still want to know why the SQL does not contain something like WHERE Amount > {?Amount}”

Let me reiterate.

In CR the statement you see in the [show SQL query] is used to tell CR what:
- fields you want to use
- tables those fields are in
- the relationship of those tables are.

The record filtration is ALL done at the client (this could be a CR server box) via parameters that are then used in the selection formula.

Why does the tool do this?
Because it was designed as a presentation layer for reporting not business rule processing (Though there has been some major improvement to this over the last three version). It was never really good at record filtration and aggregation.

That is why you want to use an OLAP datasource or move the demoralization and aggregation back to the DB server via stored procedure. Many organization cannot afford to configure and maintain a data warehouse so stored procedures are usually the way they go. In doing so your data is 99% the way you need to for final output.

If you are a small shop with a small DB, then you can get away with embedded CR record flirtation. AT 200K rows I think that you will be forced to ether process your reports in BATCH (at night) or be forced to move the business rule processing back to the server for timely reports.

If you are saying:

“Steve I can not use stored procedure, and a data warehouse project is out of the question. I need to do this ALL in CR!!!” Then I say good luck, and lets see what we can do to help… but I think you know my option in this matter.
[sig]<p>Steven Fowler<br><a href=mailto:info@fowlerconsulting.com>info@fowlerconsulting.com</a><br><a href=

http://www.fowlerconsulting.com>Fowler

Consulting</a><br>[/sig]

 

[olympian]

Steve! Thanks for your (detailed) attention on this.

Now. Guess what? I have learned of a MR1 patch today. And guess what? It looks like this is going to fix things! It seems the entire Parameter Insert process is completely different from what has been distributed over the network to the users - it looks like they're all going to get a swift performance boost - instantly.

I have taken on board all that you have said and will no doubt improve our setup somewhere along the line. Thanks again. [sig][/sig]

 

[FowlerConsulting]

The 8.0v CRPE (print engin) is said to be 70% better that that of the 7.0v

For what it is worth... [sig]<p>Steven Fowler<br><a href=mailto:info@fowlerconsulting.com>info@fowlerconsulting.com</a><br><a href=

http://www.fowlerconsulting.com>Fowler

Consulting</a><br>[/sig]

 

[olympian]

Yes I think a good tip for fixing v7.0 problems IS to get v8.0. I am going to be quite interested in the SCR that'll come with Visual Studio.net though. [sig][/sig]