Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing not necessary joins


Removing not necessary joins

Author
Message
madhukars 32911
madhukars 32911
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Hi,

I am building a application, which will generates a MASTER query with 15 fields & 5 tables joins.
Now, the user selects only some fields from that master fields and generates the CHILD query. This is creating a performance issue.

To minimize this, i would like to remove the not necessary joins in the child query. Is there any approach or solution for the same.
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
I don't think that there is any "generic" approach.
Nothing usefull can be advised without knowing any sort of details.
Providing invloved table's and other objects DDLs, query itself and its current query plan could be helpful...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44376
The SQL parser/algebriser removes unnecessary tables from queries (unnecessary being ones which don't filter and don't return columns)

Are you sure that's what's causing the performance problems?


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


madhukars 32911
madhukars 32911
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Currently we are working with Northwind DB.
In the Master Query, we have the query as below
SELECT
[Customers].[CustomerID],
[Customers].[CompanyName],
[Customers].[City],
[Customers].[Region],
[Customers].[Country],
[Orders].[OrderDate],
[Orders].[Freight],
[OrderDetails].[UnitPrice],
[OrderDetails].[Quantity],
[OrderDetails].[Discount],
[Shippers].[CompanyName],
[Products].[ProductName],
[Categories].[CategoryName]
FROM
[Customers]
INNER JOIN [Orders]
ON
[Customers].[CustomerID]=[Orders].[CustomerID]
INNER JOIN [OrderDetails]
ON
[Orders].[OrderID]=[OrderDetails].[OrderID]
INNER JOIN [Shippers]
ON
[Orders].[ShipVia]=[Shippers].[ShipperID]
INNER JOIN [Products]
ON
[OrderDetails].[ProductID]=[Products].[ProductID]
INNER JOIN [Categories]
ON
[Products].[CategoryID]=[Categories].[CategoryID]



Now, in the child query the user is selecting only 2 fields
Categories.CategoryName, Orders.Freight

Then, we have to remove the unnecessary tables like Customers, Shippers and generate the query as below

SELECT

[Categories].[CategoryName],
[Orders].[Freight]

FROM
[Orders]
INNER JOIN [OrderDetails]
ON
[Orders].[OrderID]=[OrderDetails].[OrderID]
INNER JOIN [Products]
ON
[OrderDetails].[ProductID]=[Products].[ProductID]
INNER JOIN [Categories]
ON
[Products].[CategoryID]=[Categories].[CategoryID]

How can we make this in a automatic way using any code or approach
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
Create a dedicated view for this or use dynamic SQL to build relevant query, as in your case INNER JOINs in the MASTER query ensures that complete "data-tree" exists.
If you just remove them, you may find that the returned data will not be the same as with having them.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17580 Visits: 32260
Write two queries. SQL Server doesn't really support code reuse in a useful way. If you have two different sets of requirements, write two different queries.

----------------------------------------------------
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
... SQL Server doesn't really support code reuse in a useful way...

I would say that it's very arguable statement...
;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17580 Visits: 32260
Eugene Elutin (5/23/2014)
... SQL Server doesn't really support code reuse in a useful way...

I would say that it's very arguable statement...
;-)


And I am more than willing to have that discussion. w00t

----------------------------------------------------
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44376
Eugene Elutin (5/23/2014)
... SQL Server doesn't really support code reuse in a useful way...

I would say that it's very arguable statement...
;-)


I'd take Grant's side in any such argument.

In general, it's a trade off (in SQL) between code reusability and performance. Yes, there are way to write code like this so that it's reusable in a myriad of possible queries. The performance will suffer badly as a result.


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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search