Urgent SQL view help required
-
Hi,
Urgent help required please...
I am having big problems with a View within our Database.
At the core of the View is a Table containing 680,000 records. This Table is linked to 10+ other Tables in order to form a denormalised recordset to be used to build a Cube.
Last week this View worked fine. It returned 680,000 records in 35 seconds (not sure if that is a good time or not). However, at the end of last week I rename one of the Columns. This column does not link to other Tables.
Now, since this change (plus 100 extra records added)...
If I do....
Select count(*) from vwTest
This takes 4 seconds and returned the correct number of rows.
However, if I do
Select top 1 * from vwTest, after 4 minutes it still hasn't worked.
Why is select count and select top so vastly different. What could possible be wrong with the View. I can open it in designer etc and it looks OK.
Here is the SQL.
SELECT dbo.tcOrder.System_Code, dbo.trSystem.System_Description AS System, dbo.tcOrder.Order_Number,
dbo.trReportingRegion.Reporting_Region_Description AS Reporting_Region, dbo.tcOrder.Global_Production_Location_Code,
dbo.trGlobalProductionLocation.Global_Production_Location, dbo.tcProduct.Product_Description, dbo.tcOrder.Product_Delivery_Lead_Time,
dbo.trProductionTeam.Production_Team_Description AS Production_Team, dbo.tcProduct.Global_Master_Customer_Code AS Master_Customer_Code,
dbo.trMasterCustomer.Master_Customer_Name AS Master_Customer, dbo.trFamily.Family_Description AS Product_Family,
dbo.tcProduct.Report_Group, dbo.tcProduct.Global_Product_Line_Code, dbo.trGlobalProductLine.Global_Product_Line, dbo.tcProduct.Program_Name,
trRegion_Request.Region_Name AS Ship_To_Region, trRegion_Deliver_To.Region_Name AS Deliver_To_Region,
dbo.tcOrder.Date_Received AS Order_Receive_Date, dbo.tcOrder.Resolution_Date_Of_Last_Hold_Reason,
dbo.tcOrder.Global_Production_Location_Received_Date, dbo.tcOrder.Date_Sent_To_Production, dbo.tcOrder.Packed_Date,
dbo.tcOrder.Shipment_Due_Date, dbo.tcOrder.Shipment_Date, dbo.tcOrder.Quantity_Shipped, dbo.tcOrder.Order_Value_In_USD,
dbo.tcProductGlobalProductionLocation.Local_Product_Line, dbo.tcOrder.Turn_Time_Calculation_Start_Date,
dbo.tcOrder.Number_Of_Days_Waiting_For_Production, dbo.tcOrder.Number_Of_Days_To_Produce,
dbo.tcOrder.Number_Of_Days_Waiting_For_Shipment, dbo.tcOrder.Number_Of_Days_In_Production,
dbo.tcOrder.Number_Of_Days_Shipped_Early_Or_Late, dbo.tcOrder.Total_Turn_Time,
dbo.tcOrder.Adjusted_Number_Of_Days_Shipped_Early_Or_Late, dbo.tcOrder.Adjusted_Total_Turn_Time, dbo.tcOrder.Reason_Code,
tcCustomerMaster_Ship_To.Region_Code, dbo.trReason.Reason, dbo.trBusinessUnitAssignment.Business_Unit
FROM dbo.tcOrder INNER JOIN
dbo.trSystem ON dbo.tcOrder.System_Code = dbo.trSystem.System_Code INNER JOIN
dbo.trGlobalProductionLocation ON
dbo.tcOrder.Global_Production_Location_Code = dbo.trGlobalProductionLocation.Global_Production_Location_Code INNER JOIN
dbo.trReportingRegion ON dbo.trGlobalProductionLocation.Reporting_Region_Code = dbo.trReportingRegion.Reporting_Region_Code INNER JOIN
dbo.tcProduct ON dbo.tcOrder.System_Code = dbo.tcProduct.System_Code AND
dbo.tcOrder.Product_Number = dbo.tcProduct.Product_Number INNER JOIN
dbo.trMasterCustomer ON dbo.tcProduct.Global_Master_Customer_Code = dbo.trMasterCustomer.Master_Customer_Code INNER JOIN
dbo.trFamily ON dbo.tcProduct.System_Code = dbo.trFamily.System_Code AND dbo.tcProduct.Family_Code = dbo.trFamily.Family_Code INNER JOIN
dbo.trGlobalProductLine ON dbo.tcProduct.Global_Product_Line_Code = dbo.trGlobalProductLine.Global_Product_Line_Code INNER JOIN
dbo.trReason ON dbo.tcOrder.Reason_Code = dbo.trReason.Reason_Code LEFT OUTER JOIN
dbo.trBusinessUnitAssignment ON dbo.trMasterCustomer.Master_Customer_Code = dbo.trBusinessUnitAssignment.Master_Customer_Code AND
dbo.tcOrder.Global_Production_Location_Code = dbo.trBusinessUnitAssignment.Global_Production_Location_Code LEFT OUTER JOIN
dbo.tcCustomerMaster tcCustomerMaster_Deliver_To LEFT OUTER JOIN
dbo.trRegion trRegion_Deliver_To ON tcCustomerMaster_Deliver_To.System_Code = trRegion_Deliver_To.System_Code AND
tcCustomerMaster_Deliver_To.Region_Code = trRegion_Deliver_To.Region_Code ON
dbo.tcOrder.System_Code = tcCustomerMaster_Deliver_To.System_Code AND
dbo.tcOrder.System_Code = tcCustomerMaster_Deliver_To.System_Code AND
dbo.tcOrder.System_Code = tcCustomerMaster_Deliver_To.System_Code AND
dbo.tcOrder.System_Code = tcCustomerMaster_Deliver_To.System_Code AND
dbo.tcOrder.Deliver_To_Account_Number = tcCustomerMaster_Deliver_To.Account_Number LEFT OUTER JOIN
dbo.tcCustomerMaster tcCustomerMaster_Ship_To LEFT OUTER JOIN
dbo.trRegion trRegion_Request ON tcCustomerMaster_Ship_To.Region_Code = trRegion_Request.Region_Code AND
tcCustomerMaster_Ship_To.System_Code = trRegion_Request.System_Code ON
dbo.tcOrder.System_Code = tcCustomerMaster_Ship_To.System_Code AND dbo.tcOrder.System_Code = tcCustomerMaster_Ship_To.System_Code AND
dbo.tcOrder.System_Code = tcCustomerMaster_Ship_To.System_Code AND
dbo.tcOrder.System_Code = tcCustomerMaster_Ship_To.System_Code AND
dbo.tcOrder.Ship_To_Account_Number = tcCustomerMaster_Ship_To.Account_Number LEFT OUTER JOIN
dbo.trProductionTeam INNER JOIN
dbo.tcProductGlobalProductionLocation ON
dbo.trProductionTeam.Production_Team_Code = dbo.tcProductGlobalProductionLocation.Production_Team_Code ON
dbo.trGlobalProductionLocation.Global_Production_Location_Code = dbo.tcProductGlobalProductionLocation.Global_Production_Location_Code AND
dbo.tcProduct.System_Code = dbo.tcProductGlobalProductionLocation.System_Code AND
dbo.tcProduct.Product_Number = dbo.tcProductGlobalProductionLocation.Product_Number
WHERE (dbo.tcOrder.Shipment_Date >=
(SELECT [Data_Cube_Minimum_Shipment_Date]
FROM trWPR2Settings)) AND (dbo.tcOrder.Shipment_Date <=
(SELECT [Data_Cube_Maximum_Shipment_Date]
FROM trWPR2Settings))
-
Neil,
What column specifically did you change?
Did you ALTER the view after to made the column name change?
Has the WHERE clause shipment date span changed? Run the SELECT TOP 1 * manually on the main table, then add the JOINed tables one at a time to diagnose the views issues.
Note, I'd like to see the trWPR2Settings JOINed as a derived table for possibly better performance.
Have fun.
Once you understand the BITs, all the pieces come together
-
The view I changed its one of the selected Columns in this view. It is a column in the tcOrder file. The column too had nothing to do with Indexes either so I thought it would be a safe change to make.
I haven't altered the View at all. Just ran it after the column renaming and bosh... the view goes from 30 seconds execution time to ... well, I haven't seen it finish, I know that it is more than 22 minutes though.
I don't really know what performance to expect from SQL for this sort of thing. I am self taught so don't really know what target to aim for. I have checked out all the indexes and everything looks fine.
Please could you explain the derived table functionality you mentioned.
-
As far as the "derived" table usage, give this a try...
Replace your entire WHERE clause with something like...
INNER JOIN (
SELECT TOP 1
[Data_Cube_Minimum_Shipment_Date] as MinDate,
[Data_Cube_Maximum_Shipment_Date] as MaxDate
FROM trWPR2Settings ) Derived
ON dbo.tcOrder.Shipment_Date BETWEEN Derived.MinDate and Derived.MaxDate
Even though if trWPR2Settings only has 1 record, then a regular INNER JOIN could work.
This is just an example, but the number of records & tables you are trying to get, you can really spend some time on this query and most likely optimize its performance. Again, manually create the SELECT again by adding 1 join at a time to see incrementally how each JOIN affect the result time, then target the worst ones first for optimization.
Once you understand the BITs, all the pieces come together -
Hi Neil,
Does any of the SQLs involved use ORDER BY ?
Can you rename the column back to its old name and gather execution plan ?
A simple comparation of execution plans may reveal the cause for the problem.
Also, I would check/update statistics on the affected table.
Regards,
Goce Smilevski.
-
ThomasH,
Thankyou for your ideas.
I have tried the derived Table link and this is the results.
Prior to adding the derived link to my select the view took 45 seconds when executing a select * from it in Query Analyser.
When I add the derived Tables code this execution time rockets up to 3 minutes 10 seconds.
Surely I am not doing something too complicated?
My SQL code as it stands currently is...
SELECT dbo.tcOrder.System_Code, dbo.trSystem.System_Description AS System, dbo.trReportingRegion.Reporting_Region_Description AS Reporting_Region,
dbo.tcOrder.Global_Production_Location_Code, dbo.trGlobalProductionLocation.Global_Production_Location, dbo.tcProduct.Product_Description,
dbo.tcOrder.Product_Delivery_Lead_Time, dbo.trProductionTeam.Production_Team_Description AS Production_Team,
dbo.tcProduct.Global_Master_Customer_Code AS Master_Customer_Code, dbo.trMasterCustomer.Master_Customer_Name AS Master_Customer,
dbo.trFamily.Family_Description AS Product_Family, dbo.tcProduct.Report_Group, dbo.tcProduct.Global_Product_Line_Code,
dbo.trGlobalProductLine.Global_Product_Line, dbo.tcProduct.Program_Name, Region_ShipTo.Region_Name AS Ship_To_Region,
Region_DeliverTo.Region_Name AS Deliver_To_Region, dbo.tcOrder.Shipment_Date, dbo.tcOrder.Quantity_Shipped, dbo.tcOrder.Order_Value_In_USD,
dbo.tcProductGlobalProductionLocation.Local_Product_Line, dbo.tcOrder.Number_Of_Days_Waiting_For_Production,
dbo.tcOrder.Number_Of_Days_To_Produce, dbo.tcOrder.Number_Of_Days_Waiting_For_Shipment, dbo.tcOrder.Number_Of_Days_In_Production,
dbo.tcOrder.Number_Of_Days_Shipped_Early_Or_Late, dbo.tcOrder.Total_Turn_Time,
dbo.tcOrder.Adjusted_Number_Of_Days_Shipped_Early_Or_Late, dbo.tcOrder.Adjusted_Total_Turn_Time, dbo.tcOrder.Reason_Code,
dbo.trReason.Reason, dbo.trBusinessUnitAssignment.Business_Unit
FROM dbo.trProductionTeam RIGHT OUTER JOIN
dbo.tcProductGlobalProductionLocation INNER JOIN
dbo.tcOrder INNER JOIN
dbo.trSystem ON dbo.tcOrder.System_Code = dbo.trSystem.System_Code INNER JOIN
dbo.trGlobalProductionLocation ON
dbo.tcOrder.Global_Production_Location_Code = dbo.trGlobalProductionLocation.Global_Production_Location_Code INNER JOIN
dbo.trReportingRegion ON dbo.trGlobalProductionLocation.Reporting_Region_Code = dbo.trReportingRegion.Reporting_Region_Code INNER JOIN
dbo.tcProduct ON dbo.tcOrder.System_Code = dbo.tcProduct.System_Code AND
dbo.tcOrder.Product_Number = dbo.tcProduct.Product_Number INNER JOIN
dbo.trMasterCustomer ON dbo.tcProduct.Global_Master_Customer_Code = dbo.trMasterCustomer.Master_Customer_Code INNER JOIN
dbo.trFamily ON dbo.tcProduct.System_Code = dbo.trFamily.System_Code AND dbo.tcProduct.Family_Code = dbo.trFamily.Family_Code INNER JOIN
dbo.trGlobalProductLine ON dbo.tcProduct.Global_Product_Line_Code = dbo.trGlobalProductLine.Global_Product_Line_Code INNER JOIN
dbo.trReason ON dbo.tcOrder.Reason_Code = dbo.trReason.Reason_Code ON
dbo.tcProductGlobalProductionLocation.System_Code = dbo.tcOrder.System_Code AND
dbo.tcProductGlobalProductionLocation.Product_Number = dbo.tcOrder.Product_Number AND
dbo.tcProductGlobalProductionLocation.Global_Production_Location_Code = dbo.tcOrder.Global_Production_Location_Code ON
dbo.trProductionTeam.Production_Team_Code = dbo.tcProductGlobalProductionLocation.Production_Team_Code LEFT OUTER JOIN
dbo.trRegion Region_ShipTo INNER JOIN
dbo.tcCustomerMaster CustomerMaster_ShipTo ON Region_ShipTo.System_Code = CustomerMaster_ShipTo.System_Code AND
Region_ShipTo.Region_Code = CustomerMaster_ShipTo.Region_Code ON dbo.tcOrder.System_Code = CustomerMaster_ShipTo.System_Code AND
dbo.tcOrder.Ship_To_Account_Number = CustomerMaster_ShipTo.Account_Number LEFT OUTER JOIN
dbo.tcCustomerMaster CustomerMaster_DeliverTo RIGHT OUTER JOIN
dbo.trRegion Region_DeliverTo ON CustomerMaster_DeliverTo.System_Code = Region_DeliverTo.System_Code AND
CustomerMaster_DeliverTo.Region_Code = Region_DeliverTo.Region_Code ON
dbo.tcOrder.System_Code = CustomerMaster_DeliverTo.System_Code AND
dbo.tcOrder.Deliver_To_Account_Number = CustomerMaster_DeliverTo.Account_Number LEFT OUTER JOIN
dbo.trBusinessUnitAssignment ON
dbo.tcOrder.Global_Production_Location_Code = dbo.trBusinessUnitAssignment.Global_Production_Location_Code AND
dbo.trMasterCustomer.Master_Customer_Code = dbo.trBusinessUnitAssignment.Master_Customer_Code INNER JOIN
(SELECT TOP 1 [Data_Cube_Minimum_Shipment_Date] AS MinDate, [Data_Cube_Maximum_Shipment_Date] AS MaxDate
FROM trWPR2Settings) Derived ON dbo.tcOrder.Shipment_Date BETWEEN Derived.MinDate AND Derived.MaxDate
-
Changing things can make a big difference....
Anyway, I noticed that you have an INNER JOIN on [dbo.tcorder] without an ON clause ??? This should give you an error ???? (same with [dbo.trRegion Region_ShipTo], [dbo.tcCustomerMaster CustomerMaster_DeliverTo])
Also, your INNER JOIN [dbo.trReason] has 2 ON clauses ????
As far as performance, you could...
-- To get rid of the Derived table, and the access to trWPR2Settings within the SELECT
-- Before the SELECT
Declare @MinDate DateTime, @MaxDate DateTime
SELECT TOP 1 @MinDate = [Data_Cube_Minimum_Shipment_Date],
@MaxDate = [Data_Cube_Maximum_Shipment_Date]
FROM trWPR2Settings
-- Then somewhere (Either the WHERE, or the ON clause) put
dbo.tcOrder.Shipment_Date BETWEEN @MinDate AND @MaxDate
There are other things to do also.... Again, work with 1 table at a time, and see which one gives you a performance hit, then tweak.
Once you understand the BITs, all the pieces come together -
ThomasH,
Thankyou for your comments.
I will copy the SQL into NotePad and format the joins to identify any duplicates. I don't understand why they are in there as I am using the SQL View Designer. Was hoping that the SQL this produced would be OK. I know my joins in the Database Diagram are OK.
I like your idea of performing the select first to obtain the dates. However, if my understanding is OK I thought a View can only work on one select at a time so I couldn't use this type of code?
-
Yes, you are correct in that a VIEW has to be a SELECT statement. I often think of a T-SQL script to do the work when "CREATE VIEW..." is not in the segment of code I'm looking at. However, unless you specifically need a VIEW, in many cases a T-SQL "script" producing the result set may provide performance benifits.
Once you understand the BITs, all the pieces come together
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply