Have a table (only used for testing) like this:
CREATE TABLE Table1([Field1] INTEGER)
It has 100000 rows and all the values in Field1 are 1.
There are no indexes.
Now I run queries that include a UDF. The code of this UDF is in VBScript
and generally
this is slow.
This is the first SQL:
select testargs('abc', 12, 1.123) as xxx from table1
This produces an integer value and obviously it will be same for all the
rows.
This runs in about 3 seconds.
This is the second SQL:
select 0 + testargs('abc', 12, 1.123) as xxx from table1
Again obviously this will give exactly the same results.
Now though it runs in some 10 milliseconds.
I guess the reason for this difference is that SQLite doesn't recognize
that the result of the
first SQL will always be the same, so the UDF runs 100000 times and that it
does recognize
this for the second SQL and the UDF only runs once.
Firstly, is this a bug?
I can see though that this scenario is pointless and won't happen other
than in experimenting.
Secondly, can the Explain statement tell me that indeed the UDF runs 100000
times in the first SQL and only once for the second SQL?
Explain of the first SQL:
addr opcode p1 p2 p3 p4 p5 comment
---------------------------------------------------
0 Init 0 8 0 00 Start at 8
1 OpenRead 0 528 0 0 00 root=528 iDb=0; Table1
2 Rewind 0 6 0 00
3 Function0 7 2 1 TestArgs(3) 03 r[1]=func(r[2..4])
4 ResultRow 1 1 0 00 output=r[1]
5 Next 0 3 0 01
6 Close 0 0 0 00
7 Halt 0 0 0 00
8 Transaction 0 0 4911 0 01 usesStmtJournal=0
9 TableLock 0 528 0 Table1 00 iDb=0 root=528 write=0
10 String8 0 2 0 abc 00 r[2]='abc'
11 Integer 12 3 0 00 r[3]=12
12 Real 0 4 0 1.123 00 r[4]=1.123
13 Goto 0 1 0 00
Explain of the second
SQL:
addr opcode p1 p2 p3 p4 p5 comment
---------------------------------------------------
0 Init 0 8 0 00 Start at 8
1 OpenRead 0 528 0 0 00 root=528 iDb=0; Table1
2 Rewind 0 6 0 00
3 Add 3 2 1 00 r[1]=r[3]+r[2]
4 ResultRow 1 1 0 00 output=r[1]
5 Next 0 3 0 01
6 Close 0 0 0 00
7 Halt 0 0 0 00
8 Transaction 0 0 4911 0 01 usesStmtJournal=0
9 TableLock 0 528 0 Table1 00 iDb=0 root=528 write=0
10 Integer 0 2 0 00 r[2]=0
11 String8 0 4 0 abc 00 r[4]='abc'
12 Integer 12 5 0 00 r[5]=12
13 Real 0 6 0 1.123 00 r[6]=1.123
14 Function0 7 4 3 TestArgs(3) 03 r[3]=func(r[4..6])
15 Goto 0 1 0 00
Thanks for any insight.
RBS
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
