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

Removing not necessary joins Expand / Collapse
Author
Message
Posted Friday, May 23, 2014 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:28 AM
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.
Post #1573960
Posted Friday, May 23, 2014 5:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1573962
Posted Friday, May 23, 2014 5:08 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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1573965
Posted Friday, May 23, 2014 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 12:28 AM
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
Post #1573977
Posted Friday, May 23, 2014 6:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1573982
Posted Friday, May 23, 2014 8:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1574039
Posted Friday, May 23, 2014 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
... 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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1574064
Posted Saturday, May 24, 2014 7:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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.


----------------------------------------------------
"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 #1574299
Posted Saturday, May 24, 2014 10:47 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:52 AM
Points: 42,445, Visits: 35,501
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 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 #1574320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse