none
SQLite Async and any other performance tips RRS feed

  • Question

  • Hi
    Been using SQLite for a app I have created and getting on okay with it

    Just can't find much documentation relating to Async usage of it and wondered if any SQLite experts around here who can advise?

    I notice it has Async equivalents of commands such as ExecuteNonQuery, ExecuteReader, Open etc

    Basically my app is storing log details and will have potentially millions of entries over time, so any thing I can do to improve performance, make use of threading, async etc will all be very useful

    So any great pointers on here would be gratefully appreciated

    Can post code on how I have done bits if needed for comments on better ways to do it etc if needed - always willing to learn and take criticism

    Darren Rose

    Monday, September 4, 2017 9:01 PM

Answers

  • I wouldn't anticipate issues until you get into an environment where multiple updates from different users or processes occur concurrently. Under this scenario there will be locking at the database level with SQLite. The below link might address some of your concerns (see both #5 and #6):

    http://www.sqlite.org/faq.html#q5


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by wingers Sunday, September 10, 2017 7:25 PM
    Monday, September 4, 2017 10:20 PM
  • One thing to consider with async operations, although when done right your app will be responsive when performing many backend operations you may very well learn that it takes more time to perform those operations asynchronous in windows forms applications so experiment with this before committing to async. You may find some parts will benefit while other operations don't.

    If this was a web application then by all means use async.

    When experimenting consider iterator methods too.

    Lastly, make sure to optimize queries e.g. SELECT (field list) rather than SELECT * etc.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 5, 2017 2:16 PM
    Moderator

All replies

  • I wouldn't anticipate issues until you get into an environment where multiple updates from different users or processes occur concurrently. Under this scenario there will be locking at the database level with SQLite. The below link might address some of your concerns (see both #5 and #6):

    http://www.sqlite.org/faq.html#q5


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by wingers Sunday, September 10, 2017 7:25 PM
    Monday, September 4, 2017 10:20 PM
  • Hi wingers,

    By my search, I just find that using SQLite Async in UWP app or Windows store app, can you tell me that your application is vb.net or UWP or other?

    Here is the information about SQLite Async, please refer to:

    https://code.msdn.microsoft.com/windowsapps/Using-SQLite-Asynchronously-b8372137

    https://github.com/oysteinkrog/SQLite.Net-PCL

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 5, 2017 2:42 AM
    Moderator
  • Hi wingers,

    By my search, I just find that using SQLite Async in UWP app or Windows store app, can you tell me that your application is vb.net or UWP or other?

    Thanks, my app is a VB.net winform application

    Darren Rose

    Tuesday, September 5, 2017 8:58 AM
  • I wouldn't anticipate issues until you get into an environment where multiple updates from different users or processes occur concurrently. Under this scenario there will be locking at the database level with SQLite. The below link might address some of your concerns (see both #5 and #6):

    http://www.sqlite.org/faq.html#q5


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thanks Paul, will just be one app updating this database, so should never be concurrent users/processes

    Darren Rose

    Tuesday, September 5, 2017 1:32 PM
  • One thing to consider with async operations, although when done right your app will be responsive when performing many backend operations you may very well learn that it takes more time to perform those operations asynchronous in windows forms applications so experiment with this before committing to async. You may find some parts will benefit while other operations don't.

    If this was a web application then by all means use async.

    When experimenting consider iterator methods too.

    Lastly, make sure to optimize queries e.g. SELECT (field list) rather than SELECT * etc.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 5, 2017 2:16 PM
    Moderator
  • Thanks Karen

    Darren Rose

    Wednesday, September 6, 2017 7:56 PM
  • Hi wingers,

    If you have solved your issue now, please remember to close your thread by marking the helpful post as answer, or you can also share your solution here and mark it. It is beneficial to other community members who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 7, 2017 1:57 AM
    Moderator