Particular SQL Query taking higher time when run in background

[Former Member]

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.

[Former Member]

Hi Saransh,

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

Thanks & Regards

Richa

[Former Member]

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

[Former Member]

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

[Former Member]

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

[vinodkumar_thangavel]

Hi,

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

Regards,

Vinodkumar.

[Former Member]

Hi Vinod,

Yes, that worked. Thanks.

Saransh Walia

[Former Member]

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,