[This post is an archive from the old SQL Yoga forums]
I'm not sure how the various databases compare, but I am working with sqlite, and I need the operation of inserting data to be as fast as possible. My question is whether it is a lot slower to link data using "sqlrecord_Link" than it is to simply insert new records. In a test import of 4,000 handler references, inserting all the records took a fraction of a second, but adding 2-4 keywords per entry using "sqlrecord_Link" seemed to slow right down as the size of the database grew - taking over a second for each addition of keywords to a linked handler name.
is there a fundamental reason why link is significantly slower or is there likely to be some reason my code that I can optimise?
Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecord_cr
Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecord_cr
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecor
What sort of tests have you done to determine where the bottle-neck is?
When working with SQLite the first thing to check is whether you are wrapping the entire operation in a single transaction. Each time you write to SQLite a transaction is started if one isn't open and a journal file is created on disk. This can really slow things down if you are doing mass inserts.
Try using dbconn_beginTransaction and dbconn_commitTransaction before the loop that links things together and see if that speeds things up.
When working with SQLite the first thing to check is whether you are wrapping the entire operation in a single transaction. Each time you write to SQLite a transaction is started if one isn't open and a journal file is created on disk. This can really slow things down if you are doing mass inserts.
Try using dbconn_beginTransaction and dbconn_commitTransaction before the loop that links things together and see if that speeds things up.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecor
Yes - this is a major factor - it seems sqlYoga automatically wraps record modification in a transaction when you use a numerically indexed multi-RecordArray?
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecor
sqlrecord_create, sqlrecord_delete and sqlrecord_update do wrap the whole operation in a transaction as they may fire off multiple queries. sqlrecord_link/unlink work on single records so do not start/end a transaction.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder