June 17, 2013 at 9:41 am
Hi Friends,
I have an unusual problem that I've been unable to find any info to help, so I'm hoping someone can give me a clue. :crazy:
I have VBA code that builds dynamic queries that are sent in pass-through queries to SQL Server. This has been working fine. Now, a client reported that this process hangs.
On stepping through the code, what I found is that each subquery with a NOT EXISTS() is causing the query to hang.
When I rewrite the query to use a LEFT OUTER JOIN, then the query runs.
I believe something on the server has changed since these queries ran before.
I don't want to rewrite all of my SQL just for the sake of this one installation. Is there some setting on SQL Server or an update they may have run that could cause EXISTS or subqueries to not work?
Below is only one example of how I rewrote a query to make it run. Then the process went ahead until it hit another NOT EXISTS subquery.
Here is one query with NOT EXISTS() that hangs:
-- Using NOT EXISTS()
[font="Courier New"]INSERT INTO tForecast ( ExportDate, Dept, Class, Item, Vendor, WeekEndDate, DPCI, BaseSales, EventSales, TotalSales, BaseOrder, EventOrder, TotalOrder, EventDescrip, CollabSales, CollabOrders, EventType, UPC, ReasonCode, FSI, PromotionalDisplay, PicturedItem, Lift, Shipment_Import, Shipment_Domestic, Orders_Total, Orders_Import )
SELECT X.ExportDate, X.Dept, X.Class, X.Item, X.Vendor, X.WeekEndDate, X.DPCI, X.BaseSales, X.EventSales, X.TotalSales, X.BaseOrder, X.EventOrder, X.TotalOrder, X.EventDescrip, X.CollabSales, X.CollabOrders, X.EventType, X.UPC, X.ReasonCode, X.F_S_I, X.Promotional_Display, X.Pictured_Item , X.Lift, X.Shipment_Import, X.Shipment_Domestic, X.Orders_Total, X.Orders_Import
FROM
( SELECT '5/25/2013' AS ExportDate, tForecast.Dept, tForecast.Class, tForecast.Item, tForecast.Vendor, tForecast.WeekEndDate, tForecast.DPCI, tForecast.BaseSales, tForecast.EventSales, tForecast.TotalSales, tForecast.BaseOrder, tForecast.EventOrder, tForecast.TotalOrder, tForecast.EventDescrip, tForecast.CollabSales, tForecast.CollabOrders, tForecast.EventType, tForecast.UPC, tForecast.ReasonCode, IsNull([FSI],0) AS F_S_I, IsNull([PromotionalDisplay],0) AS Promotional_Display, IsNull([PicturedItem],0) AS Pictured_Item, tForecast.Lift, tForecast.Shipment_Import, tForecast.Shipment_Domestic, tForecast.Orders_Total, tForecast.Orders_Import
FROM tForecast INNER JOIN
(SELECT DISTINCT DeptID, ClassID, VendorID FROM AVP_FORECAST_HOLDING_usersName) AS XQ2
ON (tForecast.Class = XQ2.ClassID) AND (tForecast.Dept = XQ2.DeptID) AND (tForecast.Vendor = XQ2.VendorID)
WHERE (((tForecast.WeekEndDate)>'5/18/2013') AND ((tForecast.ExportDate)='5/18/2013'))
) X
WHERE
NOT EXISTS(SELECT DPCI FROM tForecast WHERE x.DPCI = tForecast.DPCI AND tForecast.ExportDate = '5/25/2013')[/font]
-- Here is the same query rewritten to use LEFT OUTER JOIN that runs:
[font="Courier New"]
INSERT INTO tForecast ( ExportDate, Dept, Class, Item, Vendor, WeekEndDate, DPCI, BaseSales, EventSales, TotalSales, BaseOrder, EventOrder, TotalOrder, EventDescrip, CollabSales, CollabOrders, EventType, UPC, ReasonCode, FSI, PromotionalDisplay, PicturedItem, Lift, Shipment_Import, Shipment_Domestic, Orders_Total, Orders_Import )
SELECT X.ExportDate, X.Dept, X.Class, X.Item, X.Vendor, X.WeekEndDate, X.DPCI, X.BaseSales, X.EventSales, X.TotalSales, X.BaseOrder, X.EventOrder, X.TotalOrder, X.EventDescrip, X.CollabSales, X.CollabOrders, X.EventType, X.UPC, X.ReasonCode, X.F_S_I, X.Promotional_Display, X.Pictured_Item , X.Lift, X.Shipment_Import, X.Shipment_Domestic, X.Orders_Total, X.Orders_Import
FROM
( SELECT '5/25/2013' AS ExportDate, tForecast.Dept, tForecast.Class, tForecast.Item, tForecast.Vendor, tForecast.WeekEndDate, tForecast.DPCI, tForecast.BaseSales, tForecast.EventSales, tForecast.TotalSales, tForecast.BaseOrder, tForecast.EventOrder, tForecast.TotalOrder, tForecast.EventDescrip, tForecast.CollabSales, tForecast.CollabOrders, tForecast.EventType, tForecast.UPC, tForecast.ReasonCode, IsNull([FSI],0) AS F_S_I, IsNull([PromotionalDisplay],0) AS Promotional_Display, IsNull([PicturedItem],0) AS Pictured_Item, tForecast.Lift, tForecast.Shipment_Import, tForecast.Shipment_Domestic, tForecast.Orders_Total, tForecast.Orders_Import
FROM tForecast INNER JOIN
(SELECT DISTINCT DeptID, ClassID, VendorID FROM AVP_FORECAST_HOLDING_usersName) AS XQ2
ON (tForecast.Class = XQ2.ClassID) AND (tForecast.Dept = XQ2.DeptID) AND (tForecast.Vendor = XQ2.VendorID)
WHERE (((tForecast.WeekEndDate)>'5/18/2013') AND ((tForecast.ExportDate)='5/18/2013'))
) X
LEFT OUTER JOIN
(SELECT DISTINCT DPCI, ExportDate FROM tForecast WHERE tForecast.ExportDate = '5/25/2013') Y ON x.dpci = Y.DPCI AND x.ExportDate = Y.exportdate WHERE y.DPCI IS NULL[/font]
June 17, 2013 at 9:53 am
Hi FishRman.
Not sure why it should suddenly start failing. What version(s) of MSSQL are you seeing this on.
What applications and versions is the VBA running in and is the user application on the same machine/network as the server.
Do you know whether SQL is actually getting the Remote Proc Call from the application.
I know you said that the VBA was writing dynamic SQL. This will have a number of performance impacts (one being that SQL will probably not be able to Cache the query plan) Also I advocate that data access is always done by the database and that all reads and writes should be via stored procedures. No only is this significantly faster in most cases, but you can control security and ensure that all front end applications get and consume data in the same way by implementing these sprocs as part of an API interface.
June 17, 2013 at 9:55 am
I've seen this behaviour before, and tracked it down to when the outer reference was either coming from a JOIN or from a derived table.
In the end I inserted the data from the main query into a temporary table before doing a NOT EXISTS between the Temp and Chec table.
You could also do a SELECT * FROM on the NOT EXISTS clause as I belive SQL Server is optimised for the SELECT * in this case.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 17, 2013 at 10:15 am
Jason-299789 (6/17/2013)
You could also do a SELECT * FROM on the NOT EXISTS clause as I belive SQL Server is optimised for the SELECT * in this case.
Don't think it matters. It'll pretty much ignore *, constant, column as it's logically all the same.
Nothing inherently wrong with NOT EXISTS, although remember that they're not semantically identical, so you can't really compare them.
Could you post Actual Execution Plans for both query forms?
June 17, 2013 at 10:24 am
aaron.reese (6/17/2013)
I know you said that the VBA was writing dynamic SQL. This will have a number of performance impacts (one being that SQL will probably not be able to Cache the query plan) Also I advocate that data access is always done by the database and that all reads and writes should be via stored procedures. No only is this significantly faster in most cases, but you can control security and ensure that all front end applications get and consume data in the same way by implementing these sprocs as part of an API interface.
It's not true that query plans will not be cached when SQL is called direct from code. Whether they're re-usable for different parameter values will depend on how it's called in application code.
June 17, 2013 at 12:42 pm
aaron.reese (6/17/2013)
Hi FishRman.Not sure why it should suddenly start failing. What version(s) of MSSQL are you seeing this on.
I don't have direct access to their SQL Server so I'll have to get back to you on that.
What applications and versions is the VBA running in and is the user application on the same machine/network as the server.
Access 2007
The app is not on the same machine as the SQL Server.
The app is on the same network as the SQL Server.
Do you know whether SQL is actually getting the Remote Proc Call from the application.
Without actually geting access to the SQL Server and looking at the activity monitor, I couldn't say for sure. But the fact that I can paste the query into a pass-through query and watch it hang. Then paste the other version of it into the same pass-through query and watch it work implies to me that the command is getting through.
I know you said that the VBA was writing dynamic SQL. This will have a number of performance impacts (one being that SQL will probably not be able to Cache the query plan) Also I advocate that data access is always done by the database and that all reads and writes should be via stored procedures. No only is this significantly faster in most cases, but you can control security and ensure that all front end applications get and consume data in the same way by implementing these sprocs as part of an API interface.
No disagreement here but this is a 10 year old system that we have to maintain as we rewrite the entire app.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply