Function is slow when ...

Question

Post reply

Function is slow when ...

Sal.Projects

Valued Member

Points: 71
More actions
August 10, 2005 at 6:17 am

#168267

I have a table valued function whose input parameters are begin and end date with time. The problem is if i set the begin and end date in local variable and execute this function it takes more than 3 sec's. If i hardcode the date it takes less than a second. I am using qa. What might me the problem?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What happens if you copy the parameters to local variables and use those in the query?

Sal.Projects Valued Member Points: 71 More actions · Answer 2

Sal.Projects

Valued Member

Points: 71

August 10, 2005 at 6:42 am

#580977

Am i right?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

What does your queryplan say? It's the best way to start looking to see what is different between the two..

/Kenneth

Sal.Projects Valued Member Points: 71 More actions · Answer 4

It takes more time. Let me see the query plan.This is my code inside that function

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE TapTime BETWEEN DATEADD(hh, -2, @dStart) AND DATEADD(hh, -2, @dEnd)

AND Furnace = @nEAF AND dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber) > 80

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

August 10, 2005 at 7:05 am

#580993

You're right it's possible. Did you try my trick???

gbn SSCertifiable Points: 6429 More actions · Answer 6

Remi, is this not for stored procs?

Anyway, try this:

Use local vars as Remi said AND do the DATEADD functions on the local vars before the query itself so there are no fns or calculations in the where clause.

When you hard code do you hardcode into the DATADD call or hardcode into the WHERE clause?

Cheers, Shawn

Sal.Projects Valued Member Points: 71 More actions · Answer 7

I think Mr.Shawn has not seen my Code? This is my hardcoded query

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE TapTime BETWEEN DATEADD(hh, -2, '03/03/2003 07:00 AM') AND

DATEADD(hh, -2, '03/03/2003 07:00 PM')

AND Furnace = 2 AND dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber) > 80

This query takes less than a second. The Execution plan for this is Index seek for HeatTable and BookMark lookup 96%.

This is the query which i am using for local variable

DECLARE @dStart AS DATETIME

DECLARE @dEnd AS DATETIME

DECLARE @nEAF AS INTEGER

SET @dStart = '03/03/2003 07:00 AM'

SET @dEnd = '03/03/2003 07:00 PM'

SET @nEAF = 2

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE TapTime BETWEEN DATEADD(hh, -2, @dStart) AND DATEADD(hh, -2, @dEnd)

AND Furnace = @nEAF AND dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber) > 80

Whereas it takes more than 5 minutes with execution plan and not yet completed. W/o Execution plan it takes 1.48 secs

gbn SSCertifiable Points: 6429 More actions · Answer 8

When I meant hardcoded, I meant did you put:

DATEADD(hh, -2, '03/03/2003 07:00 AM')

or

'03/03/2003 05:00 AM'

That is, before or after function...?

Anyway, query plan, not query.

IN QA

Do Ctrl-L and that is a query plan but cannot be pasted.

Then Query menu, "Current connection properties", check "set showplan_text"

Execute script.

You get this (textual query plan), which can be cut and pasted

StmtText

---------------------------

select * from sysservers

(1 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE[sysservers].[srvnetname]=Convert(If ([sysservers].[srvproduct]='SQL Server') then [sysservers].[datasource] else NULL), [sysservers].[isremote]=Convert((Convert([sysservers].[srvstatus])&32)/32), [sysservers].[rpc]=Convert(Conv

|--Clustered Index Scan(OBJECT[master].[dbo].[sysservers].[csysservers]))

Sal.Projects Valued Member Points: 71 More actions · Answer 9

Thank you for ur patient

here it is

StmtText

------------------------------

SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @dStart AS DATETIME

DECLARE @dEnd AS DATETIME

DECLARE @nEAF AS INTEGER

SET @dStart = '03/03/2003 07:00 AM'

SET @dEnd = '03/03/2003 07:00 PM'

SET @nEAF = 2

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE TapTime BETWEEN DATEADD(hh, -2, @dStart) AND DATEADD(hh, -2, @dEnd)

AND Furnace = @nEAF AND dbo.fnMS_PR_GetTorchWeigh

(4 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1015]), [Expr1003]=If ([Expr1016]=0) then NULL else [Expr1017]))

|--Stream Aggregate(DEFINE[Expr1015]=Count(*), [Expr1016]=COUNT_BIG([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])), [Expr1017]=SUM([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber]))))

|--Hash Match(Inner Join, HASH[Bmk1000])=([Bmk1000]), RESIDUAL[Bmk1000]=[Bmk1000]))

| |--Index Seek(OBJECT[Tracking].[dbo].[HeatTable].[IX_HeatTableTapTime]), SEEK[HeatTable].[TapTime] >= dateadd(hour, -2, [@dStart]) AND [HeatTable].[TapTime] <= dateadd(hour, -2, [@dEnd])) ORDERED FORWARD)

| |--Filter(WHERE[dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])>80))

| |--Index Scan(OBJECT[Tracking].[dbo].[HeatTable].[PK_HeatTable]))

|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1012], [Expr1013], [Expr1014]))

