September 24, 2013 at 7:26 am
Can anyone help me understand what is happening here? I have a tall table containing a list of IDs and a numeric value that refers to a type of demog. It's being queried through a series of subqueries that are joined together eg....
select distinct ID from (select subquery1.RID RID from
((Select ID from demogtable where (demogID='133766')) Subquery1 inner join
(Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join
(Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join
(Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join
(Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join
(Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1
This might seem like an inefficient way of doing it but due to the way the table is set up, it returns about 45000 IDs in approx 5 seconds.
The problem comes when I try to use this results set as a subquery to update another table e.g....
update Outputtable set queryID=3483 where ID in (select distinct ID from (select subquery1.ID ID from
((Select ID from demogtable where (demogID='133766')) Subquery1 inner join
(Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join
(Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join
(Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join
(Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join
(Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null
This query takes several hours to complete and causes the CPU to run at 99%. I can't understand why though, as the subquery, when run in isolation is so fast. The outputtable is about 50k, and has only just been created by the process, so isn't locked.
Even stranger, I can make the query run fast again, if I apply a TOP to the subquery. e.g.....
update Outputtable set queryID=3483 where ID in (select distinct top 10000000 ID from (select subquery1.ID ID from
((Select ID from demogtable where (demogID='133766')) Subquery1 inner join
(Select ID from demogtable where (demogID='127811')) Subquery2 on subquery1.ID=subquery2.ID inner join
(Select ID from demogtable where (demogID='196565')) Subquery3 on subquery1.ID=subquery3.ID inner join
(Select ID from demogtable where (demogID='12566')) Subquery4 on subquery1.ID=subquery4.ID inner join
(Select ID from demogtable where (demogID='201175')) Subquery5 on subquery1.ID=subquery5.ID inner join
(Select ID from demogtable where (demogID='201209')) Subquery6 on subquery1.ID=subquery6.ID)) tab1) and queryid is null
Someone told me that using the TOP statement can be a good trick to make SQL actualise a subquery in tempdb before attempting to move on, and that appears to be what's happening here, but I can't understand why the subquery would cause issues when run inline.
If I run sp_lock when the first update statement is running, it appears that there are 4k+ locks going on.
Can anyone explain this behaviour?
September 24, 2013 at 8:43 am
Without looking at execution plans, nope. I can't tell you what's happening. Adding the TOP doesn't "actualize the table in tempdb" whatever the heck that means. It does give the optimizer other choices for the query which may be resulting in a different execution plan (probably is a radically different plan). But without seeing the plans in question, I'm not going to guess at this.
Instead of a bunch of nested queries, why not just use the JOIN operator?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2013 at 9:29 am
We need to see the query plan to help.
But you might try inserting the SELECT stuff you need for the update into a temp table (NOT table variable) and running the update with a join to that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2013 at 10:25 am
Thanks for the responses. I'll try to get the execution plans added as soon as I can
September 24, 2013 at 11:35 pm
Can't you avoid the sub queries used these many times?
try inserting the subquery data to temp table and update and see how query performs.
as suggested above plan is best to see where it spends more resource.
Regards
Durai Nagarajan
September 25, 2013 at 4:47 am
Unfortunately not, as that's the way the DB is set up. However, in testing, we find it is very fast doing it this way, and using temp tables seems to slow it down considerably. Also, in SQL 2012, it doesn't seem to make any difference at all if you use subqueries or joins.
The only exception to this is if you are using an EXCEPT, which slows things down a lot. In this scenario, we do use temptables
September 25, 2013 at 10:11 am
My experience is that JOINs in sub-queries soon become nested loops.
Also, using EXISTS sub-queries negates the need for a DISTINCT.
I would try something like:
WITH UpdID
AS
(
SELECT ID
FROM demogtable d1
WHERE d1.demogID = '133766'
AND EXISTS (SELECT 1 FROM demogtable d2WHERE d2.demogID = '127811' AND D2.ID = D1.ID)
AND EXISTS (SELECT 1 FROM demogtable d3WHERE d3.demogID = '196565' AND D3.ID = D1.ID)
AND EXISTS (SELECT 1 FROM demogtable d4WHERE d4.demogID = '12566' AND D4.ID = D1.ID)
AND EXISTS (SELECT 1 FROM demogtable d5WHERE d5.demogID = '201175' AND D5.ID = D1.ID)
AND EXISTS (SELECT 1 FROM demogtable d6WHERE d6.demogID = '201209' AND D6.ID = D1.ID)
)
UPDATE Outputtable
SET queryID = 3483
WHERE queryID IS NULL
AND EXISTS (SELECT 1 FROM UpdID U WHERE U.ID = Outputtable.ID);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply