Performance Increasing from Complex Select query with Joins on table having huge records.

[former_member628875]

Hello Experts,

I came across a select query which have joins and getting data from a table have record around 4708000500 records.

select query is like this:

SELECT DbTableName~bukrsMIN( DbTableName~abg_jahr ) AS abg_jahrMIN( DbTableName~abg_monat ) AS abg_monatSUM( DbTableName~betrag ) AS betragSUM( DbTableName~mwst_betrag ) AS mwst_betrag
DbTableName~ablif DbTableName~koart DbTableName~abg_datum DbTableName~abg_ze
DbTableName~simu_kz
abgpro~abgrstep FROM DbTableName AS DbTableName
INNER JOIN abgpro AS abgpro ON abgpro~abgid EQ DbTableName~abgid
INTO CORRESPONDING FIELDS OF TABLE it_DbTableNameWHERE DbTableName~abg_jahr = s_gjahrAND DbTableName~abg_monat = s_monatAND DbTableName~bukrs IN r_bukrsAND DbTableName~storno_kz = ' 'AND DbTableName~simu_kz = s_simu "' '

GROUP BY DbTableName~ablif DbTableName~bukrs DbTableName~koart DbTableName~abg_datum DbTableName~abg_zeit
DbTableName~simu_kz
abgpro~abgrstep ORDER BY DbTableName~ablif DbTableName~bukrs DbTableName~koart DbTableName~abg_datum DbTableName~abg_zeit
DbTableName~simu_kz
abgpro~abgrstep.

We know that we can simplify the select query by avoiding joins and aggregation etc.

But My questions are:-

1. On what we should focus to improve the performance?

2. What are the things we should keep in mind while dealing with this kind of big table?

3. If at all we increase the performance, how significantly it can be?

Thank you so much in advance .

[former_member628875]

Hello Experts,

I came across a select query which have joins and getting data from a table have record around 4708000500 records.

select query is like this:

SELECT DbTableName~bukrsMIN( DbTableName~abg_jahr ) AS abg_jahrMIN( DbTableName~abg_monat ) AS abg_monatSUM( DbTableName~betrag ) AS betragSUM( DbTableName~mwst_betrag ) AS mwst_betrag
DbTableName~ablif DbTableName~koart DbTableName~abg_datum DbTableName~abg_ze
DbTableName~simu_kz
abgpro~abgrstep FROM DbTableName AS DbTableName
INNER JOIN abgpro AS abgpro ON abgpro~abgid EQ DbTableName~abgid
INTO CORRESPONDING FIELDS OF TABLE it_DbTableNameWHERE DbTableName~abg_jahr = s_gjahrAND DbTableName~abg_monat = s_monatAND DbTableName~bukrs IN r_bukrsAND DbTableName~storno_kz = ' 'AND DbTableName~simu_kz = s_simu "' '

GROUP BY DbTableName~ablif DbTableName~bukrs DbTableName~koart DbTableName~abg_datum DbTableName~abg_zeit
DbTableName~simu_kz
abgpro~abgrstep ORDER BY DbTableName~ablif DbTableName~bukrs DbTableName~koart DbTableName~abg_datum DbTableName~abg_zeit
DbTableName~simu_kz
abgpro~abgrstep.

We know that we can simplify the select query by avoiding joins and aggregation etc.

But My questions are:-

1. On what we should focus to improve the performance?

2. What are the things we should keep in mind while dealing with this kind of big table?

3. If at all we increase the performance, how significantly it can be?

Thank you so much in advance .

[Sandra_Rossi]

Database performance is a very complex thing to explain in few lines, so my recommendation is to hire a database administrator + expert in the concerned functional domain/database model.

Okay just one hint: look at the execution plan, it explains what choice the database does.

Do you really extract 5 billion lines? That's the problem, it can only be slow 😉

[joltdx]

Also, if you avoid the join and aggregation, you also miss data and need to select again and do calculations with the data later, so that is probably not be an improvement performance-wise...

Are you selecting 5 000 000 000 records, or is that the size of the table? How many records are you selecting? And how long does this query take now? Is it really slow?

And then it's also dependent on which database you are using? And for these volumes, how much memory is available in the DB and in the server? What are you doing with the data afterwards? Why are you selecting this? etc...

[former_member628875]

@jorgen_lindqvist41 & @sandra.rossi: Thank you so much for your comments. Answering to your question.

This is the number of records in the Table.

How many records are you selecting: Not sure, of course not all but large ammount. like this, the report has another query. Due to this, it takes so many hours like 5 to 6 hours(Sometimes it also get timeout). This is really slow.

It's an ECC system, not a HANA database.

How much memory in the server: I Don't know will try to find out.Just wanted to know how significant it can be.

This is a report for analysis mostly.

My question: Is it possible to increase the performance by improving the select query? In this situation where DB is not HANA and having 5BN of records.

[Sandra_Rossi]

What database is using your ECC system? It's very important to tell, so that people can better help!

Did you try to analyze the execution plan? (search the Web if you don't know what it means, then ask more precise questions after that)

Did you try to parallelize the query?

Etc.

Search the Web for additional hints concerning improving performance for your specific database. Not SAP forums (except if it's Sybase).