Re: [sqlite] Newb-ish performance questions

As the topic goes I am new to Sqlite. The gui I'd been using was compiled with an older version, than the 3.6.10 cli I downloaded. So things were a bit choppy. I'll stick to using just the downloaded Sqlite cli, now I (sort of) know what I'm doing with it. I'll post the results once I've

Re: [sqlite] Newb-ish performance questions

On Feb 23, 2009, at 2:42 AM, Kim Boulton wrote: > > But OR is a lot slower than using UNION ALL on both Sqlite and Mysql The optimizer was significantly enhanced for version 3.6.8 in order to better handle OR in WHERE clauses. What version of SQLite did you test this with? D. Richard

Re: [sqlite] Newb-ish performance questions

Why do you need the 7 single-column indexes? Do you ever do a lookup on a single column? Bear in mind that only 1 index is used per query, so having seven separate indexes on seven separate columns means that six are always unused. I'm curious why the UNION is faster than the OR'ed

Re: [sqlite] Newb-ish performance questions

On 23/02/2009 8:14 PM, Kim Boulton wrote: > Hehe, probably a combination of rubbish grep (i used regex function in a > text editor) and vaccuming a 4GB table at the same time. google("scientific method") :-) > > @echo off > setlocal > set starttime=%time% > egrep --count >

Re: [sqlite] Newb-ish performance questions

Hehe, probably a combination of rubbish grep (i used regex function in a text editor) and vaccuming a 4GB table at the same time. @echo off setlocal set starttime=%time% egrep --count "(W[CEF][SZ]|..W[CEF]S|...W[CEF]S|W[3CEF]S[25]..|W3S..|.11[CEF]S.)," my-30-million-rows-of-data.txt set

Re: [sqlite] Newb-ish performance questions

On 23/02/2009 5:14 PM, Kim Boulton wrote: > Hello, > > Thanks > > The grep regex on the text file found around 10,000 lines over 5 minutes > (out of a total possible 200,000 rows), at which time I stopped it, > interesting experiment anyway :-) Uh-huh ... so you'd estimate that it would take

Re: [sqlite] Newb-ish performance questions

Woo, that's a bit better. Setting the page_size = 4096 gets the query time down to 17 seconds, so it's now faster than Mysql. Result! I also tried changing the query to: SELECT * FROM mytable WHERE (c5 = 'W' AND c6 IN ('C','E','F') AND c7 IN ('S','Z')) OR (c3 = 'W' AND c4 IN ('C','E','F') AND

Re: [sqlite] Newb-ish performance questions

Woo, that's a bit better. Setting the page_size = 4096 gets the query time down to 17 seconds, so it's now faster than Mysql. Result! I also tried changing the query to: SELECT * FROM mytable WHERE (c5 = 'W' AND c6 IN ('C','E','F') AND c7 IN ('S','Z')) OR (c3 = 'W' AND c4 IN ('C','E','F') AND

Re: [sqlite] Newb-ish performance questions

Sorry, I forgot to mention that I was running the query twice, first to cache it, second to measure the speed. Yeah it's a horrible query but produces the fastest results in Mysql. maybe using OR instead works better on Sqlite, working on that one. Problem I have with indexes in sqlite is that

Re: [sqlite] Newb-ish performance questions

You have a good point there, I've been using that wrongly. I'll try that. Thanks Kees Nuyt wrote: > On Sun, 22 Feb 2009 08:48:00 +, Kim Boulton > wrote in k...@jesk.co.uk, General Discussion > of SQLite Database : > > >> *Then queried the

Re: [sqlite] Newb-ish performance questions

Hello, Thanks The grep regex on the text file found around 10,000 lines over 5 minutes (out of a total possible 200,000 rows), at which time I stopped it, interesting experiment anyway :-) Using OR instead of UNION in Mysql is definitely slower, although maybe, as someone has mentioned

Re: [sqlite] Newb-ish performance questions

Would loading the 30 million row csv file via the command line be wrapped in inside a single transaction, thus building a very rollback log? I like to break my bulk loads into nice chunks. Also it could be the MySQL parser does a better job of optimizing the unusual select. On Sun, Feb 22,

Re: [sqlite] Newb-ish performance questions

On Sun, Feb 22, 2009 at 01:29:09PM +0100, Kees Nuyt wrote: > > PRAGMA page_size = 2000; /*this doesn't make any difference*/ > > PRAGMA page_size will only make a difference if you use it > when creating the database (before the first table is > created), or just before a VACUUM statement. >

Re: [sqlite] Newb-ish performance questions

For starters, I think that loading the index into the cache in MySQL is biasing your performance measures. SQLite will automatically load pages of any necessary indexes into memory as part of executing the query, but doing so takes time. By preloading the index on MySQL, you're removing that

Re: [sqlite] Newb-ish performance questions

On Sun, 22 Feb 2009 08:48:00 +, Kim Boulton wrote in k...@jesk.co.uk, General Discussion of SQLite Database : > *Then queried the Sqlite3 table with:* > PRAGMA cache_size = 2000; */uses up 1.5GB RAM regardless*/ cache_size is expressed in number

Re: [sqlite] Newb-ish performance questions

On 22/02/2009 7:48 PM, Kim Boulton wrote: > Thanks for the hints so far. > > Here is my existing Mysql setup and what I've tried with Sqlite > > *Mysql Table structure:* > CREATE TABLE `mydb`.`mytable` ( > `c1` >

Re: [sqlite] Newb-ish performance questions

Thanks for the hints so far. Here is my existing Mysql setup and what I've tried with Sqlite *Mysql Table structure:* CREATE TABLE `mydb`.`mytable` ( `c1`

Re: [sqlite] Newb-ish performance questions

On Fri, Feb 20, 2009 at 05:22:33AM +, Kim Boulton scratched on the wall: > Hello, > > I'm trying out Sqlite3 with an eye to improving the performance of > queries on an existing MySQL database. > > I've imported the data into sqlite which is approx. 30 million rows of > part numbers each

Re: [sqlite] Newb-ish performance questions

was it 4 times slower to load? or 4 times slower to query? also we need some examples. On Thu, Feb 19, 2009 at 9:33 PM, Thomas Briggs wrote: > Depending on the nature of the data and queries, increasing the > block size may help. > > Posting some information about your

Re: [sqlite] Newb-ish performance questions

Depending on the nature of the data and queries, increasing the block size may help. Posting some information about your schema and queries is the only way to get truly good advice on this though, I think. There is no "-runfast" switch you can include on the command line to fix things. :)

[sqlite] Newb-ish performance questions

Hello, I'm trying out Sqlite3 with an eye to improving the performance of queries on an existing MySQL database. I've imported the data into sqlite which is approx. 30 million rows of part numbers each with a price. So far, it's approx. four times slower than the MySQL version, and the size