December 9, 2010 at 9:41 pm
Dear All,
I have this query
SELECT Address.ID, Address.Postcode, Address.CityTown, Address.SuburbDistrict, Address.StateProvince, Address.Country_ID, Address.AddressType_ID, Address.Postcode_ID
FROM Address
LEFT JOIN
s2_usn ON (Address.ID=s2_usn.table_id and s2_usn.tablename='Address')
WHERE (Address.ID IN (SELECT Address_ID FROM Account LEFT JOIN s2_usn on (Account.ID=s2_usn.table_id and s2_usn.tablename='Account')
WHERE ((Country_ID = 63) AND IsActive = 1 AND s2_usn.ID IS NULL)))
OR (Address.ID IN (SELECT Account_Address.Address_ID FROM Account_Address INNER JOIN Account ON Account_Address.Account_ID = Account.ID
LEFT JOIN s2_usn on (Account.ID=s2_usn.table_id and s2_usn.tablename='Account')
WHERE ((Account.Country_ID = 63) AND Account.IsActive = 1 AND s2_usn.ID IS NULL)))
AND (Country_ID = 63) AND s2_usn.ID IS NULL
It was running earlier within 1 to 2 minutes now its taking more than 10 minutes to run because of this my application is getting timed out.
As this is vendor application i can't change the T-SQL code is it possible to do something to increase performance.
In the query plan it shows Index spool(eager spool) with 66% cost and table spool(laze spool) 23% cost.
How to minimize this,earlier it was running fine now its making worse from last 2 week.
Help me to get resolution for this.
Thanks,
Gangadhar
December 10, 2010 at 2:01 am
If there's been no changes to indexing or data volume, you've either got stale statistics, or you need to double check your system for memory, cpu, or drive pressure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 10, 2010 at 3:40 am
Please post the actual execution plan so we can see if there's a possible index issue.
December 10, 2010 at 4:20 am
As it is using an index spool , that does suggest that extra indexing is needed, since that is effectively what it is doing....
December 10, 2010 at 4:24 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2010 at 7:35 am
Try this query
SELECTt1.ID,
t1.Postcode,
t1.CityTown,
t1.SuburbDistrict,
t1.StateProvince,
t1.Country_ID,
t1.AddressType_ID,
t1.Postcode_ID
FROM(
SELECTadr.ID,
adr.Postcode,
adr.CityTown,
adr.SuburbDistrict,
adr.StateProvince,
adr.Country_ID,
adr.AddressType_ID,
adr.Postcode_ID
FROMdbo.[Address] AS adr
LEFT JOINdbo.s2_usn AS s2 ON s2.table_id = adr.id
and s2.tablename = 'address'
WHEREadr.Country_ID = 63
AND s2.ID IS NULL
) AS t1
INNER JOIN(
SELECTDISTINCT
u.AddressID
FROM(
SELECTacc.Address_ID AS adr1,
aa.Address_ID AS adr2
FROMAccount AS acc
LEFT JOINs2_usn AS s2 ON s2.table_id = acc.ID
AND s2.tablename = 'Account'
LEFT JOINAccount_Address AS aa ON aa.Account_ID = acc.id
WHEREacc.Country_ID = 63
AND acc.IsActive = 1
AND s2.ID IS NULL
) AS d
UNPIVOT(
AddressID
FOR theCol IN (d.adr1, d.adr2)
) AS u
WHEREu.AddressID IS NOT NULL
) AS t2 ON t2.AddressID = t1.ID
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply