topic Re: Particular SQL Query taking higher time when run in background in Application Development and Automation Discussions

Particular SQL Query taking higher time when run in background

Former Member — Mon, 19 Jan 2015 09:44:55 GMT

Hi,

I am facing an issue from below SQL query.

SQL Query ->

SELECT spras prodh vtext

FROM t179t

INTO TABLE i_t179t

FOR ALL ENTRIES IN hierarchy_table

WHERE ( spras = c_english OR spras = v_spras )

AND prodh = hierarchy_table-hierarchy.

hierarchy_table has around 1000 records and t179t has 680,000 records. i_t179t will have 1000 records as a result.

Now this query is taking around 2 seconds when run in dialog mode, but when the same is run in background it takes around 3 minutes.

Performance traces and SM66 shows confirms that this is the only query which is taking such a long time and affecting performance.

Re: Particular SQL Query taking higher time when run in background

Former Member — Mon, 19 Jan 2015 12:16:14 GMT

Hi Saransh,

For better performance, you can use joins on your tables.

Thanks & Regards

Richa

Re: Particular SQL Query taking higher time when run in background

Former Member — Mon, 19 Jan 2015 12:36:58 GMT

Hi Richa,

But I only have a single table from which data needs to fetched. The problem is why there is so much time difference between Dialog and background mode.

Thanks,

Saransh

Re: Particular SQL Query taking higher time when run in background

Former Member — Tue, 20 Jan 2015 05:33:48 GMT

Hi Saransh,

Generally, background jobs are slower than the dialog mode processes.

Plus it also depends upon the number of background jobs running at that point of time.

Thanks & Regards

Richa

Re: Particular SQL Query taking higher time when run in background

Former Member — Tue, 20 Jan 2015 06:48:17 GMT

Hi Richa,

Yes, you are right but the query is returning the result in 3 minutes for every run. I mean no matter whenever we fire that query the time taken is same.

So there is some issue/lag which is always happening. Do you think it is DATABASE level issue.?

Thanks,

Saransh Walia

Re: Particular SQL Query taking higher time when run in background

vinodkumar_thangavel — Tue, 20 Jan 2015 09:25:19 GMT

Hi,

Try to avoid OR condition it implies that the query is being executed twice.

Regards,

Vinodkumar.

Re: Particular SQL Query taking higher time when run in background

Former Member — Tue, 20 Jan 2015 10:59:18 GMT

Hi Saransh,

There can be 3 main for the performance issue in case of Dialog and background mode:

When the job is scheduled for the background process, it waits for the planned execution time in the job scheduling table.

If the time has come and free background work processes are available, the job is distributed to a background work process by the background scheduler and then executed.

Time is spent in this process whereas the same thing doesn't happen in case of dialog mode.

Second main reason is Dialog processes work with a shared memory segment (extended memory). Background processes have their private heap memory.

And the Third reason can be the database parameter which might need tuning for the performance optimization.

Regards,

Re: Particular SQL Query taking higher time when run in background

Former Member — Tue, 20 Jan 2015 11:34:06 GMT

Hi Vinod,

Yes, that worked. Thanks.

Saransh Walia