Re: [sqlite] User function calculates for every row before applying any where condition
On 04/11/2011 05:32 PM, Enrico Thierbach wrote: > > On 11.04.2011, at 10:18, Maxim V. Shiyanovsky wrote: > >> Does SQlite (3.6.23.1 to be precise) perform any optimization when user >> function appears in the statement? >> >> I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with >> sqlite3_create_function. >> >> Suppose, we have 2 tables: >> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records >> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records >> >> Using query: >> SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON >> Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50 >> I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1. >> >> Using subquery does not do anything with the problem. > > SELECT sq.id, MY_FUNC(sq.str1, sq.str2) FROM >(SELECT Tbl2.id, Tbl1.str AS str1, Tbl2.str AS str2 FROM Tbl2 JOIN Tbl1 ON > Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50) sq > > really doesn't help? This doesn't seem right. What is the whole database schema? Is this the exact query in use or a modified version? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User function calculates for every row before applying any where condition
On 11.04.2011, at 10:18, Maxim V. Shiyanovsky wrote: > Does SQlite (3.6.23.1 to be precise) perform any optimization when user > function appears in the statement? > > I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with > sqlite3_create_function. > > Suppose, we have 2 tables: > Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records > Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records > > Using query: > SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON > Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50 > I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1. > > Using subquery does not do anything with the problem. SELECT sq.id, MY_FUNC(sq.str1, sq.str2) FROM (SELECT Tbl2.id, Tbl1.str AS str1, Tbl2.str AS str2 FROM Tbl2 JOIN Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50) sq really doesn't help? /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User function calculates for every row before applying any where condition
Does SQlite (3.6.23.1 to be precise) perform any optimization when user function appears in the statement? I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with sqlite3_create_function. Suppose, we have 2 tables: Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records Using query: SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50 I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1. Using subquery does not do anything with the problem. Any suggestion on how to avoid 1 evaluation of time-consuming function? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users