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 joins runs in 6.5 seconds This is surprising. Are you sure about this timing, it doesn't make sense given the 4.5 second timings below. The only thing I can think of is that reversing the order of the second half of the where clause caused it to be slower. Can you try the following? Select ne.* From Entity AS ne Join Link AS l Where l."Source.Id" = ne.Id AND l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' OR l."Target.Id" = ne.Id AND l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' Version with unions runs in 15ms This is as I suspected. Each sub select runs a table scan of the entity table and uses an index to lookup matching rows in the link table. These results are then combined. As I mentioned in my post to Samuel, it may be faster to swap the order of the tables in the subselects. It will then scan the link table and use the entity id index to lookup matching rows in the entity table. Creating multi-column index (source.id, target.id) 4.5 sec This shouldn't have any effect because the index will not be used if you already have the other indexes. Removing all indexes for source.id and target.id4.5 sec OK, this should be the time for a full cartesian product scan, i.e. a scan through each row of the link table combined with each row if the entity table, or a intermediate joined table of N links times M entities. Running query with first part of where clause and no indexes2.5 sec So it seems that indexes are not used at all, and that is pretty strange I didn't state it in my original post, but this query was automatically generated by object relational-mapper, that generates hundreds of similar queries. I see that replacement of OR with UNION and AND with INTERSECT will probably solve the problem, but yet it seems that using OR and AND in WHERE clause is a better way to write queries, especially with complex nested conditions. Ah. That explains the unnecessary quotes and brackets. I'm not sure why it is including the unnecessary tables thought. Using OR and AND in the where clause might make sense for a database with a much more sophisticated optimizer which effectively rewrites your queries for you, or one that can use multiple indexes to implement a table scan. SQLite doesn't do many optimizations and is limited to a single index for each table scan. For more info on how sqlite implements queries see http://www.sqlite.org/google-talk-slides/page-001.html around slide 40 or so (if memory serves me). To get optimal performance from sqlite you may have to hand tune some of your queries, or make your query generator more intelligent. Some information on db: About 1500 records in each table Original query returns about 10 rows So a full cartesian join results in about 1500 x 1500 or 2,250,000 rows which takes about 4.5 seconds to scan looking for matching entity.id and link.target.id or link.source.id, and then checking the other link id for the specified value. Given the tables are about the same size it would probably be best to remove the indexes on source id and target id and the reorder the tables in the queries so that they both use the same index on entity id. Select e.* From Link AS l Join Entity AS e on l."Source.Id" = e.Id Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' UNION Select e.* From Link AS l Join Entity AS e on l."Target.Id" = e.Id Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' This will do two scans through the 1500 records in the link table for a total of 3000 rows. For each row it will do in index lookup in the entity table to find the matching entity. In fact, sqlite will probably do the where clause comparison before the index lookup so that the lookup is only done for rows that match the where condition (an optimization). Effectively you are scanning 300 rows and doing 10 indexed lookups. Any suggestions on why indexes are not used and how to make SQLite use them for this query? Indexes are not used where there is no way to know which one to use or where a single index can't provide the required information. You can't join the entity table to the link table using two different columns, source.id and target.id, using an index. You could try this as well with an index on each of the source id and target id link fields. Select e.* From Entity AS e left Join Link AS sl on sl."Source.Id" = e.Id left Join Link as tl on tl."Target.Id" = e.Id Where sl."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' OR tl."Source.Id" =
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 bind my values and step so each bind gets executed until it equals SQLITE_DONE then reset? I guess I am just unclear on how to execute my prepared statement and set my values in a loop. Is there a better way to do what I want? Am I completely off base? Any info is much appreciated. This is my first time working with the sqlite3 api and sqlite so please forgive any ignorance. Thanks a lot, - Jon - To unsubscribe, send email to [EMAIL PROTECTED] -
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 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 this would be really nice. Can the Make that is provided build a libsqlite3.a and libsqlite3.so from the amalgamated sqlite3.c ??? Thanks Ken - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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' 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? If not this would be really nice. You may pass arbitrary compilation options as shown above, or you may override the compiler itself with ./configure CC=/path/to/gcc32bit > Can the Make that is provided build a libsqlite3.a and libsqlite3.so > from the amalgamated sqlite3.c ??? No. But the following quick-n-dirty-cut-n-paste patch will (hopefully) do the job :) --- Makefile.in-orig 2007-05-02 19:12:21.0 +0400 +++ Makefile.in 2007-05-03 00:16:07.0 +0400 @@ -130,6 +130,9 @@ LIBOBJ = alter.lo analyze.lo attach.lo a vdbe.lo vdbeapi.lo vdbeaux.lo vdbefifo.lo vdbemem.lo \ where.lo utf.lo legacy.lo vtab.lo +LIBOBJ = sqlite3.lo + + # All of the source code files. # SRC = \ @@ -315,6 +318,9 @@ lemon$(BEXE): $(TOP)/tool/lemon.c $(TOP) # Rules to build individual files # +sqlite3.lo: sqlite3.c + $(LTCOMPILE) -c sqlite3.c + alter.lo: $(TOP)/src/alter.c $(HDR) $(LTCOMPILE) -c $(TOP)/src/alter.c -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
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 > actual prepare statement or just the parameters. It doesn't even clear the parameters. The old values are preserved. You can rebind some or all of them. > So technically at > the end > of my loop I can call reset and rebind? Yes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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: Wednesday, May 02, 2007 5:48 PM To: SQLite Subject: [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 how to execute > my prepared statement and set my values in a loop. You have to bind all parameters after sqlite3_prepare or sqlite3_reset calls, and before making the first sqlite3_step call. Once you call step, you can't change parameters until you call reset. Usually you would call step in a loop until it retuns SQLITE_DONE, but it's not mandatory (you can stop early). Once you are done processing the results, call sqlite_reset to make the statement ready for new execution, or call sqlite3_finalize to destroy the statement. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[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 how to execute my prepared statement and set my values in a loop. You have to bind all parameters after sqlite3_prepare or sqlite3_reset calls, and before making the first sqlite3_step call. Once you call step, you can't change parameters until you call reset. Usually you would call step in a loop until it retuns SQLITE_DONE, but it's not mandatory (you can stop early). Once you are done processing the results, call sqlite_reset to make the statement ready for new execution, or call sqlite3_finalize to destroy the statement. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[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 reset? I guess I am just unclear on how to execute my prepared statement and set my values in a loop. Is there a better way to do what I want? Am I completely off base? Any info is much appreciated. This is my first time working with the sqlite3 api and sqlite so please forgive any ignorance. Thanks a lot, - Jon
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" = '06d15df5-4253-4a65-b91f-cca52da960fe' AND l."Source.Id" = ne.Id OR l."Target.Id" = ne.Id AND l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' And in 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" = '06d15df5-4253-4a65-b91f-cca52da960fe' AND l."Source.Id" = ne.Id Index for target.id is used -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: Thursday, May 03, 2007 1:04 AM To: sqlite-users@sqlite.org Subject: 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 look at the internal "program" that your query will generate, you can use simply EXPLAIN SELECT . - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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 look at the internal "program" that your query will generate, you can use simply EXPLAIN SELECT . - To unsubscribe, send email to [EMAIL PROTECTED] -
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
Creating multi-column index (source.id, target.id) 4.5 sec
Removing all indexes for source.id and target.id4.5 sec
Running query with first part of where clause and no indexes2.5 sec
So it seems that indexes are not used at all, and that is pretty strange
I didn't state it in my original post, but this query was automatically
generated by object relational-mapper, that generates hundreds of similar
queries. I see that replacement of OR with UNION and AND with INTERSECT will
probably solve the problem, but yet it seems that using OR and AND in WHERE
clause is a better way to write queries, especially with complex nested
conditions.
Some information on db:
About 1500 records in each table
Original query returns about 10 rows
Any suggestions on why indexes are not used and how to make SQLite use them
for this query?
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 02, 2007 6:58 PM
To: sqlite-users@sqlite.org
Subject: 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 = [Node_Entity].LOCAL_ID , [Link] AS
> [Link_Link] JOIN [Entity] AS [Link_Entity] ON [Link_Link].LOCAL_ID =
> [Link_Entity].LOCAL_ID
>
>
>
> Where ((
>
> (([Link_Link].[Target.Id]='06d15df5-4253-4a65-b91f-cca52da960fe') AND
> ([Link_Link].[Source.Id]=[Node_Entity].[Id]))
>
>
>
> OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND
> ([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe'))
>
> ))
>
>
>
You have an unnecessarily complicated query. First I re-wrote your query
using standard quotes and eliminated the unnecessary quotes and
brackets. I also replaced the alias names with something shorter to make
the existing query clearer.
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" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Target.Id" = ne.Id
AND l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
Now it is clear that you are joining two tables that are not referenced
by the query at all. The node table and the second join on the entity
table (which you called link entity) are not needed. After these are
removed you have an equivalent but much simpler query. Note, I also
rearranged the terms in the second half of the OR clause to be in the
same order as the first half.
Select ne.*
From Entity AS ne
Join Link AS l
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Target.Id" = ne.Id
It might be clearer to yet separate the two halves of this query and
combine the results using a union.
Select ne.*
From Entity AS ne
Join Link AS l on l."Source.Id" = ne.Id
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
UNION
Select ne.*
From Entity AS ne
Join Link AS l on l."Target.Id" = ne.Id
Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
This should run in a reasonable time given that you have indexes on
Link("Target.Id") and Link("Source.Id")
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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? If not this would be really nice. You may pass arbitrary compilation options as shown above, or you may override the compiler itself with ./configure CC=/path/to/gcc32bit > Can the Make that is provided build a libsqlite3.a and libsqlite3.so > from the amalgamated sqlite3.c ??? No. But the following quick-n-dirty-cut-n-paste patch will (hopefully) do the job :) --- Makefile.in-orig2007-05-02 19:12:21.0 +0400 +++ Makefile.in 2007-05-03 00:16:07.0 +0400 @@ -130,6 +130,9 @@ LIBOBJ = alter.lo analyze.lo attach.lo a vdbe.lo vdbeapi.lo vdbeaux.lo vdbefifo.lo vdbemem.lo \ where.lo utf.lo legacy.lo vtab.lo +LIBOBJ = sqlite3.lo + + # All of the source code files. # SRC = \ @@ -315,6 +318,9 @@ lemon$(BEXE): $(TOP)/tool/lemon.c $(TOP) # Rules to build individual files # +sqlite3.lo:sqlite3.c + $(LTCOMPILE) -c sqlite3.c + alter.lo: $(TOP)/src/alter.c $(HDR) $(LTCOMPILE) -c $(TOP)/src/alter.c -- Tomash Brechko - To unsubscribe, send email to [EMAIL PROTECTED] -
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 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 this would be really nice. >> >> Can the Make that is provided build a libsqlite3.a and libsqlite3.so >> from the amalgamated sqlite3.c ??? >> >> Thanks >> Ken >> - To unsubscribe, send email to [EMAIL PROTECTED] -
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 easy/suboptimal
route to retrieve the records would be to perform a SELECT per GUID
selected. I thought of something like this:
SELECT * FROM People where GUID in ("ABC", "RDT", "TUV");
Is there a better way to include all these GUIDs on a single SQL
statement to speed things up?
Questions I have:
1) Is this the best way to solve the problem? Suggestions?
Tito,
You might want to use a temp table to hold your list of GUIDs. Then you
can use a single precompiled query to do the select.
select * from People where GUID in (select GUID from temp_guids)
As it is, you will have to prepare your query for each lookup. The temp
table would replace the loop that binds the parameters with a loop that
inserts the guids into the temp table.
Also, you are using double quotes for string literals, these should be
single quotes instead.
... in ('ABC', 'RTD', 'TUV')
2) Is there a limit on the number of parameters I can pass to "in"?
What if I have, say, 500 to retrieve? Will SQLite complain about this?
As far as I know there is no hard limit on the number of parameters you
can use in a query. If you want to precompile the query you will need to
have as many as your maximum requirement. In that case you will have to
bind all the parameters before the query, and then reset them to null
after the query so that the values are reset for the next query (i.e you
will have to bind each variable twice). If you don't do this a query
with many parameters followed by a query with a few will still have
values bound to the higher numbered parameters left over from the first
query.
In the alternate, temp table approach, you would create the table if it
doesn't exist
execute("begin")
execute("create temp table if not exists temp_guids(guid primary key)")
Then loop to insert the GUIDs using a precompiled insert with one parameter
s = prepare(insert into t values(?))
for each guid in guid_list:
bind(s, 1, guid)
execute(s)
execute ("commit")
Now execute your guid lookup query
people = execute("select * from People where GUID in (select guid
from temp_guids)"
And finally clear the temp table
execute("delete from temp_guid")
Of course you would have to try each methods to see which is fastest.
The single query requires compilation for each lookup, a single
precompiled query requires binding each variable twice but has the
advantage that the VDBE code will build an index from the parameters on
the fly (without an associated table), the temp table approach can be
done by executing only prepared sql statements but will build two
records (one in the table and one in the index) for each record.
I seem to be rambling so I will stop now.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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 build a 32bit version vs a 64 bit? If not this would be really nice. Can the Make that is provided build a libsqlite3.a and libsqlite3.so from the amalgamated sqlite3.c ??? Thanks Ken - To unsubscribe, send email to [EMAIL PROTECTED] -
[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 this would be really nice. Can the Make that is provided build a libsqlite3.a and libsqlite3.so from the amalgamated sqlite3.c ??? Thanks Ken
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 temporary table, perhaps in ram memory, where you store the GUIDS, e.g. CREATE TEMP TABLE MyGUIDS(GUID); Then pull them all out of the people table all at once with: SELECT * FROM People WHERE GUID IN (SELECT GUID FROM MyGUIDS) ORDER BY Thank you very much, -- Tito - To unsubscribe, send email to [EMAIL PROTECTED] -
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 right. try it, if SQLite complains, you will know, and you will have to approach the problem differently ;-) I'll try that. Thanks a lot, -- Tito - To unsubscribe, send email to [EMAIL PROTECTED] -
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 temporary table, perhaps in ram memory, where you store the GUIDS, e.g. CREATE TEMP TABLE MyGUIDS(GUID); Then pull them all out of the people table all at once with: SELECT * FROM People WHERE GUID IN (SELECT GUID FROM MyGUIDS) ORDER BY [opinions mine, not my company's] - To unsubscribe, send email to [EMAIL PROTECTED] -
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 sources
and then retrieve the info from the database. The easy/suboptimal
route to retrieve the records would be to perform a SELECT per GUID
selected. I thought of something like this:
SELECT * FROM People where GUID in ("ABC", "RDT", "TUV");
Is there a better way to include all these GUIDs on a single SQL
statement to speed things up?
When you say "speed things up," is it not fast enough yet? Numbers
would be helpful.
Questions I have:
1) Is this the best way to solve the problem? Suggestions?
unless you change your attribution (for example, tag your data by
GUID-types, if there is such a thing in your case), the above is the
best way, perhaps the only way. Internally, I believe the IN clause
gets converted to a set of OR matches as in
GUID = 'ABC' OR GUID = 'XYZ' OR... (or is it the other way around?)
2) Is there a limit on the number of parameters I can pass to "in"?
dunno... try it.
What if I have, say, 500 to retrieve? Will SQLite complain about this?
try it, if SQLite complains, you will know, and you will have to
approach the problem differently ;-)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[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 database. The easy/suboptimal
route to retrieve the records would be to perform a SELECT per GUID
selected. I thought of something like this:
SELECT * FROM People where GUID in ("ABC", "RDT", "TUV");
Is there a better way to include all these GUIDs on a single SQL
statement to speed things up?
Questions I have:
1) Is this the best way to solve the problem? Suggestions?
2) Is there a limit on the number of parameters I can pass to "in"?
What if I have, say, 500 to retrieve? Will SQLite complain about this?
Thanks a lot,
-- Tito
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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 database? How to keep record references in sync? Because if a new records is in the destination table it gets a new auto-increment ID and all references need to be adjusted. Thanks. -- Robert M. Münch http://www.robertmuench.de - To unsubscribe, send email to [EMAIL PROTECTED] -
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 "target.id" and "source.id" in the same index. Samuel, I don't think there will be a problem since each select in the union is free to use its own index for the Link table lookup. A compound index would not help.They are only useful where you want to match one or more fields in the index. A compound index on (Target.Id, Source.Id) could not be used to locate a record given a source id without scanning every index entry since the source id one could be associated with any target id. If the union query does have a problem with a single index, then the queries could be turned around so they scan the link table and then use the index on the entity id field to locate the associated entity. This might even be faster in general, depending upon the relative sizes of the link and entity tables. Select ne.* From Link AS l Join Entity AS ne on l."Source.Id" = ne.Id Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' UNION Select ne.* From Link AS l Join Entity AS ne on l."Target.Id" = ne.Id Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe' HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
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
"source.id" in the same index.
Sam
---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 02, 2007 10:58 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] FW: Performance problem with complex where clause
...
This should run in a reasonable time given that you have indexes on
Link("Target.Id") and Link("Source.Id")
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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, with 3.3.15 and 3.3.16, I have to manually add "sqlite3_io_trace" to sqlite3.def to get the shell to link into sqlite3.exe. Otherwise, it fails with an "unresolved external..." error. This is on Windows XP and MS VC6 using the pre-processed C source distro. shell.c was generated on my Linux box (Red Hat 7.2) using "make target_source" after running the configure script in the "regular" source distro. The same link error occurs using the sqlite3.def file from the precompiled Windows DLL distro file. Thanks! -Clark - Original Message From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, May 2, 2007 7:53:35 AM Subject: Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14? "C.Peachment" <[EMAIL PROTECTED]> wrote: > > After clearing these warnings, I discovered that the Pelles C > compiler was unable to complete the compilation of sqlite3.c > and timed out after 300 seconds. So it appears that I need > to revert to separate source files that were available with > earlier versions of sqlite3. > I consider this to be a bug in Pelles C. It refuses to compile a valid ANSI C program. On the other hand, this is the first argument in favor of separate source files that makes sense to me. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
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 = [Node_Entity].LOCAL_ID , [Link] AS
[Link_Link] JOIN [Entity] AS [Link_Entity] ON [Link_Link].LOCAL_ID =
[Link_Entity].LOCAL_ID
Where ((
(([Link_Link].[Target.Id]='06d15df5-4253-4a65-b91f-cca52da960fe') AND
([Link_Link].[Source.Id]=[Node_Entity].[Id]))
OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND
([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe'))
))
You have an unnecessarily complicated query. First I re-wrote your query
using standard quotes and eliminated the unnecessary quotes and
brackets. I also replaced the alias names with something shorter to make
the existing query clearer.
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" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Target.Id" = ne.Id
AND l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
Now it is clear that you are joining two tables that are not referenced
by the query at all. The node table and the second join on the entity
table (which you called link entity) are not needed. After these are
removed you have an equivalent but much simpler query. Note, I also
rearranged the terms in the second half of the OR clause to be in the
same order as the first half.
Select ne.*
From Entity AS ne
Join Link AS l
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Source.Id" = ne.Id
OR l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
AND l."Target.Id" = ne.Id
It might be clearer to yet separate the two halves of this query and
combine the results using a union.
Select ne.*
From Entity AS ne
Join Link AS l on l."Source.Id" = ne.Id
Where l."Target.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
UNION
Select ne.*
From Entity AS ne
Join Link AS l on l."Target.Id" = ne.Id
Where l."Source.Id" = '06d15df5-4253-4a65-b91f-cca52da960fe'
This should run in a reasonable time given that you have indexes on
Link("Target.Id") and Link("Source.Id")
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[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 amalgamation wrt C preprocessor
defines? Suppose I got sources tarball, and did './configure
--some-options CFLAGS=...'. This produces a Makefile, I can do 'make
sqlite3.c', but I also would like to be able to do 'make sqlite3.o',
so that compilation would actually use all the proper C preprocessor
defines and options resulted from 'configure' run. Currently, I have
to look though generated Makefile to see what options/defines I should
use to get the same result. It there a better way?
Also, when linking with sqlite3.o options '-pthread -ldl' to gcc are
required. It would also be nice if, for instance, 'make
amalgamation-link-options' would print the required options.
Q2:
Why pread()/pwrite() aren't used by default? Yes, we don't _have_ to
use them when the connection object is not shared across threads, but
lseek() before every read()/write() is a bit of overkill.
'-D_FILE_OFFSET_BITS=64 -DUSE_PREAD64=1' did the trick for me with gcc
3.4.2 and glibc 2.3.3 (surprisingly, other combinations didn't work,
and I didn't look into that further), but I'm worried a bit that
USE_PREAD/USE_PREAD64 defines don't look like a part of the user
interface (don't begin with 'SQLITE_').
Q3 (not really core SQLite-specific, but maybe someone have the answer
right away):
I wrote a C application that prepares a statement, and then, in a
loop, inserts 24M rows as described above, binding new values on each
iteration. When I link it with libsqlite3.so.0.8.6, it runs a certain
amount of time, say, 206 seconds. When I rewrite it in Perl using
DBI+DBD::SQLite (and I'm certain that DBD::SQlite uses the same shared
library), it, of course runs longer, the whole 885 seconds. But
OProfile shows:
1416755 100.000 perl
GLOBAL_POWER_E...|
samples| %|
--
577749 40.7797 libperl.so
494994 34.9386 libsqlite3.so.0.8.6
110445 7.7956 libc-2.3.3.so
82901 5.8515 SQLite.so
81748 5.7701 DBI.so
48687 3.4365 libpthread-2.3.3.so
...
Now, 885 * 34.9% = 308 seconds, which is much bigger that 206 seconds
for C program. The main loop in Perl program is simply
for (my $i = 0; $i < 24_000_000; ++$i) {
$insert->execute($i, $i, $i);
}
where $insert is a handle of prepared statement, and the main loop of
C program is functionally the same, so I wonder, what may cause such a
big difference.
I could dig into this myself, but maybe someone has encountered the
same problem before?
Thanks!
--
Tomash Brechko
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[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 [Entity] AS [Link_Entity] ON [Link_Link].LOCAL_ID = [Link_Entity].LOCAL_ID Where (( (([Link_Link].[Target.Id]='06d15df5-4253-4a65-b91f-cca52da960fe') AND ([Link_Link].[Source.Id]=[Node_Entity].[Id])) OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND ([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe')) )) If I remove a part of where clause " OR (([Link_Link].[Target.Id]=[Node_Entity].[Id]) AND ([Link_Link].[Source.Id]='06d15df5-4253-4a65-b91f-cca52da960fe'))" It runs in 16 ms. All .Id fields are indexed. For me it looks like a bug in optimizer or like created indexes are not used for some reason Database file with sample data can be downloaded from here - http://slil.ru/24319807 (350 kb) Database structure is CREATE TABLE [Entity] (LOCAL_ID bigint NOT NULL , CHANGE_TYPE tinyint NULL , [Id] UNIQUEIDENTIFIER NULL , :); CREATE INDEX [IEntity_Id] ON [Entity]([Id]); CREATE INDEX [IEntity_Version.Time] ON [Entity]([Version.Time]); CREATE INDEX [IEntity_Version.ChangedBy.Id] ON [Entity]([Version.ChangedBy.Id]); CREATE INDEX [IEntity_Type.Id] ON [Entity]([Type.Id]); CREATE UNIQUE INDEX [IEntity_LOCAL_ID] ON [Entity](LOCAL_ID); CREATE TABLE [Link] (LOCAL_ID bigint NOT NULL , [Source.Id] UNIQUEIDENTIFIER NULL , [Source.Id$C] bit NOT NULL DEFAULT 0 , [Target.Id] UNIQUEIDENTIFIER NULL , [Target.Id$C] bit NOT NULL DEFAULT 0); CREATE INDEX [ILink_Source.Id] ON [Link]([Source.Id]); CREATE INDEX [ILink_Target.Id] ON [Link]([Target.Id]); CREATE UNIQUE INDEX [ILink_LOCAL_ID] ON [Link](LOCAL_ID); CREATE TABLE [Node] (LOCAL_ID bigint NOT NULL , [NodeName] NTEXT NULL , :); CREATE INDEX [INode_Owner.Id] ON [Node]([Owner.Id]); CREATE UNIQUE INDEX [INode_LOCAL_ID] ON [Node](LOCAL_ID);
[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), except in the body of a trigger. If you need to implement such a sequence in your program, sqlite3_changes() is a programmatic equivalent of @@ROWCOUNT. Note that, in this particular case, you don't need the check at all. If the row has a rowcount of 1, then DELETE statement will delete it, then UPDATE statement run unconditionally will be a no-op (since no row meets the condition). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[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
