[sqlite] Severe performance degradation between 3.8.4.3 and 3.8.6

[Mario M. Westphal]

Hello,

After  re-compiling my Windows application (compiled with Visual C++ 2012, 32 
Bit application) with the latest SQLite version (3.8.6) I noticed a severely 
degraded performance with certain queries. The new version is 10 or more times 
slower than the previous build I used (3.8.4.3).

1.  The table schemas are:

CREATE TABLE stack (oid INTEGER PRIMARY KEY, rtype INTEGER, toid INTEGER, state 
INTEGER, color INTEGER);
CREATE INDEX idx_stack_toid_rtype ON stack(toid,rtype);


CREATE TABLE stack_elem (soid INTEGER, oid INTEGER, FOREIGN KEY(soid) 
REFERENCES stack(oid) ON DELETE CASCADE);
CREATE INDEX idx_stack_elem_oid ON stack_elem(oid);
CREATE INDEX idx_stack_elem_soid ON stack_elem(soid);
CREATE UNIQUE INDEX idx_stack_elem_soid_oid ON stack_elem(soid,oid);


2.  The queries to run are:

2.1

SELECT DISTINCT e.oid FROM stack_elem e 
INNER JOIN stack s ON e.soid = s.oid 
INNER JOIN _temptable _t ON e.oid = _t.oid  
INNER JOIN _temptable _t2 ON s.toid = _t2.oid 
WHERE s.state = ?1 AND s.toid <> e.oid 
UNION 
SELECT DISTINCT loid FROM rel_rel r 
INNER JOIN stack s ON r.moid = s.toid 
INNER JOIN _temptable _t ON s.toid = _t.oid WHERE s.rtype = ?3 AND s.state = ?1 
ORDER BY 1 

2.2

DELETE FROM stack WHERE oid IN (
SELECT stack.oid FROM stack
LEFT JOIN stack_elem ON stack_elem.soid = stack.oid AND stack_elem.oid 
<> stack.toid 
WHERE stack_elem.soid IS NULL)
AND stack.rtype =?1


Especially the 2.2 query has become an order of magnitude slower.
Reverting back to the 3.8.4.3 build immediately solves the problem and restores 
performance for the same database.

Thanks for looking into this. 
If more information or sample data is needed, let me know.


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

Re: [sqlite] How to control cpu usage while transaction in progress

[Keith Medcalf]

On Wednesday, 20 August, 2014, at 02:40, dd  asked:

>  Executing like query in a transaction. Query works with multiple tables
>and table has 1 million records on desktop clients. CPU goes high when
>transaction in progress.

>   Is there any way to control the CPU without adding sleep statements?

As suggested, the only way to artificially impose limits on execution is 
through the OS dispatcher thread priority mechanisms.  Execution will proceed 
at the maximum rate permitted by the first fully consumed resource.  This 
resource may be CPU, I/O, memory bandwidth, video bandwidth, etc.  In your case 
it would appear that the limit is set by CPU resources.  By lowering the thread 
priority of the executing thread you can request that the OS prioritize CPU 
allocation to other competing threads.

This will not reduce CPU usage -- it will only allow you to make this 
particular task have less priority than competing threads.  Total CPU usage 
will remain at 100%.

>   Is there any alternative solution for like queries? (for ex: delete *
>from emp where empname like "%a")

Alternative solution for what?  Looking through a table for all rows where the 
last character of a column is an 'a' character requires visiting every row in 
turn and testing the value of the last character and there is nothing you can 
do about this fact.  

You could, for example, avoid the full table scan by adding a field to the 
table called emanpme and put in it the reversed value of the empname field when 
you populate the table (and on every update of the empname field), and create 
an index on that column.  You would then rephrase you query as "delete * from 
emp where emanpme like 'a%';" which would limit the visitation via a partial 
index scan.  This is, however, a solution that you implement at design time.




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

Re: [sqlite] Problem with Update Statement

[Hick Gunter]

Obviously the problem was caused by incorrectly cobbling together theSQLite 
statement.

AFAICT the original code produces

UPDATE RecordGrid SET 
LineNumber='',self_empty_info_gender_PRect=',,,'
 WHERE RecordGridID='

Which is clearly invalid (the RHS of the WHERE condition is not terminated), 
with extraneous text after the end of the value ignored ( no & operators to 
concatenate the strings).

This would have become clear if you had dumped the CommandText as previously 
suggested

-Ursprüngliche Nachricht-
Von: Wycliff Mabwai [mailto:wycliff.mab...@digitaldividedata.com]
Gesendet: Dienstag, 19. August 2014 20:26
An: sqlite-users
Betreff: Re: [sqlite] Problem with Update Statement

Solved the problem with parameterized queries as below, without parameterized 
queries the update statement doesn't work on SQLite.

...
SQLITEcmd200.CommandText = "UPDATE RecordGrid SET 
LineNumber=@LineNumber, 
self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE 
RecordGridID=@RecordGridID"

SQLITEcmd200.Parameters.AddWithValue("@LineNumber",reade300.GetInt32(11))

SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect",...
SQLITEcmd200.Parameters.AddWithValue("@RecordGridID",...

...

>> SQLITEcmd2.CommandText = "UPDATE RecordGrid SET
>> LineNumber='" & reade20.GetInt32(11) & "',self_empty_info_gender_PRect= '"
>> & IIf(reade20.IsDBNull(22), String.Empty, reade20.GetString(22)) &
>> "," & IIf(reade20.IsDBNull(23), String.Empty, reade20.GetString(23))
>> & "," & IIf(reade20.IsDBNull(24), String.Empty,
>> reade20.GetString(24)) & "," & IIf(reade20.IsDBNull(25),
>> String.Empty, reade20.GetString(25)) & "' WHERE RecordGridID='"chombo"'"




---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with Update Statement

[Wycliff Mabwai]

Solved the problem with parameterized queries as below, without
parameterized queries the update statement doesn't work on SQLite.

Private Sub ReplaceRGrid()
''On Error Resume Next

Dim SQLITEcons As New SQLite.SQLiteConnection
Dim SQLITEcmd As New SQLite.SQLiteCommand
Dim SQLITEcmd2 As New SQLite.SQLiteCommand
Dim SQLITEcmd200 As New SQLite.SQLiteCommand
connection.Open()
Dim cmd2 As New MySqlCommand("set net_write_timeout=9; set
net_read_timeout=9", connection) 'Setting tiimeout on mysqlServer
cmd2.ExecuteNonQuery()
cmd2.Dispose()
connection.Close()
SQLITEcons.ConnectionString = "Data
Source=172.16.0.14\Dexter14\44576\" & ComboBox1.Text & "\Data\" &
ComboBox1.Text & ".db3; Version=3;"

Dim query As String = "SELECT * FROM ancestry44576coods where
batch_no like '" & ComboBox1.Text & "' order by Image,section,orderKwa"
Dim cmd As New MySqlCommand(query, connection)
Dim reade As MySqlDataReader
connection.Open()
reade = cmd.ExecuteReader
While reade.Read
'Dim chombo As String = reade.GetString(10) &
reade.GetString(12) & reade.GetString(11)
'MsgBox(chombo)

Try
SQLITEcons.Open()
SQLITEcmd = SQLITEcons.CreateCommand()
'SQLITEcmd2 = SQLITEcons.CreateCommand()
SQLITEcmd.CommandText = "REPLACE INTO RecordGrid
(RecordGridID,ImageID,LineNumber) VALUES ('" & reade.GetInt32(10) &
reade.GetInt32(12) & reade.GetInt32(11) & "','" & reade.GetInt32(10) &
"','" & reade.GetInt32(11) & "')"


SQLITEcmd.CommandType = CommandType.Text
'SQLITEcmd2.CommandType = CommandType.Text
SQLITEcmd.ExecuteNonQuery()
' SQLITEcmd2.ExecuteNonQuery()
SQLITEcmd.Dispose()
'SQLITEcmd2.Dispose()
SQLITEcons.Close()
'SQLITEcons.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information)
connection.Close()
End Try


End While
reade.Close()
connection.Close()

Dim query300 As String = "SELECT * FROM ancestry44576coods where
batch_no like '" & ComboBox1.Text & "' order by Image,section,orderKwa"
Dim cmd300 As New MySqlCommand(query300, connection)
Dim reade300 As MySqlDataReader
connection.Open()
reade300 = cmd300.ExecuteReader
While reade300.Read

Try
SQLITEcons.Open()
SQLITEcmd200 = SQLITEcons.CreateCommand()
If reade300.GetString(21) = "TextBox1" Then
SQLITEcmd200.CommandText = "UPDATE RecordGrid SET
LineNumber=@LineNumber,
self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE
RecordGridID=@RecordGridID"
'Dim SQLITEcmd200 As New SQLiteCommand(query204,
SQLITEcons)
SQLITEcmd200.Parameters.AddWithValue("@LineNumber",
reade300.GetInt32(11))

SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect",
IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," &
IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," &
IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," &
IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)))
SQLITEcmd200.Parameters.AddWithValue("@RecordGridID",
reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11))
SQLITEcmd200.ExecuteNonQuery()
'SQLITEcmd200.Dispose()
ElseIf reade300.GetString(21) = "TextBox2" Then
SQLITEcmd200.CommandText = "UPDATE RecordGrid SET
LineNumber=@LineNumber,
self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE
RecordGridID=@RecordGridID"
'Dim SQLITEcmd200 As New SQLiteCommand(query204,
SQLITEcons)
SQLITEcmd200.Parameters.AddWithValue("@LineNumber",
reade300.GetInt32(11))

SQLITEcmd200.Parameters.AddWithValue("@self_empty_info_gender_PRect",
IIf(reade300.IsDBNull(22), String.Empty, reade300.GetString(22)) & "," &
IIf(reade300.IsDBNull(23), String.Empty, reade300.GetString(23)) & "," &
IIf(reade300.IsDBNull(24), String.Empty, reade300.GetString(24)) & "," &
IIf(reade300.IsDBNull(25), String.Empty, reade300.GetString(25)))
SQLITEcmd200.Parameters.AddWithValue("@RecordGridID",
reade300.GetString(10) & reade300.GetString(12) & reade300.GetString(11))
SQLITEcmd200.ExecuteNonQuery()
'SQLITEcmd200.Dispose()
ElseIf reade300.GetString(21) = "TextBox3" Then
SQLITEcmd200.CommandText = "UPDATE RecordGrid SET
LineNumber=@LineNumber,
self_empty_info_gender_PRect=@self_empty_info_gender_PRect WHERE
RecordGridID=@RecordGridID"
'Dim 

Re: [sqlite] Question about coalesce and data types

[Clemens Ladisch]

Martin Engelschalk wrote:
> create table TestTable (col_a numeric);
> insert into  TestTable (col_a) values (1);
>
> retrieve the row, as expected:
>
> select * from TestTable where col_a = '1';
>
> do not retrieve the row:
>
> select * from TestTable where coalesce(col_a, 5) = '1'
>
> Can someone please explain this to me or point me to some documentation?

The col_a column has numeric affinity, so the string '1' is converted
into a number.  

The return value of the function has NONE affinity, so no automatic
conversion happens.  


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

[sqlite] Question about coalesce and data types

[Martin Engelschalk]

Hello list,

I checked the coalesce function and observed the follwoing results:

I create a simple table with one column and one row:

create table TestTable (col_a numeric);
insert into  TestTable (col_a) values (1);
commit;

The following statements retrieve the row, as expected:

select * from TestTable where col_a = 1;
select * from TestTable where col_a = '1';

Now when introducing coalesce, the following statements also retrieve 
the row:


select * from TestTable where coalesce(col_a, 5) = 1
select * from TestTable where coalesce(col_a, '5') = 1

Bur the two next statements do not retrieve the row:

select * from TestTable where coalesce(col_a, 5) = '1'
select * from TestTable where coalesce(col_a, '5') = '1'

When using coalesce, it seems to matter what is right of the = sign in 
the where clause. When comparing directly with the column, this dows not 
matter.
The same effect can be observed if i replace the constant '1' to the 
right of the = with a bind variable that I bind with sqlite_bind_text.


Can someone please explain this to me or point me to some documentation?

Thank you
Martin

--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] How to control cpu usage while transaction in progress

[Hick Gunter]

SQLite is supposed to process queries as fast as possible.

Run your heavyweight queries in a dedicated thread and use your OS' way of 
prioritizing threads to lessen the "felt impact" on "interactive" threads (at 
the cost of increasing elapsed time).

-Ursprüngliche Nachricht-
Von: dd [mailto:durga.d...@gmail.com]
Gesendet: Mittwoch, 20. August 2014 10:40
An: General Discussion of SQLite Database
Betreff: [sqlite] How to control cpu usage while transaction in progress

Hi all,

  Executing like query in a transaction. Query works with multiple tables and 
table has 1 million records on desktop clients. CPU goes high when transaction 
in progress.

   Is there any way to control the CPU without adding sleep statements?

   Is there any alternative solution for like queries? (for ex: delete * from 
emp where empname like "%a")

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


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to control cpu usage while transaction in progress

[dd]

Hi all,

  Executing like query in a transaction. Query works with multiple tables
and table has 1 million records on desktop clients. CPU goes high when
transaction in progress.

   Is there any way to control the CPU without adding sleep statements?

   Is there any alternative solution for like queries? (for ex: delete *
from emp where empname like "%a")

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