SQL Clone
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5220 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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91705 Visits: 45285
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5220 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
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42693 Visits: 32667
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5220 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
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42693 Visits: 32667
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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91705 Visits: 45285
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