|--Compute Scalar(DEFINE[Expr1012]=Convert([@nEAF])-1, [Expr1013]=Convert([@nEAF])+1, [Expr1014]=If (Convert([@nEAF])-1=NULL) then 0 else 6|If (Convert([@nEAF])+1=NULL) then 0 else 10))

| |--Constant Scan

|--Index Seek(OBJECT[Tracking].[dbo].[HeatTable].[IX_HeatTableFurnace]), SEEK[HeatTable].[Furnace] > [Expr1012] AND [HeatTable].[Furnace] < [Expr1013]), WHEREConvert([HeatTable].[Furnace])=[@nEAF]) ORDERED FORWARD)

(11 row(s) affected)

StmtText

-----------------------------

SET STATISTICS PROFILE OFF

(1 row(s) affected)

This for the other query

StmtText

------------------------------

SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE TapTime BETWEEN DATEADD(hh, -2, '03/03/2003 07:00 AM') AND

DATEADD(hh, -2, '03/03/2003 07:00 PM')

AND Furnace = 2 A

(1 row(s) affected)

StmtText

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1010]), [Expr1003]=If ([Expr1011]=0) then NULL else [Expr1012]))

|--Stream Aggregate(DEFINE[Expr1010]=Count(*), [Expr1011]=COUNT_BIG([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])), [Expr1012]=SUM([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber]))))

|--Filter(WHERE[dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])>80 AND [HeatTable].[Furnace]=2))

|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Tracking].[dbo].[HeatTable]))

|--Index Seek(OBJECT[Tracking].[dbo].[HeatTable].[IX_HeatTableTapTime]), SEEK[HeatTable].[TapTime] >= 'Mar 3 2003 5:00AM' AND [HeatTable].[TapTime] <= 'Mar 3 2003 5:00PM') ORDERED FORWARD)

(5 row(s) affected)

StmtText

-----------------------------

SET STATISTICS PROFILE OFF

(1 row(s) affected)

gbn SSCertifiable Points: 6429 More actions · Answer 10

Thanks.

At a first glance, and I've never got the hang of reading text plans...

1. Try declaring @nEAF as the same datatype as the column

2. Try moving the DATEADD calls into the local vars and do BETWEEN @startvar and @endvar

Cheers

noeld SSC Guru Points: 96590 More actions · Answer 11

|--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1015]), [Expr1003]=If ([Expr1016]=0) then NULL else [Expr1017]))

|--Stream Aggregate(DEFINE[Expr1015]=Count(*), [Expr1016]=COUNT_BIG([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])), [Expr1017]=SUM([dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber]))))

|--Hash Match(Inner Join, HASH[Bmk1000])=([Bmk1000]), RESIDUAL[Bmk1000]=[Bmk1000]))

| |--Index Seek(OBJECT[Tracking].[dbo].[HeatTable].[IX_HeatTableTapTime]), SEEK[HeatTable].[TapTime] >= dateadd(hour, -2, [@dStart]) AND [HeatTable].[TapTime] <= dateadd(hour, -2, [@dEnd])) ORDERED FORWARD)

| |--Filter(WHERE[dbo].[fnMS_PR_GetTorchWeightForHeat]([HeatTable].[HeatNumber])>80))

| |--Index Scan(OBJECT[Tracking].[dbo].[HeatTable].[PK_HeatTable]))

|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1012], [Expr1013], [Expr1014]))

|--Compute Scalar(DEFINE[Expr1012]=Convert([@nEAF])-1, [Expr1013]=Convert([@nEAF])+1, [Expr1014]=If (Convert([@nEAF])-1=NULL) then 0 else 6|If (Convert([@nEAF])+1=NULL) then 0 else 10))

| |--Constant Scan

|--Index Seek(OBJECT[Tracking].[dbo].[HeatTable].[IX_HeatTableFurnace]), SEEK[HeatTable].[Furnace] > [Expr1012] AND [HeatTable].[Furnace] < [Expr1013]), WHEREConvert([HeatTable].[Furnace])=[@nEAF]) ORDERED FORWARD)

(11 row(s) affected)

That index scan is killing the performance you have to help the optimizer figure out that it can filter first using the dates as it does in the second case!

After following Shawn C sugestions go one step further and use the SARG" >= and <= " instead of the non SARG "between" Like:

SELECT COUNT(HeatNumber) AS HeatsCount,

Sum(dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber)) AS Tons

FROM HeatTable

WHERE

TapTime >= @StartTime

AND TapTime <= @EndTime

AND Furnace = @nEAF -- Remember to use the same data type

AND dbo.fnMS_PR_GetTorchWeightForHeat(HeatNumber) > 80

* Noel

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 12

I agree with the above postings. Change:

SET @dStart = '03/03/2003 07:00 AM'

SET @dEnd = '03/03/2003 07:00 PM'

To:

SET @dStart=DATEADD(hh,-2,'03/03/2003 07:00 AM')

SET @dEnd=DATEADD(hh,-2,'03/03/2003 07:00 AM')

and change your WHERE clause to:

WHERE TapTime BETWEEN @dStart AND @dEnd AND Furnace = @nEAF AND dbo.fnMS_PR_GetTorchWeigh

Assumption is you have an index covering TapTime. Using the DATEADD function in the WHERE clause may be preventing SQL from correctly using the index.