topic Re: Join performance in Application Development and Automation Discussions

Join performance

Former Member — Thu, 08 Feb 2007 07:22:16 GMT

I have used the join technique to access BKPF entries joining with another temporary table i have created.

I have done a SQL trace to check the indexes being used and it does use the primary key from BKPF. However for say 60000 records to be returned to the application server the time taken is more than 2 minutes. I notice that it happens in packets of say 100 records each and this number is not the same always.

Can this be improved. are there any parameters which can be used to control the number of records from one single database round trip.

Kindly advice.

Re: Join performance

christian_wohlfahrt — Thu, 08 Feb 2007 09:05:28 GMT

Hi!

I think, this packages are based on network packages (of about 2KB). So if you want to have more lines per packet, reduce the fields you select (instead of having select *).

Regards,

Christian

Re: Join performance

Former Member — Thu, 08 Feb 2007 09:33:10 GMT

Thank you Christian,

Is it normal for 60000 rows to be transported from DB to App server to take over 2 minutes. I am selecting only 4 fields from the table. Can anything else be done.

Re: Join performance

christian_wohlfahrt — Thu, 08 Feb 2007 11:57:15 GMT

Hi,

when your 4 fields have about 20 bytes, then you have already 2KB packages for data transfer. In modern networks bigger packages are possible - but the link between DB and application server is usually not a bottle neck. Nevertheless - there are many places were performance can get lost. Even scanning huge (physical) areas of the HD to get the 4 fields from different entries will be slower then a simple sequential reading.

There are definitely faster systems / tables, where you can get 60 000 entries in less then 2 minutes, but difficult to say, where in your case the time is spend.

Re: Join performance

Former Member — Fri, 09 Feb 2007 10:30:47 GMT

hi,

If you are using clauses like into corresponding field or some thing like it is expected to slow down. Plus in the where clause try giving all the primary keys for selection if not all atleast try to put the maximum. Select/Endselect is a direct performance bug. Instead try using into table ,Try any of these things and if found useful ,reward thank you.

Mathivanan G

Re: Join performance

Former Member — Fri, 09 Feb 2007 11:11:15 GMT

Hi,

Please use the intrernal table of HASHED type... This allots memory for large number of data records..

u can go for hashed tables because u know that the amount of data retrieved is more..

This will definitly reduce the load on the serveer,,

Thanks

rajiv