Recognize slow running join?

[former_member184582]

Hi

In my universe one of the join is taking much time? how did you recognize that join?

Thank You.

[Former Member · Accepted solution]

Hi Ganesh,

Which reporting database are you using?

In few databases, we have EXPLAIN command available which can give you details on the time taken by SQL at different phases for execution.

Syntax could be:

EXPLAIN (<your SQL>)

Apart from this, you can try below approach:

1. To narrow down to the join, first of all check the report which is taking time to execute.

2. Take out the SQL of the report and check the tables and columns coming into picture.

3. Check the joins one by one and see which one is returning larger result set.

4. Once found, check the cardinalites between the tables and check the columns on which they are joined. (Sometimes the primary column has repeating values which in turn return larger result set)

5. Change the column for join in case primary column does not have distinct or unique values.

If the joins, cardinalities and data in the database is correct but Huge, you can try to apply INDEXING on the required column.

Sit with your DBA and troubleshoot as he would be the best person to guide you

Regards,

Yuvraj

[Former Member]

Check the join SQL.

The fastest joins are inner joins on indexed integers.

The further you get away from that, the more cumbersome the join will become.

Converting a date to a character then joining would kill off the indexing you applied to a date.

Outer joins increase performance.

Multiple statements take longer to process e.g. A.col1 = B.col1 and A.col2 between B.col2 and B.col3

A lot of it will be by inspection but also by identifying slow running queries and viewing their explain plans.

[Former Member]

Agree with Mark, small typo though: Outer joins decrease performance.

[Former Member]

Yes, I meant response time, not performance. Oops

[Former Member]

Hi Ganesh,

Is your query answered? If yes, I would request you to close the thread.

Few other threads are also open but answered.

Regards,

Yuvraj

[Former Member]

Hii Ganesh ,

Kindly install fiddler in your system and then fire the SQL Query or Webi report and based on this Fiddler will provide you detail report of time taken right from Request and Response of the query .

Download Link :http://fiddler2.com/

Regards

Jeetan