March 30, 2009 at 4:17 pm
Hello all,
I have a query that has quite a complex subquery that performs ~30% better with certain query options.
The problem is, if I attempt to add the subquery to the rest of my query and put the option at the end, SQL Server returns that it can not generate a query plan because of my options.
Is there a way to add the option to JUST the subquery?
Is there a way around this?
Link to my blog http://notyelf.com/
March 30, 2009 at 4:26 pm
Shannon, could you please post up the query and subquery so that we have some idea of what you are attempting. You may have to do a workaround using a temporary table, or perhaps a cte might work. There's not really a lot to go on from your description.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2009 at 4:40 pm
Hi Bob,
Sorry about that. It is quite a large query and I would have to edit it quite a lot to post it here :).
I will show you the gist of it though
SELECT
FROM -- This is the complex subquery
LEFT JOIN Division Table
LEFT JOIN Warehouse Table
LEFT JOIN Item Table
LEFT JOIN Costs Table
That is the main query. As you can see there is no grouping, distinct or any of that type of calculation in the main query.
Here is the subquery
SELECT company, Warehouse, Item, MIN(Date) AS Date, Qty
FROM(SELECT company, Warehouse, Item, MIN(Date) AS Date, Qty FROM Availability
GROUP BY company, Warehouse, Item, Qty
HAVING MIN(Date) > CAST(CONVERT(NCHAR(8),GETDATE(),112) AS DECIMAL(8,0))
UNION
SELECT company, Warehouse, Item, CAST(CONVERT(NCHAR(8),GETDATE(),112) AS DECIMAL(8,0)) AS Date, Qty FROM
(SELECT T1.company, T1.Warehouse, T1.Item, MIN(T1.Date) AS Date, T1.Qty FROM Availability AS T1
JOIN (SELECT Warehouse, Item, MAX(Date) AS Date FROM .Availability
WHERE Date <= CAST(CONVERT(NCHAR(8),GETDATE(),112) AS DECIMAL(8,0)) GROUP BY Warehouse, Item
UNION
SELECT Warehouse, Item, MIN(Date) AS Date FROM Availability GROUP BY Warehouse, Item
HAVING MIN(Date) > CAST(CONVERT(NCHAR(8),GETDATE(),112) AS DECIMAL(8,0))) AS T2
ON T1.Warehouse = T2.Warehouse AND T1.Item = T2.Item AND T1.Date = T2.Date
GROUP BY T1.company, T1.Warehouse, T1.Item, T1.Qty) AS STDT) AS X
GROUP BY company, Warehouse, Item, Qty
Having the option of; OPTION (HASH JOIN, HASH UNION, HASH GROUP) greatly improves performance. But I do not know how to add this option within the subquery (or if there even is a way).
Perhaps a CTE is the next solution like you said Bob?
I will give that a whirl and see how that helps 🙂
Link to my blog http://notyelf.com/
March 30, 2009 at 5:12 pm
Actually I found out the problem.
I had a column in the query that was a minor subquery. I changed this to a variable instead and now it works fine 😀
Link to my blog http://notyelf.com/
March 30, 2009 at 5:18 pm
Great 🙂
Thanks for taking the time to let us know.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply