Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to reduce the joins in sql querry Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 2:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 69, Visits: 306
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
Post #1513748
Posted Wednesday, November 13, 2013 2:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
Firstly, are the joins the problem?

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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1513754
Posted Wednesday, November 13, 2013 3:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 69, Visits: 306
with joins can i use temp tables can it improve the performance
Post #1513770
Posted Wednesday, November 13, 2013 3:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:05 AM
Points: 69, Visits: 306
with out joins can i use temp tables whether it will improve performance or not
Post #1513771
Posted Wednesday, November 13, 2013 3:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #1513772
Posted Wednesday, November 13, 2013 6:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 4,358, Visits: 6,193
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 at GMail
Post #1513838
Posted Wednesday, November 13, 2013 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 13,741, Visits: 10,716
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1513856
Posted Thursday, November 14, 2013 4:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1514217
Posted Thursday, November 14, 2013 5:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1514222
Posted Thursday, November 14, 2013 5:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #1514236
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse