nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Performance problem with left Joins

arunkumarb

Dear All,

In my report,I am facing performance problem with more numebr of left joins.

For example, If I need to display 15 columns in my report,where these 15 columns are available in 15 different tables. So because of these 15 columns, I am performing left joins on all 15 tables. For this reason, the query will take more time to execute. This causes performance issue.
How to avoid this problem.

Regards,
Arun

Find more posts tagged with

Comments

Hans_vd

Hi Arun,

Are you performing the joins in the query or are you creating joint data sets?

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110784" data-time="1350914206" data-date="22 October 2012 - 06:56 AM">
Hi Arun, 
 
Are you performing the joins in the query or are you creating joint data sets? </blockquote>
 
 
Hi Hans_vd, 
 
Thanks for your reply. I am performing the joins in the Query only and Directly I am pasting this query in dataset editor. 
 
Regards, 
Arun

Hans_vd

So it is not really a BIRT issue, is it?

Do you really need to LEFT join the 15 tables? Is INNER join not possible?
Do you have the right indexes on the right columns?
How big are these tables?

Why do you want data from 15 different tables in the same report? Are you sure you have your datamodel designed the right way?

A lot of questions :-)
But "I have a performance problem" is just too little information to solve it.

Regards
Hans

arunkumarb

Hi Hans,
I will explain my issue with more clearly.

For example, In a couple of months ago, My management asked me to prepare one report in that report they want to see every employee details like id,name,date of birth, date of joining, department,designation,previous company,total experience, salary etc.

If the above information optional for every employee that means if some x employee is having partial information i.e. he is not having date of birth and previous company details in my database, leave those fields as blank and show remaining fields.

In my DB the tables design is like this:

1. Employeeid will be in primary table.
2. Date of birth will be in personal table.
3. Date of joining will be in profile table.
Like that all the fields are available in different tables.

For example

Assume primary table is having 5 employee ids and personal table is having 3 employee data of births.
If I use left join on those two tables i will get 5 employee ids and 3 employee data of births and remaining two employees data of births as null.

But If I use inner join I will get only three employee ids and their date of births. I will not get employee detils those who are not having their date of birth details in personal table.

So thats why I am going for left join instead of inner join.

Hope you can understand my words and correct me if i wrong.

Regards,
Arun

Hans_vd

I understand.

So Personal table and Profile table have employeeid in it. Is there an index on these columns?

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110837" data-time="1350976316" data-date="23 October 2012 - 12:11 AM">
I understand. 
 
So Personal table and Profile table have employeeid in it. Is there an index on these columns? </blockquote>
 
 
Hi Hans_vd, 
Thanks for your concern. 
Yes, Personal and profile table have employeeid. There is no index on these columns. 
 
Regards, 
Arun

Hans_vd

Do all or some of these tables have a lot of data?
Do all of these 15 tables have no index on employeeid?
if the answer to both questions is yes, then I can understand why you're having performance problems.

If you read data from a non-indexed table, the database will have to read every record and see if the employeeid equals the employeeid you are looking for.
Unless your report will show data for every employee in your database I would defenitely put indexes on the tables.

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110968" data-time="1351244887" data-date="26 October 2012 - 02:48 AM">
Do all or some of these tables have a lot of data? 
Do all of these 15 tables have no index on employeeid? 
if the answer to both questions is yes, then I can understand why you're having performance problems. 
 
If you read data from a non-indexed table, the database will have to read every record and see if the employeeid equals the employeeid you are looking for. 
Unless your report will show data for every employee in your database I would defenitely put indexes on the tables. </blockquote>
 
 
Hi Hans, 
 
primary table is having 10800 records. Like each and every table is having approximately 10k records. 
I do not know about indexs. I need to ask my team lead or project manager for indexes in primary and remaining tables. Let me know how to check whether the table is having indexes or not?? 
 
 
Regards, 
Arun

Hans_vd

What database are you selecting from?
And do you have a tool to look at your database? Or how do you have acces to it?

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110972" data-time="1351251316" data-date="26 October 2012 - 04:35 AM">
What database are you selecting from? 
And do you have a tool to look at your database? Or how do you have acces to it? </blockquote>
 
 
Hi Hans, 
I am using MySql 5.5 Database. In that we are having different schemas like heterohrm_prod, adm_prod, lcm_prod etc. 
 
I am using heterohrm_prod schema. heterohrm_prod schema is having tables like primary, personal, profile etc. 
 
We are using MySql Query browser, It is UI tool for to develop queries. 
 
we are creating a new JDBC Driver Data source for each and every report. From this connection we are accessing data from Data base to my report. 
 
Regards, 
Arun

Hans_vd

Hi Arun, 
 
I don't know MySQL Query Browser, but I'm sure that you can browse tables/indexes with it. 
Otherwise, take a look at <a class='bbc_url' href='http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table'>this</a>

arunkumarb

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110974" data-time="1351258302" data-date="26 October 2012 - 06:31 AM">
Hi Arun, 
 
I don't know MySQL Query Browser, but I'm sure that you can browse tables/indexes with it. 
Otherwise, take a look at <a class='bbc_url' href='http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table'>this</a> </blockquote>
 
 
Ok Hans. Thank you so much.