November 13, 2013 at 2:39 am
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
November 13, 2013 at 2:48 am
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
November 13, 2013 at 3:22 am
with joins can i use temp tables can it improve the performance
November 13, 2013 at 3:23 am
with out joins can i use temp tables whether it will improve performance or not
November 13, 2013 at 3:26 am
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
November 13, 2013 at 6:25 am
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
November 13, 2013 at 6:48 am
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
November 14, 2013 at 4:34 am
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
November 14, 2013 at 5:11 am
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;-)
November 14, 2013 at 5:42 am
Bhuvnesh (11/14/2013)
mpradeep23 (11/13/2013)
how do i join tables with out joinsYou 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
November 20, 2013 at 3:36 am
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.
November 20, 2013 at 8:52 am
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