[sqlite] Where to create index, a doubt ...
Hi list, I was told that indexing greatly enhance the speed of SQlite, however, i was cautioned that it comes in as an overhead, in terms of increased size of the database.The Database, that I am handling, isn't larger. Also, I was told, that one should watch, where to create the indexes. I have created index after creating the table in the database.Should I create the index, at the end, when all the data gets itself into the database ? Also it would be nice to know,if the inserts are slower when we are adding data to the table with indexes already applied to it ? General guidelines about indexing will be greatly appreciated. Thanks in advance, Atul -- View this message in context: http://www.nabble.com/Where-to-create-index%2C-a-doubt-...-tp25693438p25693438.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster inserts in SQlite ...
Hi Nicolas, My application is windows based. I did set the pragma Page Size = 4096 as suggested by Alexey. but, it hardy made any difference to the speed. I am abandoning the multi-threading idea, as the other components used in my application aren't thread safe. Also, i was wondering, if apart from Begin Transaction/End Transaction, is there any other method for batched inserts in SQlite ? Can i use triggers for this ? If one has to use ZFS in tandem with Windows OS,(mine is Vista/Xp),how is this achieved ? Thanks for awesome support, Regards, Atul. Nicolas Williams wrote: > > On Mon, Sep 21, 2009 at 03:37:02PM -0400, Pavel Ivanov wrote: >> > Have you any IO operations? As result you have dependence of page >> > size. >> >> Though your performance most probably will not depend on these >> operations because they will be executed at some random times by OS. >> And they will be collected to have multiple blocks in one operation >> anyway... >> I don't have good knowledge of how disk cache works in kernel to say >> if it will be beneficiary to send data there in chunks equal to blocks >> on disk as opposed to chunks of any arbitrary size... > > It is well-known that matching DB page size and filesystem record size > is important for improving performance. > > SQLite3 supports power-of-2 page sizes from 512 to 32KB. > > Most filesystems use 512, 4096 or 8192 byte blocks. SQLite3's default > pagesize is 1024 bytes. > > In the case of ZFS the fs recordsize is variable up to 128KB, and can be > tuned per-dataset. You should set the recordsize to 32KB for ZFS > datasets containing SQLite3 DBs, and you should set the SQLite3 pagesize > to 32KB. > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Faster-inserts-in-SQlite-...-tp25530282p25661817.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to use sqlite 3.6.18 in visual studio 2008 ?
Hi, I want a statically linked SQlite Library connected to my application.I am using Visual Studio 2008.I created my own version using the three files as sqlite3.h,sqlite3.c and sqlite3ext.h.It does creates a Lib file,and i am getting it linked in to my application, however when i try to make any call to Sqlite from my application, i get a crash in sqlite3_mutex_enter function. Is there any preprocesser that i need to set, to support the multithreading(is guess sqlite3_mutex has to do something with multithreading ...)? I had the same thing working for my previous sqlite version (3_6_13), although in that vcproj, i was adding in the entire source code under source files.(sqlite-source-3.6.13.zip) Is there anything that i missed ? Regards, Atul -- View this message in context: http://www.nabble.com/how-to-use-sqlite-3.6.18-in-visual-studio-2008---tp25530703p25530703.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to compile SQlite 3.3.18 in visual studio 2008 ?
Hi, I want a statically linked SQlite Library connected to my application.I am using Visual Studio 2008.I created my own version using the three files as sqlite3.h,sqlite3.c and sqlite3ext.h.It does creates a Lib file,and i am getting it linked in to my application, however when i try to make any call to Sqlite from my application, i get a crash in sqlite3_mutex_enter function. I had the same thing working for my previous sqlite version (3_3_13), although in that vcproj, i was adding in the entire source code under source files.(sqlite-source-3.3.13.zip) Is there anything that i missed ? Regards, Atul -- View this message in context: http://www.nabble.com/how-to-compile-SQlite-3.3.18-in-visual-studio-2008---tp25530675p25530675.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faster inserts in SQlite ...
Hi List, I have been using SQlite to store the information from my application.However,when i did a time analysis, I found that the sqlite contributes to Half of the total time.The data that i am storing in the Database using SQlite is retrieved on a per entity basis. In order, to enhance the speed, i had set pragmas, 1. PRAGMA journal_mode = MEMORY; 2. PRAGMA synchronous = OFF; 3. PRAGMA temp_store = MEMORY; 4. PRAGMA count_changes = OFF; 5. PRAGMA cache_size = 12000; i also avoid the update calls, and i do all the insertion in between Begin Transaction and End Transaction.I was planning to call the insertion calls to SQLite in a separate thread.I am using the SQLite Version 3.3.13.When i dig in through documentation, the latest version was 3.3.18.FAQ says that the SQlite is Thread safe. Please suggest me if I should work in this direction. Thanks in Advance, Atul -- View this message in context: http://www.nabble.com/Faster-inserts-in-SQlite-...-tp25530282p25530282.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] STL and SQLite
Are you planning on querying for values through SQLITE or are you just using it for persistent storage? If it's just for storage then it's really easy. Thanks Teg-3, I am planning to use it for storage Regards, Atul -- View this message in context: http://www.nabble.com/STL-and-SQLite-tp25340733p25348961.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] STL and SQLite
It would be great if i can refer to any example for this Regards, Atul Michal Seliga wrote: > > Atul_Vaidya wrote: >> Hi, >> How to store a multimap in SQlite database ? >> My requirement is that i want to store a multimap in a column >> of >> the SQlite table >> Regards, >> Atul > > but you have to use some additional > encoding (base64, bintohex, uuencode/uudecode, whatever you want) to make > sure > that stored text is pure ASCII and content won't be damaged > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/STL-and-SQLite-tp25340733p25341643.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] STL and SQLite
Hi, How to store a multimap in SQlite database ? My requirement is that i want to store a multimap in a column of the SQlite table Regards, Atul -- View this message in context: http://www.nabble.com/STL-and-SQLite-tp25340733p25340733.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_exec fails on arm
Any help on this issue will be welcome, hi, Can you please specify what statement you are passing in to the sqlite3_exec please ? Atul -- View this message in context: http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25340486.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Making Update fast
Did you tried using the pragmas refer sqlite documentation for pragmas http://www.sqlite.org/pragma.html Atul souvik.datta wrote: > > > > Update set Flag=1 where Filename=; > > The updates taking huge amount of time. I tried to wrap these updates > within transactions (50 updates within one transaction) but that is > actually causing the a Read thread (the one which is trying to read the > Metadata info ) to starve. > If I am not using the transaction , the read thread response is much > better but then the updates are running real slow. > > Can some suggest how I can improve this? > > Thanks and Regards, > Souvik > > Please do not print this email unless it is absolutely necessary. > > The information contained in this electronic message and any attachments > to this message are intended for the exclusive use of the addressee(s) and > may contain proprietary, confidential or privileged information. If you > are not the intended recipient, you should not disseminate, distribute or > copy this e-mail. Please notify the sender immediately and destroy all > copies of this message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient > should check this email and any attachments for the presence of viruses. > The company accepts no liability for any damage caused by any virus > transmitted by this email. > > www.wipro.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Making-Update-fast-tp25269409p25271497.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Hi List, I want to make this query faster, for this, i created indexes for Entity_Id and grpuid and also used a pragmas : PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; PRAGMA count_changes = OFF; PRAGMA cache_size = 12000; I also do Begin Transaction and End Transaction, but still i havn't achieved the speed the way i like. My Questions : 1. Am i missing out on anything? 2. Entity_ID is a Primary Integer Key, so is there any need to create an Index for it,as per my understanding,Indexing increases the database size and also the INSERT gets slower Thanks in Advance, Atul Igor Tandetnik wrote: > > Atul_Vaidya wrote: >> Hi, I have three tables, >> 1. Table Entity_xdata containing following fields >>> Entity_id|Layer|grpuid| >> >> 2. Table, group_xdata_pipe containing following fields >>> grpuid|LNV| >> >> 3. Table group_id_vs_reggappname containing following fields >>> grpuid|reggappname| >> >> Now, I need to Fire a query to SQlite where in I get the list of all >> the distinct LNVs. Currently I achieve it by following two query >> commands as follows >> SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN >> group_id_vs_regappname ON(Entity_xdata.grpuid = >> group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = >> 'CPD1') >> I get the grpuids using this command and then i use the grpuids that >> i get from this query, as an input to my next query, something like >> this ... >> >> SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view >> FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY >> Line_Number_View ASC",query_result[x]) >> >> My question is >> Is it possible to combine both these querries into one ? > > SELECT DISTINCT Line_Number_View > FROM group_xdata_pipe WHERE grpuid IN ( > SELECT Entity_xData.grpuid > FROM Entity_xdata INNER JOIN group_id_vs_regappname ON ( > Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND > group_id_vs_regappname.reg_appname = 'CPD1') > ) > ORDER BY Line_Number_View ASC; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25271121.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table
A.J.Millan wrote: > > Perhaps a seudo-code who let the same result using the standard API > functions would be enough. > > Check this out,It might help > > http://www.sqlite.org/cintro.html will help. > > Also see http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for example of > use. > > Atul > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Looking-for-a-w_char-alternative-for-sqlite3_get_table-tp25188955p25236073.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Atul_Vaidya wrote: > > > SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN > group_id_vs_regappname ON(Entity_xdata.grpuid = > group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = > 'CPD1') > I get the grpuids using this command and then i use the grpuids that i get > from this query, as an input to my next query, something like this ... > > SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view FROM > group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY > Line_Number_View ASC",query_result[x]) > > My question is > Is it possible to combine both these querries into one ? or in other words > How to Select using Result obtained from other Select statements ? > Thanks in Advance, > Atul > atul.vai...@prototechsolutions.com Hi, I was able to achieve this by using two INNER JOINS as follows Select DISTINCT group_xdata_pipe.Line_Number_View from group_xdata_pipe INNER JOIN Entity_xData ON(group_xdata_pipe.grpuid = Entity_xData.grpuid)INNER JOIN group_id_vs_regappname ON(Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = 'CPD1') Thanks, and any suggestions to this query are welcome, i am sure, there must be some more elegant ways of doing this. Regards, Atul -- View this message in context: http://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25151110.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Hi, I have three tables, 1. Table Entity_xdata containing following fields |Entity_id|Layer|grpuid| 2. Table, group_xdata_pipe containing following fields |grpuid|LNV| 3. Table group_id_vs_reggappname containing following fields |grpuid|reggappname| Now, I need to Fire a query to SQlite where in I get the list of all the distinct LNVs. Currently I achieve it by following two query commands as follows SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN group_id_vs_regappname ON(Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = 'CPD1') I get the grpuids using this command and then i use the grpuids that i get from this query, as an input to my next query, something like this ... SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY Line_Number_View ASC",query_result[x]) My question is Is it possible to combine both these querries into one ? or in other words How to Select using Result obtained from other Select statements ? Thanks in Advance, Atul -- View this message in context: http://www.nabble.com/how-to-Select-using-results-from-other-selects-in-a-single-SQlite-querry-statement---tp25149885p25149885.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re triving the database name from the sqlite pointer ....
Hi, I have a Sqlite3 pointer. Is there any way to get the filename of this pointer ? Regards, Atul -- View this message in context: http://www.nabble.com/Retriving-the-database-name-from-the-sqlite-pointer--tp25020127p25020127.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there a substitute for Sqlite3_get_table ?
Hi, I have recently implemented SQlite into my Application which is an Engineering Analysis Software.I have implemented the DB in such a way that, I use the row_id as a primary integer key using this as an identifier for different fields in the table.Most of the fields in the table are VARCHARs.When I query the database,using the function sqlite3_get_table, i get the result in the form of char**. My question is, is it possible in SQlite to retrieve the data stored in the database in the format in which it is stored when the data was inserted in it ? for eg: if I store the data as an Int, will I get it back as an Int, from what i get from the get_table function is a char**.If yes, then will this enhance the speed of querying and how to achieve this,without using sqlite3_get_table ? Regards, Atul. -- View this message in context: http://www.nabble.com/Is-there-a-substitute-for-Sqlite3_get_table---tp24529898p24529898.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
