Re: [sqlite] SQLite caching

Isn't it more likely that your database is pulled up into the OS disk cache? Try rebooting the computer between runs and the cache should be cleared. On Mon, Feb 23, 2009 at 8:44 AM, manohar s wrote: > Hi, > I am doing some performance analysis on my SQLite queries. The

Re: [sqlite] SQLite caching

On Feb 23, 2009, at 2:44 PM, manohar s wrote: > Hi, > I am doing some performance analysis on my SQLite queries. The > problem is > SQLite seems to be caching query results. > I tried restarting my program, that is not helping. only if i don't > access > that database for 2 days then it is

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 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] SQLite caching

Thanks for your quick replies, although restarting my machine, disc cache is cleared, I am trying to find an utility which could do the job without requiring a restart. Regards, Manohar.S On Mon, Feb 23, 2009 at 1:47 PM, Dan wrote: > > On Feb 23, 2009, at 2:44 PM,

[sqlite] Sqlite optim

Hello everyone, I'm doing some web logs analysis. I decided to use a sqlite database because the application has to be easily portable to another workstation or server and there is absolutly no need of multi-clients. Moreover, I don't want to spend my time dealing with the database. What I want

Re: [sqlite] Sqlite question: group by column with multiple tags?

Thanks for the answer. Igor Tandetnik wrote: > This monstrosity gives the correct answer in your specific example, but > it relies on there being exactly two tags per folder. It can be any number of tags per folder. Igor Tandetnik wrote: > > Consider normalizing your database. Split into two

Re: [sqlite] Windows XP: How Can I Get Higher CPU usage from SQLite

Funny... "Fred Williams" wrote... > Since the dawn of digital computers the CPU has been waiting on the I/O. > Want to go faster? Get a faster mass storage device. Then your CPU usage > will most likely jump all the way up to 9% - 14%! > > You can't believe what a 300 card per minute 80

Re: [sqlite] Windows XP: How Can I Get Higher CPU usage from SQLite

"P Kishor" wrote... > On Sun, Feb 22, 2009 at 2:14 PM, Fred Williams > wrote: >> Since the dawn of digital computers the CPU has been waiting on the I/O. >> Want to go faster? Get a faster mass storage device. Then your CPU >> usage >> will most likely jump all the

Re: [sqlite] Windows XP: How Can I Get Higher CPU usage from SQLite

"Alexey Pechnikov" wrote... > Hello! > > On Sunday 22 February 2009 22:56:36 jose isaias cabrera wrote: >> I have left everything default, so I have not set any PRAGMA settings. > > Try this: > pragma cache_size=1; > pragma page_size=16384; > vacuum; Wow, thanks. That did help. Cool, so

[sqlite] Is sqlite the good choice in my case ? And how can I optimize my SQL requests

Hello everyone, I'm doing some web logs analysis. I decided to use a sqlite database because the application has to be easily portable to another workstation or server and there is absolutly no need of multi-clients. Moreover, I don't want to spend my time dealing with the database. What I want

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

[sqlite] sqlite3_open expensive?

hi! how expensive is sqlite3_open? i'm working on a small (one-thread-per-connection) server. is it smart (in terms of cpu-time and memory usage) to use sqlite3_open for every connection or is it better to create a pool of pre-opened sqlite-connections? regards, luky ps:

[sqlite] SQLITE_THREADSAFE=1 and in-memory databases

Hello, I have compiled compiled sqlite with SQLITE_THREADSAFE=1 and inside my application I have 2 concurrent threads that need to open some in- memory databases that must be completely independents like unique file on disk. I open the in-memory dbs with: sqlite3_open(":memory:", ) ... but

Re: [sqlite] Sqlite optim

Hello! On Monday 23 February 2009 13:09:58 Jérôme Loyet wrote: > My questions: > 1- Is there a better way to populate the `siblings` table ? (optimize > my sql request) You may use compose index on (cookie,referer,date) and REAL datatype for dates. > 2- What can I do to optimize the all

[sqlite] Xcode project for amalgamation

I am tinkering with (aka learning) Xcode 3.1.2, and am trying to set up an Xcode project for SQLite amalgamation. What could be simpler, no? My Xcode project is throwing errors. Could someone directly (offlist) email me their Xcode project file so I may learn from it? Many thanks, -- Puneet

[sqlite] sqlite cache question

Hi all, I tried to scan the list and doc pages to understand better the sqlite's concept of caching but I'm still not really sure how to change the cache parameters to get the best performance: Assuming I change the cache by pages size = 4096 and cache_size = 1 sqlite will then use appx. 40

Re: [sqlite] Windows XP: How Can I Get Higher CPU usage from SQLite

That's the reason in memory databases are so fast. If a DB is small enough you can spool the whole thing into RAM on open and spool it back out on close. If you are attempting to use the DB in any kind of multi user environment client system large cache sizes are going to play H--- with

[sqlite] Alpha numeric collation

I understand this problem can be solved with a custom collation, however, if at all possible I'd prefer to not reinvent the wheel. I was curious if there were any pre-existing solutions my Google searches and reading of the documentation failed to detect. The problem: Is there any existing

Re: [sqlite] Xcode project for amalgamation

On Feb 23, 2009, at 9:15 AM, P Kishor wrote: > Ok, thanks William. I started again with BSD Dynamic Library template, > and this time I got only one error... a warning about an unused > variable 'err' on line 26510 of sqlite3.c. That line reads > > 26509> /* if mkdir fails, handle as lock file

Re: [sqlite] Alpha numeric collation

Simon Davies wrote: > If your data is in an integer column then an order by on the data > column gives what you ask for: > SNIP > sqlite> select data from tst2 order by case cast( data as integer > )=data when 1 then cast( data as integer ) else data end; > 1 > 4 > 9 > 10 > 51 > a Thanks very

Re: [sqlite] SQLite caching

From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of manohar s Sent: Monday, February 23, 2009 4:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite caching Thanks for your quick replies, although restarting my machine, disc cache is

[sqlite] Conditional expressions and evaluation

A clarification question...given the query: create table x (x_id integer, f varchar); create table y (y_id integer, x_id integer, f varchar); insert into x values(1, 'wibble'); insert into y values(1, 1, 'foo'); insert into y values(1, 1, 'bar'); select y.y_id, case when y.f = 'foo' then 'very

Re: [sqlite] SQLite caching

In all cases, copying a large file in a ramdisk (on linux: /dev/shm) would clear all cache (ie no space for it)... just make sure you dont fill the ram (have some swap space, cache is only held in ram). Simon On Mon, Feb 23, 2009 at 1:29 PM, Griggs, Donald wrote: >

Re: [sqlite] Conditional expressions and evaluation

On Feb 23, 2009, at 1:39 PM, John Elrick wrote: > A clarification question...given the query: > > create table x (x_id integer, f varchar); > create table y (y_id integer, x_id integer, f varchar); > > insert into x values(1, 'wibble'); > insert into y values(1, 1, 'foo'); > insert into y

Re: [sqlite] sqlite cache question

Hi Marcus My understanding of SQLite caching is that in your scenario, 40MB is the *maximum* cache memory that will be used per connection - this memory is not immediately pre-allocated when you open a new connection. Using the default memory allocator (memsys1) SQLite will allocate from the heap

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] Xcode project for amalgamation

Hello! On Monday 23 February 2009 16:48:09 P Kishor wrote: > I am tinkering with (aka learning) Xcode 3.1.2, and am trying to set > up an Xcode project for SQLite amalgamation. What could be simpler, > no? Use full SQLite source tree. Amalgamation will be created automatically by build system.

[sqlite] SAVEPOINT : Seems don't work

Hello, I am quite new to use SQLite I tried to use SAVEPOINT command but didn't succeed Does this command work ? how ? MaxMax14 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about Referential IntegrityRE: Foreign key support

On 12.02.2009 06:23 CE(S)T, Roger Binns wrote: > It is true that triggers can be used to achieve referential integrity. > However you don't have to hand craft them. The front page of the wiki > links to the document explaining it: > > http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers >

Re: [sqlite] Conditional expressions and evaluation

D. Richard Hipp wrote: > On Feb 23, 2009, at 1:39 PM, John Elrick wrote: > > >> A clarification question...given the query: >> >> create table x (x_id integer, f varchar); >> create table y (y_id integer, x_id integer, f varchar); >> >> insert into x values(1, 'wibble'); >> insert into y

[sqlite] question on creating/populating table with varchar/clob data types

Hello all,   I have a TCL application that writes database records to an Oracle db. This database has grown to contain 8000 records. There are 2 columns of varchar with 4k chars and 1 column of clob which contains 1Mg max. The data in this database are scripts.   Now, I need to write another

Re: [sqlite] sqlite cache question

Thanks Dave, I guess I'll give the shared cache a try... actually, when I started I switched it on but removed afterward because I was not sure if it is a kind of sqlite standard usage. I'll also look into the heap_limit function. Marcus > Hi Marcus > > My understanding of SQLite caching is

[sqlite] Removing duplicate records

Greetings, I'd like to know if anyone has an elegant solution to the problem stated below. I know it could be brute forced but it seems that there should be an elegant SQL solution to this problem. Given the following table and data, I'd like to remove all items with duplicate types within a

Re: [sqlite] question on creating/populating table with varchar/clob data types

On Feb 23, 2009, at 3:29 PM, anjela patnaik wrote: > Hello all, > > I have a TCL application that writes database records to an Oracle > db. This database has grown to contain 8000 records. There are 2 > columns of varchar with 4k chars and 1 column of clob which contains > 1Mg max. The

Re: [sqlite] question on creating/populating table with varchar/clob data types

On Mon, Feb 23, 2009 at 2:46 PM, D. Richard Hipp wrote: > .. > > SQLite is, in fact, a TCL extension that escaped into the wild. It is > specifically designed to integrate well with Tcl/Tk. > .. Did you ever tell that story anywhere? Would be fun to read it. -- Puneet Kishor

[sqlite] SQLite vs. Oracle (parallelized)

Dr. Hipp, When you say "SQLite is way faster than Oracle in a single-user applications" do you mean that SQLite can be faster than Oracle even when Oracle's parallel processing features are being used? For example Oracle's support for parallelization can speed up table loading from an external

Re: [sqlite] SQLite vs. Oracle (parallelized)

> > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? > Malcolm, I realized that you're asking Richard, and not the peanut gallery, but I figured I might as well ask out of curiosity: why do you want to see these features in

Re: [sqlite] SAVEPOINT : Seems don't work

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 REPKA_Maxime_NeufBox wrote: > I tried to use SAVEPOINT command but didn't succeed > > Does this command work ? how ? It certainly works in my testing and works in the SQLite team testing - http://sqlite.org/testing.html Chances are you haven't got

Re: [sqlite] SQLite vs. Oracle (parallelized)

On Feb 23, 2009, at 3:54 PM, pyt...@bdurham.com wrote: > Dr. Hipp, > > When you say "SQLite is way faster than Oracle in a single-user > applications" do you mean that SQLite can be faster than Oracle even > when Oracle's parallel processing features are being used? For example > Oracle's

Re: [sqlite] mysql_num_rows equivalent in SQLite

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 bezpla...@jabse.com wrote: > Can you told me, how to find number of rows, of already > prepared SELECT query. SQLite doesn't work that way. It calculates the next result row when you ask for it, rather than calculating all of them up front. To

Re: [sqlite] SQLite vs. Oracle (parallelized)

I fully agree with DRH regarding SQLITE and single user performance. If you need to replace fopen then sqlite is a really really great product. Even if you have some mild concurrency sqlite still does pretty darned good. Oracle excels when you have many users that require concurrent database

Re: [sqlite] sqlite3_finalize removes registered custom functions?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Anil Madhavapeddy wrote: > The custom functions are registered against the database handle, but > when prepared statements are garbage collected (and hence call > sqlite3_finalize() on the statement handle), the custom functions > appear to

Re: [sqlite] Removing duplicate records

Gary O'Brien wrote: > Greetings, > > I'd like to know if anyone has an elegant solution to > the problem stated below. I know it could be brute > forced but it seems that there should be an elegant > SQL solution to this problem. > > Given the following table and data, I'd like to remove > all

[sqlite] UPDATE Field based on matching value in different Table

I have two tables each with two fields: TableA: Field1, Field2 TableB: Field3, Field4 I would like to set Field2 in TableA to the value in Field4 in TableB where TableA.Field3=TableB.Field4 Sounds simple enough but I can't figure out how to write the UPDATE SQLite for this. Can someone help?

[sqlite] Variables in Triggers

I'd like to have a condition in a trigger that examines a variable (set outside the trigger) and acts accordingly. Are there any SET style statements for environmental variables in SQLite ? Thanks D - David Moorhouse Development Director Moorhouse Works ltd phone

Re: [sqlite] UPDATE Field based on matching value in different Table

I've been meaning to ask this myself. I've been writing it like update TableA set Field2 = (select Field4 from TableB where Field3 = TableA.Field1) where Field1 = ?; --perhaps But it feels wrong. -Clark - Original Message From: Greg Robertson To:

Re: [sqlite] UPDATE Field based on matching value in different Table

On Mon, Feb 23, 2009 at 5:34 PM, Greg Robertson wrote: > I have two tables each with two fields: > > TableA: Field1, Field2 > > TableB: Field3, Field4 > > > I would like to set Field2 in TableA to the value in Field4 in TableB > where TableA.Field3=TableB.Field4 > > Sounds

Re: [sqlite] Variables in Triggers

"David Moorhouse" wrote in message news:vue6q453ln9isiut2m808u76qecq9ao...@4ax.com > I'd like to have a condition in a trigger that examines a variable > (set outside the trigger) and acts accordingly. > > Are there any SET style statements for environmental variables in

Re: [sqlite] UPDATE Field based on matching value in different Table

"Greg Robertson" wrote in message news:151e70a00902231534h37d10202m469a176d704d6...@mail.gmail.com > I have two tables each with two fields: > > TableA: Field1, Field2 > > TableB: Field3, Field4 > > > I would like to set Field2 in TableA to the value in Field4 in TableB >

Re: [sqlite] UPDATE Field based on matching value in different Table

That did it. Thanks Greg On Mon, Feb 23, 2009 at 7:05 PM, Griggs, Donald wrote: > Hi Greg, > > Someone on the list may give a better reply, and I'm sending this to you > directly, but I think the following will work: > > Update tableA > set Field2 = ( select

Re: [sqlite] Removing duplicate records

"Gary O'Brien" wrote in message news:93fda2e5d3cb442884be95e1b84fc...@garysldc13y00e > Given the following table and data, I'd like to remove > all items with duplicate types within a container, > keeping the most recent (largest item_id) item of that > type within each

Re: [sqlite] SQLite vs. Oracle (parallelized)

Dr. Hipp and others, Thank you for your replies to my question. Regards, Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE Field based on matching value in different Table

"Greg Robertson" wrote in message news:151e70a00902231728j608612b8n491e84b11c70c...@mail.gmail.com > That did it. > > Thanks > > Greg > > On Mon, Feb 23, 2009 at 7:05 PM, Griggs, Donald > wrote: >> Hi Greg, >> >> Someone on the list may give a

Re: [sqlite] Removing duplicate records

"Lawrence Chitty" wrote in message news:49a32a32.6040...@ntlworld.com > Gary O'Brien wrote: >> Given the following table and data, I'd like to remove >> all items with duplicate types within a container, >> keeping the most recent (largest item_id) item of that >>

Re: [sqlite] question on creating/populating table with varchar/clob data types

On Feb 23, 2009, at 3:54 PM, P Kishor wrote: > On Mon, Feb 23, 2009 at 2:46 PM, D. Richard Hipp > wrote: >> > .. >> >> SQLite is, in fact, a TCL extension that escaped into the wild. It >> is >> specifically designed to integrate well with Tcl/Tk. >> > .. > > Did you ever

Re: [sqlite] question on creating/populating table with varchar/clob data types

On Mon, Feb 23, 2009 at 8:01 PM, D. Richard Hipp wrote: > > On Feb 23, 2009, at 3:54 PM, P Kishor wrote: > >> On Mon, Feb 23, 2009 at 2:46 PM, D. Richard Hipp >> wrote: >>> >> .. >>> >>> SQLite is, in fact, a TCL extension that escaped into the wild. It >>> is

Re: [sqlite] question on creating/populating table with varchar/clob data types

Dr. Hipp, > The story goes like this: ... Great story! Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite vs. Oracle (parallelized)

Hi Billy, >> Are there any plans to enhance SQLite to support some of Oracle's >> parallel processing or partitioning capabilities? > I realized that you're asking Richard, and not the peanut gallery, but > I figured I might as well ask out of curiosity: why do you want to > see these features

Re: [sqlite] SQLite vs. Oracle (parallelized)

On Mon, Feb 23, 2009 at 9:34 PM, wrote: > Hi Billy, > >>> Are there any plans to enhance SQLite to support some of Oracle's >>> parallel processing or partitioning capabilities? > >> I realized that you're asking Richard, and not the peanut gallery, but >> I figured I might

Re: [sqlite] SQLite vs. Oracle (parallelized)

P Kishor, >> Most computers these days are multi-core. .. > One of things easy to overlook is that SQLite is not a PC-exclusive software. About 10 million copies of SQLite run on iPhone. Who knows how many run on other handhelds, embedded platforms, Vxworks, the like. SQLite used to fit on a

Re: [sqlite] SAVEPOINT : Seems don't work

On Feb 24, 2009, at 3:02 AM, REPKA_Maxime_NeufBox wrote: > Hello, > > I am quite new to use SQLite > > I tried to use SAVEPOINT command but didn't succeed > > Does this command work ? how ? We hope so. What happened to indicate it did not succeed? In what way did the SAVEPOINT command

[sqlite] a question on the callback function's return values of sqlite3_exec()

the question is on the callback function's return values of sqlite3_exec() when using sqlite3_exec() to do "select * from ...", how to get all the return values by using the callback function? struct olt_info { int olt_index; int olt_logo; char* olt_line; // int nmber; }; int

[sqlite] How to use wild characters to select rows?

Dear All, Please can anybody help me how to use wild characters to select rows? With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How to use wild characters to select rows?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Pramoda M. A wrote: > Please can anybody help me how to use wild characters to select rows? http://www.catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux)