Query using the command window

[retygh45]

I have some people here who are new to Crystal Reports and they're used to using the SQL query window in MS SQL. So they just copy and paste their SQL query into the Command window, which returns the data set, then they use Selection Export to narrow down the results and format it into a report.

So their query Command will be a large data set, then their Selection expert will prompt the user for a data range and/or a userID to narrow down the data for the final report.

These reports are taking a long time to run and often timing out. My question is this: Does Crystal 11 work this way, execute the query, which returns a large data set, then narrow it down after it's returned based on the data range? This would seem to be the cause for the time outs. Or is it smart enough to take the query command and apply the selection criteria to the SQL BEFORE requesting it from SQL Server?

Any thoughts are greatly appreciated, thanks!

 

[lbass]

The selection criteria should be built directly into the command. If instead you use the selection expert, the entire dataset from the query is returned and the selection is done locally--which is why the reports are so slow.

-LB

 

[synapsevampire]

It works that way because you designed it that way.

Create the parameters within the Command Object window and apply them there, as well as any other criteria.

-k

 

[retygh45]

Ok, thanks! That's what I thought. And these are not my reports, I'm just trying to investigate why they're so slow when they run them, but fast when I build them myself (b/c I don't use the command window).

Also, is there a way to add the parameters into the CommandObject window so that they prompt the user to enter, for example, a date range?

Thanks again!