[asterisk-bugs] [JIRA] (ASTERISK-24050) [patch] Improve AstDb I/O When Updating Rows

[Michael L. Young (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-24050?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=220847#comment-220847 ] 

Michael L. Young commented on ASTERISK-24050:
---------------------------------------------

Yep, I suck at sqlite3 it would seem.

Looks like sqlite3_prepare() does not handle multiple SQL statements.  It will only handle the first one if there are multiple ones present.  What is frustrating is that I was pretty sure I tested that updates were happening and now when I tested today I see that this is not happening like Josh pointed out due to the failing test.

I turned on tracing in sqlite3 and saw that the INSERT was the only statement being handled and not the UPDATE.

I have a patch to fix this regression and I have tested it... I am very sure I have tested it... been testing and testing it.... This better work now :)

> [patch] Improve AstDb I/O When Updating Rows
> --------------------------------------------
>
>                 Key: ASTERISK-24050
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-24050
>             Project: Asterisk
>          Issue Type: Improvement
>      Security Level: None
>          Components: Core/AstDB
>    Affects Versions: Feature Tracker
>            Reporter: Michael L. Young
>            Assignee: Michael L. Young
>            Severity: Minor
>         Attachments: astdb-insert-update-io-help_trunk_v2.diff
>
>
> When updating a row, we are currently doing an INSERT OR REPLACE INTO.  The downside to this is that the row is deleted if it exists and then a new row is inserted.  So, we are hitting the disk twice.  One for the deletion and one for the insertion.
> The proposed patch will attempt to do an INSERT INTO and if it fails because a row with that key exists, we will ignore that.  Then we will attempt to perform an UPDATE on the existing row.  If a record was INSERTED, the UPDATE statement will end up doing nothing.
> Some testing that was performed on an older server with older IDE storage and few peers:
> _before patch_
> Re-Register | INSERT OR REPLACE INTO | Avg. 404 ms
> Register | INSERT OR REPLACE INTO | Avg. 442 ms
> _after patch_
> Re-Register | INSERT OR IGNORE; UPDATE | Avg. 361.5 ms
> Register | INSERT OR IGNORE; UPDATE | Avg 419 ms



--
This message was sent by Atlassian JIRA
(v6.2#6252)