Speed of "sqlrecord_Link" vs "sqlrecord_set" / "sqlrecord_cr

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
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

Post by trevordevore » Fri Oct 11, 2013 2:17 pm

[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?
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

trevordevore
VIP Livecode Opensource Backer
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

Post by trevordevore » Fri Oct 11, 2013 2:18 pm

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.
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

trevordevore
VIP Livecode Opensource Backer
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

Post by trevordevore » Fri Oct 11, 2013 2:18 pm

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

trevordevore
VIP Livecode Opensource Backer
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

Post by trevordevore » Fri Oct 11, 2013 2:18 pm

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

Post Reply

Return to “SQL Yoga”