Same query much slow in function, any idea?

9 Dec 2005 12:35 AM

davidw

if I run this
declare @c int,@y int
select @c=10005143,@y=2005
insert @hit
select id=startmm,hit=count(id)
            from memlog
           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
          group by startmm

it takes 10 seconds

but put it in a function, like below, and run
select * from utest()
it takes 40 second, any idea?

create function utest() returns
@hit table(a int,b int)
as
begin
declare @c int,@y int
select @c=10005143,@y=2005
insert @hit
select id=startmm,hit=count(id)
            from memlog
           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
          group by startmm
return
end

thanks!

9 Dec 2005 1:21 AM

ML

Difficult to say without seeing the execution plan. How many rows? It just
might be that a temporary table is created when the function is executed to
store the data before returning it to the client.

ML

---
http://milambda.blogspot.com/

9 Dec 2005 5:32 AM

Steve Kass

Seeing the CREATE TABLE statement and CREATE INDEX statements
for memlog would help, as would having an idea of the data distribution,
i.e., how many rows the table contains, and how many of those rows
satisfy each of the individual conditions of the WHERE clause.

I can suggest that an inline UDF is likely to perform better, since it
doesn't have to materialize the result set as a separate step from returning
the result set:

create function utest()
returns table as return
select startmm as id, count(id) as hit
from memlog
where (LoginXCentralMemID=1005143 or MemID=1005143)
and startyy=2005
group by startmm

Steve Kass
Drew University

davidw wrote:

Show quote

>if I run this
>declare @c int,@y int
>select @c=10005143,@y=2005
>insert @hit
>select id=startmm,hit=count(id)
>            from memlog
>           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
>          group by startmm
>
>it takes 10 seconds
>
>but put it in a function, like below, and run
>select * from utest()
>it takes 40 second, any idea?
>
>create function utest() returns
> @hit table(a int,b int)
>as
>begin
>declare @c int,@y int
>select @c=10005143,@y=2005
>insert @hit
>select id=startmm,hit=count(id)
>            from memlog
>           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
>          group by startmm
>return
>end
>
>thanks!
>
>
>
>

9 Dec 2005 7:46 AM

davidw

Thanks for reply. thank you ML on the previous reply too.

The table has around 6 million records, it is clustered index is

LoginXCentralMemID
MemID
ID

all them are interger, it has some date fields, all are interger too, they
are

startyy,startmm,startdd,starthh,startww

the five date related fields has another non-clustered index.

I did a test, if I run

> select startdd as id, count(id) as hit
> from memlog
> where (LoginXCentralMemID=1005143 or MemID=1005143)
> and startyy=2005 and startmm=11
> group by startdd

it takes 3 seconds, and I put it into a test function, it takes 3 seconds
too.

But the query I show you before takes 10 seconds when run directly, but 40
seconds in a function.

And I just tried the inline solution, it takes 10 seconds, so it works just
like run the query directly. But the thing is my function take a parameter,
and run different queries inside it, so it can not be a inline function.

I am not sure how to get execution plan from a function.

Any idea what else I can do to figure out the issue?

thanks!

David

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:%23NGlBII$FHA.3992@TK2MSFTNGP15.phx.gbl...
> Seeing the CREATE TABLE statement and CREATE INDEX statements
> for memlog would help, as would having an idea of the data distribution,
> i.e., how many rows the table contains, and how many of those rows
> satisfy each of the individual conditions of the WHERE clause.
>
> I can suggest that an inline UDF is likely to perform better, since it
> doesn't have to materialize the result set as a separate step from
returning
> the result set:
>
> create function utest()
> returns table as return
> select startmm as id, count(id) as hit
> from memlog
> where (LoginXCentralMemID=1005143 or MemID=1005143)
> and startyy=2005
> group by startmm
>
> Steve Kass
> Drew University
>
> davidw wrote:
>
> >if I run this
> >declare @c int,@y int
> >select @c=10005143,@y=2005
> >insert @hit
> >select id=startmm,hit=count(id)
> >            from memlog
> >           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
> >          group by startmm
> >
> >it takes 10 seconds
> >
> >but put it in a function, like below, and run
> >select * from utest()
> >it takes 40 second, any idea?
> >
> >create function utest() returns
> > @hit table(a int,b int)
> >as
> >begin
> >declare @c int,@y int
> >select @c=10005143,@y=2005
> >insert @hit
> >select id=startmm,hit=count(id)
> >            from memlog
> >           where (LoginXCentralMemID=@c or MemID=@c) and startyy=@y
> >          group by startmm
> >return
> >end
> >
> >thanks!
> >
> >
> >
> >

9 Dec 2005 8:28 AM

amish

When you pass parameters in the where clause of the function or SP some
times the query engine can not use index properly.If you create a
complete sql string in function using parameter and execute it as a
dynamic sql it will run as u expect.

Like @Execstr = ' select id=startmm,hit=count(id) from memlog where
(LoginXCentralMemID= ' + @c + 'or MemID= ' + @c + ') and startyy= ' +
@y +' group by startmm '
Exec (@execstr).

Regards
Amish

9 Dec 2005 6:07 PM

davidw

I don't think you can run that in a function.

Show quote

"amish" <shahami***@gmail.com> wrote in message
news:1134116896.747683.221140@g49g2000cwa.googlegroups.com...
> When you pass parameters in the where clause of the function or SP some
> times the query engine can not use index properly.If you create a
> complete sql string in function using parameter and execute it as a
> dynamic sql it will run as u expect.
>
> Like @Execstr = ' select id=startmm,hit=count(id) from memlog where
> (LoginXCentralMemID= ' + @c + 'or MemID= ' + @c + ') and startyy= ' +
> @y +' group by startmm '
> Exec (@execstr).
>
>
> Regards
> Amish
>