Dramatic slowdown of sql when placed in a function

Dramatic slowdown of sql when placed in a function

Post by Mike Mor » Thu, 11 Mar 2004 00:19:14



Hi. I currently have a fairly large query which I have been optimizing
for later use in a function. There are things in the query which I
have been keeping constant whilst optimizing, but which are variables
in the function. When I run this query as sql, with the `variables'
constant, I get a runtime of about 3 or 4 seconds. However, when I
place this same sql in an sql function, and then pass my constants
from before in as arguments, I get a runtime of about 215 seconds.

I am trying to understand how this could be. How does putting the sql
in a function affect the query optimiser? Would putting it in as a
plpsql function help? How else can I retain the original speed?

I'm planning on using this functionality from JDBC, so I'd rather not
be forced to put the sql into the JDBC directly, but a difference of 2
orders of magnitude in speed can't be ignored.

Many thanks

--
Mike

 
 
 

Dramatic slowdown of sql when placed in a function

Post by Jeff Boe » Thu, 11 Mar 2004 00:17:12



Quote:>Hi. I currently have a fairly large query which I have been optimizing
>for later use in a function. There are things in the query which I
>have been keeping constant whilst optimizing, but which are variables
>in the function. When I run this query as sql, with the `variables'
>constant, I get a runtime of about 3 or 4 seconds. However, when I
>place this same sql in an sql function, and then pass my constants
>from before in as arguments, I get a runtime of about 215 seconds.

>I am trying to understand how this could be. How does putting the sql
>in a function affect the query optimiser? Would putting it in as a
>plpsql function help? How else can I retain the original speed?

My first guess would be that the indexes being used in the query are
mis-matching on data type compared to your function arguments. For instance,

create function foobar(text) as
'begin
 select * from foobar_table where col_a=$1;
 end' ...

I may have some syntax wrong up there, but the idea is that you are passing in a
parameter of some datatype (text here) and then using it in a select statement
against a column which we will assume is of some datatype other than "text". If
the index your query uses is not picking up the datatype conversion properly,
then you may have a sequential scan instead.

To verify this, you might do these:

EXPLAIN
select * from foobar_table where
col_a=<THE_CONSTANT_VALUE>;

vs.

EXPLAIN
select * from foobar_table where
col_a=<THE_CONSTANT_VALUE>::<THE_PARAMETER_TYPE>;

--
~~~~~~~~~~~~~~~~| Genius may have its limitations, but stupidity is not
Jeff Boes       | thus handicapped.


 
 
 

Dramatic slowdown of sql when placed in a function

Post by Mike Mor » Thu, 11 Mar 2004 06:21:30




> >Hi. I currently have a fairly large query which I have been optimizing
> >for later use in a function. There are things in the query which I
> >have been keeping constant whilst optimizing, but which are variables
> >in the function. When I run this query as sql, with the `variables'
> >constant, I get a runtime of about 3 or 4 seconds. However, when I
> >place this same sql in an sql function, and then pass my constants
> >from before in as arguments, I get a runtime of about 215 seconds.

[ ... ]

> My first guess would be that the indexes being used in the query are
> mis-matching on data type compared to your function arguments. For instance,

[ ... ]

Hi. I think it is something like this that is going on. A couple of
the variables are dates which are
specified in the table as 'timestamp without time zone', whilst the
function was using 'timestamp with time zone'. I confirmed the
slowdown by casting the types to the 'slow' type in the original
query.

However, when I change the signature of the function and do a cast of
the variable within the function body I still get the same speed. I
even cast the arguments to the function given at the psql prompt and
still I get the same speed.

I will have to sanity-check this again tomorrow (posting from home)
but I couldn't see anywhere else that I could force the type to be the
same as that specified on the table.

Many thanks,

--
Mike

 
 
 

1. dramatic slowdown at -np 2

We are developing an application on a four node Beowulf while we wait
for the remaining nodes to arrive.  Speed-up has been close to
predicted with -np 3 and 4 in a master/slave mode.  But when I run at
-np 2 the speed drops to approximately 1/4 of the original serial
application.  (On 4 nodes - that is 1 master and 3 slaves, we get
about 2.5 times speed-up relative to the original application.)  All
runs produce the same answer.

In the MPI code we have basic SEND, RECV, BCAST and REDUCE - nothing
fancy.  Does anyone know if any of these or other MPI functions run
into problems with one to one master slave ration?  Any other
enlightenment would be welcome.

Cheers,

John Harrop

2. testgi

3. Using user-defined functions as add-ins?

4. Two monitor solution

5. Excel 97 - Engineering Functions add-ins

6. IMAGEWRITER II 4-sale

7. Excel 5 - How to use Add-Ins Analysis functions in VBA

8. Cannot get back on MSN after crash

9. Help with Excel Add-ins and function macros

10. Recognizing functions in Add-Ins

11. Whens amped 2 comin out? gettin bored!

12. whens ps3 coming out

13. VBA - Placing Date & Time Functions on a Worksheet