Re: [sqlite] Database creation and inserts speedup

[Radu Lodina]

Hi,

General consideration:

A. Run insert in "bulk fashion":
BEGIN
INSERT INTO  /* ~1 - 2000 (or more) inserts line*/
COMMIT

B. If index have complex clause please don't create index until you finish 
data upload in tables.

Good look


- Original Message - 
From: "J. R. Westmoreland" 
To: "'General Discussion of SQLite Database'" 
Sent: Friday, May 08, 2009 4:45 PM
Subject: [sqlite] Database creation and inserts speedup


>I have a program that creates a database with a number of tables and a few
> indexes.
>
> I'm just inserting in this program and not doing any lookups.
>
> I create the indexes after all records have been inserted.
>
> The input for the database is a LARGE number of records.
>
>
>
> The data is mapping data for the U.S. and therefore contains millions of
> entries.
>
> The current run is taking about three days for the entire U.S. to 
> complete.
>
> A single state, say California, is taking several hours.
>
>
>
> Any suggestions on speed-ups?
>
>
>
> Granted this only has to be done once but if there is a failure along the
> line it could be bad. 
>
>
>
> Thanks,
>
> J. R.
>
>
>
>
>
>
>
> 
>
> J. R. Westmoreland
>
> E-mail: j...@jrw.org
>
> Twitter: GeneralJR
>
>
>
> ___
> 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] Sqlite versus mySQL in PHP

[Radu Lodina]

One (and first) shoot: - try to use :

BEGIN TRANSACTION



COMMIT TRANSACTION




- Original Message - 
From: "Anton Rifco" 
To: 
Sent: Friday, March 27, 2009 11:28 AM
Subject: [sqlite] Sqlite versus mySQL in PHP


> Hi guys,
>
> I would like to ask a question about sqlite in php. I don't know if I am
> sending this message to the right place, but I don't know where else to 
> ask.
>
> Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit
> faster than mysql and postgreSQL. But, when I perform a little testing yo
> compare them in php, I got a contrast result.
>
> For both testing, I use this table :
> CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c 
> varchar(20),
>  d varchar(20), e varchar(20),  f varchar(20),
>  g varchar(20), h varchar(20),  i varchar(20), j
> varchar(20))
>
> for that table, I perform 5000 insert for both sqlite and mysql :
> here's for sqlite code :
> === sqlite-test.php ===
> $time_start = microtime(true);
> $db = sqlite_open('pegawai.db', 0666, $sqliteerror);
> for($i = 0; $i < 5000; $i++)
>{
>$sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
>$q = sqlite_query($db, $sql);
>}
> sqlite_close($db);
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> =
>
> and here's for mysql code :
> = mysql-test.php 
> .
> $time_start = microtime(true);
> for($i = 0; $i < 5000; $i++)
>{
>$sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
>$q = mysql_query($sql);
>}
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> ..
> ===
>
> when I run those 2 script, the first one (sqlite) run for 43.4918169975
> seconds, while the second (mysql) only take 0.52137298584 seconds.
>
> It means that sqlite run 80x longer than mysql did (very contrast with the
> one I read in http://www.sqlite.org/speed.html).
>
> Please tell me where do I did wrong. Thanks very much.
>
> Regards,
>
> --
> Anton Rifco S
>
> Student of Informatics Department
> School of Electrical Engineering and Informatics
> Institute Technology of Bandung
> ___
> 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] Problems with some SQL

[Radu Lodina]

The problem:

Field UserGroup.UserGroupID is NOT declared as autoincrement field and 
statement:

INSERT INTO UserGroup (UserGroupShortTitle ...

don't fill this field.

Check yourself with:
SELECT * FROM UserGroup

Check create autoincrement field : http://www.sqlite.org/faq.html#q1

CREATE TABLE UserGroup (
 `UserGroupID` INTEGER PRIMARY KEY,
  

Please see the INTEGER keywords in DDL.

Good luck.



- Original Message - 
From: 
To: 
Sent: Thursday, March 19, 2009 11:31 PM
Subject: [sqlite] Problems with some SQL


> Heya,
>
> I'm currently working on a simple setup for a news and user system.
> However, I'm having some issues with the SQL I'm using. It's available @
> http://geekerzoid.pastebin.com/m532d173a
>
> I keep getting "SQL error: foreign-key violation: UserGroup.UserGroupID"
>
> Any help would be greatly appreciated.
>
> ~ Selyoid
>
> ___
> 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] about DELETE and JOIN

[Radu Lodina]

Hi cyrille,

Please try:

DELETE FROM Words WHERE IID NOT IN
   ( SELECT Words_ID FROM Feedbacks_has_Words )

Regards
Radu Lodina

On 2/9/06, Cyrille37 <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I've got a SELECT query that I would like to transform as a DELETE query ,
> but I could not write that DELETE query ;o{
>
> Here is the case :
>
> In a search engine, I would like to erase all words that are no more
> attached to a feedback entry.
>
> table Words ( IID, Word )
> table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
> table Feedbacks( IID,  )
>
> When a word has no more feedback attached (relation is table
> Feedbacks_has_Words) ,
> it should be deleted.
>
> To find such words, this SELECT query works fine :
>
> select IID,Word, Feedbacks_IID
> from Words LEFT  JOIN Feedbacks_has_Words
> on IID = Words_IID
> where Feedbacks_IID is null
>
> I could not figure how to right the DELETE Query ...
> Have you got a idea about it ???
>
> Thanks a lot,
> cyrille
>
>

Re: [sqlite] SQLite acces from vbScript

[Radu Lodina]

Take a look to :
  http://www.terrainformatica.com/sqlitedb/

Personally I don't use it, but looks nice.

Regards
Radu Lodina


On 1/30/06, John Latimer <[EMAIL PROTECTED]> wrote:
>
> Thanks for the lead, I'll go looking for it...
>
>   John L>
>
> Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
>   all you need is an OLEDB or ODBC wrapper for sqlite, and there are some
> around, check the wiki.
> once you install one, your sqlite is accesible from ADO, just like any
> other database (almost)
>
> 
>
> From: John Latimer [mailto:[EMAIL PROTECTED]
> Sent: Mon 30-Jan-06 12:06 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite acces from vbScript
>
>
>
> I am programming in vbScript within the framework of a larger program on a
> PC platform and need to have access to SQLite for my database needs.
>
> I am an inexperienced programmer, so I wouldn't be surprised if the answer
> is very obvious to you , but oblivious to me...
>
> I require use of an SQLite database with full database query
> functionallity using vbScript.
>
> John L>
>
>
>
>
> John A. Latimer
>
>
>
> The Hologenic Man
>
>
>
>
>
> Discovery: The more I learn, the more I learn how little I know...
>
>
> Goal: There's strength in simplicity.
>
> Note: Goal not always achieved.
>
>
>
>
>
>
>
>
>
>
>
>
>
> John A. Latimer
>
>
>
> The Hologenic Man
>
>
>
>
>
> Discovery: The more I learn, the more I learn how little I know...
>
>
> Goal: There's strength in simplicity.
>
> Note: Goal not always achieved.
>
>
>
>
>
>
>

Re: [sqlite] Lite Network wrapper

[Radu Lodina]

Hi - looks great.

Do you have a plan to share also the source code of server ?

Regards
Radu Lodina


On 11/11/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> This is a wrapper for SQLite that allows multiple connections accross a
> network to a single threaded SQLIte server (all clients are serviced on
> the same connection). It also adds a simple security layer.
>
> It was designed for general purpose db use and IPC for
> embedded/automation systems, allthougth the performance suprised me! I
> expected latency to drop when running large queries, as all other
> clients must wait for completion before they can start executing. In
> order to improve that I buffered the result sets so they can be dumped
> quickly out of the way, and SQLite can pump them out very quickly :-)
>
> There is a C client source which is similar to the
> sqlite_execute/callback interface.
>
> There is also a VB example, but it is not much good :-)
>
> Windows and Linux binaries statically linked to SQLite 3 are available,
> they should run without 'extra' libraries.
>
> The underlying protocol is a very simple TCP/IP based system, not RPC's.
> It allows clients to be easily implemented into anything with sockets,
> such as application scripting languages for example.
>
> More info and downloads:
>
> http://users.iol.it/irwin
>
>
>
>
>

Re: [sqlite] CHECK constraints

[Radu Lodina]

Hi,

In DB2 7.x the insert statement is also ok.



On 11/3/05, Nemanja Corlija <[EMAIL PROTECTED]> wrote:
>
> > Does the check constraint fail or not? Or do different
> > database engines do different things?
> In Firebird 1.5 it does fail.
>
> Though I agree with Darren's remarks, so not failing it seems to be
> more flexible.
>
> --
> Nemanja Corlija <[EMAIL PROTECTED]>
>

Re: [sqlite] Number of rows in a query result

[Radu Lodina]

Hi Alfredo,


I supose you don't use
sqlite3_get_table<http://www.sqlite.org/capi3ref.html#sqlite3_get_table>function
right ?


Call another query with:
SELECT COUNT(*) AS NrRecords FROM ( )


Radu Lodina

On 10/28/05, Alfredo Cole <[EMAIL PROTECTED]> wrote:
>
> Hi:
>
> In order to update a progress bar, I need to know the total number of rows
> returned by a query, similar to MySQL's mysql_num_rows. Is there a
> function
> like that in the C API? I may have overlooked it, but have not found it so
> far.
>
> Thank you.
>
> --
> Alfredo J. Cole
> Grupo ACyC
>

[sqlite] RE: full column names in 3.2.7

[Radu Lodina]

Hi,

This pragma works for me (3.2.7):

sqlite> pragma short_column_names=0;
sqlite> pragma full_column_names=1;
sqlite> .header on


Set short_ = 0 and full_ = 1

You can check the curent value of pragma directive with: pragma 


sqlite> pragma short_column_names;
0  // -> display of short column name deactivated


sqlite> pragma full_column_names;
1  // -> display of full column name ACTIVATED

Regards
Radu Lodina

[sqlite] How to retreive (for a column): original field name and base table name from a sqlite3_stmt*

[Radu Lodina]

Hi,

1. Retrieve original field name:

 CREATE TABLE A (ID int, Name );
SELECT ID AS MyID, Name As MyName FROM A

sqlite3_column_name - return the alias MyID - It's a way to obtain the real
name of column (ID) based on sqlite3_stmt* pointer ?

2. Retrieve base table name:

 CREATE TABLE B (ID int );
SELECT B.ID <http://B.ID> AS MyID, A.Name As MyName FROM A LEFT OUTER JOIN B
ON A.ID <http://A.ID> = B.ID <http://B.ID>

It's there a way to obtain the base table name for MyID field (B).


For both case I only have sqlite3_stmt* pointer ( parse sqlStatement string
it's not a acceptable solution).

Thank you.


Regards
Radu Lodina