Update query is killing me..
-
I have the below update statement inside a big procedure. If I comment the below portion, I am saving 10-15 minutes...Can somebody tell me how can I improve the below statment.
=====================================================================================
begin
update dbo.state_income
set totall_cityinc = ( case when (isnull(citypt,0) <>0 and isnull(distpt,0) <>0) then (citypt/distpt*100) else totall_cityinc end )
from
(
select
stateid ,
cityid,
districtid,
(districtpoint) as distpt,
(COALESCE(citypoint,1,0)) as citypt
from dbo.city
where
active = 1
group by stateid ,cityid, districtid
) t1 where stateid=@stateid and city=@cityid )
WHERE stateid=@stateid and city=@cityid
end
-
The first course of action when attempting this sort of exercise is
to try and work out what the code is trying to achieve.
In this case your derived table (t1) will return multiple rows, but the where
clause is on stateid and city, which are probably the same in every row, and not
joined in any way to state_income, meaning that the total_cityinc column will be
updated with a random value from t1.
If you want any help with this we will need more info.
At the very least we will need a sample schema, and an explanation of exactly what
the code is supposed to do.
-
You can help us help you by reading the first article I have linked in my signature block below that regarding asking for assistance.
-
Heh... the first step towards troubleshooting any code is to make it readable... when I do that to your code example, a certain problem with the code sticks out like a sore thumb...
update dbo.state_incomeset totall_cityinc = (
case
when (isnull(citypt,0) <>0
and isnull(distpt,0) <>0)
then (citypt/distpt*100)
else totall_cityinc
end
)
from (
select stateid ,
cityid,
districtid,
districtpoint as distpt,
COALESCE(citypoint,1,0) as citypt
from dbo.city
where active = 1
group by stateid ,cityid, districtid
) t1
where stateid=@stateid and city=@cityid ) --<<<Paren will certainly cause an error here
WHERE stateid=@stateid and city=@cityid
BUT, that's not what's causing the performance problem. What's causing your performance problem is two fold... first, you've used an illegal form of UPDATE in that you have a join in an UPDATE and the target table is not in the FROM clause. If you look in Books Online, you will never see such an example.
Second, the absence of a proper join to the target table makes for a cross join. Further, I believe you want to add DistrictID to the join. If not, then remove it from the inner join as it serves no purpose other than to make a partial cross join that that violates the intent of the update.
Here's the code... I'd really appreciate it if you'd spent a little time formatting your own code in the future to make it easier on those of us who try to help.
update dbo.state_incomeset totall_cityinc = case
when isnull(citypt,0) <>0
and isnull(distpt,0) <>0
then (citypt/distpt*100)
else totall_cityinc
end
from dbo.State_Income si --added
INNER JOIN
(
select stateid ,
cityid,
districtid,
districtpoint as distpt,
COALESCE(citypoint,1,0) as citypt
from dbo.city
where active = 1
and stateid=@stateid and city=@cityid --added
group by stateid ,cityid, districtid
) t1
ON si.StatID = t1.StateID, --added
AND si.CityID = t1.CityID, --added
AND si.DistrictID = t1.DistrictID --added
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
if you want you can try using Common Table Expression (CTE) method.. ans see how long it will take
WITH CITY_INFO(stateid ,cityid,districtid,distpt,citypt
(
select stateid ,
cityid,
districtid,
districtpoint as distpt,
COALESCE(citypoint,1,0) as citypt
from dbo.city
where active = 1
and stateid=@stateid and city=@cityid --added
group by stateid ,cityid, districtid
)
update dbo.state_income
set totall_cityinc = case
when isnull(citypt,0) <>0
and isnull(distpt,0) <>0
then (citypt/distpt*100)
else totall_cityinc
end
from dbo.State_Income si --added
INNER JOIN CITY_INFO t1
ON si.StatID = t1.StateID, --added
AND si.CityID = t1.CityID, --added
AND si.DistrictID = t1.DistrictID --added
Hi Jeff - will CTE be fine to use in this context..
-
Joseph (3/14/2009)
if you want you can try using Common Table Expression (CTE) method.. ans see how long it will takeWITH CITY_INFO(stateid ,cityid,districtid,distpt,citypt
(
select stateid ,
cityid,
districtid,
districtpoint as distpt,
COALESCE(citypoint,1,0) as citypt
from dbo.city
where active = 1
and stateid=@stateid and city=@cityid --added
group by stateid ,cityid, districtid
)
update dbo.state_income
set totall_cityinc = case
when isnull(citypt,0) <>0
and isnull(distpt,0) <>0
then (citypt/distpt*100)
else totall_cityinc
end
from dbo.State_Income si --added
INNER JOIN CITY_INFO t1
ON si.StatID = t1.StateID, --added
AND si.CityID = t1.CityID, --added
AND si.DistrictID = t1.DistrictID --added
Hi Jeff - will CTE be fine to use in this context..
Because you used the target of the update in the FROM clause of the joined update, that'll work just fine, Joseph. Nicely done.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply