Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: SQL Expression Compiling Takes Forever Post Reply Post New Topic
Author Message
wolfgang667
Newbie
Newbie


Joined: 31 Mar 2015
Location: United States
Online Status: Offline
Posts: 10
Quote wolfgang667 Replybullet Topic: SQL Expression Compiling Takes Forever
    Posted: 02 Feb 2016 at 8:07am
As I write this I have a SQL expression that has been taking 4 hours to compile (and still is not done). I know the SQL is correct and returns the correct value. It took this long to compile the first time I wrote it. I just needed to make 1 simple change and of course it is now taking forever to compile again. So my questions are this, is there any way to not have it compile when you save it? Like a report option I can turn off? My assumption here is no. The second question would be, is there some trick to how you write them to make them compile quicker? I have some that go fairly quick (couple of minutes) but some (most) just take forever. To the point I have almost quit using them. Could it be related to the processing power of my PC or the sheer size of the table (currently around 4.5 million rows). Would appreciate any hints or tricks to make them compile / run faster. Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2016 at 3:27am
4 hours seems quite excessive.

What exactly are you using the SQL expression(s) for?
Do you have access to creating stored procedures or views as your data source?
Why are you choosing a SQL expression over a crystal syntax formula?
I believe that generally speaking the value of the crystal sql expression is to try and push that formula processing onto the server for execution and then use the result in the selection process.
IP IP Logged
wolfgang667
Newbie
Newbie


Joined: 31 Mar 2015
Location: United States
Online Status: Offline
Posts: 10
Quote wolfgang667 Replybullet Posted: 03 Feb 2016 at 3:46am
I do have access to create views and stored procedures and will often go that route if it is something that I envision re-using over time. I will use a SQL expression for one offs. In this case I am looking for a single row of data that applies to a particular patient. In order to do this in Crystal natively I would either have to use a subreport which will bog down the running of the report or drop everything in the details section and write a formula to set a shared variable value when the condition exists. The problem with going that route is that when you have 4 or 5 of these scenarios (which is typical) you soon end up with dozens of formulas instead of like 4-5 expressions. I think in this scenario I will probably just create a generic view with my data that I can re-use later unless there is some other option that I am just overlooking. From a high level I am trying to determine what room a patient was in at a specific time when they were in a specific department. I know the department so the SQL runs against the separate table to see when their entry and exit into the room occurred when the department and patient ID is equal to the department and patient ID in the main report. Sorts by entry time descending and returns the top 1 (I.E. the first room they were in during the specified period of time that they were in the department). I have to do similar things such as determining what insurance provider they had at a specified period of time, for example when lab work was done etc.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2016 at 4:30am
It sounds to me like you are using the SQL syntax as complex select process rather then a row level formula. I tend not to use the SQL expression in crystal so it may have more use than I am aware of but
I don't think that this item is intended to be used as an alternative to a stored proc or command but rather as a way to try to create a row level formula that is pushed to the server so the result of that formula can be used in the crystal select or computations. This is for users that do not have access to create stored procs or views but want to gain some server performance.
Have you considered using a command as your source?
IP IP Logged
wolfgang667
Newbie
Newbie


Joined: 31 Mar 2015
Location: United States
Online Status: Offline
Posts: 10
Quote wolfgang667 Replybullet Posted: 03 Feb 2016 at 4:46am
Unfortunately my SQL is just mehhhh. I write enough to do small things or accomplish something specific etc. This SQL would be gigantic and while I could probably do it, the time it would take for me to write it would be prohibitive to go that route. I may have come across a different way to do it by joining a view (that I didn't know existed until 30 minutes ago) by department which will limit my results to only the rooms while they were in the department in question. Appreciate the responses though. I am always looking to advance my knowledge and find that input from like minded folks is the best way for me to look at something differently! Have a great day!
IP IP Logged
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.016 seconds.