Query on TEMP view.

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

Query on TEMP view.

Deepak Hegde
Hi All,


I am facing a problem where in defined function registered to sqlite is called multiple time withput considering the filter.


Ex:

Table and entry:

ID  NAME  PPID

1    a.mp3   2

2    b.mp3   3


Query:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE smart_search(NAME,ID)


OutPut count = 1 (This is fine)


Here smart_search() is defined and given to sqlite DB.

We are expecting smart_search() to be called from sqlite only for the entry with PPID 2 and that will be for ID 1 a.mp3.

But we see that this is called 2 times and for both the entry.


As per my understanding. filter of PPID is added for the inner query smart_search() should have called only for one entry. Is this the expected behavior?


If i change the query as below then the smart_search() is called for only one entry.

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)


Please help to understand on this.


Thanks and Regards

Deepak
_______________________________________________
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: Query on TEMP view.

Keith Medcalf

There are a myriad of reasons for the behaviour you are seeing and they affect only performance and not correctness.  In other words, you think that your UDF is more "expensive" to compute than the PPID == 2 test, and therefore the least expensive test should be performed first so that the more expensive operation does not need to be performed where its result would merely be discarded by virtue of the lesser expensive ANDed condition.

The other thing is that the subquery is likely being flattened -- again this depends on the version of SQLite3 you are using.  However, assuming that the query is being flattened then:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE smart_search(NAME,ID)
which is exactly the same as (with the aliasing removed as it adds nothing of significance)
SELECT COUNT(ID) FROM (SELECT ID, NAME, PPID FROM AUDIO WHERE PPID=2) WHERE smart_search(name,id)
which should be flattened to
SELECT COUNT(ID) FROM AUDIO WHERE PPID=2 and smart_search(name,id)

*See the query flattening rules at https://www.sqlite.org/optoverview.html#subquery_flattening

That said, however, I am unable to reproduce with the current tip of trunk.  

What version of sqlite3 are you using?


---
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 Hegde, Deepakakumar
>(D.)
>Sent: Monday, 27 August, 2018 23:47
>To: [hidden email]
>Subject: [sqlite] Query on TEMP view.
>
>Hi All,
>
>
>I am facing a problem where in defined function registered to sqlite
>is called multiple time withput considering the filter.
>
>
>Ex:
>
>Table and entry:
>
>ID  NAME  PPID
>
>1    a.mp3   2
>
>2    b.mp3   3
>
>
>Query:
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE smart_search(NAME,ID)
>
>
>OutPut count = 1 (This is fine)
>
>
>Here smart_search() is defined and given to sqlite DB.
>
>We are expecting smart_search() to be called from sqlite only for the
>entry with PPID 2 and that will be for ID 1 a.mp3.
>
>But we see that this is called 2 times and for both the entry.
>
>
>As per my understanding. filter of PPID is added for the inner query
>smart_search() should have called only for one entry. Is this the
>expected behavior?
>
>
>If i change the query as below then the smart_search() is called for
>only one entry.
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)
>
>
>Please help to understand on this.
>
>
>Thanks and Regards
>
>Deepak
>_______________________________________________
>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
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Query on TEMP view.

Hick Gunter
In reply to this post by Deepak Hegde
In the sqlite shell, enter the .explain command and then

EXPLAIN QUERY PLAN <your query>

for an explanation of the plan, and

EXPLAIN <your query>

for the generated bytecode. This usually helps to understand what sqlite is thinking (although maybe not why).

Note that WHERE constraints are applied to the input set whereas HAVING constraints are applied to the output set, so perhaps changing the "outer" constraint to HAVING smart_search() will yield the desired effect.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Hegde, Deepakakumar (D.)
Gesendet: Dienstag, 28. August 2018 07:47
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Query on TEMP view.

Hi All,


I am facing a problem where in defined function registered to sqlite is called multiple time withput considering the filter.


Ex:

Table and entry:

ID  NAME  PPID

1    a.mp3   2

2    b.mp3   3


Query:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE smart_search(NAME,ID)


OutPut count = 1 (This is fine)


Here smart_search() is defined and given to sqlite DB.

We are expecting smart_search() to be called from sqlite only for the entry with PPID 2 and that will be for ID 1 a.mp3.

But we see that this is called 2 times and for both the entry.


As per my understanding. filter of PPID is added for the inner query smart_search() should have called only for one entry. Is this the expected behavior?


If i change the query as below then the smart_search() is called for only one entry.

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)


Please help to understand on this.


Thanks and Regards

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Query on TEMP view.

Keith Medcalf
In reply to this post by Deepak Hegde

HAVING is only applicable to GROUP BY's.  That is, the WHERE clauses constrain what goes into the sorter for the "group by" operation and the HAVING clauses constrain what comes out of the sorter from the "group by" operation and is returned as a query result.

I think that the issue is that the evaluation of conditions is not necessarily evaluated according to cost (though this may have changed in various versions of the optimizer).  The simplest way to evaluate the "cost" is to determine for each "expression1 op expression2" the number of "operations" involved and then to evaluate in order of ascending cost, where simple operations (+-*/<<>> <= = == => etc) have a cost of say 1, and function calls are assigned a higher (arbitrary) cost of say 10 (100, 1000?).  Each "expression op expression" can then have a cost assigned to it so that they can be evaluated in order of ascending cost thus hopefully bypassing the evaluation of more expensive conditional expressions in cases like this.

Whether and to what degree the optimizer does this I do not know.  Richard will know the answer to this one, and in particular which versions do what ...

---
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 Hick Gunter
>Sent: Tuesday, 28 August, 2018 00:30
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] Query on TEMP view.
>
>In the sqlite shell, enter the .explain command and then
>
>EXPLAIN QUERY PLAN <your query>
>
>for an explanation of the plan, and
>
>EXPLAIN <your query>
>
>for the generated bytecode. This usually helps to understand what
>sqlite is thinking (although maybe not why).
>
>Note that WHERE constraints are applied to the input set whereas
>HAVING constraints are applied to the output set, so perhaps changing
>the "outer" constraint to HAVING smart_search() will yield the
>desired effect.
>
>-----Urspr√ľngliche Nachricht-----
>Von: sqlite-users [mailto:sqlite-users-
>[hidden email]] Im Auftrag von Hegde, Deepakakumar
>(D.)
>Gesendet: Dienstag, 28. August 2018 07:47
>An: [hidden email]
>Betreff: [EXTERNAL] [sqlite] Query on TEMP view.
>
>Hi All,
>
>
>I am facing a problem where in defined function registered to sqlite
>is called multiple time withput considering the filter.
>
>
>Ex:
>
>Table and entry:
>
>ID  NAME  PPID
>
>1    a.mp3   2
>
>2    b.mp3   3
>
>
>Query:
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE smart_search(NAME,ID)
>
>
>OutPut count = 1 (This is fine)
>
>
>Here smart_search() is defined and given to sqlite DB.
>
>We are expecting smart_search() to be called from sqlite only for the
>entry with PPID 2 and that will be for ID 1 a.mp3.
>
>But we see that this is called 2 times and for both the entry.
>
>
>As per my understanding. filter of PPID is added for the inner query
>smart_search() should have called only for one entry. Is this the
>expected behavior?
>
>
>If i change the query as below then the smart_search() is called for
>only one entry.
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)
>
>
>Please help to understand on this.
>
>
>Thanks and Regards
>
>Deepak
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___________________________________________
> Gunter Hick | Software Engineer | Scientific Games International
>GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>0430013 | (O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>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