Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NickyJ
Starting Member
46 Posts |
 Posted - 2005-08-17 : 10:06:34
|
| I have traced an T-SQL statement as a web page on a live production box is running slow and often timing out. The trace returned "SELECT LtdCompany.LtdCompanyID, LtdCompany.Name, LtdCompany.RegNumber, LtdCompany.VATNumber, LtdCompany.VATEffectiveDate, LtdCompany.BankName, LtdCompany.Branch, LtdCompany.AccountName, LtdCompany.AccountNumber, LtdCompany.BSRef, LtdCompany.SortCode, LtdCompany.isLtdCompany, Address.Address1, Address.Address2, Address.Address3, Address.Address4, Address.Address5, Address.PostCode FROM LtdCompany INNER JOIN (SELECT DISTINCT A.LtdCompanyID FROM LtdCompany AS A WITH (NOLOCK) INNER JOIN LtdCompany AS B WITH (NOLOCK) ON A.LtdCompanyIdentification = B.LtdCompanyIdentification INNER JOIN LtdComContractor WITH (NOLOCK) ON B.LtdCompanyID = LtdComContractor.LtdCompanyID INNER JOIN Contract WITH (NOLOCK) ON LtdComContractor.ContractID = Contract.ContractID AND Contract.ComLocationID IN (2) ) AS LtdCom ON LtdCompany.LtdCompanyID = LtdCom.LtdCompanyID AND LtdCompany.Name LIKE 'l%' INNER JOIN LtdComContractor WITH (NOLOCK) ON LtdCompany.LtdCompanyID = LtdComContractor.LtdCompanyID AND (('2005/08/17' BETWEEN LtdComContractor.EffectiveDateFrom AND LtdComContractor.EffectiveDateTo) OR ('2005/08/17' > LtdComContractor.EffectiveDateTo AND LtdCompany.isLatest = 1)) INNER JOIN LtdCompany AS LtdCom1 WITH (NOLOCK) ON LtdCompany.LtdCompanyIdentification = LtdCom1.LtdCompanyIdentification INNER JOIN plcAddress WITH (NOLOCK) ON LtdCom1.LtdCompanyID = plcAddress.PlacementID AND plcAddress.AddressType = 4 AND (('2005/08/17' BETWEEN plcAddress.EffectiveDateFrom AND plcAddress.EffectiveDateTo) OR ('2005/08/17' >= plcAddress.EffectiveDateFrom AND ISDATE(plcAddress.EffectiveDateTo) = 0)) INNER JOIN Address WITH (NOLOCK) ON plcAddress.AddressID = Address.AddressID UNION SELECT LtdCompany.LtdCompanyID, LtdCompany.Name, LtdCompany.RegNumber, LtdCompany.VATNumber, LtdCompany.VATEffectiveDate, LtdCompany.BankName, LtdCompany.Branch, LtdCompany.AccountName, LtdCompany.AccountNumber, LtdCompany.BSRef, LtdCompany.SortCode, LtdCompany.isLtdCompany, Address.Address1, Address.Address2, Address.Address3, Address.Address4, Address.Address5, Address.PostCode FROM LtdCompany INNER JOIN (SELECT DISTINCT A.LtdCompanyID FROM LtdCompany AS A WITH (NOLOCK) INNER JOIN LtdCompany AS B WITH (NOLOCK) ON A.LtdCompanyIdentification = B.LtdCompanyIdentification INNER JOIN LtdComContractor WITH (NOLOCK) ON B.LtdCompanyID = LtdComContractor.LtdCompanyID INNER JOIN Contract WITH (NOLOCK) ON LtdComContractor.ContractID = Contract.ContractID AND Contract.ComLocationID IN (2) ) AS LtdCom ON LtdCompany.LtdCompanyID = LtdCom.LtdCompanyID AND LtdCompany.Name LIKE 'l%' INNER JOIN LtdComContractor WITH (NOLOCK) ON LtdCompany.LtdCompanyID = LtdComContractor.LtdCompanyID INNER JOIN (SELECT LtdCompany.LtdCompanyIdentification, MIN(LtdComContractor.EffectiveDateFrom) AS EffectiveDateFrom FROM LtdCompany WITH (NOLOCK) INNER JOIN LtdComContractor WITH (NOLOCK) ON LtdCompany.LtdCompanyID = LtdComContractor.LtdCompanyID GROUP BY LtdCompany.LtdCompanyIdentification HAVING MIN(LtdComContractor.EffectiveDateFrom) > '2005/08/17') AS C ON C.LtdCompanyIdentification = LtdCompany.LtdCompanyIdentification AND C.EffectiveDateFrom = LtdComContractor.EffectiveDateFrom INNER JOIN plcAddress WITH (NOLOCK) ON LtdCom.LtdCompanyID = plcAddress.PlacementID AND plcAddress.AddressType = 4 AND C.EffectiveDateFrom = plcAddress.EffectiveDateFrom INNER JOIN Address WITH (NOLOCK) ON plcAddress.AddressID = Address.AddressID UNION SELECT LtdCompany.LtdCompanyID, LtdCompany.Name, LtdCompany.RegNumber, LtdCompany.VATNumber, LtdCompany.VATEffectiveDate, LtdCompany.BankName, LtdCompany.Branch, LtdCompany.AccountName, LtdCompany.AccountNumber, LtdCompany.BSRef, LtdCompany.SortCode, LtdCompany.isLtdCompany, Address.Address1, Address.Address2, Address.Address3, Address.Address4, Address.Address5, Address.PostCode FROM LtdCompany INNER JOIN (SELECT DISTINCT A.LtdCompanyID FROM LtdCompany AS A WITH (NOLOCK) INNER JOIN LtdCompany AS B WITH (NOLOCK) ON A.LtdCompanyIdentification = B.LtdCompanyIdentification INNER JOIN LtdComContractor WITH (NOLOCK) ON B.LtdCompanyID = LtdComContractor.LtdCompanyID INNER JOIN Contract WITH (NOLOCK) ON LtdComContractor.ContractID = Contract.ContractID AND Contract.ComLocationID IN (2) ) AS LtdCom ON LtdCompany.LtdCompanyID = LtdCom.LtdCompanyID AND LtdCompany.Name LIKE 'l%' INNER JOIN LtdComContractor WITH (NOLOCK) ON LtdCompany.LtdCompanyID = LtdComContractor.LtdCompanyID INNER JOIN (SELECT LtdCompany.LtdCompanyIdentification, MAX(LtdComContractor.EffectiveDateTo)AS EffectiveDateTo FROM LtdCompany WITH (NOLOCK) INNER JOIN LtdComContractor WITH (NOLOCK) ON LtdCompany.LtdCompanyID = LtdComContractor.LtdCompanyID GROUP BY LtdCompany.LtdCompanyIdentification HAVING MAX(LtdComContractor.EffectiveDateTo) < '2005/08/17') AS C ON C.LtdCompanyIdentification = LtdCompany.LtdCompanyIdentification AND C.EffectiveDateTo = LtdComContractor.EffectiveDateTo INNER JOIN plcAddress WITH (NOLOCK) ON LtdCom.LtdCompanyID = plcAddress.PlacementID AND plcAddress.AddressType = 4 AND ((C.EffectiveDateTo BETWEEN plcAddress.EffectiveDateFrom AND plcAddress.EffectiveDateTo) OR (C.EffectiveDateTo >= plcAddress.EffectiveDateFrom AND ISDATE(plcAddress.EffectiveDateTo) = 0)) INNER JOIN Address WITH (NOLOCK) ON plcAddress.AddressID = Address.AddressID ORDER BY LtdCompany.Name"I noticed am getting locks when this page runs though above reveals WITH (NOLOCK) so don't get that.The above seems rather complex to me!!! Please feel free to rate the above syntax, the coders of the ASP pages are saying the page in quetion is running slow as the database has grown, yet the largest table here contains 25000 records hardly large!! Regds |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2005-08-17 : 10:45:43
|
| Should add that there are 3 UNIONS within and I noticed running them separate returns record counts of say 121 records0 records39 recordswhen run together I get 49 records what would that imply, I also did this whilst removing the DISTINCT and still got 49 records.Rgds |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-17 : 11:07:07
|
| A UNION inherently removes any duplicates (you need a UNION ALL to not do that).The code is dreadful. Its got inherently slow constructions in it, repeated sections that could be moved so they only execute once, dates in non date type fields in a way that prevents use of indexes, aparently completely redudant self-joined tables, nested select statements which are not sufficiently constrained - there are additional constraints outside the nested usage. And the whole use of (NOLOCK) scares the pants of me - plus there are at least 3 places where that hint has been missed off - possible deliberately, hard to say.What more can I say!Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-08-17 : 11:26:39
|
| Have a look at the 'estimated execution plan'....have you got indices, statistics, etc on the tables? Giving us the relevant DDL for the tables would help. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2005-08-17 : 12:15:28
|
| No not written by me, it's from a Web Application I just put a Trace on and this is the T-SQL, I shall post up the DDL for the tables asap. Any simplification of the above would help, I am slowly breaking it down but I don't venture into SQL too often I'm afraid so please be patient, seeing that though is enough to scare me off forever! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-17 : 12:25:39
|
| "Any simplification of the above would help"Well ... if it was me I wouldn't start from thereI would get a definition of what its supposed to do and rewrite it from scratch (albeit with one eye on the original as a source for Cut&Paste!)Kristen |
|
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2005-08-17 : 12:29:23
|
| I thought that would be the answer!! I'll probably do that and compare Execution Plans and returned results, thing is the SQL is passed in Dynamically and I don't have much say in the ASP code, maybe once I have rewritten the SQL I could justify my argument.ThanksNick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-17 : 12:39:21
|
"I thought that would be the answer"Sorry about that!"SQL is passed in Dynamically"Yup, I'd assumed that. Not too much you can do about that, particularly if the actual SQL varies from case-to-case.Running it via sp_ExecuteSQL, and parameterising it, would help if the same query "style" repeats reasonably frequently - SQL Server will cache a plan for it - and a monster this big might actually be taking a while to build a plan for.But my Aproach #1 would be to use a Stored Procedure - it might be that using a temporary table to hold some of the recuring data would make the difference between Night and Day  - and building temporary tables etc. into a dynamically generated pile of SQL is going to be hard work I would think. So ... if the variability of the SQL itself is not great (hopefully there is none at all, other than the criteria parameters), then an Sproc will have the benefit of better efficiency and performance (plus all the other things that Sprocs score over dynamic SQL - I can list some when that turns out to be the best method and you need to justify it to your colleagues!)Kristen |
|
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2005-08-18 : 04:14:01
|
| So a fun day lies ahead of me, and best of all I've a hangover to match!! Thanks for input. |
|
|
|
|
|
|
|
|
Here's a challenge for anyone!??