Delay in concurrent run same SQL with different parameter through Crystal report

Database

Delay in concurrent run same SQL with different parameter through Crystal report

Hi Dieter and Team,

 

In my current project I need to convert SQLs from DB2 to Teradata ( queries are simple ) and run them through crystal report application as front end. Now in the existing scenario they are running around 900+ same SQLs with different parameter (no of result rows is same for all ) in DB2 using 20 concurrent sessions and the job finishes in 5-7 minutes. However,when I tried to embed my converted Teradata SQLs in crystal report , only 10 parallel SQLs are taking more than 10 minutes which implies 900+ SQL would take huge time which will violate my SLA. I checked individual query execution from DBQL log and saw that impact cpu in less than 30 and PJI is less than 0.5 and if I run them in silo it's getting completed within couple of seconds. Our production system has a system limit of 2-5 concurrent sessions ( I might not be using the correct terminology ). Could you please kindly advise if there is any way to enhance the report run time. Do we need to change the settings somewhere ? Do we need to increase the number of concurrency in the system ? Or it could be a network connection issue. I have noticed in viewpoint query monitor that queries are staying in responding state for long time but finish off quickly once it comes to ACTIVE state.

 

Thanks in advance.

1 REPLY
Senior Supporter

Re: Delay in concurrent run same SQL with different parameter through Crystal report

This won't be easy to answer here - there are different things to consider:

Physical design of the tables - are the queries accessing the PI / PPI / SI,

Workload Management 

etc.

Clearly " Our production system has a system limit of 2-5 concurrent sessions ( I might not be using the correct terminology )" would limit your ability to run 20 concurrent sessions...

If the queries are all tactical be sure they are running in a high priority group...