Quantcast

Faster check: > or !=

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Faster check: > or !=

jose isaias cabrera-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

Adam DeVita-2
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 <[hidden email]> on behalf of jose isaias cabrera <[hidden email]>
Sent: February 13, 2017 10:32:00 AM
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

Simon Slavin-3
In reply to this post by jose isaias cabrera-3

On 13 Feb 2017, at 3:32pm, jose isaias cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

jose isaias cabrera-3
In reply to this post by Adam DeVita-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

jose isaias cabrera-3
In reply to this post by Simon Slavin-3


On 2017-02-13 18:07, Simon Slavin wrote:

> On 13 Feb 2017, at 3:32pm, jose isaias cabrera <[hidden email]>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

Simon Slavin-3
In reply to this post by jose isaias cabrera-3

On 13 Feb 2017, at 9:03pm, jose isaias cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Faster check: > or !=

James K. Lowden
In reply to this post by jose isaias cabrera-3
On Mon, 13 Feb 2017 15:32:00 +0000
jose isaias cabrera <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...