> 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 <math...@spoerr.org> 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<std::string> 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
