Slingshot Alternative

1

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?

2

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

Andrew

1 Like
3

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)

4

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.

1 Like
5

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.

6

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

into one:
select Size,Model from Families where ID="+ID;

image
image.png1979×853 74 KB

1 Like
7

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

8

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

image
image.png2858×808 351 KB

2 Likes
9

Thanks, that was exactly the answer i needed

10

Hi guys,

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.

Boris

image
image847×528 148 KB

11

Here is a snip from MYSQL workbench.

image
image1270×746 202 KB

12

assume you mean columns, not tables

There are a few ways to achieve this:

  • Use an UPDATE query, not INSERT if you just want to update existing information (no new records)

  • Use an INSERT ON DUPLICATE KEY UPDATE query 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 INSERT query 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')
or
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