How to reduce the joins in sql querry

  • Hi

    I have select query with 10 Joins in place with different i want to improve the performance of the

    query how do i join tables with out joins

    thanks

    Pradeep

  • Firstly, are the joins the problem?

    And short answer, you don't join tables without joins.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • with joins can i use temp tables can it improve the performance

  • with out joins can i use temp tables whether it will improve performance or not

  • Maybe, maybe not. Depends on the specifics of the queries, among other things. Temp tables aren't a replacement for joins, they're not an automatic performance improvement.

    Again, are you sure that the joins are the cause of the performance problems? If so, how did you determine that?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Assuming the query can complete, run it with show actual execution plan enabled. Then start looking for a) huge disparities between estimated and actual rows and b) sort/hash spills to disk. When you see either, consider a temp table intermediate object to break things apart and give the optimizer a better chance to get good plans (and avoid spills).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Maybe you need to add some indexes to the tables. Do you have any index specified currently on any of the tables?

    It's very hard to help you without the table DDL and the query involved.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Just to be clear, 10 join operations is not very much. Joins are not a bad thing within a relational data structure. In fact, they're fairly necessary.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • mpradeep23 (11/13/2013)


    how do i join tables with out joins

    You can .... for that you have to do table level changes like merging two tables (which contains parent-child relationship or similar kind of data).

    and that process is known as denormalization.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (11/14/2013)


    mpradeep23 (11/13/2013)


    how do i join tables with out joins

    You can .... for that you have to do table level changes like merging two tables (which contains parent-child relationship or similar kind of data).

    and that process is known as denormalization.

    and it's usually a bad idea.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 10 or more joins are not a problem.

    Bad normalized/designed data structure and no properly indexed tables are.

    Just try to outer join two big tables by no indexed columns or using implicit data type conversion and you ill fell the pain.

    (inner) joining 10 huge (well designed) tables by (tunned) indexed columns ill run smoothly.

  • jcb (11/20/2013)


    ...(inner) joining 10 huge (well designed) tables by (tunned) indexed columns ill run smoothly.

    Until you have some data value skew, in which case the probability of getting screwed increases with increasing numbers of joins

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply