Re: [sqlite] SQLITE starvation.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 16/01/13 07:59, Shuki Sasson wrote:
> When we run the test we see that two threads are trying hundreds of
> time to get through the begin unsuccessfully.
Look at the code for the default busy handler (sqliteDefaultBusyCallback).
It keeps retrying with increasing backoff. If you do not like these
intervals then write your own busy handler that uses a better algorithm
for your use case.
These are the default intervals used in milliseconds (providing you have
sleep granularity better than seconds which is the case on Windows and
with usleep).
{ 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 }
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAlD4erkACgkQmOOfHg372QQWPwCfZlMikT1DnuiflZDa58pIdnD9
vwYAoKRI+Z5UQWl6NP6rm8K1eyrRxYht
=vuw2
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is anyone have problems called sqlite3 on the WindowsPhone using VB?
drj1...@aol.com wrote: > > I am using VB and trying to call sqlite3.dll on the Windows 8 Phone > development environment , I want to find out whether that version of the DLL > supports calls from VB ? > >From VB, you'll need to use one of the available managed wrappers available for SQLite for Windows Phone 8. Some possible options are: https://www.nuget.org/packages/sqlite-net http://sqlwinrt.codeplex.com/ Also see Tim Heuer's blog for more information: http://timheuer.com/blog/ -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-column index is not used with IN operator
On 01/17/2013 09:17 PM, Selen Schabenberger wrote: I was trying to avoid the INDEXED BY or the "+" operator as I cannot make sure that the search with the index will always (for every database created by my application) be faster than with the PK. I prefer the optimizer does the cost calculations and makes the choice. Do you want me to write another ticket for the enhancement you mentioned? Don't worry about it for now. Changes to the optimizer are things that need to be considered really carefully. It's too easy to improve one type of query to the detriment of others. Dan. -Selen From: Dan Kennedy To: General Discussion of SQLite Database Sent: Thursday, January 17, 2013 2:38 PM Subject: Re: [sqlite] Multi-column index is not used with IN operator On 01/17/2013 06:32 PM, Selen Schabenberger wrote: Dan, I have just realised that with the sqlite_stat3 table, the query optimizer uses the INDEX IDX_TAG. When I drop this table, the PK is used as you said. But the concatenated index is still not used with the stat3. Looks like with stat3 enabled the estimate of the number of rows matched by the WHERE clause is better. So it uses INDEX_IDX_TAG. But not the composite index as the stat3 estimation is disabled if the query uses more than the first column of the index (in this case it uses two - 'Tag' and 'Flag'). That's probably something that could be enhanced at some point. Knowing that the query could be 60 times faster, is there anything I can do to speed up this query or you think it is a bug in the optimizer? You could figure out why the stat1 data is deceptive in this case and whether there is anything that can be done about it. Or add an INDEXED BY clause to the query. Adding a unary '+' operator in front of 'Id' might work too. Dan. ___ 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
Re: [sqlite] Multi-column index is not used with IN operator
I was trying to avoid the INDEXED BY or the "+" operator as I cannot make sure that the search with the index will always (for every database created by my application) be faster than with the PK. I prefer the optimizer does the cost calculations and makes the choice. Do you want me to write another ticket for the enhancement you mentioned? -Selen > > From: Dan Kennedy >To: General Discussion of SQLite Database >Sent: Thursday, January 17, 2013 2:38 PM >Subject: Re: [sqlite] Multi-column index is not used with IN operator > >On 01/17/2013 06:32 PM, Selen Schabenberger wrote: >> >> Dan, I have just realised that with the sqlite_stat3 table, the query >> optimizer uses the INDEX IDX_TAG. When I drop this table, the PK is used >> as you said. But the concatenated index is still not used with the stat3. > >Looks like with stat3 enabled the estimate of the number of rows matched >by the WHERE clause is better. So it uses INDEX_IDX_TAG. But not the >composite index as the stat3 estimation is disabled if the query uses >more than the first column of the index (in this case it uses two - >'Tag' and 'Flag'). That's probably something that could be enhanced at >some point. > >> Knowing that the query could be 60 times faster, is there anything I can >> do to speed up this query or you think it is a bug in the optimizer? > >You could figure out why the stat1 data is deceptive in this case and >whether there is anything that can be done about it. > >Or add an INDEXED BY clause to the query. > >Adding a unary '+' operator in front of 'Id' might work too. > >Dan. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-column index is not used with IN operator
On 01/17/2013 06:32 PM, Selen Schabenberger wrote:
Dan, I have just realised that with the sqlite_stat3 table, the query
optimizer uses the INDEX IDX_TAG. When I drop this table, the PK is used
as you said. But the concatenated index is still not used with the stat3.
Looks like with stat3 enabled the estimate of the number of rows matched
by the WHERE clause is better. So it uses INDEX_IDX_TAG. But not the
composite index as the stat3 estimation is disabled if the query uses
more than the first column of the index (in this case it uses two -
'Tag' and 'Flag'). That's probably something that could be enhanced at
some point.
Knowing that the query could be 60 times faster, is there anything I can
do to speed up this query or you think it is a bug in the optimizer?
You could figure out why the stat1 data is deceptive in this case and
whether there is anything that can be done about it.
Or add an INDEXED BY clause to the query.
Adding a unary '+' operator in front of 'Id' might work too.
Dan.
- Selen
*From:* Selen Schabenberger
*To:* General Discussion of SQLite Database
; Dan Kennedy
*Sent:* Wednesday, January 16, 2013 3:46 PM
*Subject:* Re: [sqlite] Multi-column index is not used with IN operator
Sorry, wrong query wrong result.
But still when I add the order by, the index is used:
Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012,
1148015, 1148016, 1148018, 1148020, 1148022, 1148040, 1148042,
1148079, 1148136, 1148138, 1148191, 1148232, 1148234, 1167643,
1167659, 1167660, 1167663, 1167667, 1167671, 1167675 ) ) and Flag=1)
order by Id limit 200
selectId order from detail
0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~33 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
0 0 0 &n bsp; USE TEMP B-TREE FOR ORDER BY
>
> From: Dan Kennedy mailto:danielk1...@gmail.com>>
>To: General Discussion of SQLite Database mailto:sqlite-users@sqlite.org>>
>Sent: Wednesday, January 16, 2013 3:40 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
>On 01/16/2013 09:22 PM, Selen Schabenberger wrote:
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag'
INTEGER
>> NOT NULL, 'Flag' INTEGER NOT NULL );
>> ANALYZE sqlite_master;
>> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132
1289');
>> INSERT INTO "sqlite_stat1"
>> VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
>> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag',
'Id');
>> CREATE INDEX IDX_TAG on Message (Tag);
>> COMMIT;
>>
>> Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
>> 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
1147912,
>> 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015,
1148016,
>> 1148018, 1148020, 1148022, 1148040, 1148042, 1148079, 1148136,
1148138,
>> 1148191, 1148232, 1148234, 1167643, 1167659, 1167660, 1167663,
1167667,
>> 1167671, 1167675 ) ) and Flag=1) limit 200
>>
>> selectId order from detail
>> 0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~432 rows)
>> 0 0 0 EXECUTE LIST SUBQUERY 1
>> 0 0 0 & nbsp; USE TEMP B-TREE FOR ORDER BY
>
>The query and the result above don't match.
>
>If I add the ORDER BY clause to the query it uses the IPK index.
>
>Dan.
>___
>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] Is anyone have problems called sqlite3 on the Windows Phone using VB?
Hi, I am using VB and trying to call sqlite3.dll on the Windows 8 Phone development environment , I want to find out whether that version of the DLL supports calls from VB ? Thanks, Jerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-column index is not used with IN operator
Dan, I have just realised that with the sqlite_stat3 table, the query optimizer
uses the INDEX IDX_TAG. When I drop this table, the PK is used as you said.
But the concatenated index is still not used with the stat3.
Knowing that the query could be 60 times faster, is there anything I can do to
speed up this query or you think it is a bug in the optimizer?
- Selen
>
> From: Selen Schabenberger
>To: General Discussion of SQLite Database ; Dan
>Kennedy
>Sent: Wednesday, January 16, 2013 3:46 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
>Sorry, wrong query wrong result.
>
>But still when I add the order by, the index is used:
>
>Select * from Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888,
>1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914,
>1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016, 1148018,
>1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138, 1148191,
>1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667, 1167671,
>1167675 ) ) and Flag=1) order by Id limit 200
>
>
>selectId order from detail
>0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG
>(Tag=?) (~33 rows)
>0 0 0 EXECUTE LIST SUBQUERY 1
>0 0 0 USE TEMP B-TREE FOR ORDER BY
>
>
>
>
>
>>
>> From: Dan Kennedy
>>To: General Discussion of SQLite Database
>>Sent: Wednesday, January 16, 2013 3:40 PM
>>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>>
>>On 01/16/2013 09:22 PM, Selen Schabenberger wrote:
>>> PRAGMA foreign_keys=OFF;
>>> BEGIN TRANSACTION;
>>> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER
>>> NOT NULL, 'Flag' INTEGER NOT NULL );
>>> ANALYZE sqlite_master;
>>> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132 1289');
>>> INSERT INTO "sqlite_stat1"
>>> VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
>>> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
>>> CREATE INDEX IDX_TAG on Message (Tag);
>>> COMMIT;
>>>
>>> Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
>>> 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912,
>>> 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016,
>>> 1148018, 1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138,
>>> 1148191, 1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667,
>>> 1167671, 1167675 ) ) and Flag=1) limit 200
>>>
>>> selectId order from detail
>>> 0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~432 rows)
>>> 0 0 0 EXECUTE LIST SUBQUERY 1
>>> 0 0 0 & nbsp; USE TEMP B-TREE FOR ORDER BY
>>
>>The query and the result above don't match.
>>
>>If I add the ORDER BY clause to the query it uses the IPK index.
>>
>>Dan.
>>___
>>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
Re: [sqlite] Intermittent "malformed database schema"
Can you get rid of the SSD portion, and see if you can emulate this on another type of drive listed below and see if you get the same results? USB Key - Slowest Platter - Slower RAM Drive - Faster RAM - Fastest (RamDrive is marked as faster as there's still going to be latency converting from a file system to something usable, whereas putting the DB in memory directly removes that abstraction layer.) Is the application multi-threaded, or, single thread? How many connections to the database is there? You mention exclusive lock, so I'd assume just one connection. In order to copy to RAM directly, you can use the SQLite backup methods to copy to a :memory: database. BACKUP THE ORIGINAL with a standard copy just to make sure you're copying the database the right direction. (*hugs his version control software*) On Tue, Jan 15, 2013 at 9:15 AM, Simon Slavin wrote: > > On 15 Jan 2013, at 1:56pm, Marcus Ilgner wrote: > > > [answers] > > Okay, you reached the limit of what I know, but I hope the information you > supplied lets someone else figure out the cause of what you see. > > Simon. > ___ > 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
