Some progress in this. Doing a trivial file write (set Read-Only to True and then back to False) gives me the same speed benefit. So at least this takes SQLite out of the equation in solving this problem.
RBS -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 22:15 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely trivial update will do it, so it looks somehow the db write on the desktop does makes it go fast. I can't check now if the same applies when doing this db write on the first PC and hopefully it does. Maybe I need some app to compare the 2 db files to see what is going on here. Baffling me. RBS -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:54 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow ----- addr opcode p1 p2 p3 ------------------------------------ 0 Goto 0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore 2 0 8 IfMemZero 2 31 9 IfMemPos 2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto 0 14 13 MemStore 3 1 14 Integer 0 0 15 OpenRead 1 15123 keyinfo(1,BINARY) 16 SetNumColumns 1 2 17 Integer 823 0 18 IsNull -1 30 19 MakeRecord 1 0 d 20 MemStore 4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 + 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next 1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback 1 0 33 Halt 0 0 34 Transaction 0 0 35 VerifyCookie 0 397 36 Goto 0 1 37 Noop 0 0 Fast ----- addr opcode p1 p2 ------------------------------------ 0 Goto 0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore 2 0 8 IfMemZero 2 31 9 IfMemPos 2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto 0 14 13 MemStore 3 1 14 Integer 0 0 15 OpenRead 1 15123 16 SetNumColumns 1 2 17 Integer 831 0 18 IsNull -1 30 19 MakeRecord 1 0 20 MemStore 4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next 1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback 1 0 33 Halt 0 0 34 Transaction 0 0 35 VerifyCookie 0 400 36 Goto 0 1 37 Noop 0 0 So, what possibly could explain this? There can be no cache effect as I can reverse it from fast to slow and vice versa by copying one or the other database. RBS -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE PATIENT_ID = 123) There is an index on field PATIENT_ID. Takes query takes quite long, say about 1 second. Now I do the following: On the home PC I access the SQLite db file, again with 3.6.1 and I drop a table in that database. This table is only small and completely irrelevant In the above query. After doing that I copy that db file to the mobile device, so overwriting the old file. Now when I run that same query it is a multitude faster, say maybe 20 milli-seconds. I can achieve the same by dropping an index, again in a small table, completely unrelated to the above query and I see the same speed gain. I have compared the 2 different database files, so stats, all the different pragma's etc. and I can't see the difference. I have also compared the SQLite query plans and they are the same (using the index) for both files. So what possibly could explain the difference in speed? I know it is a bit of a long-winded question, but maybe somebody has some idea what is going on here and if so, very grateful for that as I can't see it. RBS _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
