Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Show SQL Query missing some report criteria Post Reply Post New Topic
Page  of 2 Next >>
Author Message
kbirchmore
Newbie
Newbie


Joined: 28 Feb 2008
Location: United States
Online Status: Offline
Posts: 6
Quote kbirchmore Replybullet Topic: Show SQL Query missing some report criteria
    Posted: 29 Feb 2008 at 5:49am
I have a report that is based off of 4 large tables (smallest table has 3.3 million rows) and was taking a very long time to run (hours!).  I have found that one of the problems is that Crystal is discarding some of the report criteria when creating the sql statement.
 
the criteria it is discarding is using an IN clause like this:
{MPATPFRM.FORMID} in ('HO 04 11''HO 05 02')
 
If I change this to be
({MPATPFRM.FORMID}='HO 04 11' or {MPATPFRM.FORMID}='HO 05 02')
 
it will add this criteria to the sql statement and it goes from running for hours to running in under 5 minutes.
 
I am using crystal Reports 9.2.3.1699 and Crystal Reports XI 11.0.0.1994 and am having this issue in both versions.
 
I have many other reports that are using the IN clause the same as above and have found these reports are also not including this criteria in the sql statement Crystal creates but I never looked into this as these reports ran in under 5 minutes to start with as the tables they run against are much smaller.
 
In some of these reports there is many criteria in the IN clause
 
Is there a way to have Crystal create the sql statement to include this criteria in the sql statement without having to break out each item in the list into its own statement?
 
Kris
 
 
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 29 Feb 2008 at 7:20am

Hi,

The best way to do is code view or stored procedure at backend and then use that in crystal which is the most efficient way of doing reports with large DataSets.

 

Cheers

Rahul

IP IP Logged
kbirchmore
Newbie
Newbie


Joined: 28 Feb 2008
Location: United States
Online Status: Offline
Posts: 6
Quote kbirchmore Replybullet Posted: 29 Feb 2008 at 7:48am
Problem is that the developers do not have the ability to create stored procedures or views, so I am looking for way that is similar to Oracle sql's IN clause where columnvalue in ('A','B','C') without them actually having to use Add Command and type all the sql directly in.
 
 
Kris
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 29 Feb 2008 at 2:26pm
Unfortunately, this is one of Crystal's limitations.   Some things, like the "IN" structure don't make it into the where clause of the SQL that Crystal generates when you're using just tables.
 
However, you don't have to enter ALL of your SQL in a Command in order to use a command.  You might try a command for just the table you're trying to filter and then link it to your other tables.  I don't know for sure if this will work, but it's an option to try.
 
-Dell
IP IP Logged
kbirchmore
Newbie
Newbie


Joined: 28 Feb 2008
Location: United States
Online Status: Offline
Posts: 6
Quote kbirchmore Replybullet Posted: 29 Feb 2008 at 2:31pm

Thanks.  I will have them give that a try.

 
Kris
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Posted: 02 Mar 2008 at 3:31pm
When designing a report I often use Database | Show SQL Query, to see how crystal is forming the query. Crystal can include some criteria and send it through to the server, but other criteria are not filtered until after the data is read, which can result is poor performance. Sometimes you need to change the way the criteria are specified, as in you example above. Just use what works.

If the report is a summary and does not print line details, sometimes crystal will send aggregate functions through [Sum(), Count() ...] if Data | Perform Grouping on Server is checked, but the conditions for that to work seem rather limited and I'm never used it successfully. In that case sometimes I write the query by hand Database | Database Expert | Write a command. This can dramatically improve performance.

It is possible to write part of the SQL in a command and link it to the other tables. The problem with this is that crystal is less able to optimize criteria  for the other tables in the query. Also, the join between the command and other tables cannot be optimized and performance may be worse. You could write the entire query as a command, but this approach is less flexible. It makes the report harder to maintain, and if the report accepts different parameters, crystal is unable to optimize the query according to the parameters. Sometimes you can't win.

In some cases a query will run faster if you change the order of links Database | Database Expert | Links | Order Links

IP IP Logged
kbirchmore
Newbie
Newbie


Joined: 28 Feb 2008
Location: United States
Online Status: Offline
Posts: 6
Quote kbirchmore Replybullet Posted: 04 Mar 2008 at 10:36am

Database | Show SQL Query is how I found that all the criteria specified in the Record Selection formula was not being used in the SQL query.

We had our in clause formatted like this ('A''B''C') and we found by changing it to ['A','B','C'] this criteria is now used in the sql query. 
 
We have also been unsuccesful using group on server to return aggregate functions and just have the database return all rows and have crystal perform the sums and just suppress detail when users want just totals.
 
We also prefer not to write too many commands as this does make the report slightly more difficult to maintain if the end user decides they want additional criteria from another table on the report.  By just adding the table and linking it to existing tables, it is easier to add this additional information.
 
Many times, I find Crystal does not do auto joins well when you add new tables and we clear all links and then link tables, but it is possible this is not always done.   We have never tried changing the order of our joins, but we will give this a shot.
 
Thanks for all the advice.
 
Kris
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 04 Mar 2008 at 10:56am
I turn off Automatic Smart Linking on the Database tab of Options so that I never have to remember to remove the automatic links.  I have our other developers set this way as well.  As I like to say, smart linking isn't smart.
 
-Dell
IP IP Logged
kbirchmore
Newbie
Newbie


Joined: 28 Feb 2008
Location: United States
Online Status: Offline
Posts: 6
Quote kbirchmore Replybullet Posted: 04 Mar 2008 at 11:46am
I see that option in Crystal Xi but don't see it in Crystal 9. 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 04 Mar 2008 at 1:30pm
You may have to look for it on another tab.  I've been working in Crystal since version 4.5 and I've always been able to turn it off.
 
-Dell
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.