October 1, 2010 at 12:04 pm
Hi ALL,
We have a stored procedure that is hanging the whole server and the CPU usage goes high. It is just doing simple updates and inserts.
First, we have a temp file where the data comes as a raw format and does some updates on the TEMP table.
Second, the insertion will happen from the TEMP table to the main table
Third,Updates happen on the table which uses the Self joins.
Now we are rebuilding the indexes before the SP execution. Still the SP is in a hung state and we had to kill the session and rebuild indexes manually. Once we do this job runs fine.
We have three indexes:
PK_SOLAR_PT_DATA1_1 clustered Index Panel_id,Serial_num
IX_OBJECTID Object_id
iX_MUNI MUNICISPALITY
and the SP:
update SOLAR_PT_DATA set
pdate_time = fdata.install_date,
daily_kwhr = fdata.daily_kwh
from
(select serial_num, max(date_time) as cdate, max(lifetime_kwhr) as mkw, install_date,
max(lifetime_kwhr)/datediff(d,install_date,max(date_time)) as daily_kwh
from solar_pt_data
where serial_num in (select serial_num from solar_pt_data group by serial_num having count(cast(date_time as varchar(12)))=1)
and install_date is not null
group by serial_num, install_date) fdata
where SOLAR_PT_DATA.serial_num = fdata.serial_num
and SOLAR_PT_DATA.date_time = fdata.cdate
and SOLAR_PT_DATA.daily_kwhr is null
--panel has reported in the past - calculate daily kwhr from max and previous dates
update SOLAR_PT_DATA set
pdate_time = fdata.pdate,
daily_kwhr = fdata.daily_kwh
from
--joins the max data and previous data queries to calcuate the daily_kwhr reading
(select mdata.serial_num, cdate, mkw, pdate, pkw, (mkw-pkw)/datediff(d,pdate, cdate) as daily_kwh
from
-- gets the lifetime kwhr reading at max date
(select mkw.serial_num, cdate, lifetime_kwhr as mkw
from
--get max date for each serial number
(select serial_num, max(date_time) as cdate
from SOLAR_PT_DATA
where serial_num in (select serial_num from SOLAR_PT_DATA group by serial_num having count(cast(date_time as varchar(12)))>1)
group by serial_num) mdata
join SOLAR_PT_DATA mkw on mdata.serial_num=mkw.serial_num and mdata.cdate=mkw.date_time) mdata
join
-- gets the lifetime kwhr reading at the previous date
(select pd.serial_num, pdate, lifetime_kwhr as pkw
from
--gets previous date from max date
(select serial_num, max(date_time) as pdate
from SOLAR_PT_DATA b
where b.date_time <
(select max(date_time)
from SOLAR_PT_DATA pt
where pt.serial_num=b.serial_num )
and cast(b.date_time as varchar(12))<>
cast((select max(date_time) from SOLAR_PT_DATA pt where pt.serial_num=b.serial_num ) as varchar(12))
group by serial_num) pd
join SOLAR_PT_DATA spd on pd.serial_num=spd.serial_num and pd.pdate = spd.date_time) pdata
on mdata.serial_num=pdata.serial_num) fdata
where SOLAR_PT_DATA.serial_num = fdata.serial_num and SOLAR_PT_DATA.date_time = fdata.cdate and SOLAR_PT_DATA.daily_kwhr is null
Insert happens fine. but once the the SP reached update statement. The SP hungs....
Please let me know if you want more information...
October 1, 2010 at 12:32 pm
Please post table definitions, index definitions and execution plan, as per http://qa.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
October 1, 2010 at 12:47 pm
Ow... my eyes...
What I am seeing though is alot of sub-selects in where clauses of sub-selects in where clauses of sub-selects in where clauses in....
Yes I'd like to see some query plans too but it appears to me we have some RBAR logic worked in here too...
Table variables are your friend. they aren't going to bite, I promise!
October 1, 2010 at 12:55 pm
getoffmyfoot (10/1/2010)
Ow... my eyes...What I am seeing though is alot of sub-selects in where clauses of sub-selects in where clauses of sub-selects in where clauses in....
Yes I'd like to see some query plans too but it appears to me we have some RBAR logic worked in here too...
Table variables are your friend. they aren't going to bite, I promise!
Here, this may "look better" that way :
update SOLAR_PT_DATA
set pdate_time = fdata.install_date
, daily_kwhr = fdata.daily_kwh
from (
select serial_num
, max(date_time) as cdate
, max(lifetime_kwhr) as mkw
, install_date
, max(lifetime_kwhr) / datediff(d , install_date ,
max(date_time)) as daily_kwh
from solar_pt_data
where serial_num in (
select serial_num
from solar_pt_data
group by serial_num
having count(cast(date_time as varchar(12))) = 1 )
and install_date is not null
group by serial_num
, install_date
) fdata
where SOLAR_PT_DATA.serial_num = fdata.serial_num
and SOLAR_PT_DATA.date_time = fdata.cdate
and SOLAR_PT_DATA.daily_kwhr is null
--panel has reported in the past - calculate daily kwhr from max and previous dates
update SOLAR_PT_DATA
set pdate_time = fdata.pdate
, daily_kwhr = fdata.daily_kwh
from --joins the max data and previous data queries to calcuate the daily_kwhr reading
(
select mdata.serial_num
, cdate
, mkw
, pdate
, pkw
, ( mkw - pkw ) / datediff(d , pdate , cdate) as daily_kwh
from -- gets the lifetime kwhr reading at max date
(
select mkw.serial_num
, cdate
, lifetime_kwhr as mkw
from --get max date for each serial number
(
select serial_num
, max(date_time) as cdate
from SOLAR_PT_DATA
where serial_num in (
select serial_num
from SOLAR_PT_DATA
group by serial_num
having count(cast(date_time as varchar(12))) > 1 )
group by serial_num
) mdata
join SOLAR_PT_DATA mkw
on mdata.serial_num = mkw.serial_num
and mdata.cdate = mkw.date_time
) mdata
join -- gets the lifetime kwhr reading at the previous date
(
select pd.serial_num
, pdate
, lifetime_kwhr as pkw
from --gets previous date from max date
(
select serial_num
, max(date_time) as pdate
from SOLAR_PT_DATA b
where b.date_time < (
select max(date_time)
from SOLAR_PT_DATA pt
where pt.serial_num = b.serial_num
)
and cast(b.date_time as varchar(12)) <> cast((
select max(date_time)
from SOLAR_PT_DATA pt
where pt.serial_num = b.serial_num
) as varchar(12))
group by serial_num
) pd
join SOLAR_PT_DATA spd
on pd.serial_num = spd.serial_num
and pd.pdate = spd.date_time
) pdata
on mdata.serial_num = pdata.serial_num
) fdata
where SOLAR_PT_DATA.serial_num = fdata.serial_num
and SOLAR_PT_DATA.date_time = fdata.cdate
and SOLAR_PT_DATA.daily_kwhr is null
October 1, 2010 at 2:08 pm
Ha, yea you did the same thing I did and ran that through some formatting. My eyes are finally uncrossed.
This segment of sql concerns me -
where b.date_time < (
select max(date_time)
from SOLAR_PT_DATA pt
where pt.serial_num = b.serial_num
)
and cast(b.date_time as varchar(12)) <> cast((
select max(date_time)
from SOLAR_PT_DATA pt
where pt.serial_num = b.serial_num
) as varchar(12))
seems like that would be a good place to start. Maybe calculate that thing once and hold it in a variable, and use the variable in the where clause. I have a feeling that is getting run once per row. But again, table definitions and execution plans would be lovely...
October 1, 2010 at 2:37 pm
getoffmyfoot (10/1/2010)
seems like that would be a good place to start. Maybe calculate that thing once and hold it in a variable, and use the variable in the where clause. I have a feeling that is getting run once per row. But again, table definitions and execution plans would be lovely...
Only problem there is you'd change the logic. The MAX() statements have a limiter that's not self contained.
He'll need to build a temp structure on serial numbers for max dates, then bring that in with an index on the maxed data and hook that to the table.
This thing's uuugly.
(No, I haven't finished going through it, just this stuck out.)
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
October 1, 2010 at 2:54 pm
Converting the date like that is automatically an index/table scan, so I'm sure that piece is hurting performance. Convert your input to match your static data, not the other way around.
This could also be improved by turning most of the subqueries into CTEs so fields are gradually filtered out. I'll take a crack at it in a min..
October 1, 2010 at 4:44 pm
getoffmyfoot (10/1/2010)
What I am seeing though is alot of sub-selects in where clauses of sub-selects in where clauses of sub-selects in where clauses in....Yes I'd like to see some query plans too but it appears to me we have some RBAR logic worked in here too...
You are aware that, in most cases, correlated subqueries do not run per-row of the outer query? It's a commonly held (and slow dying) myth
Table variables are your friend. they aren't going to bite, I promise!
Sure about that?
Edit: I really shouldn't post late at night.
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
October 2, 2010 at 7:19 am
GilaMonster (10/1/2010)
getoffmyfoot (10/1/2010)
What I am seeing though is alot of sub-selects in where clauses of sub-selects in where clauses of sub-selects in where clauses in....Yes I'd like to see some query plans too but it appears to me we have some RBAR logic worked in here too...
You are aware that, in most cases, correlated subqueries do not run per-row of the outer myth. It's a commonly held (and slow dying) myth
Didn't know that; I'll research more on that. Thanks!
Table variables are your friend. they aren't going to bite, I promise!
Sure about that?
You don't think there would be some performance gain here by breaking some of the subquery logic into table variables instead of lots of sub-selects? Of course you'd have to test to know for sure... but seems like a good way to start to me. Not knowing how many rows are in each table tho, and how many could be potentially stored in a table variable, I realize that there could be some pitfalls with that approach. Perhaps a CTE would be a better recommendation?
October 2, 2010 at 7:48 am
getoffmyfoot (10/2/2010)
You don't think there would be some performance gain here by breaking some of the subquery logic into table variables instead of lots of sub-selects?
Maybe, but there are downsides to table variables too. If you'd said temp tables I would have been closer to agreeing.
Perhaps a CTE would be a better recommendation?
Considering that a CTE is nothing more than a named subquery, it's unlikely to do anything more than improve readability.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply