[sqlite] System.DllNotFoundException: SQLite.Interop.DLL

[Grant Dunoon]

Hi

I am really trying to get my head around whats happening as I can't seem to
find any examples.

I have a asp.net app running on x86 Ubuntu(11.04)/Apache2/Mono(2.6.7
ASP.NETVersion: 2.0.50727.1433).

The app was running OK with the Managed Only System.Data.SQLite.dll
 (1.0.66.0) which I understand uses the linux systems sqlite.so.

I upgrade to the latest Managed Only System.Data.SQLite 1.0.74.0 which I
understand I have to rename sqlite.so to SQLite.Interop.dll
/SQLite.Interop.DLL (tried both) and placed in the apps bin folder.

When I run the app and try to access the database i'm getting the
error: System.DllNotFoundException: SQLite.Interop.DLL.

I have been able to run the app on Windows with the windows interop dll, so
I even tried coping over that version  to see if it would at least see the
dll and try and rule out any compile time setup issues. But no, just got the
same message: System.DllNotFoundException: SQLite.Interop.DLL

So I'm not sure what I'm doing and could really do with guidance.

Thank you
Grant
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How best to determine changes in a db

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/22/2011 02:06 PM, Kent Tenney wrote:
> I make copies periodically, so I have 2 files
> shotwell_2011-07-21.db and shotwell_2011-07-22.db

Shotwell is open source so you could also modify it to meet your needs.  You
won't be the first Shotwell user who wants it to sync across multiple
machines (I'm one too!).

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4qMGQACgkQmOOfHg372QQcDgCgncwlGd7uTMI1CjrzOqWf/oaP
vlEAn0EBj1EuDj5je/4JdN+scWNLOUQ3
=p5/A
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How best to determine changes in a db

[Kent Tenney]

On Fri, Jul 22, 2011 at 5:06 PM, Simon Slavin  wrote:
>
> On 22 Jul 2011, at 10:47pm, Kent Tenney wrote:
>
>> I can't add data, the db belongs to another app, I just watch.
>>
>>> [snip]
>>
>> I don't care about any of the particulars you describe, only:
>
> Okay that's not so bad.
>
>> updated = []
>> for row in rows:
>> if row[newer] != row[older]:
>>   updated.append(row)
>
> Since you cannot touch the other app's tables your best approach is probably 
> to keep your own copy of the app's tables.  Either an exact copy or, as you 
> described, a hash of the result of SELECT * of each row.  Then you just have 
> three operations to notice the three kinds of changes: two SELECT ... JOINs 
> (or SELECT ... EXCEPT) to spot new rows and deleted rows, and a comparison of 
> hashes to spot UPDATEs.  Then you rewrite your hashes so you're ready for 
> next time.
>
> Good luck with it.

Thanks!
Kent

>
> 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

[sqlite] LARGEST_INT64 compile error with gcc versions 4.5.1 & 4.6

[Toll Dave]

Hello list

 

Has anyone tried compiling SQLite with gcc versions 4.5.1 or 4.6?

 

Using amalgamated SQLite version 3.6.23.1 with -DSQLITE_INT64_TYPE=long
(to work on 32-bit embedded system), I see the following errors:

 

NX/MAIN/opentv/external/opensrc/sqlite/sqlite3.c: In function
'doubleToInt64':

NX/MAIN/opentv/external/opensrc/sqlite/sqlite3.c:47670:3: warning: left
shift

count >= width of type

NX/MAIN/opentv/external/opensrc/sqlite/sqlite3.c:47670:3: error:
initializer

element is not computable at load time

NX/MAIN/opentv/external/opensrc/sqlite/sqlite3.c:47671:3: warning: left
shift

count >= width of type

NX/MAIN/opentv/external/opensrc/sqlite/sqlite3.c:47671:3: error:
initializer

element is not computable at load time

 

With this modification, it compiles:

#define LARGEST_INT64 ((i64)0x7fff)

instead of

#define LARGEST_INT64 (0x|(((i64)0x7fff)<<32))

 

With earlier versions of gcc this results in a warning rather than an
error. I have not tried the latest version of SQLite, but I don't see
any difference in the definition of LARGEST_INT64.

 

Cheers,

Dave.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How best to determine changes in a db

[Simon Slavin]

On 22 Jul 2011, at 10:47pm, Kent Tenney wrote:

> I can't add data, the db belongs to another app, I just watch.
> 
>> [snip]
> 
> I don't care about any of the particulars you describe, only:

Okay that's not so bad.

> updated = []
> for row in rows:
> if row[newer] != row[older]:
>   updated.append(row)

Since you cannot touch the other app's tables your best approach is probably to 
keep your own copy of the app's tables.  Either an exact copy or, as you 
described, a hash of the result of SELECT * of each row.  Then you just have 
three operations to notice the three kinds of changes: two SELECT ... JOINs (or 
SELECT ... EXCEPT) to spot new rows and deleted rows, and a comparison of 
hashes to spot UPDATEs.  Then you rewrite your hashes so you're ready for next 
time.

Good luck with it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How best to determine changes in a db

[Kent Tenney]

On Fri, Jul 22, 2011 at 4:22 PM, Simon Slavin  wrote:
>
> On 22 Jul 2011, at 10:06pm, Kent Tenney wrote:
>
>> I need to monitor changes in an an sqlite3 file an application uses.
>>
>> I make copies periodically, so I have 2 files
>> shotwell_2011-07-21.db and shotwell_2011-07-22.db
>>
>> For example, I'm interested in PhotoTable, which had rows added,
>> deleted and changed between those days.
>>
>> Figuring out additions seems straightforward, there is an 'id' column.
>>
>> I would like advice on ways to determine changes and deletions.
>> I'm currently accessing the files via Python's sqlite3 library, though
>> plan to start using SqlAlchemy.
>>
>> I expect I'm not the first to have interest in this, but have been
>> unable to locate any discussion of it.
>
> You're effectively asking how to sych two copies of the same database.  
> That's something I used to write about a lot.  It's impossible unless you add 
> more data.

:-[

I can't add data, the db belongs to another app, I just watch.

>
> You first do a LEFT JOIN and a RIGHT JOIN to find rows which were added and 
> rows which were deleted.  No real problem, just two SELECTs on the 'rowid' 
> column every TABLE must have.  But how do you find rows which were updated ?  
> You have to write a SELECT that compares every field, which means either some 
> custom logic for each TABLE, or a routine that finds out all the column names 
> returned when you SELECT * then turns that into a SELECT that compares each 
> column of each row.  Messy.

I'm expecting a bit of mess.
So far, I'm considering 2 approaches

- dump to sql and diff the files. Initial tests seem to show some
unexpected results
- generate a "rowhash" for each record, hash a concat of all values in a row.

I don't care about any of the particulars you describe, only:

updated = []
for row in rows:
 if row[newer] != row[older]:
   updated.append(row)



  And even then, do you care if a row was changed twice ?  If the same
field was changed twice do you need to know the 'middle' value ?  If
two fields were changed do you need to know which order the changes
were made ?  If a row was changed then deleted do you need to know the
change ?
>
> The most detailed approach is journaling.  Write your own routine that you 
> call whenever you do DELETE or UPDATE.  It does what you want but also writes 
> a timestamped entry to a 'log' TABLE.  To list all the changes, just read 
> whatever you noted in your 'log' TABLE.  This gives you as much data as you 
> care to log.  It might be simplest to log the SQL command itself.
>
> A less detailed approach is to add a 'lastChange' column to every table.  
> Whenever you INSERT or UPDATE, make sure you update this column too.  You use 
> a SELECT ... JOINs to find deletions, but you can SELECT on the lastChange 
> column to find insertions and updates.
>
> 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

Re: [sqlite] How best to determine changes in a db

[Simon Slavin]

On 22 Jul 2011, at 10:06pm, Kent Tenney wrote:

> I need to monitor changes in an an sqlite3 file an application uses.
> 
> I make copies periodically, so I have 2 files
> shotwell_2011-07-21.db and shotwell_2011-07-22.db
> 
> For example, I'm interested in PhotoTable, which had rows added,
> deleted and changed between those days.
> 
> Figuring out additions seems straightforward, there is an 'id' column.
> 
> I would like advice on ways to determine changes and deletions.
> I'm currently accessing the files via Python's sqlite3 library, though
> plan to start using SqlAlchemy.
> 
> I expect I'm not the first to have interest in this, but have been
> unable to locate any discussion of it.

You're effectively asking how to sych two copies of the same database.  That's 
something I used to write about a lot.  It's impossible unless you add more 
data.

You first do a LEFT JOIN and a RIGHT JOIN to find rows which were added and 
rows which were deleted.  No real problem, just two SELECTs on the 'rowid' 
column every TABLE must have.  But how do you find rows which were updated ?  
You have to write a SELECT that compares every field, which means either some 
custom logic for each TABLE, or a routine that finds out all the column names 
returned when you SELECT * then turns that into a SELECT that compares each 
column of each row.  Messy.  And even then, do you care if a row was changed 
twice ?  If the same field was changed twice do you need to know the 'middle' 
value ?  If two fields were changed do you need to know which order the changes 
were made ?  If a row was changed then deleted do you need to know the change ?

The most detailed approach is journaling.  Write your own routine that you call 
whenever you do DELETE or UPDATE.  It does what you want but also writes a 
timestamped entry to a 'log' TABLE.  To list all the changes, just read 
whatever you noted in your 'log' TABLE.  This gives you as much data as you 
care to log.  It might be simplest to log the SQL command itself.

A less detailed approach is to add a 'lastChange' column to every table.  
Whenever you INSERT or UPDATE, make sure you update this column too.  You use a 
SELECT ... JOINs to find deletions, but you can SELECT on the lastChange column 
to find insertions and updates.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How best to determine changes in a db

[Kent Tenney]

Howdy,

I need to monitor changes in an an sqlite3 file an application uses.

I make copies periodically, so I have 2 files
shotwell_2011-07-21.db and shotwell_2011-07-22.db

For example, I'm interested in PhotoTable, which had rows added,
deleted and changed between those days.

Figuring out additions seems straightforward, there is an 'id' column.

I would like advice on ways to determine changes and deletions.
I'm currently accessing the files via Python's sqlite3 library, though
plan to start using SqlAlchemy.

I expect I'm not the first to have interest in this, but have been
unable to locate any discussion of it.

Thanks,
Kent
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

Thank you for explaining this. 

I guess you're right about query planner deciding to avoid index usage based on 
stats. 

22 июля 2011, 18:30 от Richard Hipp :
> On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко 
> wrote:
> 
> > >
> > > Please post the results of:
> > >
> > > SELECT * FROM sqlite_stat1;
> > >
> > >
> >   tbl = log
> >  idx = idxlog_kind_computer_process_who_id_msg_created_at
> >  stat = 2815667 563134 563134 469278 74097 1 1 1
> >
> 
> The first number on "stat" is the number of rows in the table.  The 2nd
> number is the average number of rows that have the same value for the first
> column of the index.  The 3rd number is the average number of rows that have
> the same value for the first 2 columns of the index.  And so forth.
> 
> There are a huge number of rows that have the same value for the first 4
> terms of this index, which shows us that this is a really lousy index.
> Ideally, you want the second number in the "stat" column to be something
> small, like 10.
> 
> If you do not run ANALYZE, SQLite has no way of knowing that the index is
> mostly useless.  SQLite assumes that the index is a good one, and that the
> 2nd integer in "stat" is 10.  And it therefore tries to use the index.  But
> since the index is so bad, the resulting performance is slow.
> 
> After running ANALYZE, SQLite realizes that the index is lousy and avoids
> using it.  Hence, performance is much better.
> 
> 
> >
> >  tbl = log
> >  idx = idxlog_kind_computer_process_id_who_msg_created_at
> >  stat = 2815667 563134 563134 469278 1 1 1 1
> >
> >  tbl = log
> >  idx = idxlog_kind_computer_id_process_who_msg_created_at
> >  stat = 2815667 563134 563134 1 1 1 1 1
> >
> >  tbl = log
> >  idx = idxlog_kind_id_computer_process_who_msg_created_at
> >  stat = 2815667 563134 1 1 1 1 1 1
> >
> >  tbl = log
> >  idx = idxlog_kind_computer_process_who_msg_created_at
> >  stat = 2815667 563134 563134 469278 74097 2 2
> >
> >  tbl = log
> >  idx = idxlog_created_at
> >  stat = 2815667 106
> >
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cannot add interop assembly

[Brad Laney]

So many replies! Hard to reply to them all, but I understand the issue now. I 
didn't know it was native.

>> Their beliefs do not impact how the code needs to be deployed in order to
function properly.

It does because it's my director. For us to deploy DLLs they "have to be added 
as a reference".
They do not allow for deploy scripts to push DLLs to folders unless part of the 
project.
But since it is an unmanaged DLL, I'll just tell them "either drop SQLite or 
let me copy as content".
I'm sure they'll let me keep SQLite =)

>> So the company does not use any purely native DLLs in their .NET apps?

Correct, the company has absolutely 0 DLLs required to copy as content.

>> Adding the non-mixed mode assembly to the GAC is problematic and not
recommended.

Okay, I won't add it to the GAC then. I'll just argue for my point of having it 
in a Lib folder and copy as content.

Thank you for your help!
I'll now be signing off.

Brad

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Thursday, July 21, 2011 7:30 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Cannot add interop assembly

Corr

>
> Yeah that is what I did in the interim to get it to work.
>

Ok, good.

>
> The issue being my company does not believe in the DLLs being in a lib
folder inside a project.
>

Their beliefs do not impact how the code needs to be deployed in order to
function properly.

>
> Instead they have a Library folder at the same level of the solution file,
and everything is added by ref.
>

That will work for managed DLLs; however, the SQLite.Interop.dll is a purely
native DLL.  It cannot be added as a managed reference because it does not
contain any managed code or metadata.

>
> So unless I add it by ref I have to break company coding standards.
>

So the company does not use any purely native DLLs in their .NET apps?

>
> Unless it is in the bin, SQLite errors saying it cannot find the interop.
>

Yes, this is why I suggested that it be added to the project as a "content"
file and copied into the "bin" folder for deployment purposes.

>
> Guess I could add it to the GAC but that also breaks my companys coding
standards.
>

Adding the non-mixed mode assembly to the GAC is problematic and not
recommended.

>
> Isn't it odd that I cannot just add it as a ref?
>

No, it's not odd, it's a pure native DLL.  Adding a reference requires the
DLL to be a managed assembly.

--
Joe Mistachkin

___
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] System.Data.Sqlite problem

[Black, Michael (IS)]

And don't you wish Microsoft and other's could spit out intelligent error 
messages like:



"64-bit application trying to load 32-bit DLL".



I ding my students whey do stupid stuff like:



FILE *fp=fopen(filename,"r");

if (fp == NULL) {

  fprintf(stderr,"Cannot open file\n");

}



Instead of

if (fp == NULL) {

  perror(filename);

}



Which actually tells you what the problem is, permissions, not existing, etc



It would be so simple for the DLL manager to be able to do this.  But it's the 
same as Microsoft's old error message of "Cannot load dll".  To which I always 
screamed "what DLL and why not"



You're just seeing the new version of it.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Joe Mistachkin [sql...@mistachkin.com]
Sent: Friday, July 22, 2011 10:31 AM
To: 'General Discussion of SQLite Database'
Subject: EXT :Re: [sqlite] System.Data.Sqlite problem


Jack Hughes wrote:
>
> The 64 bit version works like a charm but the 32 bit version unfortunately
does not. The error
> message given by test.exe is given at the end of the message. The
BadImageFormatException might
> suggest a bad download... so I've re-downloaded a number of times and the
result with the 32 bit
> version is always the same.
>

This is the expected behavior, let me explain why.

First off, I'm going to assume you downloaded the non-mixed-mode packages.
They both include a native "SQLite.Interop.dll" (for the appropriate
processor architecture) that contains the SQLite core.  They also include a
pure managed "System.Data.SQLite.dll" (marked as "pure IL" in the CLR
header).

When any .NET application marked as "pure IL" runs on 64-bit Windows, it
will run as a 64-bit process.  Similarly, if you run that exact same
application on 32-bit Windows, it will run as a 32-bit process.  A 64-bit
process cannot load a 32-bit DLL.

Therefore, the "test.exe" program starts as a 64-bit process and then
attempts to load the 32-bit "SQLite.Interop.dll", which raises the
BadImageFormatException.

--
Joe Mistachkin

___
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] System.Data.Sqlite problem

[Jack Hughes]

ok Joe that's a relief. Thanks for your help, much appreciated.
-Jack

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: 22 July 2011 16:32
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.Sqlite problem


Jack Hughes wrote:
>
> The 64 bit version works like a charm but the 32 bit version unfortunately
does not. The error
> message given by test.exe is given at the end of the message. The
BadImageFormatException might
> suggest a bad download... so I've re-downloaded a number of times and the
result with the 32 bit
> version is always the same. 
>

This is the expected behavior, let me explain why.

First off, I'm going to assume you downloaded the non-mixed-mode packages.
They both include a native "SQLite.Interop.dll" (for the appropriate
processor architecture) that contains the SQLite core.  They also include a
pure managed "System.Data.SQLite.dll" (marked as "pure IL" in the CLR
header).

When any .NET application marked as "pure IL" runs on 64-bit Windows, it
will run as a 64-bit process.  Similarly, if you run that exact same
application on 32-bit Windows, it will run as a 32-bit process.  A 64-bit
process cannot load a 32-bit DLL.

Therefore, the "test.exe" program starts as a 64-bit process and then
attempts to load the 32-bit "SQLite.Interop.dll", which raises the
BadImageFormatException.

--
Joe Mistachkin

___
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] System.Data.Sqlite problem

[Joe Mistachkin]

Jack Hughes wrote:
>
> The 64 bit version works like a charm but the 32 bit version unfortunately
does not. The error
> message given by test.exe is given at the end of the message. The
BadImageFormatException might
> suggest a bad download... so I've re-downloaded a number of times and the
result with the 32 bit
> version is always the same. 
>

This is the expected behavior, let me explain why.

First off, I'm going to assume you downloaded the non-mixed-mode packages.
They both include a native "SQLite.Interop.dll" (for the appropriate
processor architecture) that contains the SQLite core.  They also include a
pure managed "System.Data.SQLite.dll" (marked as "pure IL" in the CLR
header).

When any .NET application marked as "pure IL" runs on 64-bit Windows, it
will run as a 64-bit process.  Similarly, if you run that exact same
application on 32-bit Windows, it will run as a 32-bit process.  A 64-bit
process cannot load a 32-bit DLL.

Therefore, the "test.exe" program starts as a 64-bit process and then
attempts to load the 32-bit "SQLite.Interop.dll", which raises the
BadImageFormatException.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select names where occurences are greater than 10

[chiefmccrossan]

That's brilliant thank you very much :clap:

Adam


Black, Michael (IS) wrote:
> 
> select id,count(distinct track) from tracks group by id having
> count(distinct track)>=10;
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of chiefmccrossan [adammccros...@gmail.com]
> Sent: Thursday, July 21, 2011 2:15 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite] Select names where occurences are greater than
> 10
> 
> 
> Perfect! Thanks a million! =)
> 
> Just wondering, is there anyway to select only unique results in what is
> returned and no duplicates?
> 
> For example, Elvis Presley has a track called Teddy Bear in my table 6
> times
> but I only want to include it once?
> 
> 
> luuk34 wrote:
>>
>> On 21-07-2011 18:59, chiefmccrossan wrote:
>>>
>>> I have a Tracks table containing columns for artistname and trackname. I
>>> want
>>> to be able to select all artist names that have more than ten tracks.
>>> For
>>
>> SELECT artisname, count(tracks)
>> FROM Tracks
>> GROUP BY artistname
>> HAVING count(tracks)>10
>>
>>
>>
>>> example Elvis Presley has more than 20 tracks in the Tracks table but
>>> Johnny
>>> Cash has only 5 tracks. I need to be able to count Elvis and discount
>>> Johnny
>>> cash?
>>>
>>> Any idea how i can do this?
>>>
>>> Thanks
>>> Adam
>>
>>
>> --
>> Luuk
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> 
> --
> View this message in context:
> http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32110258.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32116159.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Tito Ciuro]

Hello all,

Perhaps my post dated Aug. 19, 2009 will help a little bit:

http://www.cocoabuilder.com/archive/cocoa/242954-core-data-dog-slow-when-using-first-time-after-boot.html

-- Tito

On Jul 22, 2011, at 10:40 AM, Simon Slavin wrote:

> 
> On 22 Jul 2011, at 2:11pm, Григорий Григоренко wrote:
> 
>> Let's say it'll shrink by 50%. Still, if first-time runnning query timing 
>> will change from 2 min to 1 min it is still not acceptable. 
>> I cannot wait even a minute without logging.
>> 
>> So, before restructuring database and re-writing code I just want to 
>> understand SQLITE behaviour.
>> The question is: does SQLITE need to load whole index to start using it? Or 
>> SQLITE can use partial loads to quickly find records it needs (defined by 
>> columns in conditions)?
> 
> SQLite loads only the pages it needs.  It doesn't load a whole index into 
> memory unless the entire index fits in one page (only a few rows).  If your 
> search still runs too slowly your problem is more likely to be that you are 
> searching on text fields rather than integer fields.
> 
> Searching on integers is /must/ faster than searching on test fields.  If you 
> store the id code of the computer in your log file rather than the text name 
> of the computer, a search for log entries for a particular computer will be 
> faster.  I don't know what you're doing with your 'kind' column, but again if 
> you assign integer values to your different values for 'kind' and store those 
> instead, your search will be faster.
> 
> 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

Re: [sqlite] Strange result using JOIN

[Kit]

2011/7/22 Sintoni Stefano (GMAIL) :
> More thanks,
> I understand that but why the same query, with the same data, return two
> different result using different SQLite-based Win32 applications ?
>
> In any case how I can take the result I need ? I not have other column
> right for made one ORDER BY and I need to respect the sequence or CODE
> table like
> Any idea ?
> Stefano

INSERT INTO ELAB SELECT CODE.* FROM JOB JOIN CODE ON
CODE.CODE=JOB.CODE ORDER BY JOB.rowid,CODE.rowid;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Richard Hipp]

On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко wrote:

> >
> > Please post the results of:
> >
> > SELECT * FROM sqlite_stat1;
> >
> >
>   tbl = log
>  idx = idxlog_kind_computer_process_who_id_msg_created_at
>  stat = 2815667 563134 563134 469278 74097 1 1 1
>

The first number on "stat" is the number of rows in the table.  The 2nd
number is the average number of rows that have the same value for the first
column of the index.  The 3rd number is the average number of rows that have
the same value for the first 2 columns of the index.  And so forth.

There are a huge number of rows that have the same value for the first 4
terms of this index, which shows us that this is a really lousy index.
Ideally, you want the second number in the "stat" column to be something
small, like 10.

If you do not run ANALYZE, SQLite has no way of knowing that the index is
mostly useless.  SQLite assumes that the index is a good one, and that the
2nd integer in "stat" is 10.  And it therefore tries to use the index.  But
since the index is so bad, the resulting performance is slow.

After running ANALYZE, SQLite realizes that the index is lousy and avoids
using it.  Hence, performance is much better.


>
>  tbl = log
>  idx = idxlog_kind_computer_process_id_who_msg_created_at
>  stat = 2815667 563134 563134 469278 1 1 1 1
>
>  tbl = log
>  idx = idxlog_kind_computer_id_process_who_msg_created_at
>  stat = 2815667 563134 563134 1 1 1 1 1
>
>  tbl = log
>  idx = idxlog_kind_id_computer_process_who_msg_created_at
>  stat = 2815667 563134 1 1 1 1 1 1
>
>  tbl = log
>  idx = idxlog_kind_computer_process_who_msg_created_at
>  stat = 2815667 563134 563134 469278 74097 2 2
>
>  tbl = log
>  idx = idxlog_created_at
>  stat = 2815667 106
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Jay A. Kreibich]

On Fri, Jul 22, 2011 at 03:37:34PM +0200, Sintoni Stefano (GMAIL) scratched on 
the wall:
> More thanks,
> I understand that but why the same query, with the same data, return two
> different result using different SQLite-based Win32 applications ?

  My best guess is that they're using different versions or slightly
  different builds of the SQLite core library.  Changes and improvements
  in the query optimizer can result in different orderings.

  Regardless, you should never trust the order of a SELECT that lacks
  an ORDER BY, even for the same version of the same code on the same
  database.  Adding or removing an index, running ANALYZE, or many other
  things can alter the order of results.  SQLite even has a PRAGMA to
  test for unintentional order dependencies (reverse_unordered_selects).

> In any case how I can take the result I need ?

  In theory, you could just apply that PRAGMA.  That's a rather 
  dangerous thing to do, however, and is very likely to break if the
  environment changes in any way.  
  
  At the end of the day, the application code is making incorrect 
  assumptions about the data layout, expecting an undefinable order
  type.  It is the application code that needs to get fixed to accept
  data in an order that the SQL standard can provide.  This isn't true
  of only SQLite, but all SQL database systems.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Black, Michael (IS)]

Try "order by code.rowid"

I think that will do what you want and fits in with what sqlite3's behavior 
should be.

As long as you don't delete rows you'll be OK.
http://www.sqlite.org/autoinc.html



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Sintoni Stefano (GMAIL) [stefano.sintoni.cnigr...@gmail.com]
Sent: Friday, July 22, 2011 8:54 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Strange result using JOIN


I don't have other column good for use the SELECT.

Now I don't have good columns to use the SELECT, now I'm afraid my only
solution is to create a new column with a value of the order generated
by my application.

Stefano


On 07/22/2011 03:45 PM, Simon Slavin wrote:
> On 22 Jul 2011, at 2:37pm, Sintoni Stefano (GMAIL) wrote:
>
>> In any case how I can take the result I need ? I not have other column
>> right for made one ORDER BY and I need to respect the sequence or CODE
>> table like
>>
>> The table CODE.
>> CODE;VALUE;
>> 61311;18462F;
>> 61311;18461F;
> SQL has no concept of the order of rows in a TABLE.  A TABLE is a collection 
> of rows in no particular order.  You could issue three SELECT commands one 
> after the other and they could return the rows in three different orders and 
> that is perfectly acceptable given the SQL standard.
>
> When you want the results of a SELECT to appear in a particular order you 
> specify that using an 'ORDER BY' clause for your SELECT statement.
>
> What controls the order you want to see on your output ?  Is it always 
> descending order of the VALUE column ?  Is it the order you put the data in 
> in ?  Something else ?
>
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Sintoni Stefano (GMAIL)]

I don't have other column good for use the SELECT.

Now I don't have good columns to use the SELECT, now I'm afraid my only
solution is to create a new column with a value of the order generated
by my application.

Stefano


On 07/22/2011 03:45 PM, Simon Slavin wrote:
> On 22 Jul 2011, at 2:37pm, Sintoni Stefano (GMAIL) wrote:
>
>> In any case how I can take the result I need ? I not have other column
>> right for made one ORDER BY and I need to respect the sequence or CODE
>> table like
>>
>> The table CODE.
>> CODE;VALUE;
>> 61311;18462F;
>> 61311;18461F;
> SQL has no concept of the order of rows in a TABLE.  A TABLE is a collection 
> of rows in no particular order.  You could issue three SELECT commands one 
> after the other and they could return the rows in three different orders and 
> that is perfectly acceptable given the SQL standard.
>
> When you want the results of a SELECT to appear in a particular order you 
> specify that using an 'ORDER BY' clause for your SELECT statement.
>
> What controls the order you want to see on your output ?  Is it always 
> descending order of the VALUE column ?  Is it the order you put the data in 
> in ?  Something else ?
>
> 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

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> > >
> > > Something that might speed up your operation is to issue the SQL command
> > 'ANALYZE' just once.  The results are stored in the database file, so you
> > can just do it manually now you have some data in the database.  It gives
> > the query optimizers lots of clues about how best to optimize each query. So
> > do an ANALYZE, then close the database and do your timing tests again.
> > >
> >
> >
> > Well, ANALYZE has changed something. Now query is using PRIMARY KEY and
> > executing almost instantly :-)
> >
> 
> Please post the results of:
> 
> SELECT * FROM sqlite_stat1;
> 
> 

Sure,

  tbl = T
  idx = 
 stat = 1

  tbl = item
  idx = idxitem_name_value
 stat = 21 7 1

  tbl = log
  idx = idxlog_kind_computer_process_who_id_msg_created_at
 stat = 2815667 563134 563134 469278 74097 1 1 1

  tbl = log
  idx = idxlog_kind_computer_process_id_who_msg_created_at
 stat = 2815667 563134 563134 469278 1 1 1 1

  tbl = log
  idx = idxlog_kind_computer_id_process_who_msg_created_at
 stat = 2815667 563134 563134 1 1 1 1 1

  tbl = log
  idx = idxlog_kind_id_computer_process_who_msg_created_at
 stat = 2815667 563134 1 1 1 1 1 1

  tbl = log
  idx = idxlog_kind_computer_process_who_msg_created_at
 stat = 2815667 563134 563134 469278 74097 2 2

  tbl = log
  idx = idxlog_created_at
 stat = 2815667 106
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Jean-Christophe Deschamps]

>I understand that but why the same query, with the same data, return two
>different result using different SQLite-based Win32 applications ?

SQL is essentially based on set theory.  Sets don't have intrinsic 
ordering and may be enumerated in whatever order.  What you think are 
distinct results are not distinct from the SQL point of view.

>In any case how I can take the result I need ? I not have other column
>right for made one ORDER BY and I need to respect the sequence or CODE
>table like
>
>The table CODE.
>CODE;VALUE;
>61311;18462F;
>61311;18461F;
>
>The table JOB
>CODE;NUM;
>61311;1;
>61311;1;
>
>Table ELAB with desired result.
>CODE;VALUE;
>61311;18462F;
>61311;18461F;
>61311;18462F;
>61311;18461F;

Add a sequence number (or simply an explicit INTEGER primary key) to 
your tables and ORDER BY the result on them.



--
j...@antichoc.net  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Simon Slavin]

On 22 Jul 2011, at 2:37pm, Sintoni Stefano (GMAIL) wrote:

> In any case how I can take the result I need ? I not have other column
> right for made one ORDER BY and I need to respect the sequence or CODE
> table like
> 
> The table CODE.
> CODE;VALUE;
> 61311;18462F;
> 61311;18461F;

SQL has no concept of the order of rows in a TABLE.  A TABLE is a collection of 
rows in no particular order.  You could issue three SELECT commands one after 
the other and they could return the rows in three different orders and that is 
perfectly acceptable given the SQL standard.

When you want the results of a SELECT to appear in a particular order you 
specify that using an 'ORDER BY' clause for your SELECT statement.

What controls the order you want to see on your output ?  Is it always 
descending order of the VALUE column ?  Is it the order you put the data in in 
?  Something else ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Fri, Jul 22, 2011 at 5:25 PM, Григорий Григоренко  wrote:
> Database is "insert-only". There wasn't any deletes or updates, will VACUUM 
> actually help in this case? I though it was about unused space?
>

There's also internal fragmentation coming from the nature of indexes.
If your inserted data have to be placed in the middle of the index and
this allocates a new page at the end of the file then the logical
order of the pages used for this index will be non-sequential in terms
of hard disc access.

>
> And after finding this position inside index SQLITE clearly sees that 
> previous and next record does not match query, so query should return nothing.
>
> This cannot take long. It's like scanning B-TREE to a predefined position and 
> then reading just 2 records near.
>
> And that's all. Why spending 2 minutes?


You query contained the id range not existed in the db so maybe here
can be some optimization improvement. That's why probably Richard
asked you to send the analyzed data. But as I suppose you're not going
to supply non existing id ranges :) so if we change the id range to a
valid one, the data will contain many records and sqlite should read
the index that is internally fragmented due to the reasons explain
above.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Simon Slavin]

On 22 Jul 2011, at 2:11pm, Григорий Григоренко wrote:

> Let's say it'll shrink by 50%. Still, if first-time runnning query timing 
> will change from 2 min to 1 min it is still not acceptable. 
> I cannot wait even a minute without logging.
> 
> So, before restructuring database and re-writing code I just want to 
> understand SQLITE behaviour.
> The question is: does SQLITE need to load whole index to start using it? Or 
> SQLITE can use partial loads to quickly find records it needs (defined by 
> columns in conditions)?

SQLite loads only the pages it needs.  It doesn't load a whole index into 
memory unless the entire index fits in one page (only a few rows).  If your 
search still runs too slowly your problem is more likely to be that you are 
searching on text fields rather than integer fields.

Searching on integers is /must/ faster than searching on test fields.  If you 
store the id code of the computer in your log file rather than the text name of 
the computer, a search for log entries for a particular computer will be 
faster.  I don't know what you're doing with your 'kind' column, but again if 
you assign integer values to your different values for 'kind' and store those 
instead, your search will be faster.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Sintoni Stefano (GMAIL)]

More thanks,
I understand that but why the same query, with the same data, return two
different result using different SQLite-based Win32 applications ?

In any case how I can take the result I need ? I not have other column
right for made one ORDER BY and I need to respect the sequence or CODE
table like

The table CODE.
CODE;VALUE;
61311;18462F;
61311;18461F;

The table JOB
CODE;NUM;
61311;1;
61311;1;

Table ELAB with desired result.
CODE;VALUE;
61311;18462F;
61311;18461F;
61311;18462F;
61311;18461F;


Any idea ?


Stefano


On 07/22/2011 02:08 PM, Jay A. Kreibich wrote:
> On Fri, Jul 22, 2011 at 09:23:36AM +0200, Sintoni Stefano (GMAIL) scratched 
> on the wall:
>> Hi,
>> I get a strange result using JOIN on one very simple application using
>> SQLite.
>> The table ELAB are populated with the follow SQL query:
>> INSERT INTO ELAB SELECT CODE.* FROM JOB JOIN CODE ON CODE.CODE=JOB.CODE
>> and I take the follow result.
>> CODE;VALUE;
>> 61311;18461F;
>> 61311;18462F;
>> 61311;18461F;
>> 61311;18462F;
>>
>> That is the problem.
>> But I should have this result
>> CODE;VALUE;
>> 61311;18462F;
>> 61311;18461F;
>> 61311;18462F;
>> 61311;18461F;
>   In SQL terms, these are the exact same.  Table rows are *unordered*
>   and can be returned in any order the database wants.
>
>   If you need a result in a specific order, you must use an ORDER BY
>   clause in your SELECT statement.
>
>-j
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.Sqlite problem

[Jack Hughes]

Hello,

I've just downloaded the 64 bit + 32 bit binary zip versions of 
System.Data.Sqlite version 1.0.74.0. I unzip and then run the test.exe program. 
The 64 bit version works like a charm but the 32 bit version unfortunately does 
not. The error message given by test.exe is given at the end of the message. 
The BadImageFormatException might suggest a bad download... so I've 
re-downloaded a number of times and the result with the 32 bit version is 
always the same.

I am running Windows 7 64 bit with VS2010 SP1 installed so all of 
System.Data.Sqlite's dependencies are installed. Any ideas what's going on?

Regards,
Jack Hughes

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

** Exception Text **
System.Reflection.TargetInvocationException: Exception has been thrown by the 
target of an invocation. ---> System.TypeInitializationException: The type 
initializer for 'System.Data.SQLite.SQLiteFactory' threw an exception. ---> 
System.BadImageFormatException: An attempt was made to load a program with an 
incorrect format. (Exception from HRESULT: 0x8007000B)
   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config(Int32 op, 
SQLiteLogCallback func, IntPtr pvUser)
   at System.Data.SQLite.SQLite3.SetLogCallback(SQLiteLogCallback func) in 
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:line 935
   at System.Data.SQLite.SQLiteFactory..cctor() in 
c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteFactory.cs:line 131
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   at System.RuntimeFieldHandle.GetValue(RtFieldInfo field, Object instance, 
RuntimeType fieldType, RuntimeType declaringType, Boolean& domainInitialized)
   at System.Reflection.RtFieldInfo.InternalGetValue(Object obj, Boolean 
doVisibilityCheck, Boolean doCheckConsistency)
   at System.Reflection.RtFieldInfo.GetValue(Object obj)
   at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
   at test.TestCasesDialog.runButton_Click(Object sender, EventArgs e) in 
c:\dev\sqlite\dotnet\test\TestCasesDialog.cs:line 56
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, 
Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr 
wparam, IntPtr lparam)


** Loaded Assemblies **
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.235 (RTMGDR.030319-2300)
CodeBase: 
file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll

test
Assembly Version: 1.0.0.30023
Win32 Version: 1.0.0.30023
CodeBase: 
file:///C:/Workspace/SomeProject/2.0/Libs/sqlite-netFx40-binary-Win32-2010-1.0.74.0/test.exe

System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.235 built by: RTMGDR
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll

System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.1 built by: RTMRel
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll

System
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.232 built by: RTMGDR
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll

System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_64/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll

System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.225 built by: RTMGDR
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll

System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.225 built by: RTMGDR
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll

System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
CodeBase: 
file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll

System.Numerics
Assembly Version: 4.0.0.0
Win32 Version: 

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

Database is "insert-only". There wasn't any deletes or updates, will VACUUM 
actually help in this case? I though it was about unused space?



Still. There's an index:

 idxlog_kind_computer_id_process_who_msg_created_at (

kind, computer, id, ...

)

Query is

kind = XXX AND computer = YYY and id BETWEEN ZZZ1 and ZZZ2

To my opinion this information is sufficient to jump directly to index position 
XXX,YYY,ZZZ1.

SQLITE must be able to do this real fast, isn't it?

And after finding this position inside index SQLITE clearly sees that previous 
and next record does not match query, so query should return nothing. 

This cannot take long. It's like scanning B-TREE to a predefined position and 
then reading just 2 records near.

And that's all. Why spending 2 minutes? 


Does SQLITE needs all of index to be in RAM to begin using  it? 
If so it can be the reason of bad performance...


22 июля 2011, 17:07 от Max Vlasov :
> On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко  
> wrote:
> > Okay, I rebooted and tested again.
> >
> > First run took 76 seconds. Read bytes: ~ 307 Mb, Write bytes: ~ 66 Kb.
> >
> > Re-running: 1 second, Read bytes: ~ 307 Mb, Write bytes ~ 66 Kb.
> >
> >
> 
> Grigory, you posted to me directly (without the list e-mail), I'm
> reposting this reply to the list
> 
> Ok, I downloaded the db and it actually takes long time to execute.
> But this can be explained. Actually if you change your fist id > to
> zero, the query starts to return actual results, so there are many
> records with kind='info' and computer = 'KRAFTWAY' in your database.
> So sqlite really uses the index for locating your records. But the
> problem is also that your db is very fragmented so sqlite when reading
> the index actually reads different parts of this file and it makes the
> windows cache system mad. I think vacuum command should help.
> 
> Max
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Richard Hipp]

On Fri, Jul 22, 2011 at 8:55 AM, Григорий Григоренко wrote:

>
>
>
> >
> > Something that might speed up your operation is to issue the SQL command
> 'ANALYZE' just once.  The results are stored in the database file, so you
> can just do it manually now you have some data in the database.  It gives
> the query optimizers lots of clues about how best to optimize each query. So
> do an ANALYZE, then close the database and do your timing tests again.
> >
>
>
> Well, ANALYZE has changed something. Now query is using PRIMARY KEY and
> executing almost instantly :-)
>

Please post the results of:

SELECT * FROM sqlite_stat1;


>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

You are right. There's a way of normalizing and it will certainly reduce 
database size. 
Let's say it'll shrink by 50%. Still, if first-time runnning query timing will 
change from 2 min to 1 min it is still not acceptable. 
I cannot wait even a minute without logging.

So, before restructuring database and re-writing code I just want to understand 
SQLITE behaviour.
The question is: does SQLITE need to load whole index to start using it? Or 
SQLITE can use partial loads to quickly find records it needs (defined by 
columns in conditions)?





> You need to normalize your database.
> 
> 
> 
> 
> 
> 
> 
> Though it's easy to put everything in one table it's horrendously inefficient 
> for indexing.
> 
> 
> 
> Your indexes are huge as you're putting strings in them.
> 
> 
> 
> 
> 
> 
> 
> Ideally you should never have a string in an index if you can avoid it.  Too 
> much space, too long a time to index, to long a time to compare.
> 
> 
> 
> 
> 
> 
> 
> So for starters create a "kind" table and "computer" table and just put the 
> _rowid into your table instead of the string.
> 
> 
> 
> 
> 
> 
> 
> I'll bet you things will run a lot faster and your database will shrink.
> 
> 
> 
> 
> 
> 
> 
> Same goes for other strings.
> 
> 
> 
> 
> 
> 
> 
> Michael D. Black
> 
> 
> 
> Senior Scientist
> 
> 
> 
> NG Information Systems
> 
> 
> 
> Advanced Analytics Directorate
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко  wrote:
> Okay, I rebooted and tested again.
>
> First run took 76 seconds. Read bytes: ~ 307 Mb, Write bytes: ~ 66 Kb.
>
> Re-running: 1 second, Read bytes: ~ 307 Mb, Write bytes ~ 66 Kb.
>
>

Grigory, you posted to me directly (without the list e-mail), I'm
reposting this reply to the list

Ok, I downloaded the db and it actually takes long time to execute.
But this can be explained. Actually if you change your fist id > to
zero, the query starts to return actual results, so there are many
records with kind='info' and computer = 'KRAFTWAY' in your database.
So sqlite really uses the index for locating your records. But the
problem is also that your db is very fragmented so sqlite when reading
the index actually reads different parts of this file and it makes the
windows cache system mad. I think vacuum command should help.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> Something that might speed up your operation is to issue the SQL command 
> 'ANALYZE' just once.  The results are stored in the database file, so you can 
> just do it manually now you have some data in the database.  It gives the 
> query optimizers lots of clues about how best to optimize each query. So do 
> an ANALYZE, then close the database and do your timing tests again.
> 


Well, ANALYZE has changed something. Now query is using PRIMARY KEY and 
executing almost instantly :-)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.dll for Win 64

[Richard Hipp]

On Fri, Jul 22, 2011 at 7:30 AM, Everton Vieira  wrote:

> anyone knows how to make this
> dll with the source?
>

nmake -f Makefile.msc sqlite3.dll


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Black, Michael (IS)]

You need to normalize your database.



Though it's easy to put everything in one table it's horrendously inefficient 
for indexing.

Your indexes are huge as you're putting strings in them.



Ideally you should never have a string in an index if you can avoid it.  Too 
much space, too long a time to index, to long a time to compare.



So for starters create a "kind" table and "computer" table and just put the 
_rowid into your table instead of the string.



I'll bet you things will run a lot faster and your database will shrink.



Same goes for other strings.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, July 22, 2011 5:38 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SELECT query first run is VERY slow

I have a log's database. Schema :


CREATE TABLE log(id integer primary key autoincrement,msg text,created_at 
int,kind text,computer text,process text,who text);
CREATE INDEX idxlog_created_at ON log(created_at);
CREATE INDEX idxlog_kind_computer_id_process_who_msg_created_at ON 
log(kind,computer,id desc,process,who,msg,created_at);
CREATE INDEX idxlog_kind_computer_process_id_who_msg_created_at ON 
log(kind,computer,process,id desc,who,msg,created_at);
CREATE INDEX idxlog_kind_computer_process_who_id_msg_created_at ON 
log(kind,computer,process,who,id desc,msg,created_at);
CREATE INDEX idxlog_kind_id_computer_process_who_msg_created_at ON log(kind,id 
desc,computer,process,who,msg,created_at);


Database: ~ 3.5 Gb size, ~ 2.8 mln records. page_size=8192 bytes, UTF-8.

Platform: Windows XP, Intel Core Duo 3 Ghz.

SQLITE: sqlite3.exe console, latest version (3.7.7.1).


Query:

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' AND 
computer='KRAFTWAY';


Query plan (EXPLAIN QUERY PLAN):

0|0|0|SEARCH TABLE log USING COVERING INDEX 
idxlog_kind_computer_process_who_id_msg_created_at (kind=? AND computer=?) (~2 
rows)

There are no records in database that match query, i.e. query returns nothing.


Now, the problem.


Reboot system. Run query. Elapsed: ~100 SECONDS. Re-run query. Now it executes 
in less than a second. Re-run query. The same: less than a second. Tried it 
several times.


RUNNING THIS QUERY JUST AFTER REBOOT TAKES UNPREDICTABLE LARGE TIME, FROM 1 TO 
2 MINUTES.


Is this a sqlite bug or normal behaviour?

If it's normal, there are some questions.

Am I hitting some limit with my database? Which one?

Why is SQLITE (having nicely matching index) is spending so much time to 
execute?

SQLITE has to cache some index data? Yeah but the console process (during 
execution of this query) is reading at about 800 Kb per second rate! (while HDD 
can make 100 Mb per second) Why so serious? Execuse me, why so slow?

And SQLITE process it's not consuming CPU (less than 1 percent). What is SQLITE 
doing during this long running query?

It's own private memory is holding still at about 20 Mb. It's not consuming CPU 
and HDD. What is he waiting for?


This problem makes SQLITE completely unusable in my project.


I've shared database and scripts (Warning: it's RAR archive, ZIP cannot hold 
files of 3.5 Gb size. You can download WINRAR here: 
http://www.rarlab.com/download.htm ):

http://dl.dropbox.com/u/2168777/deadly_sqlite.rar

Download size: ~150 Mb.

Unzip and execute "run.cmd".

You'll see execution plan and after a while there will be line "Seconds 
elapsed: XXX".









___
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] SELECT query first run is VERY slow

[Григорий Григоренко]

> 
> > Database extension is ".dblite"
> 
> That should be okay.  No need to change that.
> 
> Something that might speed up your operation is to issue the SQL command 
> 'ANALYZE' just once.  The results are stored in the database file, so you can 
> just do it manually now you have some data in the database.  It gives the 
> query optimizers lots of clues about how best to optimize each query. So do 
> an ANALYZE, then close the database and do your timing tests again.

Okay, will do that.

> 
> If you're still getting strange results after that, you can use the 
> command-line tool to make SQLite reveal whether it's using an index or not.  
> You do that by issuing your SELECT command with 'EXPLAIN QUERY PLAN ' at the 
> beginning of it:
> 
> EXPLAIN QUERY PLAN SELECT 1 as today, id as rowid, 
> kind,who,msg,computer,process,created_at,id FROM log WHERE id > 4070636 AND 
> id <= 9223372036854775807 AND kind = 'info' AND computer='KRAFTWAY';
> 
> Looking at the output it produces (which you may post, by all means) any 
> mention of 'scan' means it's having to read every record in the database, 
> whereas a mention of an index means it's using an index as expected.
> 

You missed it in my post - I did it. I did "EXPLAIN ..." and SQLITE says it's 
using covering index. There's nohting wrong in the query plan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange result using JOIN

[Jay A. Kreibich]

On Fri, Jul 22, 2011 at 09:23:36AM +0200, Sintoni Stefano (GMAIL) scratched on 
the wall:
> Hi,
> I get a strange result using JOIN on one very simple application using
> SQLite.

> The table ELAB are populated with the follow SQL query:
> INSERT INTO ELAB SELECT CODE.* FROM JOB JOIN CODE ON CODE.CODE=JOB.CODE
> and I take the follow result.
> CODE;VALUE;
> 61311;18461F;
> 61311;18462F;
> 61311;18461F;
> 61311;18462F;
> 
> That is the problem.
> But I should have this result
> CODE;VALUE;
> 61311;18462F;
> 61311;18461F;
> 61311;18462F;
> 61311;18461F;

  In SQL terms, these are the exact same.  Table rows are *unordered*
  and can be returned in any order the database wants.

  If you need a result in a specific order, you must use an ORDER BY
  clause in your SELECT statement.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Simon Slavin]

On 22 Jul 2011, at 12:36pm, Григорий Григоренко wrote:

> Database extension is ".dblite"

That should be okay.  No need to change that.

Something that might speed up your operation is to issue the SQL command 
'ANALYZE' just once.  The results are stored in the database file, so you can 
just do it manually now you have some data in the database.  It gives the query 
optimizers lots of clues about how best to optimize each query. So do an 
ANALYZE, then close the database and do your timing tests again.

If you're still getting strange results after that, you can use the 
command-line tool to make SQLite reveal whether it's using an index or not.  
You do that by issuing your SELECT command with 'EXPLAIN QUERY PLAN ' at the 
beginning of it:

EXPLAIN QUERY PLAN SELECT 1 as today, id as rowid, 
kind,who,msg,computer,process,created_at,id FROM log WHERE id > 4070636 AND id 
<= 9223372036854775807 AND kind = 'info' AND computer='KRAFTWAY';

Looking at the output it produces (which you may post, by all means) any 
mention of 'scan' means it's having to read every record in the database, 
whereas a mention of an index means it's using an index as expected.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Fri, Jul 22, 2011 at 3:36 PM, Григорий Григоренко  wrote:
> Database extension is ".dblite"
>
> I'm using Process Explorer 
> (http://technet.microsoft.com/en-us/sysinternals/bb896653) to monitor 
> processes cpu and i/o usage.
>
> During these long running queries I am not using any other program. I've 
> terminated any unused service  (MS SQL, for example). System idle CPU is at 
> 98-99% during all the time.
>
> I've just tried another system (of my colleague) that has Windows 7. Same 
> result: first run took 40 seconds, second: 1 second.
>


What about Read Bytes? How much did sqlite.exe actually read during the run?

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Fri, Jul 22, 2011 at 3:28 PM, Simon Slavin  wrote:
>
> Does Windows XP have some sort of task display where you can see what task is 
> hogging most of the CPU or disk access ?
>

The mentioned Task Manager (Ctrl-Alt-Del -> Task Manager) reports both
CPU and I/O Read Bytes (if one selects the column for the latter) for
every process so if Grigory shares this info with us, this will be
very useful.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

Database extension is ".dblite"

I'm using Process Explorer 
(http://technet.microsoft.com/en-us/sysinternals/bb896653) to monitor processes 
cpu and i/o usage.

During these long running queries I am not using any other program. I've 
terminated any unused service  (MS SQL, for example). System idle CPU is at 
98-99% during all the time. 

I've just tried another system (of my colleague) that has Windows 7. Same 
result: first run took 40 seconds, second: 1 second.




22 июля 2011, 15:28 от Simon Slavin :
> 
> On 22 Jul 2011, at 12:15pm, Max Vlasov wrote:
> 
> > Do you have and anitvirus software installed? It may scan the file at
> > the first usage.
> 
> What is the extension to the filename of the database file ?  Windows does 
> fancy caching for files that have some particular extensions including, IIRC, 
> '.db'.  Unfortunately although this works well for database files used for 
> Windows it's terrible for ones which aren't.
> 
> If you've used a common extension for the database file rename it '.sqlite' 
> or something that isn't going to be confused for another database format.
> 
> And to answer your question, no, it's not normal or a known bug.
> 
> Does Windows XP have some sort of task display where you can see what task is 
> hogging most of the CPU or disk access ?
> 
> 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

[sqlite] SQLite3.dll for Win 64

[Everton Vieira]

Please Help. I need an SQLite3.dll for Win 64?
 I've try to download the source and make this dll but I have not success.
 Anyone knows where to download this dll, or anyone knows how to make this
dll with the source?
 Many Thanks
Everton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Simon Slavin]

On 22 Jul 2011, at 12:15pm, Max Vlasov wrote:

> Do you have and anitvirus software installed? It may scan the file at
> the first usage.

What is the extension to the filename of the database file ?  Windows does 
fancy caching for files that have some particular extensions including, IIRC, 
'.db'.  Unfortunately although this works well for database files used for 
Windows it's terrible for ones which aren't.

If you've used a common extension for the database file rename it '.sqlite' or 
something that isn't going to be confused for another database format.

And to answer your question, no, it's not normal or a known bug.

Does Windows XP have some sort of task display where you can see what task is 
hogging most of the CPU or disk access ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

I have antivirus (avast) but it's disabled during testing.

22 июля 2011, 15:15 от Max Vlasov :
> On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко  
> wrote:
> > I have a log's database. Schema :
> >
> > Query:
> >
> > SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
> > FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' 
> > AND computer='KRAFTWAY';
> >...
> >
> >
> > Reboot system. Run query. Elapsed: ~100 SECONDS. Re-run query. Now it 
> > executes in less than a second. Re-run query. The same: less than a second. 
> > Tried it several times.
> >
> >
> > RUNNING THIS QUERY JUST AFTER REBOOT TAKES UNPREDICTABLE LARGE TIME, FROM 1 
> > TO 2 MINUTES.
> >
> >
> 
> Do you have and anitvirus software installed? It may scan the file at
> the first usage.
> 
> To be sure sqlite doesn't read too much I suggest opening Task
> Manager, choosing column I/O Read Bytes to be shown and run the query.
> This also can give a hint about the software that possibly reads the
> file all the this time.
> 
> Max
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT query first run is VERY slow

[Max Vlasov]

On Fri, Jul 22, 2011 at 2:38 PM, Григорий Григоренко  wrote:
> I have a log's database. Schema :
>
> Query:
>
> SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
> FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' 
> AND computer='KRAFTWAY';
>...
>
>
> Reboot system. Run query. Elapsed: ~100 SECONDS. Re-run query. Now it 
> executes in less than a second. Re-run query. The same: less than a second. 
> Tried it several times.
>
>
> RUNNING THIS QUERY JUST AFTER REBOOT TAKES UNPREDICTABLE LARGE TIME, FROM 1 
> TO 2 MINUTES.
>
>

Do you have and anitvirus software installed? It may scan the file at
the first usage.

To be sure sqlite doesn't read too much I suggest opening Task
Manager, choosing column I/O Read Bytes to be shown and run the query.
This also can give a hint about the software that possibly reads the
file all the this time.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECT query first run is VERY slow

[Григорий Григоренко]

I have a log's database. Schema :


CREATE TABLE log(id integer primary key autoincrement,msg text,created_at 
int,kind text,computer text,process text,who text); 
CREATE INDEX idxlog_created_at ON log(created_at); 
CREATE INDEX idxlog_kind_computer_id_process_who_msg_created_at ON 
log(kind,computer,id desc,process,who,msg,created_at); 
CREATE INDEX idxlog_kind_computer_process_id_who_msg_created_at ON 
log(kind,computer,process,id desc,who,msg,created_at); 
CREATE INDEX idxlog_kind_computer_process_who_id_msg_created_at ON 
log(kind,computer,process,who,id desc,msg,created_at); 
CREATE INDEX idxlog_kind_id_computer_process_who_msg_created_at ON log(kind,id 
desc,computer,process,who,msg,created_at); 


Database: ~ 3.5 Gb size, ~ 2.8 mln records. page_size=8192 bytes, UTF-8.

Platform: Windows XP, Intel Core Duo 3 Ghz.

SQLITE: sqlite3.exe console, latest version (3.7.7.1).


Query:

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' AND 
computer='KRAFTWAY';


Query plan (EXPLAIN QUERY PLAN):

0|0|0|SEARCH TABLE log USING COVERING INDEX 
idxlog_kind_computer_process_who_id_msg_created_at (kind=? AND computer=?) (~2 
rows)

There are no records in database that match query, i.e. query returns nothing.


Now, the problem. 


Reboot system. Run query. Elapsed: ~100 SECONDS. Re-run query. Now it executes 
in less than a second. Re-run query. The same: less than a second. Tried it 
several times. 


RUNNING THIS QUERY JUST AFTER REBOOT TAKES UNPREDICTABLE LARGE TIME, FROM 1 TO 
2 MINUTES.


Is this a sqlite bug or normal behaviour?

If it's normal, there are some questions.

Am I hitting some limit with my database? Which one?

Why is SQLITE (having nicely matching index) is spending so much time to 
execute? 

SQLITE has to cache some index data? Yeah but the console process (during 
execution of this query) is reading at about 800 Kb per second rate! (while HDD 
can make 100 Mb per second) Why so serious? Execuse me, why so slow?

And SQLITE process it's not consuming CPU (less than 1 percent). What is SQLITE 
doing during this long running query? 

It's own private memory is holding still at about 20 Mb. It's not consuming CPU 
and HDD. What is he waiting for?


This problem makes SQLITE completely unusable in my project.


I've shared database and scripts (Warning: it's RAR archive, ZIP cannot hold 
files of 3.5 Gb size. You can download WINRAR here: 
http://www.rarlab.com/download.htm ):

http://dl.dropbox.com/u/2168777/deadly_sqlite.rar

Download size: ~150 Mb.

Unzip and execute "run.cmd".

You'll see execution plan and after a while there will be line "Seconds 
elapsed: XXX".









___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem about the function sqlite3_get_table_printf(...)?

[lhg803]

hi,
can you tell me that when sqlite3 begin to support 
sqlite3_get_table_printf(...)?
thank you in advance!!!
/lhg

2011-07-20
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension

[Alexey Pechnikov]

2011/7/22 Abhinav Upadhyay :
> Thanks for pointing out that mail archive discussion. I wasn't using
> compress/uncompress because uncompress requires you to store the size
> of the compressed buffer which is returned by the compress function
> while compressing. But that email discussion suggests a nifty trick to
> overcome this.

And you can get the content size without decompression. It can be useful.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension

[Abhinav Upadhyay]

On Fri, Jul 22, 2011 at 1:32 PM, Abhinav Upadhyay
 wrote:
> On Fri, Jul 22, 2011 at 12:38 PM, Alexey Pechnikov
>  wrote:
>> But why you don't use compress/uncompress functions from DRH? See
>> http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html
>>
>> I did wrap these into extension and add SQLITE_COMPRESS_MIN_LENGTH
>> http://sqlite.mobigroup.ru/artifact/a5da96353bb851b34114052ba85041fdffb725cd
>> http://sqlite.mobigroup.ru/artifact/56df1be3c402d7d49c3a13be704a2ff22c3003d2
>>
>> http://sqlite.mobigroup.ru/dir?name=ext/compress
>>
>
> Thanks for pointing out that mail archive discussion. I wasn't using
> compress/uncompress because uncompress requires you to store the size
> of the compressed buffer which is returned by the compress function
> while compressing. But that email discussion suggests a nifty trick to
> overcome this.
>
> I implemented the compress/uncompress functions as suggested by
> RIchard in the email, however, it is still not working for me. The
> database is getting compressed fine, but  there is problem with
> decompression. I seem to be getting null values for the column values
> in the result set of my query.
>
> For example:
>
> $./my_program "print a document"
>
> (null)((null))
> (null)
>
> The above are three column values that I am trying select in my query.
> "pring a document" is a query that I tried to execute. Although this
> is definitely an improvement from before :)
>

Sorry, it was my mistake. I had done a small error in calling
uncompress. Now it seems to be working fine.

Thanks a ton for the help. You saved my day :-)

--
Abhinav
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension

[Abhinav Upadhyay]

On Fri, Jul 22, 2011 at 12:38 PM, Alexey Pechnikov
 wrote:
> But why you don't use compress/uncompress functions from DRH? See
> http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html
>
> I did wrap these into extension and add SQLITE_COMPRESS_MIN_LENGTH
> http://sqlite.mobigroup.ru/artifact/a5da96353bb851b34114052ba85041fdffb725cd
> http://sqlite.mobigroup.ru/artifact/56df1be3c402d7d49c3a13be704a2ff22c3003d2
>
> http://sqlite.mobigroup.ru/dir?name=ext/compress
>

Thanks for pointing out that mail archive discussion. I wasn't using
compress/uncompress because uncompress requires you to store the size
of the compressed buffer which is returned by the compress function
while compressing. But that email discussion suggests a nifty trick to
overcome this.

I implemented the compress/uncompress functions as suggested by
RIchard in the email, however, it is still not working for me. The
database is getting compressed fine, but  there is problem with
decompression. I seem to be getting null values for the column values
in the result set of my query.

For example:

$./my_program "print a document"

(null)((null))  
(null)

The above are three column values that I am trying select in my query.
"pring a document" is a query that I tried to execute. Although this
is definitely an improvement from before :)

Thanks
Abhinav
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange result using JOIN

[Sintoni Stefano (GMAIL)]

Hi,
I get a strange result using JOIN on one very simple application using
SQLite.

I have made one Win32 custom application (made with MS Visual Studio 6)
using sqlite3.dll rel. 3.7.2.

On my situation I have three table like follow.

Table CODE made with:
CREATE TABLE CODE (CODE TEXT DEFAULT '',VALUE TEXT DEFAULT '');
Table JOB made with:
CREATE TABLE JOB (CODE TEXT DEFAULT '',NUM INTEGER DEFAULT 0);
Table ELAB made with:
CREATE TABLE CODE (CODE TEXT DEFAULT '',VALUE TEXT DEFAULT '');

The table CODE are populated by INSERT query with the follow data:
CODE;VALUE;
61311;18462F;
61311;18461F;

The table JOB are populated by INSERT query with the follow data:
CODE;NUM;
61311;1;
61311;1;

The table ELAB are populated with the follow SQL query:
INSERT INTO ELAB SELECT CODE.* FROM JOB JOIN CODE ON CODE.CODE=JOB.CODE
and I take the follow result.
CODE;VALUE;
61311;18461F;
61311;18462F;
61311;18461F;
61311;18462F;

That is the problem.
But I should have this result
CODE;VALUE;
61311;18462F;
61311;18461F;
61311;18462F;
61311;18461F;

I take the right result performing the same SQL command using other
SQLite based Win32 application like SQLiteAdmin or SQLiteMan.
Where is my error ?

Thanks in advance.

Stefano

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [FTS3] The Compress and Uncompress functions and extension

[Alexey Pechnikov]

But why you don't use compress/uncompress functions from DRH? See
http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html

I did wrap these into extension and add SQLITE_COMPRESS_MIN_LENGTH
http://sqlite.mobigroup.ru/artifact/a5da96353bb851b34114052ba85041fdffb725cd
http://sqlite.mobigroup.ru/artifact/56df1be3c402d7d49c3a13be704a2ff22c3003d2

http://sqlite.mobigroup.ru/dir?name=ext/compress

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handle leak using IIS on windows 7?

[Doug]

This was exactly the problem; I didn't realize the 'static' variables are
persisted between page views in ASP.Net

Adding an '_instance = null;' fixed the issue.

Thanks muchly.

Cheers,
Doug.

On Tue, Jul 19, 2011 at 11:58 AM, Joe Mistachkin wrote:

>
> After reading the code, I noticed the following:
>
> 1. From the static Dump method, an instance of the DbLogger class is
> created via the static Get method and stored in the _instance static
> variable.
>
> 2. The connection itself is opened in the constructor for the DbLogger
> class via the InitDb method.
>
> 3. Prior to returning a result, the Dump method closes the connection
> and sets the _connection instance variable to null.
>
> 4. The second time the Dump method is executed, the existing instance
> of the DbLogger class will be used (i.e. the one stored in the static
> _instance variable).
>
> 5. This existing instance of the DbLogger class no longer has a valid
> connection because it was previously closed (and set to null).
>
> 6. Newly created commands will not have a valid connection.
>
> 7. Attempting to execute a command without a valid connection will
> result in the exception you are seeing.
>
> --
> Joe Mistachkin
>
> ___
> 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