May 23, 2014 at 4:55 am
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.
May 23, 2014 at 5:04 am
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...
May 23, 2014 at 5:08 am
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
May 23, 2014 at 5:51 am
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
May 23, 2014 at 6:25 am
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.
May 23, 2014 at 8:01 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2014 at 8:35 am
... SQL Server doesn't really support code reuse in a useful way...
I would say that it's very arguable statement...
😉
May 24, 2014 at 7:08 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2014 at 10:47 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply