cancel
Showing results for 
Search instead for 
Did you mean: 

Command Table of Month and Year performance

Former Member
0 Kudos

In my database I have several tables that contain a stat date. The output requirements are for each month as below.

Jan-15

Feb-15

Mar-15

Apr-15

May-15

Jun-15

But my data might have months where there is no data so those months of course will not produce rows.

I DO NOT have the ability to create a table to left join so that my output will meet requirements and creating a table to link to in excel is not an option either. So after many hours of working , as a novice, with SQL I came up with this command table and left join to another command table where I convert the date in my stats table to date(year, month and change day to 1).

select

a.StatDate

     from(

     select CAST(dateadd(yy,nummonths.a,(dateadd(m,numyears.a,{d '2009-12-1'}) )) as DATE) as StatDate

          from

          (select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 ) as nummonths,

(select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10  union select 11 union select 12 ) as numyears

) as a

This all works quite nicely but my question is in regards to how crystal reports will process this and the effect on performance for large datasets. Does CR run this query for every record it matches on the left join?

I hope this question makes sense.

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

You really want to avoid joining multiple commands or a command and any tables in your report.  When you do this, Crystal pulls all of the data into memory and does the join there instead of pushing the join to the database where it is processed more efficiently.

Depending on the type of database you're connecting to, you may be able to use a "With" clause to get this all into a single command.  I know this works in MS SQL Server and Oracle.  It would look something like this:

With dates as (

select

a.StatDate

     from(

     select CAST(dateadd(yy,nummonths.a,(dateadd(m,numyears.a,{d '2009-12-1'}) )) as DATE) as StatDate

          from

          (select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 ) as nummonths,

(select 1 as a union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10  union select 11 union select 12 ) as numyears

) as a

)

Select...

from MyTable...

  left join dates on MyTable.StatDate = dates.StatDate

where...

-Dell

Answers (2)

Answers (2)

Former Member
0 Kudos

thanks for the responses!

abhilash_kumar
Active Contributor
0 Kudos

Hi Randy,

CR does not run this SQL for every query it matches.

It sends this query to the database > receives results > stores it in some temp area.

It also sends the query that the other tables generate > stores the results in some temp area.

It then needs to Join these two resultsets locally > process record selection formulae locally.

All of this does tend to slow down things as CR needs to join > filter and process the final results.

-Abhilash