CODEWITHSUNDEEP
sql-servert-sql
d90
d90

Reputation: 787

TSQL Query of View takes a long time

I have a series of views I created for a bunch of different reports. They build off each other to a final view that returns less than 5k records. When I open this view, it takes less than a second to run. If I add a WHERE collumn IS NOT NULL it takes over 2 minutes. While this is fine for reports I don't need to run very often, there are others that I have to run weekly, daily, and sometimes more often.

My solution was to create a stored procedure that makes a table from the view's data, and query that. I set the stored procedure to run before the scheduled reports automatically fire off. This works OK but I have to run the stored procedure manually every time I want to get an updated report.

Is there a better way?

View [MW_Submitted_ROY] to find Submitted Weeks financials. This view combines an active table with historic data in a static table. I massaged the data in the static table to be in the same format as the active one. 

SELECT     oe_po_no, cus_no, user_def_fld_1, user_def_fld_2, tot_sls_amt
FROM         MW_Submitted_Weeks_OldMacola
WHERE     MW_Submitted_Weeks_OldMacola.oe_po_no LIKE 'adv%'
UNION
SELECT     oe_po_no, cus_no, user_def_fld_1, user_def_fld_2, tot_sls_amt
FROM         oehdrhst_sql
WHERE     oehdrhst_sql.oe_po_no LIKE 'adv%'

View [MW_Company_Weeks] to generate all possible weeks per company that they would submit financials for the current and previous year. 

SELECT     MW.MW_Weeks.Year, MW.MW_Weeks.Week, MW.MW_CompanyCodes.cmp_code
FROM         MW.MW_Weeks CROSS JOIN
                  MW.MW_CompanyCodes
 WHERE     (MW.MW_Weeks.WEDate <= GETDATE()) AND (MW.MW_Weeks.Year > YEAR(GETDATE()) - 2)

View [MW_Submitted_Weeks_By_RBC_ROY] combines more info about the company I need to generate reports

 SELECT     dbo.cicmpy.textfield2 AS [Group], dbo.cicmpy.cmp_code, dbo.cicmpy.cmp_name,          dbo.cicmpy.cmp_fadd3 AS Owner, dbo.cicmpy.SalesPersonNumber, 
                  MW.MW_Submitted_ROY.user_def_fld_1 AS Year, MW.MW_Submitted_ROY.user_def_fld_2 AS Week, MW.MW_Submitted_ROY.oe_po_no AS Description, 
                  MW.MW_Submitted_ROY.tot_sls_amt
 FROM         dbo.cicmpy INNER JOIN
                  MW.MW_Submitted_ROY ON dbo.cicmpy.cmp_code = MW.MW_Submitted_ROY.cus_no
 WHERE     (dbo.cicmpy.cmp_code LIKE 'C%') AND (dbo.cicmpy.cmp_status = 'a') AND (dbo.cicmpy.cmp_type = 'C')

Which brings us to the final View [MW_Missing_Weeks] which shows me which weeks are missing, and the value of the previous year submitted financials. This is where adding the 'is not null' makes it take forever.

 SELECT     MW.MW_Company_Weeks.Year, MW.MW_Company_Weeks.Week, MW.MW_Company_Weeks.cmp_code, bPrev.tot_sls_amt, bPrev.[Group], bPrev.cmp_name, bPrev.Owner, 
                  bPrev.SalesPersonNumber, bPrev.Description
 FROM         MW.MW_Company_Weeks LEFT OUTER JOIN
                  MW.MW_Submitted_Weeks_By_RBC_ROY ON MW.MW_Company_Weeks.cmp_code = MW.MW_Submitted_Weeks_By_RBC_ROY.cmp_code AND 
                  MW.MW_Company_Weeks.Year = MW.MW_Submitted_Weeks_By_RBC_ROY.Year AND MW.MW_Company_Weeks.Week = MW.MW_Submitted_Weeks_By_RBC_ROY.Week LEFT OUTER JOIN
                  MW.MW_Submitted_Weeks_By_RBC_ROY AS bPrev ON MW.MW_Company_Weeks.cmp_code = bPrev.cmp_code AND MW.MW_Company_Weeks.Year = bPrev.Year + 1 AND 
                  MW.MW_Company_Weeks.Week = bPrev.Week
 WHERE     (MW.MW_Submitted_Weeks_By_RBC_ROY.cmp_code IS NULL) AND bPrev.tot_sls_amt IS NOT NULL

Upvotes: 3

Views: 10004

Answers (1)

BClaydon
BClaydon

Reputation: 1970

In SSMS go to Query > Display Estimated Execution Plan, or hit Ctrl + L. Find the part of the plan that takes the highest percentage and see if there is "Missing Index" in green. Right click and choose "Missing Index Details." That will create the code to make the index. You just need to add the index name.

It will look something like this:

/*
USE [Your Database]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[YourTable] ([ColA])
INCLUDE ([ColB],[ColC])
GO
*/

That is a quick and dirty way to create an index.

You can use the Query Execution Plan to troubleshoot of issues too. It'll take some practice.

Upvotes: 7

Related Questions