Re: [sqlite] Faster check: > or !=
On Mon, 13 Feb 2017 15:32:00 + jose isaias cabrera wrote: > I have a bunch of records to check, and I am wondering which is a > faster check. I am attaching a network DB as client, > > ATTACH 'h:\bkup\test.db' AS client; If you're concerned about speed or reliability, it would be far, far faster to use locally attached storage. Any difference in how comparisons are expressed will be dwarfed by that choice. If you want many clients to share the database from different hosts, SQLite is not the right tool for that job. Definitely not fastest, and also vulnerable to data corruption. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster check: > or !=
On 13 Feb 2017, at 9:03pm, jose isaias cabrera wrote: >> Can you set an index on id, projId, and Xtrab ? > Already have one for each. > > CREATE INDEX OpPid ON LSOpenProjects (ProjID); > CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB); No. This does not do the same thing as one index which combines all three columns. Imaging you had a phone book with firstname, surname, phonenumber. I ask you to look up "Paul Smith". You have one index with firstname and another index with surname. You can pick either index but they will not take you to the right entry. One index can take you straight to "Paul", but then you have to search all the "Paul" entries looking for "Smith". There could be thousands of "Paul" entries. The other index would take you straight to "Smith" but then you’d have to search through them for "Paul". There might be thousands of "Smiths". But create an index on (firstname,surname) and then you can go straight to the first "Paul Smith", then immediately to all the other people with the same name. You do not make good indexes by indexing columns individually. You make good indexes by looking at a WHERE clause and making an index which is perfect for that clause. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster check: > or !=
On 2017-02-13 18:07, Simon Slavin wrote: On 13 Feb 2017, at 3:32pm, jose isaias cabrera wrote: Any input would be greatly appreciated. I doubt there's much difference, though the one with the fixed timestamp string may be a touch faster. But the real speedup would come from an index like this: CREATE INDEX client.OP_IPX ON client.OpenProjects (id,ProjID,XtraB) Simon. Yes. Already done that. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster check: > or !=
On 2017-02-13 17:20, Adam DeVita wrote: How much control do you have? All of it. Can you put out your date code into an integer field? Hmmm... I could. I have to do some programming to replace the normal date with the integer date. Can you set an index on id, projId, and Xtrab ? Already have one for each. CREATE INDEX OpPid ON LSOpenProjects (ProjID); CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB); CREATE TABLE LSOpenProjects ( id integer primary key, ProjID integer,... ); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster check: > or !=
How much control do you have? Can you put out your date code into an integer field? Can you set an index on id, projId, and Xtrab ? regards, Adam From: sqlite-users on behalf of jose isaias cabrera Sent: February 13, 2017 10:32:00 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Faster check: > or != Greetings! I have a bunch of records to check, and I am wondering which is a faster check. I am attaching a network DB as client, ATTACH 'h:\bkup\test.db' AS client; and then do an INSERT based on some logic, and one of those login is checking against a variable or an actual DB value. Here are the different SQL syntaxes: BEGIN; INSERT OR REPLACE INTO OpenProjects SELECT * FROM client.OpenProjects WHERE id IN ( SELECT id FROM client.OpenProjects WHERE client.OpenProjects.id = id AND client.OpenProjects.ProjID <= 133560 AND client.OpenProjects.XtraB != XtraB -- change ); END; BEGIN; INSERT OR REPLACE INTO OpenProjects SELECT * FROM client.OpenProjects WHERE id IN ( SELECT id FROM client.OpenProjects WHERE client.OpenProjects.id = id AND client.OpenProjects.ProjID <= 133560 AND client.OpenProjects.XtraB > '2017-02-10 00:00:00' -- change ); END; Any input would be greatly appreciated. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users === CONFIDENTIALITY NOTICE: This e-mail and any attachments contain information from Lytx, Inc. and/or its affiliates, and are intended solely for the use of the named recipient(s). This e-mail may contain confidential information of Lytx and its customers. Any dissemination of this e-mail by anyone other than an intended recipient is strictly prohibited. If you are not a named recipient, you are prohibited from any further viewing of the e-mail or any attachments or from making any use of the e-mail or attachments. If you believe you have received this e-mail in error, notify the sender immediately and permanently delete the e-mail, any attachments, and all copies thereof from any drives or storage media and destroy any printouts of the e-mail or attachments. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster check: > or !=
On 13 Feb 2017, at 3:32pm, jose isaias cabrera wrote: > Any input would be greatly appreciated. I doubt there’s much difference, though the one with the fixed timestamp string may be a touch faster. But the real speedup would come from an index like this: CREATE INDEX client.OP_IPX ON client.OpenProjects (id,ProjID,XtraB) Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
