Slow table-valued function - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Slow table-valued function

Author

Topic

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 14:48:03

I have a multi-statement table-valued function. Why is it in some instances, when I execute the code within the function itself, SQL Server returns the results in one or two seconds? And then when I try calling the function using the same parameters I used the first time, the results take six seconds to return? Why such a lag just because I'm calling the function instead of executing the code within it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-18 : 14:50:02

That's the drawback of functions. They do not perform well and should be avoided if at all possible. To see what I mean, compare the execution plan of both tests (running the UDF and running the code in the UDF).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 15:32:19

I tried that and can't figure out why this query is taking so long.

select snapshot.Company, snapshot.[# Points], snapshot.[# Good],
snapshot.[# NIS], snapshot.[% Good], snapshot.[# Anomalous],
rolling2.Total_Good, rolling2.Total_Points, rolling2.Average_Good_Readings_Pct,
rolling1.Sum_Anomalous, rolling1.Count_Anomalous, rolling1.Avg_Anomalous,
cast(snapshot.[% Good] as decimal(12,3))-cast(rolling2.Average_Good_Readings_Pct as decimal(12,3)) [% Good Trend],
cast(rolling1.Avg_Anomalous as decimal(12,2))-cast(snapshot.[# Anomalous] as decimal(12,2)) [# Anomalous Trend]
from
(
select Snapshot.Company,
sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis) as [# Points],
sum(Snapshot.Points_Good+Snapshot.Analogs_Good) as [# Good],
sum(Snapshot.Points_Nis+Snapshot.Analogs_Nis-Snapshot.Points_Uncommsn-Snapshot.Analogs_Uncommsn) as [# NIS],
case when sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis)<>0 then cast(sum(Snapshot.Points_Good+Snapshot.Analogs_Good) as float)/cast(sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis) as float) else 0 end as [% Good],
sum(Snapshot.Analogs_Anomal) as [# Anomalous]
from
SCADA.Scadamom.Scada_Availability as Snapshot
where Snapshot.Timestamp='2009-12-18 13:55:03.000'
and SUBSTRING(Snapshot.Site,1,LEN('CG'))='CG'
group by Snapshot.Company
) snapshot,
(
select Company,
Sum(Sum_Analogs_Anomalous) Sum_Anomalous,
Count(Sum_Analogs_Anomalous) Count_Anomalous,
Avg(cast(Sum_Analogs_Anomalous as float)) Avg_Anomalous
from
(
select Company,Timestamp,
sum(Analogs_Anomal) Sum_Analogs_Anomalous
FROM SCADA.Scadamom.Scada_Availability
WHERE SUBSTRING(Site,1,LEN('CG'))='CG'
AND Timestamp<'2009-12-18 13:55:03.000'
AND Timestamp>=DateAdd(hour,-(240),'2009-12-18 13:55:03.000')
group by Company,Timestamp
) rolling1a
group by Company
) rolling1,
(
select Company,
SUM(Analogs_Good+Points_Good) Total_Good,
SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis) Total_Points,
case when SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis)<>0 then cast(SUM(Analogs_Good+Points_Good) as float)/cast(SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis) as float) else 0 end Average_Good_Readings_Pct
FROM SCADA.Scadamom.Scada_Availability
WHERE SUBSTRING(Site,1,LEN('CG'))='CG'
AND Timestamp<'2009-12-18 13:55:03.000'
AND Timestamp>=DateAdd(hour,-(240),'2009-12-18 13:55:03.000')
group by Company
) rolling2
where snapshot.Company=rolling1.Company
and snapshot.Company=rolling2.Company

The query consists of three subqueries. The first is called "snapshot," which takes a snapshot of the current data. The other two are called "rolling1" and "rolling2." These are in charge of getting the rolling averages. If I run each of these subqueries separately, the snapshot one returns results immediately while the other two each return their respective results in under a second.

The frustrating thing is that, when I join all the queries together like I do in the query above, then SQL Server takes between 10-15 seconds to return its data. What am I doing wrong here? Each subquery returns exactly 19 rows. So why would the query above take so much longer than its subqueries, each of which return data in under a second?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-18 : 16:17:22

Try changing it this way...

select snapshot.Company, snapshot.[# Points], snapshot.[# Good],
snapshot.[# NIS], snapshot.[% Good], snapshot.[# Anomalous],
rolling2.Total_Good, rolling2.Total_Points, rolling2.Average_Good_Readings_Pct,
rolling1.Sum_Anomalous, rolling1.Count_Anomalous, rolling1.Avg_Anomalous,
cast(snapshot.[% Good] as decimal(12,3))-cast(rolling2.Average_Good_Readings_Pct as decimal(12,3)) [% Good Trend],
cast(rolling1.Avg_Anomalous as decimal(12,2))-cast(snapshot.[# Anomalous] as decimal(12,2)) [# Anomalous Trend]
from
(
select Snapshot.Company,
sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis) as [# Points],
sum(Snapshot.Points_Good+Snapshot.Analogs_Good) as [# Good],
sum(Snapshot.Points_Nis+Snapshot.Analogs_Nis-Snapshot.Points_Uncommsn-Snapshot.Analogs_Uncommsn) as [# NIS],
case when sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis)<>0 then cast(sum(Snapshot.Points_Good+Snapshot.Analogs_Good) as float)/cast(sum(Snapshot.Points_Total+Snapshot.Analogs_Total-Snapshot.Points_Nis-Snapshot.Analogs_Nis) as float) else 0 end as [% Good],
sum(Snapshot.Analogs_Anomal) as [# Anomalous] 
from
SCADA.Scadamom.Scada_Availability as Snapshot
where Snapshot.Timestamp='2009-12-18 13:55:03.000'
and SUBSTRING(Snapshot.Site,1,LEN('CG'))='CG'
group by Snapshot.Company
) snapshot
inner join
(
select Company,
Sum(Sum_Analogs_Anomalous) Sum_Anomalous,
Count(Sum_Analogs_Anomalous) Count_Anomalous,
Avg(cast(Sum_Analogs_Anomalous as float)) Avg_Anomalous
from
(
select Company,Timestamp,
sum(Analogs_Anomal) Sum_Analogs_Anomalous
FROM SCADA.Scadamom.Scada_Availability
WHERE SUBSTRING(Site,1,LEN('CG'))='CG'
AND Timestamp<'2009-12-18 13:55:03.000'
AND Timestamp>=DateAdd(hour,-(240),'2009-12-18 13:55:03.000')
group by Company,Timestamp
) rolling1a
group by Company
) rolling1
on snapshot.Company = rolling1.Company
inner join
(
select Company,
SUM(Analogs_Good+Points_Good) Total_Good,
SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis) Total_Points,
case when SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis)<>0 then cast(SUM(Analogs_Good+Points_Good) as float)/cast(SUM(Analogs_Total+Points_Total-Analogs_Nis-Points_Nis) as float) else 0 end Average_Good_Readings_Pct
FROM SCADA.Scadamom.Scada_Availability
WHERE SUBSTRING(Site,1,LEN('CG'))='CG'
AND Timestamp<'2009-12-18 13:55:03.000'
AND Timestamp>=DateAdd(hour,-(240),'2009-12-18 13:55:03.000')
group by Company
) rolling2
on snapshot.Company = rolling2.Company

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 16:32:11

Thanks for the suggestion, vijayisonly.

Unfortunately, performance did not improve. Still took about 12 seconds to complete.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-18 : 16:49:42

Hmm...join one table first..maybe rolling1...and see how quickly you get the results.

Also can you look at the execution plan and see where the block is?

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 17:04:32

When I take rolling2 out of the query and just join snapshot to rolling1, the query returns data in under a second.

I'm not really educated on the Execution Plan. I can see, however, that well downstream that Parallelism (Repartition Streams) has a cost of 22% and, right of that, Clustered Index Seek has a cost of 56%. Those look like the biggies. If you hover over a step in the execution plan, you can see additional details. Is there something I can pass along from these details that would make this easier to diagnose? Like "Predicate"? Or "Object"? "Output list"? "Seek predicates"?

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 17:08:00

I should note.....the clustered index to which I referenced is an index on the Scada_Availability table for the TIMESTAMP field.....which represents when the data was read and written to the database. Since this is the main field that I query on, it made sense to set the clustered index on this field.

haz33
Starting Member

15 Posts

Posted - 2009-12-18 : 17:33:21

Well, I noticed the Execution Plan recommended adding a non-clustered index on a couple of fields. I did so. Now the query returns in about five seconds.

The new Execution Plan has two branches and, near or at the end of each, is Index Seek for the new non-clustered index. Each costs 34%.

I'd still like to do better than five seconds if possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources