Re: [sqlite] disable transaction support

[John Stanton]

The fastest performance you will get is with synchronous off.  That will 
relax the ACID requirement on the COMMIT but it can be unsafe if you get 
a crash during the commit.


Mohd Radzi Ibrahim wrote:

Hi Rasanth,

I'm not an expert in SQLite. Not sure what kind of performance gain you 
want to achieve. Perhaps you could try "pragma synchronous=off". Or 
in-memory database...


I guess the journal is there to provide ACID db characteristic.

For me even with that out-of-the-box, the insert performance is already 
faster than MS SQL Server 2005; (I've been working with more than 1 
million rows tables).


best regards,
Radzi.

On 26-Dec-2007, at 2:38 PM, Rasanth Akali Kandoth wrote:


Hi Radzi,
i do it with BEGIN and COMMIT. it is that, even in this case for 
transaction
support sqlite has to write into the journal files as well. i want to 
avoid

this too.

Thanks,
Rasanth

On Dec 26, 2007 11:34 AM, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:


On the contrary, sqlite work much-much faster when insert/update is
done within BEGIN and COMMIT;

regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a
table, where
transaction support is not necessary. For performance reason, i need
to
disable the transaction support in sqlite version 3.3.17 .   How can
i do
it?  any help is highly appreciated.

--
Thanks,
Rasanth




- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 







-



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disable transaction support

[Mohd Radzi Ibrahim]

Hi Rasanth,

I'm not an expert in SQLite. Not sure what kind of performance gain  
you want to achieve. Perhaps you could try "pragma synchronous=off".  
Or in-memory database...


I guess the journal is there to provide ACID db characteristic.

For me even with that out-of-the-box, the insert performance is  
already faster than MS SQL Server 2005; (I've been working with more  
than 1 million rows tables).


best regards,
Radzi.

On 26-Dec-2007, at 2:38 PM, Rasanth Akali Kandoth wrote:


Hi Radzi,
i do it with BEGIN and COMMIT. it is that, even in this case for  
transaction
support sqlite has to write into the journal files as well. i want  
to avoid

this too.

Thanks,
Rasanth

On Dec 26, 2007 11:34 AM, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>  
wrote:



On the contrary, sqlite work much-much faster when insert/update is
done within BEGIN and COMMIT;

regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a
table, where
transaction support is not necessary. For performance reason, i need
to
disable the transaction support in sqlite version 3.3.17 .   How can
i do
it?  any help is highly appreciated.

--
Thanks,
Rasanth




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disable transaction support

[Rasanth Akali Kandoth]

Hi Radzi,
i do it with BEGIN and COMMIT. it is that, even in this case for transaction
support sqlite has to write into the journal files as well. i want to avoid
this too.

Thanks,
Rasanth

On Dec 26, 2007 11:34 AM, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:

> On the contrary, sqlite work much-much faster when insert/update is
> done within BEGIN and COMMIT;
>
> regards,
> Radzi.
> On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:
>
> > Hi All,
> > I have an application which inserts large number of rows into a
> > table, where
> > transaction support is not necessary. For performance reason, i need
> > to
> > disable the transaction support in sqlite version 3.3.17 .   How can
> > i do
> > it?  any help is highly appreciated.
> >
> > --
> > Thanks,
> > Rasanth
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-

Re: [sqlite] disable transaction support

[Mohd Radzi Ibrahim]

On the contrary, sqlite work much-much faster when insert/update is  
done within BEGIN and COMMIT;


regards,
Radzi.
On 26-Dec-2007, at 12:14 PM, Rasanth Akali Kandoth wrote:


Hi All,
I have an application which inserts large number of rows into a  
table, where
transaction support is not necessary. For performance reason, i need  
to
disable the transaction support in sqlite version 3.3.17 .   How can  
i do

it?  any help is highly appreciated.

--
Thanks,
Rasanth



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] PySQLite problem

[Cesar D. Rodas]

Hello,

I was testing PySQLite and got an exception:

Traceback (most recent call last):
  File "D:\code.cesarodas.com\etopa\test2.py", line 33, in 
for result in search(raw_input("Search:")):
  File "D:\code.cesarodas.com\etopa\files.py", line 147, in search
r = cu.execute('select dir, file from files where file like :item',
{"item":
item}).fetchall()
sqlite3.OperationalError: Could not decode to UTF-8 column 'file' with text
'DJ
Marky e DJ Patife - S¶ tinHa qUe Ser cOm voce.mp3'


Any one had an idea how to solve this problem?

-- 
Best Regards

Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165

[sqlite] disable transaction support

[Rasanth Akali Kandoth]

Hi All,
I have an application which inserts large number of rows into a table, where
transaction support is not necessary. For performance reason, i need to
disable the transaction support in sqlite version 3.3.17 .   How can i do
it?  any help is highly appreciated.

-- 
Thanks,
Rasanth

Re: [sqlite] Re: Fastest way to check if new row or update existing one?

[Kees Nuyt]

On Tue, 25 Dec 2007 19:24:48 +0100, "Mag. Wilhelm Braun"
<[EMAIL PROTECTED]> wrote:

>Thanks as in my case just number 2 is possible a quite 'silly' question:
>How do you normally check if Update was successful if the specified row 
>did not exists.
>
>e.g: UPDATE MyTable SET Account='MyAccountName' WHERE ID=50
>
>If row 50 does not exists it does nothing and I seem not to get any return to 
>know?

There is an SQLite function for that.
http://www.sqlite.org/c3ref/total_changes.html

>using pysqlite.

I don't know the pysqlite wrapper, but this may be of help:
http://www.initd.org/pub/software/pysqlite/doc/usage-guide.html
Search for "total_changes attribute".

>Thanks W.Braun
>
>
>
>A. Pagaltzis wrote:
>> * Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 15:10]:
>>   
>>> QUESTION: is there a better way to make this important
>>> decision? using Sqlite
>>> 
>>
>> 1. If you are changing the entire row on every update, you can
>>simply use `INSERT OR REPLACE` (assuming there is a UNIQUE
>>column) to always do this in a single query.
>>
>>See .
>>
>> 2. If you only want to update some of the columns, particularly
>>if you are likely to update rows several times, you can use
>>`UPDATE` to try and update, and if this did not affect any
>>rows you do an `INSERT`.
>>
>> In #1, you always get the job done with a single query. In #2,
>> you are usually done after the first but sometimes need a second.
>> Both are more efficient than your current approach, which always
>> runs two queries.
>>
>> Regards,
>>   
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Fastest way to check if new row or update existing one?

[A. Pagaltzis]

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 19:30]:
> If row 50 does not exists it does nothing and I seem not to get
> any return to know?

http://sqlite.org/c3ref/changes.html

> using pysqlite.

I don’t know anything about pysqlite, but apparently you are
looking for the `rowcount` attribute on the Cursor class.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

Thanks Pagaltzis. Great help.

W.Braun

A. Pagaltzis wrote:

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 19:30]:
  

If row 50 does not exists it does nothing and I seem not to get
any return to know?



http://sqlite.org/c3ref/changes.html

  

using pysqlite.



I don’t know anything about pysqlite, but apparently you are
looking for the `rowcount` attribute on the Cursor class.

Regards,
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

Thanks as in my case just number 2 is possible a quite 'silly' question:
How do you normally check if Update was successful if the specified row 
did not exists.


e.g: UPDATE MyTable SET Account='MyAccountName' WHERE ID=50

If row 50 does not exists it does nothing and I seem not to get any return to 
know?

using pysqlite.

Thanks W.Braun



A. Pagaltzis wrote:

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 15:10]:
  

QUESTION: is there a better way to make this important
decision? using Sqlite



1. If you are changing the entire row on every update, you can
   simply use `INSERT OR REPLACE` (assuming there is a UNIQUE
   column) to always do this in a single query.

   See .

2. If you only want to update some of the columns, particularly
   if you are likely to update rows several times, you can use
   `UPDATE` to try and update, and if this did not affect any
   rows you do an `INSERT`.

In #1, you always get the job done with a single query. In #2,
you are usually done after the first but sometimes need a second.
Both are more efficient than your current approach, which always
runs two queries.

Regards,
  

Re: [sqlite] Helping with table definition?

[Gerry Snyder]

Cesar D. Rodas wrote:

Hello,

Merry Christmas for every one!

I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.
  
Here's some Tcl code that may help. It does more than look at the SQL, 
but that part may be of interest, too. It was written when my Tcl skills 
were even weaker than they are now, so it won't win any style points, 
but it has been working for years.


Merry Xmas,

Gerry


proc fillattachwin attachedname  {
# Global data filled up here:
#arrays (with first index $attachedname, second index starting at 1, 
matching line numbers) of:

#::GEB::tablename  (names of the tables)
#::GEB::tableindex ("inverse" of ::GEB::tablename e.g. 
$::GEB::tableindex(main.table1) = 1)

#::GEB::entrycount (# entries in each table),
#::GEB::fieldlist (lists of field names, indices starting at 0)
#::GEB::colnamelist (lists of column names, indices starting at 0)
#::GEB::coldeflist (lists of column def'ns--name+type, indices 
starting at 0)

#::GEB::fieldcount (numbers of fields)
#::GEB::tablesql (sql that created the table)

#  ::GEB::tablelist: table names (just names, not datatypes)
#::GEB::tablesarray($attachedname): array of tablelists
#::GEB::attachtablelist: $attachedname.$tablename -- (created in 
fillattachwins)


#::GEB::attachednames: list of attachednames, starting with main
# ::GEB::tcltablelist($attachedname): list of tables in that file 
with fields named tcl
 wm title .$attachedname "SQLite $::GEB::attachfilename($attachedname) 
as $attachedname"


 set tables [sq eval "select sql from $attachedname.sqlite_master"]
 set numtables 0
 set   ::GEB::tablelist ""
 set ::GEB::fieldcount($attachedname,max) 0
 foreach dbtable $tables {
   regsub -all { +} $dbtable { } dbtable
   if {[regexp -nocase "create table" $dbtable]} {
 incr numtables
 regsub -nocase {create table } $dbtable {} dbtable
 scan $dbtable {%[^(]%[^!]} currentname 
::GEB::tablesql($attachedname,$numtables)

 set currentname [string trim $currentname]
 set ::GEB::tablename($attachedname,$numtables) 
$attachedname.$currentname

 set ::GEB::tableindex($attachedname.$currentname) $numtables
 lappend   ::GEB::tablelist $::GEB::tablename($attachedname,$numtables)
 set tablefields($numtables) [string range 
$::GEB::tablesql($attachedname,$numtables) 1 end-1]

 regsub -all {, } $tablefields($numtables) {,} tablefields($numtables)
 set ::GEB::entrycount($attachedname,$numtables) "[sq eval "select 
count(*) from $::GEB::tablename($attachedname,$numtables)"]"

 set ::GEB::coldeflist($attachedname,$numtables) {}
 foreach fldnm  [split $tablefields($numtables) ,] {
   lappend ::GEB::coldeflist($attachedname,$numtables) [string trim 
$fldnm]

 }
 set ::GEB::fieldcount($attachedname,$numtables) [expr [regsub -all 
, $tablefields($numtables) {   } tablefields($numtables)] + 1] 
 if {$::GEB::fieldcount($attachedname,$numtables) > 
$::GEB::fieldcount($attachedname,max)} {
   set ::GEB::fieldcount($attachedname,max) 
$::GEB::fieldcount($attachedname,$numtables)

 }
   }
 }
 array set ::GEB::fieldlist [array get ::GEB::coldeflist]
3
 set ::GEB::colnamelist [firstwords ::GEB::coldeflist]
 set ::GEB::tablesarray($attachedname) $::GEB::tablelist
 set ::GEB::tcltablelist($attachedname) {}
 foreach maybetcltablename $::GEB::tablelist {
   if {[lsearch 
$::GEB::fieldlist($attachedname,$::GEB::tableindex($maybetcltablename)) 
tcl ] > -1} {

 lappend ::GEB::tcltablelist($attachedname) $maybetcltablename
   }
 }
# List the table names and other info in the main window
# While doing so, force a redraw
 $::GEB::tablestktable($attachedname) clear cache
 $::GEB::tablestktable($attachedname) configure -state normal -cols 
[expr $::GEB::fieldcount($attachedname,max) + 2] \
-rows [expr $numtables + 1] -command [list GEB::readtktable4file %i 
%r %c %s $attachedname] -cache 1 \
-drawmode single -bd 1 -ipadx 2 -titlerows 1 -titlecols 1 -anchor w 
-browsecmd [list showtableattachwin %S $attachedname] \

-padx [$::GEB::tablestktable($attachedname) cget -padx]
 $::GEB::tablestktable($attachedname) tag row headers 0
 $::GEB::tablestktable($attachedname) tag configure headers -anchor center
 $::GEB::tablestktable($attachedname) tag col entries 1
 $::GEB::tablestktable($attachedname) tag configure entries -anchor e
}

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way to check if new row or update existing one?

[Kees Nuyt]

On Tue, 25 Dec 2007 15:07:12 +0100, "Mag. Wilhelm Braun"
<[EMAIL PROTECTED]> wrote:

>hi,
>
>just a short question to speed up:
>
>as with any database one has quite often to decide if we *INSERT a NEW 
>row -- or -- UPDATE an existing row*
>
>
>at the moment I do a check select on an unique ID intege which is 
>resonable fast:
>
>code:
>
>
> SELECT ID FROM MyTable WHERE Account='MyAccountName' 
>
>
>
>I just fetch one row.
>
>if that get's a return I update otherwise I insert a new row.
>
>QUESTION: is there a better way to make this important decision? using 
>Sqlite
>
>regards W.Braun

INSERT OR REPLACE may work for you.
http://www.sqlite.org/lang_insert.html
http://www.sqlite.org/lang_replace.html
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Fastest way to check if new row or update existing one?

[A. Pagaltzis]

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 15:10]:
> QUESTION: is there a better way to make this important
> decision? using Sqlite

1. If you are changing the entire row on every update, you can
   simply use `INSERT OR REPLACE` (assuming there is a UNIQUE
   column) to always do this in a single query.

   See .

2. If you only want to update some of the columns, particularly
   if you are likely to update rows several times, you can use
   `UPDATE` to try and update, and if this did not affect any
   rows you do an `INSERT`.

In #1, you always get the job done with a single query. In #2,
you are usually done after the first but sometimes need a second.
Both are more efficient than your current approach, which always
runs two queries.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

hi,

just a short question to speed up:

as with any database one has quite often to decide if we *INSERT a NEW 
row -- or -- UPDATE an existing row*



at the moment I do a check select on an unique ID intege which is 
resonable fast:


   code:
   

SELECT ID FROM MyTable WHERE Account='MyAccountName' 


   

I just fetch one row.

if that get's a return I update otherwise I insert a new row.

QUESTION: is there a better way to make this important decision? using 
Sqlite


regards W.Braun

[sqlite] Sqlite - Latino America

[gerardo cabero]

Hello Community Sqlite
We Gerardo Antonio Cabero and Daniel Maldonado, administrators Sqlite
http://sqlite-latino.blogspot.com/ Latin America --
Sqlite Latin America?
A site for the communities of Sqlite Hablahispana, which are available
examples, documentation
We are the Latino community sqlite
. Gerardo Antonio and Daniel Maldonado

http://sqlite-latino.blogspot.com/

RE: [sqlite] Helping with table definition?

[RB Smissaert]

This is code I used a while ago. Don't use it anymore as I have a better
way to do this via my VB wrapper. There are some lines that deal with code
in other parts of my application, but I take it you can see that.
In case you didn't know this is VB(A).

Function GetSQLiteTableInfo2(strDB As String, _
 strTable As String, _
 Optional strSelect As String, _
 Optional strOmitFields As String, _
 Optional strAlias As String, _
 Optional strFields As String, _
 Optional strDataTypes As String, _
 Optional bCurrentConnection As Boolean) As
String()

 'will produce the table fields as an 0-based 1-D array
 'and make the strings:
 'field1,field2,field3 etc.
 'field1, field2, field3 etc.
 'datatype1,datatype2,datatype3 etc.
 '--
 Dim r As Long
 Dim c As Long
 Dim strSQL As String
 Dim arr
 Dim arr2
 Dim arr3
 Dim strAlias2 As String
 Dim arrOmitFields
 Dim bOmit As Boolean
 Dim bDoneFirst As Boolean
 Dim lRows As Long
 Dim strError As String
 Dim lDBHandle As Long

10   If Len(strAlias) > 0 Then
20  strAlias2 = strAlias & "."
30   End If

40   If Len(strOmitFields) > 0 Then
50  arrOmitFields = Split(strOmitFields, ",")
60  bOmit = True
70   End If

80   If bShowErrors Then
90  On Error GoTo 0
100  Else
110 On Error GoTo ERROROUT
120  End If

130  If bCurrentConnection = False Then
140 OpenDB strDB
150  End If

160  strSQL = "pragma table_info('" & strTable & "')"
170  arr = GetFromDB(strSQL, lRows, strError, strDB)

180  If lRows = -1 Then
190 GoTo ERROROUT
200  End If

210  If bOmit Then
220 For c = 0 To UBound(arr)
230If ValueIn1DArray(CStr(c + 1), arrOmitFields) = -1 Then
240   If bDoneFirst = False Then
250  strSelect = strAlias2 & arr(c, 1)
260  strFields = arr(c, 1)
270  strDataTypes = arr(c, 2)
280  bDoneFirst = True
290   Else
300  strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
310  strFields = strFields & "," & arr(c, 1)
320  strDataTypes = strDataTypes & "," & arr(c, 2)
330   End If
340End If
350 Next
360  Else
370 For c = 0 To UBound(arr)
380If c = 0 Then
390   strFields = arr(c, 1)
400   strSelect = strAlias2 & arr(c, 1)
410   strDataTypes = arr(c, 2)
420Else
430   strFields = strFields & "," & arr(c, 1)
440   strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
450   strDataTypes = strDataTypes & "," & arr(c, 2)
460End If
470 Next
480  End If

490  arr2 = Split(strFields, ",")

500  ReDim arr3(0 To UBound(arr2)) As String

510  For r = 0 To UBound(arr2)
520 arr3(r) = arr2(r)
530  Next

540  GetSQLiteTableInfo2 = arr3

550  Exit Function
ERROROUT:

560  ReDim arr2(0 To 6) As String
570  arr2(0) = "-1"
580  arr2(1) = CStr(Err.Number)
590  arr2(2) = Err.Description
600  arr2(3) = CStr(Erl)
610  arr2(4) = strTable
620  arr2(5) = strOmitFields
630  arr2(6) = strAlias

650  GetSQLiteTableInfo2 = arr2

End Function


Have a nice Christmas as well.


RBS


-Original Message-
From: Cesar D. Rodas [mailto:[EMAIL PROTECTED] 
Sent: 25 December 2007 01:35
To: sqlite-users@sqlite.org
Subject: [sqlite] Helping with table definition?

Hello,

Merry Christmas for every one!

I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.

I know that you can have the SQL definition of a table doing a "select *
from sqlite_master where type='table' ", but I need to parse SQL and
understand it.

Thanks in advance.

-- 
Best Regards

Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Helping with table definition?

[Ronny Dierckx]

Hi,

I think you should look at "PRAGMA table_info(table-name);" instead:

For each column in the named table, invoke the callback function once with 
information about that column, including the column name, data type, whether 
or not the column can be NULL, and the default value for the column.


Ronny



I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.

I know that you can have the SQL definition of a table doing a "select *
from sqlite_master where type='table' ", but I need to parse SQL and
understand it.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-