I use the Slingshot Package to query a SQLite Database. Slingshot hasn’t been updated for a few years. Is there a better (faster) package / method that can access SQLite?
In my experience, Slingshot still works very well, and is fast even with very large data sets
I’m not aware of any alternatives
If you are having performance problems, then Slingshot might not be the problem- I’d suggest looking at:
- data structure- is the data indexed ?
- query- particularly queries with joins. What kind of query is it ?i.e SELECT, UPDATE, INSERT etc
- location of data file- might not perform well from a network drive
- If it is a massive database with a lot of changes over time, a VACUUM might speed it up
Thank you for your response @Andrew_Hannell
My Database is indexed. Query is SELECT. My database is on my harddrive. I will look into VACUUM.
I think you are right - my database has less than 3000 rows. It seems to be the Dynamo Nodes that slow things down. Every time I run my routine I need to restart Revit. If I don’t there seems to be a massive lag - even modelling simple things isn’t possible after running the Graph.
I have attached my Graph. I think I need to convert a lot of it to Python to speed things up…0BV Data-Apply DB SQL.dyn (201.1 KB)
I’ll have a look at your graph later
but 3000 rows is nothing for a database- if it was millions, then issues such as indexing/vacuum would come into play.
At some point I need to start entering BIM information into my SQLite database for my Revit objects. I’m a bit nervous about the time it will take for my Graph to run to retrieve the BIM information.
It is a complex graph and I don’t fully understand it particularly without the model or the associated database
but I would suggest doing the heavy lifting within the query, rather than a series of nodes such as
List.FilterByBooleanMask, ReplaceByCondition or ==
Some SQL functions such as
CAST might be quicker than StringToNumber nodes and could be included in the query
Finally, you could combine 2 queries such as
select Size from Families where ID="+ID
select Model from Families where ID="+ID
select Size,Model from Families where ID="+ID;
How do you create an UPDATE command in Slingshot.
I downloaded SLINGSHOT and only INSERT INTO and SELECT FROM are available nodes.
I dont have DELETE or UPDATE
You use the relevant Slingshot “command” node and feed in a query
You probably want to match up model records with database records using a particular key- so you will need to use a ‘where’ clause in the query
For example- something like the elementID
update tblRoom set RoomName = 'Bathroom' where ElementID=940325
Thanks, that was exactly the answer i needed
Been trying the package and I think it is really good.
I’ve ran into 1 issue though:
When I insert data into MYSQL with the following command: “INSERT INTO electrical_prototype.boards_and_circuits(power_factor) VALUES (”+"’"+A+"’"+")"; the data loads into MYSQL as I expect it to, but it also populates the other tables with either NULL values, or if the column is set to be not NULL then with empty values. If I try to populate the “panel” column it will keep the 808 empty rows and start loading data from row number 809. Does anyone know why is it behaving this way?
Would be great to just load all the columns (around 12 lists) at once without going 1 column at a time, but I was not able to find a way to do that with this package.
Any help will be appreciated.
assume you mean columns, not tables
There are a few ways to achieve this:
INSERTif you just want to update existing information (no new records)
INSERT ON DUPLICATE KEY UPDATEquery if you have a mix of existing records that need to be updated and new records that need to be inserted
A quick hack with your
INSERTquery would be to use either a primary key (which will enforce unique values for just one column) or unique indexes if multiple columns. This would produce an error for non-unique records
Otherwise, it could be a query something like IF NOT EXISTS…
I don’t know what you mean:
It is a standard SQL query to INSERT multiple columns, either with or without specifying the column names. If without, they have to be in the right order.
INSERT INTO table VALUES (3, 'text', 17.00, 'more text')
INSERT INTO table (col1, col3, col2, col4) VALUES (3, 17.00, 'text', 'more text')
I’s probably easiest to test your query in workbench- once it works properly, replicate in Dynamo and check that the output from the code block node is exactly the same syntax