May 31, 2011 at 11:14 am
One more ref.
More than you need to know about sql plans an index tuning by "our" own Grant Fitchey
http://www.sqlservercentral.com/articles/books/65831/
Sorry to burry you like this with articles but even using all our combined brains this one might take a heck of a while to tune over this thread. It's better you become the expert or call someone in.
Plz post back with your progress and further questions.
May 31, 2011 at 11:19 am
Last one I swear :w00t:.
Might be good you read this as well :
May 31, 2011 at 12:31 pm
I'm not sure if the query itself got "transported" properly since it looks like there is no join between MainDistricts/offices on one side and the Customers "join group" on the other side. Also, MainDistricts_1 and the join to InvalidSaleCategories table are missing.
To expand Ninja's divide'n'conquer approach a little bit further I would probably start with a temp table based on
;WITH cte AS
(
SELECT sales.SalesID, SUM(Amount) AS Credits
FROM sales INNER JOIN SalesTransactions ON SalesID = sales.SalesID
WHERE
(PmtType = 'hcc'
OR PmtType = 'hck'
OR PmtType = 'PRM'
)
AND (TransStatus <> 'v')
GROUP BY sales.SalesID
)
SELECT
sales.Saledate,
Customers.LastName + ' ' + Customers.FirstName AS Customer,
sales.Saledate,
sales.SalesID,
sales.OfficeID,
sales.BrokerFee,
Dealers.isdealer,
Dealers.NAME AS Source,
dbo.GetAgentNotes
(sales.PolicylogID,
(
SELECT Agent1ID
FROM DailyPolicyLog
WHERE (LogID = sales.PolicylogID)
)
) AS AgentNotes,
sales.PolicylogID,
MainDistricts.DMName,
--MainDistricts_1.DMName AS DMName1, ???? source???
sales.SaleIsInvalid,
-- InvalidSaleCategories.InvalidSaleCategory ???? source???
ISNULL(cte.Credits, 0) AS Credits
FROM MainDistricts
INNER JOIN offices ON MainDistricts.ID = offices.MainDistrictID
INNER JOIN Customers
INNER JOIN sales ON Customers.CustomerID = sales.CustomerID
INNER JOIN Dealers ON sales.DealerID = Dealers.DelearID
INNER JOIN cte ON sales.salesID = cte.salesID
since those tables seem to be part of both parts of the query (the "original" one and the "_1").
As a next step I'd replace the dbo.GetAgentNotes function with an inline-table valued function to avoid the repetitive call (it'll be called once per row leading to multiple selects against DailyPolicyLog table). If you need some advice how to do it, please post the function and we'll se what we can do.
I'd also try to combine the calls to the SalesInsurance table into another CTE but I'm not sure if this would provide the same results (a TOP(1) without an ORDER BY is somewhat unpredictable)
:
SELECT TOP (1) Insurance.ShName, Term
FROM SalesInsurance
LEFT OUTER JOIN Insurance ON SalesInsurance.InsuranceID = Insurance.InsuranceID
WHERE (SalesInsurance.salesID = sales1.SalesID)
ORDER BY Insurance.ShName DESC
Based on that I'd index the temp table and use it to join the remaining tables. It might help to even further divide the query into more temp tables. But that's beyond me at this point due to missing sample data and a query that at least looks suspicious (most probably the query we see is not the original query bur got mangled when "travelling the web").
May 31, 2011 at 12:46 pm
@LutzM & @ninja: Thanks for the suggestion, I have noticed a couple of stored procedures have benefited by using CTEs and removing UDF calls. I am trying to rewrite some of the legacy code as the database servers were upgraded previously from SQL2K to SQL2K5 though they did not use any of the new features. In fact some of the databases were still running in 80 cmptblty.
(It is interesting being the first DBA in the environment which was built 10 years ago!)
May 31, 2011 at 12:53 pm
SC48035 (5/31/2011)
@LutzM & @ninja: Thanks for the suggestion, I have noticed a couple of stored procedures have benefited by using CTEs and removing UDF calls. I am trying to rewrite some of the legacy code as the database servers were upgraded previously from SQL2K to SQL2K5 though they did not use any of the new features. In fact some of the databases were still running in 80 cmptblty.(It is interesting being the first DBA in the environment which was built 10 years ago!)
Think positive: there's work for you to do at least for the next year that will show some positive effect almost every day!! Sounds like a fun job (as long as they let you do what you need to do...).
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply