Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Spoerr Mathias 
Hello Pavel,
 I now upgraded to 3.7.6.2 and it is much better now!
 Thank you very much for your help!
 Regards,
 Mathias
 On Mon 18/04/11 17:06 , "Pavel Ivanov" paiva...@gmail.com sent:
 OK, sorry I didn't tell you all possible word combinations to look
 for. It looks like release notes use "query planner" instead of
 "optimizer".
 Pavel
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Pavel Ivanov 
OK, sorry I didn't tell you all possible word combinations to look
for. It looks like release notes use "query planner" instead of
"optimizer".


Pavel


On Mon, Apr 18, 2011 at 10:09 AM, Spoerr Mathias  wrote:
> the last Optimizer enhancement was with 3.6.22
> http://www.sqlite.org/changes.html
>
> Thanks,
> Mathias
>
>
>
> On Mon 18/04/11 16:05 , "Pavel Ivanov" paiva...@gmail.com sent:
>
>> I checked
>> the SQLite release notes and I could not find changes for sqlite3_step.
>> Should I upgrade anyway?
>
> Did you notice in release notes something like "optimizer
> improvements"? That's what impacts the sqlite3_step performance.
>
>
> Pavel
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Spoerr Mathias 
the
last Optimizer enhancement was with 3.6.22
 http://www.sqlite.org/changes.html
 Thanks,
 Mathias
 On Mon 18/04/11 16:05 , "Pavel Ivanov" paiva...@gmail.com sent:
 > I checked
 > the SQLite release notes and I could not find changes for
sqlite3_step.
 > Should I upgrade anyway?
 Did you notice in release notes something like "optimizer
 improvements"? That's what impacts the sqlite3_step performance.
 Pavel
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Pavel Ivanov 
> I checked
> the SQLite release notes and I could not find changes for sqlite3_step.
> Should I upgrade anyway?

Did you notice in release notes something like "optimizer
improvements"? That's what impacts the sqlite3_step performance.


Pavel


On Mon, Apr 18, 2011 at 9:54 AM, Spoerr Mathias  wrote:
> Hello Pavel,
>
> thank you for your answer. SQLite Manager uses version 3.7.4, but I checked
> the SQLite release notes and I could not find changes for sqlite3_step.
> Should I upgrade anyway?
>
> Thanks,
> Mathias
>
>
>
> On Mon 18/04/11 15:49 , "Pavel Ivanov" paiva...@gmail.com sent:
>
>> Depending on the database size, it sometimes takes minutes to get a
>> result.
>> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
>> seconds.
>> 
>> I use SQLITE Version 3.6.22.
>
> Maybe SQLite Manager uses newer version of SQLite? Try to execute in
> it "SELECT sqlite_version()".
>
>
> Pavel
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Mathias Spoerr 
Hello Igor,

this is not the complete query - only the part which causes the problem. 
The complete query is:
SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface 
INNER JOIN interfaces ON devInterface.interfaces_int_id=interfaces.intf_id 
INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id 
INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id 
WHERE neighbor.l2_addr IN ( 
SELECT macAddress FROM interfaces 
INNER JOIN devInterface ON devInterface.interfaces_int_id=interfaces.intf_id

INNER JOIN device ON device.dev_id=devInterface.device_dev_id 
WHERE devInterface.device_dev_id=1 AND interfaces.l2l3 LIKE 'L2' AND
device.hwtype=2)

But when removing the 2nd part, the query time is the same. The second part
itself is very fast.

Regards,
Mathias

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Igor Tandetnik
Gesendet: Montag, 18. April 2011 15:55
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

Mathias Spoerr <math...@spoerr.org> wrote:
> I have problems with a specific query when using the "sqlite3_step"
> function:
> 
>SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface
> 
>INNER JOIN interfaces ON
> devInterface.interfaces_int_id=interfaces.intf_id
> 
>INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id
> 
>INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id

Why are you joining with all these tables, when the two fields you want to
select both belong to devInterface? Why not simply

SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface;

-- 
Igor Tandetnik

___
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] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Igor Tandetnik 
Mathias Spoerr  wrote:
> I have problems with a specific query when using the "sqlite3_step"
> function:
> 
>SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface
> 
>INNER JOIN interfaces ON
> devInterface.interfaces_int_id=interfaces.intf_id
> 
>INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id
> 
>INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id

Why are you joining with all these tables, when the two fields you want to 
select both belong to devInterface? Why not simply

SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface;

-- 
Igor Tandetnik

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

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Spoerr Mathias 
Hello Pavel,
 thank you for your answer. SQLite Manager uses version 3.7.4, but I
checked the SQLite release notes and I could not find changes for
sqlite3_step. Should I upgrade anyway?
 Thanks,
 Mathias
 On Mon 18/04/11 15:49 , "Pavel Ivanov" paiva...@gmail.com sent:
 > Depending on the database size, it sometimes takes minutes to get
a result.
 > But when using the Firefox plugin "SQLite Manager", it "only"
takes 1-2
 > seconds.
 > 
 > I use SQLITE Version 3.6.22.
 Maybe SQLite Manager uses newer version of SQLite? Try to execute in
 it "SELECT sqlite_version()".
 Pavel
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Pavel Ivanov 
> Depending on the database size, it sometimes takes minutes to get a result.
> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
> seconds.
> 
> I use SQLITE Version 3.6.22.

Maybe SQLite Manager uses newer version of SQLite? Try to execute in
it "SELECT sqlite_version()".


Pavel


On Mon, Apr 18, 2011 at 9:45 AM, Mathias Spoerr  wrote:
> Hello,
>
>
>
> I have problems with a specific query when using the "sqlite3_step"
> function:
>
>
>
>    SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface
>
>    INNER JOIN interfaces ON
> devInterface.interfaces_int_id=interfaces.intf_id
>
>    INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id
>
>    INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id
>
>
>
> I only posted the parts of the query which I identified to cause the
> problem.
>
>
>
> For querying the database, I use the following code:
>
> http://www.dreamincode.net/forums/topic/122300-sqlite-in-c/
>
>
>
> and when debugging it, it hangs at
>
>
>
>          while(true)
>
>          {
>
>     ---> result = sqlite3_step(statement);
>
>
>
>                if(result == SQLITE_ROW)
>
>                {
>
>                     std::vector values;
>
>                     ...
>
>                }
>
>                ...
>
>          }
>
>
>
> Depending on the database size, it sometimes takes minutes to get a result.
> But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
> seconds.
>
> In case the query takes several minutes, the "neighbor" and "nlink" tables
> have more than 150k entries.
>
>
>
>
>
> For buliding the database I used the Forward engineer feature of the MySQL
> Workbench tool, and modified the syntax to work for sqlite3:
>
>
>
> CREATE TABLE IF NOT EXISTS device(dev_id INTEGER PRIMARY KEY AUTOINCREMENT,
> type INT, hwtype INT, dataSource INT, hostname TEXT, sw_version TEXT,
> stpBridgeID TEXT, stpProtocol TEXT);
>
>
>
> CREATE TABLE IF NOT EXISTS interfaces(intf_id INTEGER PRIMARY KEY
> AUTOINCREMENT, intfName TEXT, intfType TEXT, phl INT, macAddress TEXT,
> ipAddress TEXT, subnetMask TEXT, duplex TEXT, speed TEXT, status TEXT,
> description TEXT, l2l3 TEXT, errLvl INT, loadLvl INT, channel_intf_id INT,
> vpc_id INT, CONSTRAINT fk_interfaces_interfaces1 FOREIGN KEY
> (channel_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE);
>
>
>
> CREATE TABLE IF NOT EXISTS devInterface (interfaces_int_id INT,
> device_dev_id INT, cdp_cdp_id INT, PRIMARY KEY (interfaces_int_id,
> device_dev_id, cdp_cdp_id), CONSTRAINT fk_dev_interface_interfaces1 FOREIGN
> KEY (interfaces_int_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE, CONSTRAINT fk_dev_interface_device1 FOREIGN KEY
> (device_dev_id) REFERENCES device (dev_id) ON DELETE CASCADE ON UPDATE
> CASCADE, CONSTRAINT fk_devInterface_cdp1 FOREIGN KEY (cdp_cdp_id) REFERENCES
> cdp (cdp_id) ON DELETE CASCADE ON UPDATE CASCADE);
>
>
>
> CREATE TABLE IF NOT EXISTS neighbor (neighbor_id INTEGER PRIMARY KEY
> AUTOINCREMENT, l2_addr TEXT NULL , l3_addr TEXT NULL);
>
>
>
> CREATE TABLE IF NOT EXISTS nlink (neighbor_neighbor_id INT,
> interfaces_intf_id INT, PRIMARY KEY (neighbor_neighbor_id,
> interfaces_intf_id), CONSTRAINT fk_table1_neighbor1  FOREIGN KEY
> (neighbor_neighbor_id ) REFERENCES neighbor (neighbor_id) ON DELETE CASCADE
> ON UPDATE CASCADE, CONSTRAINT fk_table1_interfaces1 FOREIGN KEY
> (interfaces_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
> UPDATE CASCADE);
>
>
>
> I use SQLITE Version 3.6.22.
>
>
>
>
>
> Thanks,
>
> mspoerr
>
>
>
>
>
> ___
> 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

[sqlite] Performance issues with sqlite3 sqlite3_step function

2011-04-18 Thread Mathias Spoerr 
Hello,

 

I have problems with a specific query when using the "sqlite3_step"
function:

 

SELECT DISTINCT interfaces_int_id,device_dev_id FROM devInterface 

INNER JOIN interfaces ON
devInterface.interfaces_int_id=interfaces.intf_id 

INNER JOIN nlink ON nlink.interfaces_intf_id=interfaces.intf_id 

INNER JOIN neighbor ON neighbor.neighbor_id=nlink.neighbor_neighbor_id 

 

I only posted the parts of the query which I identified to cause the
problem. 

 

For querying the database, I use the following code:

http://www.dreamincode.net/forums/topic/122300-sqlite-in-c/

 

and when debugging it, it hangs at

   

  while(true)

  {

 ---> result = sqlite3_step(statement);

 

if(result == SQLITE_ROW)

{

 std::vector values;

 ...

}

...

  }

 

Depending on the database size, it sometimes takes minutes to get a result.
But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2
seconds.

In case the query takes several minutes, the "neighbor" and "nlink" tables
have more than 150k entries.

 

 

For buliding the database I used the Forward engineer feature of the MySQL
Workbench tool, and modified the syntax to work for sqlite3:

 

CREATE TABLE IF NOT EXISTS device(dev_id INTEGER PRIMARY KEY AUTOINCREMENT,
type INT, hwtype INT, dataSource INT, hostname TEXT, sw_version TEXT,
stpBridgeID TEXT, stpProtocol TEXT);



CREATE TABLE IF NOT EXISTS interfaces(intf_id INTEGER PRIMARY KEY
AUTOINCREMENT, intfName TEXT, intfType TEXT, phl INT, macAddress TEXT,
ipAddress TEXT, subnetMask TEXT, duplex TEXT, speed TEXT, status TEXT,
description TEXT, l2l3 TEXT, errLvl INT, loadLvl INT, channel_intf_id INT,
vpc_id INT, CONSTRAINT fk_interfaces_interfaces1 FOREIGN KEY
(channel_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE);



CREATE TABLE IF NOT EXISTS devInterface (interfaces_int_id INT,
device_dev_id INT, cdp_cdp_id INT, PRIMARY KEY (interfaces_int_id,
device_dev_id, cdp_cdp_id), CONSTRAINT fk_dev_interface_interfaces1 FOREIGN
KEY (interfaces_int_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE, CONSTRAINT fk_dev_interface_device1 FOREIGN KEY
(device_dev_id) REFERENCES device (dev_id) ON DELETE CASCADE ON UPDATE
CASCADE, CONSTRAINT fk_devInterface_cdp1 FOREIGN KEY (cdp_cdp_id) REFERENCES
cdp (cdp_id) ON DELETE CASCADE ON UPDATE CASCADE);



CREATE TABLE IF NOT EXISTS neighbor (neighbor_id INTEGER PRIMARY KEY
AUTOINCREMENT, l2_addr TEXT NULL , l3_addr TEXT NULL);



CREATE TABLE IF NOT EXISTS nlink (neighbor_neighbor_id INT,
interfaces_intf_id INT, PRIMARY KEY (neighbor_neighbor_id,
interfaces_intf_id), CONSTRAINT fk_table1_neighbor1  FOREIGN KEY
(neighbor_neighbor_id ) REFERENCES neighbor (neighbor_id) ON DELETE CASCADE
ON UPDATE CASCADE, CONSTRAINT fk_table1_interfaces1 FOREIGN KEY
(interfaces_intf_id) REFERENCES interfaces (intf_id) ON DELETE CASCADE ON
UPDATE CASCADE);  

 

I use SQLITE Version 3.6.22.

 

 

Thanks,  

mspoerr

 

 

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