Tuning SQL Server Query with function

Question

Function :

create function .[data_by_date_district](@rev_datetime datetime)
returns table
as
return
(
SELECT 
     ro.Region,[DATETIME],     
      sum(datain) 'datain',
      sum(dataout) 'dataout'
  FROM 
 data.inoutdata cr 
 join 
 structure.site ro  on
 ro.site = substring(replace([siteunqid],'**',''),1,LEN(siteunqid)-1)
 where  [DATETIME]  =@rev_datetime
 group by Region,[DATETIME]
);

Executing this function with any date takes 00:00:00.

Now when I join this with another table it takes 4 sec (this table is 75 rows) :

SELECT 
     Region,[DATETIME],datain,dataout,(cr.datain+cr.dataout) 'total',SP_GEOMETRY,MI_STYLE,MI_PRINX
  FROM 
  data.data_by_date_district(DATEADD(DAY,-1,cast(GETDATE() as DATE)) ) cr
  join 
  datamap.VectorMaps.REGION_BND db
  on db.Name =  cr.Region

The first table has 1,700,000 rows of data which increases daily by 170,000 but other table has just 75 rows. Is there any way i could decrease the query time? The result of function is 75 rows.

Please attach your execution plan.

Devart
– Devart

2013-09-30 11:31:28 +00:00
Commented Sep 30, 2013 at 11:31 — Devart
– Devart, Commented Sep 30, 2013 at 11:31
@Devart Is image good because xml is too huge.

kinkajou
– kinkajou

2013-09-30 11:39:13 +00:00
Commented Sep 30, 2013 at 11:39 — kinkajou
– kinkajou, Commented Sep 30, 2013 at 11:39

Pratik Kaje · Accepted Answer · 2013-09-30 11:38:24Z

3

I think you should use common table expression instead of function to get your result set and replace function by cte in from clause. I hope this helps!

answered Sep 30, 2013 at 11:38

Pratik Kaje

7158 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kinkajou Over a year ago

nice one Pratik CTE indeed helps

Devart · Accepted Answer · 2013-11-04 14:29:49Z

2

Have you tried using the body of the function straight in your query (replacing the function call)? Functions are not very good for performance, I think.

Something like that (sorry, formatting isn't best):

SELECT Region,
       [DATETIME],
       datain,
       dataout,
       (cr.datain + cr.dataout) 'total',
       SP_GEOMETRY,
       MI_STYLE,
       MI_PRINX
FROM (
    SELECT ro.Region,
           [DATETIME],
           SUM(datain) 'datain',
           SUM(dataout) 'dataout'
    FROM data.inoutdata cr
    JOIN structure.site ro ON ro.site = SUBSTRING(REPLACE([siteunqid], '**', ''), 1, LEN(siteunqid) - 1)
    WHERE [DATETIME] = DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
    GROUP BY Region,
             [DATETIME]
) cr
JOIN datamap.VectorMaps.REGION_BND db ON db.Name = cr.Region

edited Nov 4, 2013 at 14:29

Devart

120k23 gold badges172 silver badges189 bronze badges

answered Sep 30, 2013 at 11:21

Szymon

43k16 gold badges99 silver badges115 bronze badges

5 Comments

kinkajou Over a year ago

actually I am joining with this table to receive geometry column from it. I can't directly join it as it gives "Type geometry is not comparable it can't be used in group by clause"

kinkajou Over a year ago

okay that's cool it now takes 1 sec any way to tune it better because it's just 75 rows :( .

kinkajou Over a year ago

also i see that now clustered index seek cost is reduced from 75% to 46% on table inoutdata which is having action on 176133 rows.

Szymon Over a year ago

Functions are only really good when applied in SELECT clause, I think. Otherwise they impact on performance.

kinkajou Over a year ago

I used CTE and the query ran in 00:00:00. It's fun. I don't know how this works but.

Collectives™ on Stack Overflow

Tuning SQL Server Query with function

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related