Database Techniques for Improved Reporting Performance

1
Database Techniques for Improved Reporting
Performance

• Kevin McCann, Virtual Consulting

2
The existential question

• Who Am I?
• Virtual Consulting has been a Business Objects
  VAR Partner for 10 years
• IT Consultant for 20 years
• Beta Tested Crystal Reports 1 - 16 years ago
• 20 Years SQL Experience

3
Presentation Information (Hidden Slide)

• Database Techniques for Improved Reporting
  Performance
• How you manage data access can directly impact
  the performance of your reports. In this session
  designed for technical report developers, youll
  learn a variety of advanced database techniques
  that can be used to optimize reporting
  performance. Find out how to develop
  stored procedures and SQL statements and
  understand when to use each of these techniques
  within Crystal Reports. Plus, gain valuable tips
  and tricks on changes you can make in the report
  instead of the database. Youll see live
  demonstrations using Oracle and SQL server
  showing the benefits of increased query control.
• Print_Code (please leave for Business Objects use)

4
Audience Survey

• What version of Crystal Reports are you using?
• XI
• 10
• 9
• 8.5
• 8 or earlier
• Which database do you use most frequently?
• SQL Server
• Oracle
• Sybase
• Informix
• IBM DB2
• Access
• Other

5
Topics

• Database SQL
• Pre-processing data
• Crystal SQL control
• Real life challenges
• Crystal Reports performance tips

6
Basic SQL Syntax

• Crystal Reports is a SQL Generator!
• Tables FROM
• Linking Expert JOIN or WHERE
• Fields SELECT
• Grouping ORDER BY (not a group by typically)
• Summaries typically not done in SQL
• Record Selection WHERE
• Unsupported SQL constructs
• Having
• Union (implementing by coding the SQL using
  stored procedures or views)
• Sub-queries
• Can Crystal Reports build your report?

Supplement pages 3-4
7
Stored Procedures 1/4

• Check your SQL Options so they show up
• File...OptionsDatabase TabStored Proc Check Box

Supplement pages 4
8
Stored Procedures 2/4

• Design requirements
• Advantages over views
• Incremental processing
• Temporary tables
• Example
• Using the first time
• Deleting data
• Time to run
• Modeling a complex stored procedure to avoid
  waiting for a database administrator

Supplement pages 5-7
9
Stored Procedures 3/4

• Modifying stored procedures and the impact on a
  report
• Aliasing fields or changing fields selected
• Converting data types
• Changing data types in base table
• Adding/deleting fields
• Interim logic and the from statement and where
  clause
• Oracle stored procedure requirements
• Packages and cursors
• ODBC setup

Supplement pages 7-10
10
Stored Procedure Example 4/4

• PACKAGE CURSOR_TYPES_COMMISSION_BU
• AS TYPE VOUCHER_COMMISSION_TYPE
• IS RECORD(
• VOUCHER_ID VARCHAR(10),
• CT_AMOUNT NUMBER)
•  
• TYPE COMMISSION_TYPE IS REF CURSOR RETURN
  VOUCHER_COMMISSION_TYPE
• END CURSOR_TYPES_COMMISSION_BU
•  
• PROCEDURE VOUCHER_CT_COMMISSION(
• VOUCHER_CT_COMMISSION_CV IN OUT
  CURSOR_TYPES_COMMISSION_BU.COMMISSION_TYPE,
• V_DEPTID VARCHAR2, V_START_DATE IN
  ARI_CT_TRANSACTION.SUBMITTED_DATETYPE,
• V_END_DATE IN ARI_CT_TRANSACTION.SUBMITTED_DATETY
  PE)
• AS
• BEGIN
• OPEN VOUCHER_CT_COMMISSION_CV FOR
• Select .

11
Views 1/2

• View advantages
• Case statements
• Aggregates
• Union
• Having
• Force index
• Supported by more databases than stored
  procedures
• Disadvantages
• Not as powerful as stored procedures
• Must be done in one SQL statement
• Views used for security
• Views as a meta layer
• Example

Supplement pages 10-11
12
Views 2/2

• create view demographics_vw
• as
•  
• select distinct b.facility_num, a.first_name,
  a.last_name,a.middle_initial, a.patient_id,
  b.modality, b.first_date_TRC_unit,
  b.end_date_Dialysis, b.treated_mon,
  b.treated_tue,
• b.treated_wed,b.treated_thu, b.treated_fri,
  b.treated_sat, b.treated_sun , a.MEDMANPHLID,
  a.MEDMANSEAID, a.MEDMANAUXID, a.current_record,
  a.logically_deleted,
• case when b.end_date_Dialysis is null then 'A'
  else 'I' end as Patient_Status
• from demographics a, patient_facility_detail b
• where current_record "Y"
• and a.patient_id b.patient_id

13
Topics

• Database SQL
• Pre-processing data
• Crystal SQL control
• Real life challenges
• Crystal Reports performance tips

14
Brain Teaser

• If I want my windows password to be how
  do I stop it from displaying?

15
Pre-Processing Data 1/5

• Advantages
• Supports multi-statement processing when stored
  procedures are not available
• Data is used repetitively
• Simplifies report logic
• Mechanisms
• EXE
• Database macros (stored procedures, scheduled
  jobs, COM Driver)
• Data warehouse

Supplement pages 12-15
16
Pre-Processing Data 2/5

• Considerations
• Data should not need to be real time if data is
  processed once
• If the data is more real time, then a front end
  should call the processing before the report.
  This increases wait time and needs to consider
  multi-user environments
• Key table generation
• Provides greater flexibility in report filtering.
• Can make not exists reports much simpler

Supplement pages 12-15
17
Pre-Processing Data 3/5
Screen selections for selecting parameters
18
Pre-Processing Data 4/5
Dynamic SQL to generate key table values
19
Pre-Processing Data 5/5
Linking from key table to application data tables
20
Topics

• Database SQL
• Pre-processing data
• Crystal SQL control
• Real life challenges
• Crystal Reports performance tips

21
Crystal Reports Query

• Changing the query in the Show Query window
• Advantage is that it allows you to implement
  additional SQL
• Disadvantage is that it is not easy to maintain
• Troubleshooting technique
• Use to retrieve SQL to run in the query tool
  rather than rebuilding reports
• Debugging is faster and much less destructive

Supplement page 15-16
22
Crystal Reports Command Object

• Advantages
• Creates more specific SQL outside of Crystal
• Supports unions, aggregates, etc
• Can be shared by multiple reports via repository
• Disadvantages
• SQL queries are not as specific as they can be in
  the database
• Not a database object

Supplement pages 16-17
23
Topics

• Database SQL
• Pre-processing data
• Crystal SQL control
• Real life challenges
• Crystal Reports performance tips

24
Multi-database Approach 1/3
For improved performance

• Scenario
• Using 40 databases so that clients do not see
  each others data
• Improves performance as there is less data in
  each database
• Challenge
• Issue with developing reports that can cross
  databases dynamically based on the user running
  the report
• Solution
• Using SQL Server we leveraged linked servers,
  stored procedures and dynamic SQL to redirect
  queries
• Issue
• This worked for one client in SQL server but
  another client using Informix could not do this
  as Informix does not support dynamic SQL

Supplement pages 18-20
25
Multi-database Approach 2/3
For improved performance
SELECT tblStoreMarkets.RegionId,
tblRegions.RegionName, count(distinct(tblStoreMark
ets.StoreId)) as StoresinMarket FROM ' _at_DB
'.dbo.tblStoreMarkets tblStoreMarkets INNER JOIN
' _at_DB '.dbo.tblStores tblStores ON
(tblStores.StoreID tblStoreMarkets.StoreID) INNE
R JOIN ' _at_DB '.dbo.tblMarkets tblMarkets
26
Multi-database Approach 3/3
For improved performance
CREATE proc MaskCallDetails --_at_TeamID int, _at_DB
varchar(63), _at_asmPeriod varchar(20), _at_asmReg
ions varchar(2000)   as   set _at_str
_at_DB'.dbo.spCallDetails ' _at_asmPeriod ', '
asmRegions   exec (_at_str) GO
27
Using On-demand sub-reports for Drill Down

• Scenario
• Report that retrieves almost 1 million records
  from the database
• Report initially displays 3 pages and supports
  drill down to 5 levels
• Challenge
• The Crystal Enterprise page server takes 5
  minutes to display the first page of the report
  due to the file size and number of summaries
• Solution
• Use aggregate functions in a stored procedure to
  reduce the initial record set and implement drill
  down with on-demand sub-report
• The first 4 levels only generate 1,000 unique
  values. The 5th level was 1000 to one ratio
• Issue
• Report supported dynamic grouping with dynamic
  grouping

Supplement pages 20-22
28
Topics

• Database SQL
• Pre-processing data
• Crystal SQL control
• Real life challenges
• Crystal Reports performance tips

29
Miscellaneous Troubleshooting

• Using sub-reports as part key look ups or to work
  around join limitations
• Create a formula to extract the portion of the
  string and use as linking field
• Effects of join types
• Equal versus left join
• Getting records with no children
• Using right joins for integrity checking
• Forcing equal joins in PC databases

30
8 Crystal Reports Performance Tips

• Check design and condition of database/data
  source
• Select Use Index or Server for Speed and
  Perform Grouping on Server options in Crystal
  Reports
• Check to see if database indexes support the most
  common record selection
• Make sure the table that will have the best index
  hit is first in the linking
• Avoid using OR conditions
• Create efficient formulas
• Running totals are not the holy grail!
• Dont use formulas in record selection
• Watch the record count in the status bar
• Sub-report placement

31
Stump the geek!

• Questions
• Contact information
• Email kmccann_at_virtual-consulting.net