Re: [sqlite] FW: Performance problem with complex where clause

Александр Прокофьев wrote: I've tried running versions of query that you suggested and got following results: Originally posted version 4.5 sec Version with only first-part of where clause 16 ms Yes, but this version isn't finding the same rows. :-) Rewritten version without

Re: [sqlite] stmt question

Variables are bound until you issue a reset or finalize. Jonathan Kahn wrote: Hi, I have a couple questions about using prepared statements with sqlite. If I prepare a statement can I bind variables as my value and then set the variables in a loop and execute? Or in my loop would I

Re: [sqlite] Makefile

Just removing the -g will not get rid of all the debugging information so strip will still give you a smaller executbale file. Danilo wrote: ...or you can find and delete " -g" from the Makefile! John Stanton ha scritto: You can run strip on the file. Ken wrote: Is there a way to disable

Re: [sqlite] Makefile

Thanks Tom, That was just what I was looking for Regards, Ken Tomash Brechko <[EMAIL PROTECTED]> wrote: On Wed, May 02, 2007 at 11:43:04 -0700, Ken wrote: > Is there a way to disable the -g flag for the library? Assuming you are using configure, ./configure CFLAGS='-O2'

RE: [sqlite] Re: Re: stmt question

Thanks a lot -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 02, 2007 6:00 PM To: SQLite Subject: [sqlite] Re: Re: stmt question Jonathan Kahn <[EMAIL PROTECTED]> wrote: > Ahh thanks a lot for clearing that up I wasn't sure if reset cleared > my >

RE: [sqlite] Re: stmt question

Ahh thanks a lot for clearing that up I wasn't sure if reset cleared my actual prepare statement or just the parameters. So technically at the end of my loop I can call reset and rebind? Thanks a lot, - Jon -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent:

[sqlite] Re: stmt question

Jonathan Kahn <[EMAIL PROTECTED]> wrote: If I prepare a statement can I bind variables as my value and then set the variables in a loop and execute? Or in my loop would I bind my values and step so each bind gets executed until it equals SQLITE_DONE then reset? I guess I am just unclear on

[sqlite] stmt question

Hi, I have a couple questions about using prepared statements with sqlite. If I prepare a statement can I bind variables as my value and then set the variables in a loop and execute? Or in my loop would I bind my values and step so each bind gets executed until it equals SQLITE_DONE then

RE: [sqlite] FW: Performance problem with complex where clause

Thanks for suggestion. Now I can say for certain that no index is used on Link table in query Select ne.* From Node AS n JOIN Entity AS ne ON n.LOCAL_ID = ne.LOCAL_ID JOIN Link AS l JOIN Entity AS le ON l.LOCAL_ID = le.LOCAL_ID Where l."Target.Id" =

RE: [sqlite] FW: Performance problem with complex where clause

regarding: "So it seems that indexes are not used at all, and that is pretty strange" There's a great feature in sqlite that lets you know for sure. Prefix your query with: EXPLAIN QUERY PLAN SELECT . And you can see just which, if any indices are used. For a more detailed

RE: [sqlite] FW: Performance problem with complex where clause

Thanks for your reply. I've tried running versions of query that you suggested and got following results: Originally posted version 4.5 sec Version with only first-part of where clause16 ms Rewritten version without joins runs in 6.5 seconds Version with unions runs in 15ms

Re: [sqlite] Makefile

On Wed, May 02, 2007 at 11:43:04 -0700, Ken wrote: > Is there a way to disable the -g flag for the library? Assuming you are using configure, ./configure CFLAGS='-O2' After that 'make' will use only -O2, without -g. > Is there a way to tell the Make to build a 32bit version vs a 64 > bit?

Re: [sqlite] Makefile

...or you can find and delete " -g" from the Makefile! John Stanton ha scritto: > You can run strip on the file. > > Ken wrote: >> Is there a way to disable the -g flag for the library? >> I've found that the version compiled without the -g flags is about 3 >> times smaller (right around the

Re: [sqlite] Best way to optimize this query?

Tito Ciuro wrote: Assume the following scenario: I store people in a table, like this: People ROWID, idx GUID, idx First, idx Last, idx Email ... In the app, the user can select People GUIDs from different sources and then retrieve the info from the database. The

Re: [sqlite] Makefile

You can run strip on the file. Ken wrote: Is there a way to disable the -g flag for the library? I've found that the version compiled without the -g flags is about 3 times smaller (right around the 500k mark) but the default compile is about 1.7 meg! Is there a way to tell the Make to

[sqlite] Makefile

