Cost of function call in a WHERE clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Cost of function call in a WHERE clause

Mario Bezzi
Hello,

I would like to use a user defined scalar function in a where clause,
something like:

SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnA = myfunct(X);

X is constant.  I am concerned by the performance of such a query as I
wonder if myfunct is called just once or once for every row.

I know I may probably determine it by reading SQLite generated pseudo
code, but I am not fluent at it.

Thank you in advance.

mario

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Cost of function call in a WHERE clause

Keith Medcalf

If your function is "CONSTANT" or "DETERMINISTIC" and the argument is a constant then it will only be called once per statement.  If all the above conditions are not met then it will be called for each use on each row (where use includes alias expansions).

So for example if you have a function sin() that is deterministic, then:

select ... from table where table.value < sin(1.24345);
will call the sin() function once.

select ... from table where sin(table.value) < .5;
will call the sin() function once per row.

select sin(x), sin(y) from table where sin(x) < sin(1.234);
will call the sin() function either 1 or three times per row, plus one time to generate the constant.

If the sin() function is not deterministic, then it will be called every time it needs to be computed. even if the computation is a duplicate.  This is because the (!deterministic) => volatile and subject to change based on, well, just cause it feels like changing.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Mario Bezzi
>Sent: Thursday, 21 December, 2017 11:28
>To: SQLite mailing list
>Subject: [sqlite] Cost of function call in a WHERE clause
>
>Hello,
>
>I would like to use a user defined scalar function in a where clause,
>something like:
>
>SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnA =
>myfunct(X);
>
>X is constant.  I am concerned by the performance of such a query as
>I
>wonder if myfunct is called just once or once for every row.
>
>I know I may probably determine it by reading SQLite generated pseudo
>code, but I am not fluent at it.
>
>Thank you in advance.
>
>mario
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users