Page 1 of 2 12 LastLast
Results 1 to 40 of 45

Thread: [RESOLVED] Database for desktop application

Hybrid View

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Resolved [RESOLVED] Database for desktop application

    Hello Everyone,

    I am developing a windows application in VB.NET. This application will be a stock market application. Currently my application stores stock market data in text files.

    However reading and modifying those text files is a time consuming process. I am looking for a local database that can do a good job. A free one with small footprint is preferred.

    Which database should I use? It should be fast and compatible with .net. However I want it to support SQL so a no-sql db isn't for me. I never understood why would someone go with nosql db.

    Your suggestion will be really appreciated.

    Thank you,

    GR

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database for desktop application

    nosql is a non-relational database... it has its uses, especially when there are mass amounts of textual data. I think Facebook even runs off of a nosql-based system.

    At any rate, there's multiple choices (in no particular order)- Access, SQL Express, SQLLite, MySQL.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database for desktop application

    Smallest footprint would be SQLite or Access, which are file-based databases. I don't have any proof but suspect the former would have superior size (140TB I think) and performance if programmed properly.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    Thank you guys. I want one which is easy to use with VB.NET and fast. I am not good with databases so am wondering which would be approp. to store stock market data. Currently the text files go in range of 7 - 10 MBs. I want one where retrieving, searching and inserting data is fast.

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database for desktop application

    any will be faster than your textfiles. if you plan multiuser i'd recommend SQL Express. If you are already familiar with access or other MS Office apps, stay with access as a start. if you want a small footprint go for SQLite. there are pros and cons all the way, you need to tell more about your plans to give better advice

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database for desktop application

    They're all "Easy" to use with .NET ... you just need to make sure you have the correct connector for it, but that's an easy download and install.

    As for the size... if it's multiuser, then you're going to want someting service based, my SQL Express/SQL Server/MySQL ... if it's just one user then Access wouldn't be so bad, and comes with the added bonus you can "just use" MDB/ACCDB files right out of the gate with .NET.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Thank you very much for your feedback guys.

    I will be making a stock market charting software. In such software the data is ready by multiple parts of program at same time and the read data is shown in charts and other areas. The data is also updated frequently. However the data will be used locally only (Windows) and single user will access it. But multiple programs or parts of program will access/read the data at the same time. Records will also be added, deleted and modified.

    So which is feasible, SQLite, Sql Server Express LocalDB, Access, SQL Server Compact DB or ?.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    @digitalShaman - I did give my requirements above. So what do you guys recommend ?

    Thanks.

  9. #9
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database for desktop application

    i'd recommend a true SQL Express Installation (not local mdf file) + SQL Server Management Studio (both free Downloads from MS). You can have the database installed on your Client pc and if you later find yourself in the Position where more than one Client Needs to connect, then you already have a Kind of Server architecture and scaleability. getting the Installation up and running will initially take you a few hours of Research and try and error but after getting used to MSSQL you will prob. like it. there are Installation instructions somewhere on this Forum.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Quote Originally Posted by digitalShaman View Post
    i'd recommend a true SQL Express Installation (not local mdf file) + SQL Server Management Studio (both free Downloads from MS). You can have the database installed on your Client pc and if you later find yourself in the Position where more than one Client Needs to connect, then you already have a Kind of Server architecture and scaleability. getting the Installation up and running will initially take you a few hours of Research and try and error but after getting used to MSSQL you will prob. like it. there are Installation instructions somewhere on this Forum.
    I think you misunderstood. This is a desktop software that will be installed on individual systems. Only the local database will be read by the software. I don't want a big footprint so installing sql express is not recommended. That is why I was looking at local mdf or lighter database systems.

  11. #11
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    I think you misunderstood. This is a desktop software that will be installed on individual systems. Only the local database will be read by the software. I don't want a big footprint so installing sql express is not recommended. That is why I was looking at local mdf or lighter database systems.
    i understand. however if, at any Point in time the System grows and you need to go multiuser, then you are well prepared. Access and SQLite do work well in a small multiuser Environment but there are some limitations that sqlserver does not have and with sqlserver you are on a more "professional" side than with the others (that shall not mean that the others are not professional, especially SQLite).

    But now you said you do not want a big footprint. ok so if you think SQL express is not good for you then my next recommendation is SQLite. But also here you will Need some hrs to get it going.

  12. #12
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database for desktop application

    I would use SQLite in your case since it is single-user. However, if you know the user already has Access installed then you may as well use it.

  13. #13
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Database for desktop application

    you dont have to make the choice of database upfront
    just stress test the available choices
    make a simple program that retrieves data
    and a simple program that inputs data
    start the program that inputs data a few times (multitasking,not multithreading)
    and see what you need to do to let the program that retrieves data keep up
    do not put off till tomorrow what you can put off forever

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    I noticed that SQL Server Express was preferred or SQLite or MS Access. However SQL Server Compact or SQL Express LocalDB was not mentioned. Are they not good enough ?

    Let me try SQLite. As long as database is fast, support SQL statements and allows multi thread access I am good. Since here its a desktop product only one user will access it but multiple threads can access at one go.

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database for desktop application

    SQL Express and LocalDB are pretty much the same thing... the difference is in how/when the database attaches to the locally running SQL Express services... with SQL Express straight up, the db is there, attached and you can even open up SQL Server Management Studio (SSMS) and query/update/etc... LocalDB on the other hand only attaches when you open the connection - still requires a local defailt instance of SQL Express running.

    SQL Compact is ok, only requires minimal files for installation, but comes with some limitations... It's really basic and can be difficult to manage the database. Having worked with just about all of these DBMS, unless I already have a local install of SQL Server of some kind installed, for something like this, I'd probably reach for SQLite...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    Great !!! let me try SQLite then.

  17. #17
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database for desktop application

    this thread might help you getting started:
    http://www.vbforums.com/showthread.p...ghlight=sqlite

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Resolved Re: Database for desktop application

    Quote Originally Posted by digitalShaman View Post
    this thread might help you getting started:
    http://www.vbforums.com/showthread.p...ghlight=sqlite
    Thank you

  19. #19
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database for desktop application

    FYI, here are some potential gotchas of SQLite if you don't bother reading the documentation:
    1. It's dynamically typed so you can put pretty much any value into any column regardless of what you declared it. In other words, it may not give an error if you try to insert a word into a column declared as a number.
    2. Similarly, understand how it handles dates if you will have those. It will treat them as one of 3 data types depending on how you enter them so you need to be consistent if you want date comparisons to work.
    3. If you do multiple inserts, wrap them in a transaction. Otherwise you may be limited to about 60/second on a traditional platter hard drive. You likely won't notice this on an SSD.
    4. Make sure you include the Interop DLLs in the appropriate location when deploying. Generally, the program just seems to hang if you forget them.

    3 and 4 have gotten me before. I've seen others with issues from the first 2.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    Quote Originally Posted by topshot View Post
    FYI, here are some potential gotchas of SQLite if you don't bother reading the documentation:
    1. It's dynamically typed so you can put pretty much any value into any column regardless of what you declared it. In other words, it may not give an error if you try to insert a word into a column declared as a number.
    2. Similarly, understand how it handles dates if you will have those. It will treat them as one of 3 data types depending on how you enter them so you need to be consistent if you want date comparisons to work.
    3. If you do multiple inserts, wrap them in a transaction. Otherwise you may be limited to about 60/second on a traditional platter hard drive. You likely won't notice this on an SSD.
    4. Make sure you include the Interop DLLs in the appropriate location when deploying. Generally, the program just seems to hang if you forget them.

    3 and 4 have gotten me before. I've seen others with issues from the first 2.
    Thank you very much.

    I have figured out most functions. But retrieving data is a concern. For example : using transaction method as you mentioned in point 3 it inserted 10000 records in 0.1 second which is good. But reading that data is taking time.

    Code:
    While SQLReader.Read
    text = text & vbcrlf & sqlreader(0) & "," & sqlreader(1) & "," & sqlreader(2)
    End While
    How to read fast and store locally (in array) ?

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    ... using transaction method as you mentioned in point 3 it inserted 10000 records in 0.1 second which is good.
    Yep, SQLite can achieve about half a Mio inserts per second, when Binding (a Command-Object) is used.

    Quote Originally Posted by greatchap View Post
    But reading that data is taking time.

    Code:
    While SQLReader.Read
    text = text & vbcrlf & sqlreader(0) & "," & sqlreader(1) & "," & sqlreader(2)
    End While
    How to read fast and store locally (in array) ?
    I'm don't know much about the .NET-wrapper for SQLite - but shouldn't it support the delivery
    of a "a queried Set" also in a kind of "Array-like usable Container" (a "DataSet" or something)?

    FWIW (since you mentioned a "stock-market-scenario") - here's some VB6-Code, which is able
    to import the (quite weird) MetaStock-Dataformat from Files(Folders) into an SQLite-InMemory-DB
    (and since the import-performance ist that good - it does that for each MetaStock-Folder on the fly,
    although the Import-Routines could also be made persistent with a real SQLite-FileDB of course).

    Not sure, whether that is of any help to you - but the App is small (about 110 lines of Form-Code) -
    and maybe it gives some hints about "structuring stuff" - or the way the queries are constructed
    will give you some more ideas, what's possible with SQLite-SQL... (which is quite powerful, supporting
    even CTEs).

    Here's a Link to the Demo-Code: http://vbRichClient.com/Downloads/MetaStock.zip

    And here a ScreenShot of the little App (which includes simple Charting as well).


    HTH

    Olaf

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Exclamation Re: Database for desktop application

    Thanks Schmidt, will check out your code but I dont think I have VB6 installed as of now.

    However there are some surprising facts :

    A SQLite database with 300000 has a size of 21.4 MB which same number of records in text file has a size of 15 MB. I thought database will store efficiently.

    Secondly reading that many records from text file to array using IO.File.ReadAllLines takes 0.25 seconds which reading all that through SQLite data read adapter is taking forever.

    Whats doing on? I am planning to shift to database for faster access but here if more records are loaded then things slow down.

  23. #23
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    A SQLite database with 300000 has a size of 21.4 MB which same number of records in text file has a size of 15 MB. I thought database will store efficiently.
    I'd not consider that terribly inefficient (from your numbers above, your Text-Lines will have about 50 Chars on average,
    probably denoting numbers as ...,1.234,2.456,... - and if you import that into SQLite-Double-Fields, you will have a
    bit of overhead (since a Double takes 8Bytes - whereas your Textbased-Numbers need on average 6Chars per Field ",2.456"
    because their precision isn't very high). Also not sure about your page-size - and whether you already did a compacting of your DB.

    Quote Originally Posted by greatchap View Post
    Secondly reading that many records from text file to array using IO.File.ReadAllLines takes 0.25 seconds which reading all that through SQLite data read adapter is taking forever.

    Whats doing on? I am planning to shift to database for faster access but here if more records are loaded then things slow down.
    I'm the author of the COM-wrapper for SQLite - and I can ensure you that, when something is slow -
    it's not SQLite which is slowing things down...

    Maybe the .NET-wrapper is not using the most effective strategies for filling larger Container-structures -
    but instead of the inefficient String-Concatenation-Loop in your previous post - why not checkout the DataSet-filling I've already
    mentioned in my previous post - this might already speed up the read-direction in a satisfying way.


    Code:
    'example from the docu for the .NET-wrapper
    Dim myDataSet As DataSet = New DataSet
    Dim myAdapter As SQLiteDataAdapter = New SQLiteDataAdapter("SELECT DeptNo, DName FROM Dept", sqConnection)
        myAdapter.Fill(myDataSet, "Departments")
    HTH

    Olaf
    Last edited by Schmidt; Dec 17th, 2016 at 05:38 AM.

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Thank you.

    Your code did speed up things and the whole data was loaded in dataset in 2.5 seconds.

    However what is the code to compact the database. I couldn't find it. Plus do you recommend having double type fields to be okay or is there anything lighter.

    When I create database table then i use "real" in SQL statement when create table columns. And in parameter DbType I use double. What are your thoughts on that ?

    In .NET I have to use the .net wrapper right ? I want the whole thing to be fast and efficient.

  25. #25
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    Your code did speed up things and the whole data was loaded in dataset in 2.5 seconds.

    However what is the code to compact the database. I couldn't find it.
    SQLite is quite well-documented on sqlite.org.
    Besides that, it is often the first tool of choice when it comes to App-DBs (in other languages
    than the MS-ones) - so the Web is full of answered questions to anything SQLite-related.

    Also, the SQLite-Devs are aware of that fact (its usage in a multitude of languages) -
    and thus make many "actions" available over the plain "SQL-Execute-interface"
    (one can even bind Dll-Extensions for SQLite dynamically over a simple .Execute "Some SQL")

    So, compacting a DB (once, when you have exclusive usage) can be done over a simple:
    Cnn.Execute "Vacuum"

    Another useful interface (available with normal SQL-Execute-Strings) is the "pragmas" -
    here for example the pragma for "auto-vaccum"):
    https://www.sqlite.org/pragma.html#pragma_auto_vacuum

    Many (most) pragmas will be persisted in the DB-File itself - and often need to be called at
    "DB-creation-time" - but then will influence (tune) the behaviour of that specific DB-File,
    when it is handled by the SQLite-Dll.

    Quote Originally Posted by greatchap View Post
    Plus do you recommend having double type fields to be okay or is there anything lighter.
    No - SQLite stores real-numbers always in the 8-byte IEEE format (as "Doubles") -
    if you want to use something "lighter", you could multiply with e.g. 1000 (for 3 decimal-places)
    or with 10000 (for 4) - and then store your numbers as Integers (SQLite stores Integers
    more efficiently with a dynamic Integer-Type, which goes up to 64Bit signed automatically).

    But that would require you, to adjust the math-expressions in your SQL accordingly (in case
    you stored integers in "promille" or "a thenth of a promille") - not sure if you want to go there,
    just to have a 20-30% smaller DB-File.

    Quote Originally Posted by greatchap View Post
    In .NET I have to use the .net wrapper right ? I want the whole thing to be fast and efficient.
    You could use e.g. the COM-Wrapper in .NET as well (and I'm sure it would be able to fill your
    large 300000 records-table into a cRecordset-Container in about a tenth of your 2.5seconds) -
    but the wrapper is written in VB6 - and thus the COM-Dll would be usable in .NET only for 32Bit-
    x86 compile-targets - whereas the .NET-wrapper would be usable also for 64Bit-ones.

    Also - once you have your "Raw-Data" in such large a Table - you wouldn't want to select
    "the full table" of 300000 (or perhaps more in the long run) records from it, anytime you need
    something in your App.
    Instead use appropriate Where- and Grouping-Clauses in your queries, calculating Sums/Averages
    and stuff on your Raw-Data - then returning only the e.g. 1000 Rows you need for a nice charting-
    visualization - not more.
    In that mode, most of the work is done "inside SQLite" - and the slower performance of the wrapper
    (whilst storing resultsets in a container) will become negligible.

    Olaf

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Thank a lot Schmidt.

    I have understood a few things. For example : the database is created okay and there is no point tampering with fields just to reduce size by 20% or so. Plus I was able to execute vacuum statement in sql and reduce size of database.

    I think I would stick to .net wrapper because as you said before I will be pulling only a certain number of records even if the database has loads of records. So the part is also fine.

    I did go over the documentation but there are some areas where I was either confused or did not find (for .net). For example: inserting dates. Whether I should insert data in specific format (mm/dd/yy or dd/mm/yy or yyyy/mm/dd etc) or use the system current format to insert it.

    Plus so far most interactions with the database is being done through SQL Statements. But what parameters can I pass when loading or creating database for first time. And how to use pragma statements in .net etc is something I couldn't figure out.

  27. #27
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    I did go over the documentation but there are some areas where I was either confused or did not find (for .net). For example: inserting dates. Whether I should insert data in specific format (mm/dd/yy or dd/mm/yy or yyyy/mm/dd etc) or use the system current format to insert it.
    SQLites Date- and Time functions support Date-Values in three formats:
    - Text (as ISO-Date in format 'yyyy-mm-dd hh:nn:ss')
    - Real (as Double, as 'Julian day'-numbers)
    - Integer (the seconds as Unix-Time)
    https://www.sqlite.org/lang_datefunc.html

    A wrapper is free to choose any of these Field-Types, to be compatible with SQLites built-in SQL-DateFunctions -
    but AFAIK the .NET-wrapper is supporting .NETs "Ticks"-format on the outside (which is incompatible with SQLite-functions).
    In my COM-wrapper I choose to go with storing Dates as ISO-Strings (in the DB) - compatible with SQLites date-functions - but autoconverting
    them at the wrapper-boundaries (when retrieving Dates - or filling them into Recordsets or CommandObjects) between VB(A)-OleDates and the ISO-Text-represenation.

    Quote Originally Posted by greatchap View Post
    Plus so far most interactions with the database is being done through SQL Statements. But what parameters can I pass when loading or creating database for first time. And how to use pragma statements in .net etc is something I couldn't figure out.
    As said, I'm not that familiar with the .NET-wrapper - but Pragmas are Read/Write values...

    Here a sequence, how I would write- and then read a Pragma
    (e.g. to set a larger "power-of-two"-based pagesize for a DB - making it persistent for that DB at creation-time):

    Code:
    Dim Cnn As cConnection
    Set Cnn = New_c.Connection("c:\temp\SomeNew.db", DBCreateNewFileDB)
        Cnn.Execute "pragma page_size=16384" 'write-direction, setting a page-size larger than the 4096-default - useful for larger DBs > 500MB
        Debug.Print Cnn.OpenRecordset("pragma page_size")(0) 'read-direction
    So, dealing with pragmas involves relatively simple to build strings - in write direction you
    put them in, where normally your "Insert", or "Update" CommandStrings go - and in read-direction
    you pass the Strings, where you normally place your "Select..."-SQL-String.

    HTH

    Olaf

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: Database for desktop application

    Schmidt: thank you for answering so many questions.

    Actually I am just trying to figure out the best / most efficient way to read/write records in SQLite DB.

    If I store datetime as string will it have an impact on performance or should I store datetime as the way it is now. If I store date as string I will have to re-convert it to datetime format of .net when reading and storing in variable.

    I think I should check out some samples but something are not easy to fine in doc.

    Code:
    sql_con = new SQLiteConnection
    	("Data Source=DemoT.db;Version=3;New=False;Compress=True;");
    In the above I do not know what more values can be passed or not. Couldnt find it in .net help doc.

  29. #29
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    If I store datetime as string will it have an impact on performance or should I store datetime as the way it is now. If I store date as string I will have to re-convert it to datetime format of .net when reading and storing in variable.
    Interesting thought. I didn't test it since every ounce of performance isn't needed for what I'm doing so I was very determined to be exact and, thus, I do finally pass in a string in the required format when doing an insert (e.g., pRunDate.ToString("yyyy-MM-dd") ). I doubt it is required though. I don't do anything special when selecting though, just fill a DGV. I have a .Net Date variable that is set from a DataTable value (e.g., gRunDate = dtInbound.Rows(0).Item("rundate") ).

    Edited to add: in my SQLite tables, the fields are declared as Date (which doesn't truly exist as we discussed, but maybe that also helps the .Net data adapter).

  30. #30
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Database for desktop application

    If I store datetime as string will it have an impact on performance or should I store datetime as the way it is now. If I store date as string I will have to re-convert it to datetime format of .net when reading and storing in variable.
    you should not really care about this as the engine does.
    you should use parameterized queries and pass in a date datatype. sqlite will finally store it as string and make sure you get the best performance when sorting/comparing etc. if you declare as varchar and pass in a date string, you only undermine any performance tuning that was done by the database engine developers.

    i also saw your post with string concatening somewhere along the lines. Don't do it! get a datatable and work with that! i bet that most of the performance drag you saw came from that and not from the sqlite engine.

  31. #31

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Quote Originally Posted by digitalShaman View Post
    you should not really care about this as the engine does.
    you should use parameterized queries and pass in a date datatype. sqlite will finally store it as string and make sure you get the best performance when sorting/comparing etc. if you declare as varchar and pass in a date string, you only undermine any performance tuning that was done by the database engine developers.

    i also saw your post with string concatening somewhere along the lines. Don't do it! get a datatable and work with that! i bet that most of the performance drag you saw came from that and not from the sqlite engine.
    String concatenation was a mistake and that was slowing things down. I asked question about data because of two reasons. If I use Now() to get current date and insert it in database then I don't know how is the database storing because every pc can have different default date format e.g. US (mm/dd/yyyy) or UK (dd/mm/yyyy) so on. Plus when I have read the data and wish to store it in my own date variable then I wanted to know if I need to know how SQLite is storing or there is no need and it will happen automatically.

  32. #32
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    String concatenation was a mistake and that was slowing things down. I asked question about data because of two reasons. If I use Now() to get current date and insert it in database then I don't know how is the database storing because every pc can have different default date format e.g. US (mm/dd/yyyy) or UK (dd/mm/yyyy) so on. Plus when I have read the data and wish to store it in my own date variable then I wanted to know if I need to know how SQLite is storing or there is no need and it will happen automatically.
    If you treat a date as a date then it doesn't matter what format the PC is using. the format is for DISPLAYING the data... it is not how it is stored at all... either in memory or in the db. The format of the date is just that, the format, it is not the value itself. Think about a number ... 1234 for instance... the VALUE is 1234 ... but I might format the display as $1,234 ... but the database still only stored 1234.
    Dates are the same... in fact, in most DBMSs they are stored as some kind of number... it's only when the date is displayed that it takes on what ever format it needs to.

    That said ... if you store dates as string (gives me the willies) ... use a consistent format that cannot be confused with any other format.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  33. #33

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Cool Re: Database for desktop application

    Quote Originally Posted by techgnome View Post
    If you treat a date as a date then it doesn't matter what format the PC is using. the format is for DISPLAYING the data... it is not how it is stored at all... either in memory or in the db. The format of the date is just that, the format, it is not the value itself. Think about a number ... 1234 for instance... the VALUE is 1234 ... but I might format the display as $1,234 ... but the database still only stored 1234.
    Dates are the same... in fact, in most DBMSs they are stored as some kind of number... it's only when the date is displayed that it takes on what ever format it needs to.

    That said ... if you store dates as string (gives me the willies) ... use a consistent format that cannot be confused with any other format.

    -tg
    Don't worry, I am not a bad developer. I store date as date and so on...In-fact I am trying hard to optimize my code and make it efficient. I got the date part and have tested is as well.

  34. #34
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    If I use Now() to get current date and insert it in database
    you are aware that now() returns a date and a time
    if you dont need a time part, best not to use it
    if you do need a time part, be very carefull if you query for dates, esspecialy if using the 'between' operator
    could you show a select query that querys for these date fields
    do not put off till tomorrow what you can put off forever

  35. #35

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Resolved Re: Database for desktop application

    Quote Originally Posted by IkkeEnGij View Post
    you are aware that now() returns a date and a time
    if you dont need a time part, best not to use it
    if you do need a time part, be very carefull if you query for dates, esspecialy if using the 'between' operator
    could you show a select query that querys for these date fields
    I do need the time field. I think I should be careful when using select statements with time fields present. ;-)

  36. #36
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Database for desktop application

    Quote Originally Posted by greatchap View Post
    I do need the time field
    now i am confused
    do you have a date field and a time field ?
    or only a date/time field ?
    do not put off till tomorrow what you can put off forever

  37. #37

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post Re: Database for desktop application

    Quote Originally Posted by IkkeEnGij View Post
    now i am confused
    do you have a date field and a time field ?
    or only a date/time field ?
    I need date and time both. So either I have one date field and one time field or I have one field which is date and time both. I think latter will be better.

  38. #38
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Database for desktop application

    i think the following by Olaf will interest you
    it clearly shows the possible pitfalls of using now() to fill date/time fields
    http://www.vbforums.com/showthread.p...wo-datepickers
    using now() does not have to be a problem, one just has to be aware of the possible problems
    do not put off till tomorrow what you can put off forever

  39. #39

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Question Re: Database for desktop application

    Quote Originally Posted by IkkeEnGij View Post
    i think the following by Olaf will interest you
    it clearly shows the possible pitfalls of using now() to fill date/time fields
    http://www.vbforums.com/showthread.p...wo-datepickers
    using now() does not have to be a problem, one just has to be aware of the possible problems
    You are correct searching date in database is tricky. For example the code below is not working.

    Code:
    Dim dt as date = Now
    SQLCommand.commandtext = "Select * from db where dte between " & dt
    When executed it gives error. Even when I insert # before and after date it still gives error. How do I do a date search ? (Using .net wrapper and not com object)

  40. #40

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Thumbs up Re: Database for desktop application

    The following code worked:

    Code:
    Dim dt as date = now
    SQLCommand.commandtext = "Select * from db where dte >= datetime('" & format(dt,"yyyy-MM-dd HH:mm:ss") & "')
    Basically when using select query I have to convert the date/datetime.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width