Is there a way to disable the -g flag for the library? I've found that the version compiled without the -g flags is about 3 times smaller (right around the 500k mark) but the default compile is about 1.7 meg! Is there a way to tell the Make to build a 32bit version vs a 64 bit? If not

Re: [sqlite] Best way to optimize this query?

Hi Donald, On May 2, 2007, at 11:25 AM, Griggs, Donald wrote: The ROWID is indexed implicitly I believe, so it may be slowing things slightly if you index it explicitly. Yes, I was aware of that, thanks for the heads up. Regarding: "What if I have, say, 500 to retrieve?" You can create a

Re: [sqlite] Best way to optimize this query?

Hello, On May 2, 2007, at 11:11 AM, P Kishor wrote: On 5/2/07, Tito Ciuro <[EMAIL PROTECTED]> wrote: When you say "speed things up," is it not fast enough yet? Numbers would be helpful. I've just tested it and the query is *very* fast. I was just wondering whether this type of query looked

RE: [sqlite] Best way to optimize this query?

Hi Tito, People ROWID, idx GUID, idx First, idx Last, idx Email ... The ROWID is indexed implicitly I believe, so it may be slowing things slightly if you index it explicitly. Regarding: "What if I have, say, 500 to retrieve?" You can create a

Re: [sqlite] Best way to optimize this query?

On 5/2/07, Tito Ciuro <[EMAIL PROTECTED]> wrote: Hello, Assume the following scenario: I store people in a table, like this: People ROWID, idx GUID, idx First, idx Last, idx Email ... In the app, the user can select People GUIDs from different

[sqlite] Best way to optimize this query?

Hello, Assume the following scenario: I store people in a table, like this: People ROWID, idx GUID, idx First, idx Last, idx Email ... In the app, the user can select People GUIDs from different sources and then retrieve the info from the

Re: [sqlite] Copy records from one DB to another

On Tue, 24 Apr 2007 19:57:31 +0200, Yuriy Martsynovskyy <[EMAIL PROTECTED]> wrote: What is the best way to copy records between tables located in different DB files? Hi, I want to extend this question: How to best copy records from one database (linked via different tables) to an other

Re: [sqlite] FW: Performance problem with complex where clause

Samuel R. Neff wrote: Will the original poster still run into performance problems where sqlite will only use one index per table so if targetid matches on a ton of rows sqlite has to scan them all for the matching sourceid? Perhaps a multi-column index would be appropriate here to index both

RE: [sqlite] FW: Performance problem with complex where clause

Will the original poster still run into performance problems where sqlite will only use one index per table so if targetid matches on a ton of rows sqlite has to scan them all for the matching sourceid? Perhaps a multi-column index would be appropriate here to index both "target.id" and

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

Richard, For what it's worth, it would be very convenient to have shell.c included in the preprocessed source distro. sqlite3.def would also be convenient, but the nm sqlite3.o | grep ... | sed ... >>sqlite3.def method seems to correctly generate sqlite3.def on my Windows system - EXCEPT,

Re: [sqlite] FW: Performance problem with complex where clause

Александр Прокофьев wrote: I'm running a query against sqlite database and it runs very slow - about 5 seconds(on larger database it can ran for a whole minute). Select [Node_Entity].* From [Node] AS [Node_Node] JOIN [Entity] AS [Node_Entity] ON [Node_Node].LOCAL_ID =

[sqlite] Amalgamation and CPP defines

Hello, This letter has three distinct questions, with Q1 being the main. Q1: This new amalgamation feature is really great. A simple test program inserting 24 millions rows, each row is an integer PK and three integers of data, runs 40% faster for me. But how to properly compile the

[sqlite] FW: Performance problem with complex where clause

I'm running a query against sqlite database and it runs very slow - about 5 seconds(on larger database it can ran for a whole minute). Select [Node_Entity].* From [Node] AS [Node_Node] JOIN [Entity] AS [Node_Entity] ON [Node_Node].LOCAL_ID = [Node_Entity].LOCAL_ID , [Link] AS [Link_Link] JOIN

[sqlite] Re: Is this valid sqlite stmt?

B V, Phanisekhar <[EMAIL PROTECTED]> wrote: DELETE FROM WHERE rowid = a AND refcount - 1 = 0 IF @@ROWCOUNT = 0 UPDATE SET refcount = refcount - 1 where rowid = a Is conditional statements allowed in sqlite? No. Furthermore, SQLite does not support batches (sequences of statements),

[sqlite] Is this valid sqlite stmt?

DELETE FROM WHERE rowid = a AND refcount - 1 = 0 IF @@ROWCOUNT = 0 UPDATE SET refcount = refcount - 1 where rowid = a Is conditional statements allowed in sqlite? Regards, Phani