[sqlite] Speeding up the UPSERT by using ISNULL instead of COALESCE in the subselect
Good morning,
Could anyone tell me if I should replace COALESCE with ISNULL in the
subselect in order the improve the speed of the following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue, Count,
Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY ), 1), 14)?
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?
Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?
In *Test #1*, we have a
CREATE TABLE TESTMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE). In test #1, we use the
following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)
--
In *Test #2*, we have a
CREATE TABLE TESTMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON TESTMARY(VALUE).
--
In *Test #2*, we use the same UPSERT as *Test#1*,
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)
--
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.
May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?
Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?
Good morning,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?
In *Test #1*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).
In *Test #1*, we use the following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)
--
In *Test #2*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).
--
In *Test #2*, we use the same UPSERT as *Test#1*,
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length)
VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT Count+1 FROM
FREQMARY WHERE Value='Braintree Road'), 1),14)
--
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.
May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?
Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EDIT] What is the reason for the SQLITE performance increate with CREATE TABLE UNIQUE CONFLICT IGNORE
Michael Black and Igor Tandetnik, Thank you for your reply. We wiil get back you to tomorrow with the confirmation of your answers, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [LAST EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?
Good afternoon,
What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON
CONFLICT IGNORE?
In *Test #1*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).
In *Test #1*, we use the following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)
--
In *Test #2*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)
--
In *Test #2*, we use the same UPSERT as *Test#1*,
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)
--
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.
May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?
Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [FINAL EDIT] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE? Thank you
Good evening Michael Black and Igor Tandetnik and sqlite-users group[FINAL
EDIT],
May I ask what is the reason for SQLITE UPSERT performance improvement
with UNIQUE
ON CONFLICT IGNORE?
In *Test #1*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40),NUMERICVALUE FLOAT, DATETIMEVALUE CHAR(40), COUNT INT,
LENGTH INT)
and a
CREATE UNIQUE INDEX IX_MARY ON FREQMARY(VALUE).
In *Test #1*, we use the following UPSERT:
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)
--
In *Test #2*, we have a
CREATE TABLE FREQMARY(
VALUE CHAR(40) UNIQUE ON CONFLICT IGNORE ,NUMERICVALUE FLOAT,
DATETIMEVALUE CHAR(40), COUNT INT, LENGTH INT)
--
In *Test #2*, we use the same UPSERT as *Test#1*,
INSERT OR REPLACE INTO FREQMARY(Value, NumericValue, DateTimeValue,
Count, Length) VALUES ('Braintree Road',NULL,NULL, COALESCE((SELECT
Count+1 FROM FREQMARY WHERE Value='Braintree Road'), 1),14)
--
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #1 takes 10 hours to complete.
With 1,500,000 UPSERTS wrapped by BEGIN TRANSACTION and END TRANSACTION
every 10 UPSERTS, Test #2 takes 18 minutes to complete.
May I ask what the reason for SQLITE UPSERT *huge* performance improvment
with UNIQUE ON CONFLICT IGNORE?
Thank you for your help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Would it be possible to use SQLIte to calculate Chauvents Criterion as a proxy for data streakedness?
Good morning, Would it be possible to use SQLIte to calculate Chauvents Criterion as a proxy for data streakedness? Thank you. http://math.stackexchange.com/questions/198105/chauvenets-criterion-all-my-data-points-are-outliers?rq=1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?
joe.fis...@tanguaylab.com, Michael Black, Marc. L Allen and Simon Slavin, Thank you for your help in helping me to convince our company's software architect that it is possible to calculate the streakedness of numeric data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?
joe.fis...@tanguaylab.com, Michael Black, Marc L. Allen, and Simon Slavin, Our software architect defined data streakedness based upon Chauvenet's criterion. Thank you for all of your help. In statistical theory, *Chauvenet's criterion* (named for William Chauvenethttp://en.wikipedia.org/wiki/William_Chauvenet [1] http://en.wikipedia.org/wiki/Chauvenet%27s_criterion#cite_note-1) is a means of assessing whether one piece of experimental data — an outlierhttp://en.wikipedia.org/wiki/Outlier— from a set of observations, is likely to be spurious. To apply Chauvenet's criterion, first calculate the meanhttp://en.wikipedia.org/wiki/Meanand standard deviation http://en.wikipedia.org/wiki/Standard_deviation of the observed data. Based on how much the suspect datum differs from the mean, use the normal distribution http://en.wikipedia.org/wiki/Normal_distribution function (or a table thereof) to determine the probabilityhttp://en.wikipedia.org/wiki/Probabilitythat a given data point will be at the value of the suspect data point. Multiply this probability by the number of data points taken. If the result is less than 0.5, the suspicious data point may be discarded, i.e., a reading may be rejected if the probability of obtaining the particular deviation from the mean is less than 1/(2*n*). On Tue, Feb 19, 2013 at 11:05 AM, Frank Chang frankchan...@gmail.comwrote: joe.fis...@tanguaylab.com, Michael Black, Marc. L Allen and Simon Slavin, Thank you for your help in helping me to convince our company's software architect that it is possible to calculate the streakedness of numeric data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?
Would anyone know how to use SQLITE to calculate the streakedness of data? The definition of streakedness is how many deviations away from the mean(i.e running average a numerical data streak is Thank you for your help. A variable R can be used to indicate how many deviations away from the mean a particular streak is. According to the disclosed embodiment, the level of a streak can be defined not just in (integer*deviation) distances from the mean but also as (integer*fraction_of_deviation) distances. To accomplish this, a variable R-factor can be used. The R-factor indicates the separation between two successive R-levels in terms of a fraction of the deviation. By varying the R-factor, streaks can be ranked as required. However, the credibility of the streak should also be considered, and included in a ranking mechanism. The deviation within the streak is an obvious measure of how staggered the data is within the streak. A good streak should be less staggered, or in other words, have less deviation. For this reason, a very high level streak is considered to be good, even if its deviation is more than what would normally be desired. Thus, while the level R influences the ranking positively, the deviation within the streak influences it negatively. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which C++ data structures might one use to cache a sqlite query result?
Could anyone suggest which C++ data structure to use to cache a sqlite query result? Thank you for your help ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite :Is there any subtle distinction between UNIQUE and DISTINCT?
Hello, In the latest version of SQLite, is there any subtle distinction between UNIQUE and DISTINCT? If there exists a subtle distinction between UNQUE and DISTINCT in Sqlite, what is the appropriate Insert or replace syntax, CREATE INDEX usage, and the SELECT statements? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite :Is there any subtle distinction between UNIQUE and DISTINCT?
Jay A. Kreibich, Thank you for the discussion about the SQLITE difference between DISTINCT and UNIQUE. -- Message: 14 Date: Wed, 12 Dec 2012 10:15:34 -0600 From: Jay A. Kreibich j...@kreibi.ch To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite :Is there any subtle distinction between UNIQUE and DISTINCT? Message-ID: 20121212161534.gb68...@dfjk.org Content-Type: text/plain; charset=us-ascii On Wed, Dec 12, 2012 at 06:26:54AM -0800, Frank Chang scratched on the wall: Hello, In the latest version of SQLite, is there any subtle distinction between UNIQUE and DISTINCT? If there exists a subtle distinction between UNQUE and DISTINCT in Sqlite, what is the appropriate Insert or replace syntax, CREATE INDEX usage, and the SELECT statements? Thank you. Yes, there is a difference in how they handle NULLs. UNIQUE tests on = (equals), while DISTINCT tests on IS. Basically UNIQUE considers any two NULLs to be unique, while DISTINCT considers any two NULLs to be identical. In short, if you have a table with UNIQUE constraints (but without a NOT NULL constraint) there may still a valid reason to use SELECT DISTINCT. As for syntax and so forth, the terms are not interchangeable. The docs on the sqlite.org website should explain when you can use one term or the other. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to differentiate between sqlite database empty char* strings and DBNULL char* string?
Good afternoon, Is it possible to differentiate between sqlite database empty char* strings and DBNULL char* strings? If so, what is the est way to do that? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF
Igor Tandetnik, So what is the purpose of this whole exercise Following the project gurus's example sequence of -- 1,2,3,4,3,5,6,7,8,10 -- the numeric sorted ascending subsequence is found to be 1,2,3,4,5,6,7,8,10 using an automatic variable containing the most recent monotically increasing sequence member value and traversing the array sequentially in Big-O(linear time). As a result, the length of the sorted numeric ascending subsequence is 9. The length of the entire sequence is 10. So, the sortation percentage is (9/10) * 100% = 90%. The dynamic programming longest increasing subsequence program from Wikipedia, takes Big-0(n * log (n)) time. Thank you for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 18. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF
Elefterios Stamatogiannakis, Following the project gurus's example sequence of -- 1,2,3,4,3,5,6,7,8,10 -- the numeric sorted ascending subsequence is found to be 1,2,3,4,5,6,7,8,10 using an automatic variable containing the most recent monotically increasing sequence member value and traversing the array sequentially in Big-O(linear time). As a result, the length of the sorted numeric ascending subsequence is 9. The length of the entire sequence is 10. So, the sortation percentage is (9/10) * 100% = 90%. The dynamic programming longest increasing subsequence program from Wikipedia, takes Big-0(n * log (n)) time. Thank you for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF?
With the latest version of Sqlite, Is it possible to calculate the length of the longest increasing subsequence, also referred to as sortation percent, using a sqlite UDF, user defined function? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text?
Good morning, Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text? If not, what sqlite3 C/C++ api should we use? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?
Richard Hipp, Simon Slavin, Luuk, and Keith Metcalf Thank you for your replies to our question. Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE. F:\sqlite3_6_16sqlite3.exe mdName.dat SQLite version 3.6.16 Enter .help for instructions Enter SQL statements terminated with a ; sqlite INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10'; sqlite .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to determine the length of UNICODE(UTF-8 or UTF-16) strings stored in SQLite 3.7.11 database?
Good Morning, Is it possible to determine the length of UNICODE(UTF-8 or UTF-16) strings stored in SQLite 3.7.11 database? I just tried using the IBM open-source ICU function int32_t u_strlen(const UCHAR* str) but it gives me erroneous? Evidently the u_strlen function for a UTF-8 NULL (U+) terminated. Is there a more accurate way to to determine the length of UNICODE(UTF-8 or UTF-16) strings stored in SQLite 3.7.11 database? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to inserted European accented strings(UTF-8/UTF-16) into SQLITE using SQLITE3.EXE?
Good Afternoon, Is it possible to inserted European accented strings(UTF-8/UTF-16) into SQLITE using SQLITE3.EXE? If so, could you please show us a brief example. Best Regards Frank. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Potential Solution to -- Is it possible to preclude the latest sqlite 3.7.11 Windows warning message
Good evening, I find that if I insert, #define _KERNEL32_ , at line 587 of the latest 3.7.11 sqlite3.c file. Then, the following warning message disappears from Microsoft Visual Studio C++ 2008 output -- warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 'AreFileApisANSI' is not static, identity not guaranteed. Please advise me if this change is okay for Windows sqlite3.c applications. Thank you. e:\users\frank\dqt_memorymap\sqlite\sqlite3.c(32329) : warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 'AreFileApisANSI' is not static, identity not guaranteed A quick google shows http://msdn.microsoft.com/en-us/library/9a1sy630.aspx Would using /Ze instead of /Za do what you want? Regards, Simon From: sqlite-users-requ...@sqlite.org Subject: sqlite-users Digest, Vol 52, Issue 23 To: sqlite-users@sqlite.org Date: Mon, 23 Apr 2012 12:00:02 -0400 Send sqlite-users mailing list submissions to sqlite-users@sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to sqlite-users-requ...@sqlite.org You can reach the person managing the list at sqlite-users-ow...@sqlite.org When replying, please edit your Subject line so it is more specific than Re: Contents of sqlite-users digest... Today's Topics: 1. Re: Permissions (Steinar Midtskogen) 2. Re: Permissions (Steinar Midtskogen) 3. Re: Permissions (Richard Hipp) 4. Re: Permissions (Simon Slavin) 5. free list performance (Max Vlasov) 6. help (? ?) 7. Re: help (Simon Davies) 8. Re: Permissions (Steinar Midtskogen) 9. help (? ?) 10. Help (? ?) 11. Help (? ?) 12. Re: Help (niXman) 13. Re: free list performance (Simon Slavin) 14. Re: free list performance (Ghislain Segers) 15. Memory Usage/ Drawbacks of Statements (Mohit Sindhwani) 16. Is it possible to preclude the latest sqlite 3.7.11 Windows warning message? (Frank Chang) 17. Re: help (Pavel Ivanov) 18. Re: Memory Usage/ Drawbacks of Statements (Pavel Ivanov) 19. Re: Is it possible to preclude the latest sqlite 3.7.11 Windows warning message? (Simon Davies) 20. Re: free list performance (Max Vlasov) 21. Re: free list performance (Simon Slavin) 22. Re: free list performance (Pavel Ivanov) 23. Re: Memory Usage/ Drawbacks of Statements (Mohit Sindhwani) 24. error 404 (Adam DeVita) 25. Is it possible to preclude the latest sqlite 3.7.11 Windows warning message? (Frank Chang) -- Message: 1 Date: Sun, 22 Apr 2012 18:26:50 +0200 From: Steinar Midtskogen stei...@latinitas.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Permissions Message-ID: 87397vlol1@latinitas.org Content-Type: text/plain; charset=us-ascii Stephan Beal sgb...@googlemail.com writes: Try the sticky bit: chown user:apache theDir chmod 4775 theDir I think the effect of that only is to restrict anyone but root or the owner of a file from deleting or renaming an otherwise writeable file in that directory. -- Steinar -- Message: 2 Date: Sun, 22 Apr 2012 18:40:12 +0200 From: Steinar Midtskogen stei...@latinitas.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Permissions Message-ID: 87y5pnk9eb@latinitas.org Content-Type: text/plain; charset=windows-1252 [Simon Slavin] The solution I came up with is that the database file owner also uses Apache to look at it: I use web-facing database administration software rather than opening the database in another application. (I wrote a simple one myself in PHP and JavaScript.) However this is unacceptable for some users. That gave me an idea, which should solve the problem for me. Only two applications access the database: apache or the sqlite3 commandline tool. So I simply chowned the sqlite3 application and made it setuid apache. It doesn't solve the general case, though, where any application owned by any user in a certain group should be able to access the database. You're using WAL mode. DELETE mode is the default behaviour: when the last connection to the database is closed, the journal is deleted. But you can change this to TRUNCATE or some other value that suits you. That way, the files will not have to be remade. So then you would ? I chose WAL since I'd like to have as much concurrency as possible. If TRUNCATE means that the files will always be present, never deleted, then I suppose that also could solve my problem, since the file then could be made group writeable. Any reason why sqlite doesn't use the same file permissions as the database file when creating these extra files? -- Steinar
[sqlite] Is it possible to preclude the latest sqlite 3.7.11 Windows warning message?
Good morning, We are trying to compile the latest SQLITE 3.7.11 release but we keep getting the Windows Visual Studio 8 warning message: warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 'AreFileApisANSI' is not static, identity not guaranteed showm below. Is it possible to preclude and understand the meaning of this warning message? Thank you. e:\users\frank\dqt_memorymap\sqlite\sqlite3.c(32329) : warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 'AreFileApisANSI' is not static, identity not guaranteed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to preclude the latest sqlite 3.7.11 Windows warning message?
Simon Davies, We tried your suggestion, /Ze on Visual Studio 2008, buy we are still encountering Microsoft Visual 2008 warning, Thank you for your help. e:\users\frank\dqt_memorymap\sqlite\sqlite3.c(32329) : warning C4232: nonstandard extension used : 'pCurrent' : address of dllimport 'AreFileApisANSI' is not static, identity not guaranteed, --A quick google shows http://msdn.microsoft.com/en-us/library/9a1sy630.aspx --Would using /Ze instead of /Za do what you want? --Regards, --Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db sql?
inq1ltd, Thank you for your reply. Here is an excerpt of the most recent
Windows CMD file:
set var=%1
@echo %var:~0,-4%
set abc= %var:~0,-4%
cameron.cmd %abc%
From: inq1...@inqvista.com
To: sqlite-users@sqlite.org
CC: frank_chan...@hotmail.com
Subject: Re: [sqlite] Is it possible to use substrings of Windows DOS batch
fiile parameters in sqlite3.exe -line db sql?
Date: Wed, 11 Apr 2012 11:09:44 -0400
On Tuesday, April 10, 2012 07:14:59 PM Frank Chang wrote:
Good evening, We are trying to generate automated SQLITE SQL scripts based
on the names of SQLite tables derived by substring manipulation of Windows
DOS batch file and/or Windows environment variables. For example:
/* mary.bat */
FOR /f %%a IN ('dir /b *.zip') DO CALL sub %%a
/* sub.bat */
set str=%1
set camster=%str:~0.17%
echo %str:~0,17%
E:\users\marc\NJM\spatialite_tool.exe -i -shp %str:~0,17% -d
e:\users\marc\NJM\mdMatchup.dat -t %str:~0,17% -g Geometry -c CP1252 -s
4269 E:\users\marc\NJM\sqlite.exe -line e:\users\marc\NJM\mdMatchup.dat
drop table %camster%;
I think you are asking if you can use a variable
to represent your table name.
I use this in python and have used something
similar in DOS to create, delete, update tables on the fly.
vsqldb = 'SomeDB' ## DB named and related to a variable some place
else
vtablename = 'someTtablename' ## table named some place else
con = sqlite3.connect (vsqldb) # open DB
cursor = con.cursor()
cursor.execute(DROP TABLE IF EXISTS + vtablename )
cursor.execute(VACUUM) #clean the DB
con.commit()
con.close()
Dropping the table is possible without hard coding the
table name into the Drop Table command.
jd
Invoking mary.bat at the command line generates the following command
script:
E:\TIGER2011\COUSUBCALL sub tl_2011_78_cousub.zip
E:\TIGER2011\COUSUBset str=tl_2011_78_cousub.zip
E:\TIGER2011\COUSUBset camster=str:~0.17
E:\TIGER2011\COUSUBecho tl_2011_78_cousub
tl_2011_78_cousub
E:\TIGER2011\COUSUBE:\users\marc\NJM\spatialite_tool.exe -i -shp
tl_2011_78_cou sub -d e:\users\marc\NJM\mdMatchup.dat -t tl_2011_78_cousub
-g Geometry -c CP125 2 -s 4269
SQLite version: 3.6.16
SpatiaLite version: 2.3.1
load shapefile error: table 'tl_2011_78_cousub' already exists
E:\TIGER2011\COUSUBE:\users\marc\NJM\sqlite.exe
-line e:\users\marc\NJM\mdMatchup.dat drop table str:~0.17;
SQL error: unrecognized token: :
rather than drop table t1_2011_78_cousub.
Is it possible that we using the wrong SQLite syntax in the sqlite3.exe
-line database sql_statement;? If so, what might be the correct sqlite
command string to drop the table t1_2011_78_cousub? Thank you very much.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db sql?
Message: 19 Date: Wed, 11 Apr 2012 08:31:49 -0400 From: Gabor Grothendieck ggrothendi...@gmail.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db sql? Message-ID: Gabor Gronthendieck, Thank you for your reply. An excerpt of the most recent version of our Windows CMD file is: set var=%1 @echo %var:~0,-4% set abc= %var:~0,-4% cameron.cmd %abc% ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to use substrings of Windows DOS batch fiile parameters in sqlite3.exe -line db sql?
Good evening, We are trying to generate automated SQLITE SQL scripts based on
the names of SQLite tables derived by substring manipulation of Windows DOS
batch file and/or Windows environment variables. For example:
/* mary.bat */
FOR /f %%a IN ('dir /b *.zip') DO CALL sub %%a
/* sub.bat */
set str=%1
set camster=%str:~0.17%
echo %str:~0,17%
E:\users\marc\NJM\spatialite_tool.exe -i -shp %str:~0,17% -d
e:\users\marc\NJM\mdMatchup.dat -t %str:~0,17% -g Geometry -c CP1252 -s 4269
E:\users\marc\NJM\sqlite.exe -line e:\users\marc\NJM\mdMatchup.dat drop table
%camster%;
Invoking mary.bat at the command line generates the following command script:
E:\TIGER2011\COUSUBCALL sub tl_2011_78_cousub.zip
E:\TIGER2011\COUSUBset str=tl_2011_78_cousub.zip
E:\TIGER2011\COUSUBset camster=str:~0.17
E:\TIGER2011\COUSUBecho tl_2011_78_cousub
tl_2011_78_cousub
E:\TIGER2011\COUSUBE:\users\marc\NJM\spatialite_tool.exe -i -shp tl_2011_78_cou
sub -d e:\users\marc\NJM\mdMatchup.dat -t tl_2011_78_cousub -g Geometry -c CP125
2 -s 4269
SQLite version: 3.6.16
SpatiaLite version: 2.3.1
load shapefile error: table 'tl_2011_78_cousub' already exists
E:\TIGER2011\COUSUBE:\users\marc\NJM\sqlite.exe
-line e:\users\marc\NJM\mdMatchup.dat drop table str:~0.17;
SQL error: unrecognized token: :
rather than drop table t1_2011_78_cousub.
Is it possible that we using the wrong SQLite syntax in the sqlite3.exe -line
database sql_statement;? If so, what might be the correct sqlite command
string to drop the table t1_2011_78_cousub? Thank you very much.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible Solution to Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround paradox
Dan Kennedy, I discovered yesterday that one can use sqlite prepared SQLITE
statements to reduce the CPU and memory utilization of parsing SELECT
statements used to substitute for Sqlite3BTreeMovetoUnpacked and
sqlite3_blob_reopen. Thank you
From: frank_chan...@hotmail.com
To: sqlite-users@sqlite.org
Subject: RE: Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is
I/O Bound and uses all the Physical Memory
Date: Wed, 29 Feb 2012 11:59:56 -0500
Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler
output is attached) but the profiler is full of sqlite functions and the
application runs slower because it is I/O bound and uses almost all the
physical memory.
I was thinking maybe we could write only one SQLITE SELECT statement and cache
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned
long*/ int* SubGraphBlob_,
int *Size_) {
int Size;
//sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into
the
// donee (All your BLOB are belong to us!)
// First time, open the BLOB for real, else we can re-open (faster):
char SelectStatement[256];
char WhereClause[256];
strcpy(SelectStatement, select [Rows] from AggregatedData);
sprintf(WhereClause, where [RowId] = %d,SubGraph_-IteratorPos+1);
strcat(SelectStatement, WhereClause);
int ReturnValue=sqlite3_prepare(SubGraph_-Database,
SelectStatement,-1,
SubGraph_-Statement);
int status = sqlite3_step(SubGraph_-Statement);
if (status == SQLITE_ROW) {
SubGraphBlob_ = (int*)sqlite3_column_blob(SubGraph_-Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_-Statement,0)); //2);
// if (SubGraph_-hBlob==0)
//
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,SubGraph_-hBlob);
// else
// sqlite3_blob_reopen(SubGraph_-hBlob,SubGraph_-IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_-hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_-hBlob,SubGraphBlob_,Size*sizeof(unsigned
long),0);
// SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int
*IntersectionBlob_,
/*unsigned long*/int *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs
contain the same
// value, that value is copied to PosOut in Blob_, else, the blob that is
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0 SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]SubGraphBlob_[Pos2]) {
Pos1++;
} else {
Pos2++;
}
}
IntersectionBlob_[PosOut]=0;
}
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Profiler Output
From: frank_chan...@hotmail.com
To: sqlite-users@sqlite.org
Subject: RE: Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is
I/O Bound and uses all the Physical Memory
Date: Wed, 29 Feb 2012 11:59:56 -0500
Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler
output is attached) but the profiler is full of sqlite functions and the
application runs slower because it is I/O bound and uses almost all the
physical memory.
I was thinking maybe we could write only one SQLITE SELECT statement and cache
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned
long*/ int* SubGraphBlob_,
int *Size_) {
int Size;
//sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into
the
// donee (All your BLOB are belong to us!)
// First time, open the BLOB for real, else we can re-open (faster):
char SelectStatement[256];
char WhereClause[256];
strcpy(SelectStatement, select [Rows] from AggregatedData);
sprintf(WhereClause, where [RowId] = %d,SubGraph_-IteratorPos+1);
strcat(SelectStatement, WhereClause);
int ReturnValue=sqlite3_prepare(SubGraph_-Database,
SelectStatement,-1,
SubGraph_-Statement);
int status = sqlite3_step(SubGraph_-Statement);
if (status == SQLITE_ROW) {
SubGraphBlob_ = (int*)sqlite3_column_blob(SubGraph_-Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_-Statement,0)); //2);
// if (SubGraph_-hBlob==0)
//
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,SubGraph_-hBlob);
// else
// sqlite3_blob_reopen(SubGraph_-hBlob,SubGraph_-IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_-hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_-hBlob,SubGraphBlob_,Size*sizeof(unsigned
long),0);
// SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int
*IntersectionBlob_,
/*unsigned long*/int *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs
contain the same
// value, that value is copied to PosOut in Blob_, else, the blob that is
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0 SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]SubGraphBlob_[Pos2]) {
Pos1++;
} else {
Pos2++;
}
}
IntersectionBlob_[PosOut]=0;
}
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is I/O Bound and uses all the Physical Memory
Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler
output is attached) but the profiler is full of sqlite functions and the
application runs slower because it is I/O bound and uses almost all the
physical memory.
I was thinking maybe we could write only one SQLITE SELECT statement and cache
the blobs in memory
Could you please suggest how I could fix this I/O Bound SQLIte SELECT Statement
Problem.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,/*unsigned
long*/ int* SubGraphBlob_,
int *Size_) {
int Size;
//sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into
the
// donee (All your BLOB are belong to us!)
// First time, open the BLOB for real, else we can re-open (faster):
char SelectStatement[256];
char WhereClause[256];
strcpy(SelectStatement, select [Rows] from AggregatedData);
sprintf(WhereClause, where [RowId] = %d,SubGraph_-IteratorPos+1);
strcat(SelectStatement, WhereClause);
int ReturnValue=sqlite3_prepare(SubGraph_-Database,
SelectStatement,-1,
SubGraph_-Statement);
int status = sqlite3_step(SubGraph_-Statement);
if (status == SQLITE_ROW) {
SubGraphBlob_ = (int*)sqlite3_column_blob(SubGraph_-Statement,0); //2);
Size = (sqlite3_column_bytes(SubGraph_-Statement,0)); //2);
// if (SubGraph_-hBlob==0)
//
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,SubGraph_-hBlob);
// else
// sqlite3_blob_reopen(SubGraph_-hBlob,SubGraph_-IteratorPos+1);
//
// Size=sqlite3_blob_bytes(SubGraph_-hBlob)/sizeof(unsigned long);
// sqlite3_blob_read(SubGraph_-hBlob,SubGraphBlob_,Size*sizeof(unsigned
long),0);
// SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,int
*IntersectionBlob_,
/*unsigned long*/int *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs
contain the same
// value, that value is copied to PosOut in Blob_, else, the blob that is
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0 SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]SubGraphBlob_[Pos2]) {
Pos1++;
} else {
Pos2++;
}
}
IntersectionBlob_[PosOut]=0;
}
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to reduce the number of times sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application?
Good morning, We have a SQLITE C++ application which tries to find the
intersection between the blobs in separate sqlite database tables(which we call
subgraphs) ,containing record numbers. When we profile the code below we find
that the top profiler user is sqlite3BTreeMoveToUnpacked. I have attached the
profiler outputs whivh we obtained using SQLITE 3.7.10.
We were wondering if it is possible to reduce the number of times
sqlite3BTreeMoveToUnpacked is called with an SQLITE C++ application? Thank you.
void cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO *SubGraph_,unsigned
long *SubGraphBlob_,
int *Size_) {
int Size;
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into the
// donee (All your BLOB are belong to us!)
// First time, open the BLOB for real, else we can re-open (faster):
if (SubGraph_-hBlob==0)
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,SubGraph_-hBlob);
else
sqlite3_blob_reopen(SubGraph_-hBlob,SubGraph_-IteratorPos+1);
Size=sqlite3_blob_bytes(SubGraph_-hBlob)/sizeof(unsigned long);
sqlite3_blob_read(SubGraph_-hBlob,SubGraphBlob_,Size*sizeof(unsigned long),0);
SubGraphBlob_[Size]=0;
if (Size_!=0)
*Size_=Size;
}
void cIntersectingDedupe::IntersectBlobs(sSUBGRAPHINFO *SubGraph_,unsigned long
*IntersectionBlob_,
unsigned long *SubGraphBlob_) {
int Pos1,Pos2,PosOut;
GetSubGraphBlob(SubGraph_,SubGraphBlob_);
// Perform the intersection. We walk though the two blobs, if the blobs contain
the same
// value, that value is copied to PosOut in Blob_, else, the blob that is
'behind' is
// incremented so it can 'catch up' to the other:
Pos1=Pos2=PosOut=0;
while (IntersectionBlob_[Pos1]!=0 SubGraphBlob_[Pos2]!=0) {
if (IntersectionBlob_[Pos1]==SubGraphBlob_[Pos2]) {
IntersectionBlob_[PosOut++]=IntersectionBlob_[Pos1++];
Pos2++;
} else if (IntersectionBlob_[Pos1]SubGraphBlob_[Pos2]) {
Pos1++;
} else {
Pos2++;
}
}
IntersectionBlob_[PosOut]=0;
} ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faster Alternative to sqlite3_blob_open
sqlite3_blob_reopen.ppt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faster alternative to sqlite3_blob_open
Good morning,We have been using sqlite3_blob_open in the function below.When I
profile the application using Very Sleepy I notice that sqlite3_blob_open is
one of he biggest consumers of CPU time. Is it possible to find a faster
alternative to sqlite3_blob_open? Thank you
unsigned long *cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO
*SubGraph_,int *Size_) {
int Size;
sqlite3_blob *Blob;
unsigned long *RetVal;
/* The new function sliqte3_blob_reopen() may be a bit faster? */
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into the
// donee (All your BLOB are belong to us!)
0.08s
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,Blob);
0.42s Size=sqlite3_blob_bytes(Blob)/sizeof(unsigned long);
0.22s RetVal=new unsigned long[(Size+1)*sizeof(unsigned long)];
0.06s sqlite3_blob_read(Blob,RetVal,Size*sizeof(unsigned long),0);
0.01s sqlite3_blob_close(Blob);
RetVal[Size]=0;
0.30s if (Size_!=0)
*Size_=Size;
0.01s return RetVal;
0.06s }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster Alternative to sqlite3_blob_open
Roger Binns, I ran some tests today and found that sqlite3_blob_reopen is
significantly faster than sqlite3_blob_open. Attached is the Very Sleepy Full
Profiler report. The modified C++ code is shown below. Thank you.
unsigned long *cIntersectingDedupe::GetSubGraphBlob(sSUBGRAPHINFO
*SubGraph_,int *Size_) {
int Size;
unsigned long *RetVal;
// Grab a BLOB and put it into a unsigned long buffer. As the BLOB contains
record numbers,
// we will never see a value of zero. Thus, we use 0 to mark the end of the
array.
//
// Note that we can have BLOBs of size 0, though. If we used DistillSubGraph to
// dedupe the subgraph, some records will be consolidated into others. The
// donor record's BLOB gets zapped because all of it's BLOB was rolled into
the
// donee (All your BLOB are belong to us!)
if (SubGraph_-hBlob == 0){
sqlite3_blob_open(SubGraph_-Database,main,AggregatedData,Rows,SubGraph_-IteratorPos+1,0,(SubGraph_-hBlob));
}
else{
sqlite3_blob_reopen(SubGraph_-hBlob,SubGraph_-IteratorPos+1);
}
Size=sqlite3_blob_bytes(SubGraph_-hBlob)/sizeof(unsigned long);
RetVal=new unsigned long[(Size+1)*sizeof(unsigned long)];
sqlite3_blob_read(SubGraph_-hBlob,RetVal,Size*sizeof(unsigned long),0);
RetVal[Size]=0;
if (Size_!=0)
*Size_=Size;
return RetVal;
}
Frank has a file to share with you on SkyDrive. To view it, click the link
below.
sqlite3_blob_reopen.ppt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table
Igor Tandetnik,
How come you only show one function? A user-defined aggregate function is
actually represented by two C[++] functions - one that is called for every
row
and performs actual aggregation, and another that's called at the end of
each
group, reports the result and resets the state machine to prepare for the
next
group. You can use sqlite3_context to store state between invocations - see
sqlite3_aggregate_context.
We have defined 2 C++ function XStep and XFinalize(shown below).
The group by last name BLOB results look accurate. Thank you for your help.
void cIntersectingGroupCache::XFinalize(sqlite3_context *context){
listCtx *p;
char *buf=NULL;
buf = (char *) malloc ((sizeof(int) *
((cIntersectingGroupCache*)(p-TheThis))-Column2.size())+ 4);
if (buf == NULL)
printf(malloc error in XFinalize, buf\n);
sqlite3_result_blob(context,buf,
(((cIntersectingGroupCache*)(p-TheThis))-Column2.size()*sizeof(int))
+ 4, free);
((cIntersectingGroupCache*)(p-TheThis))-Column2.clear();
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database
Igor Tandetnik, I tried your suggestion
insert or replace into BlobLastNameTest(rowid, FieldName, Vertices)
select min(rowid), FieldName, AGGREGATEBLOB(Vertices,rowid,%d,\'%s\')
from BlobLastNameTest group by FieldName,
(The AGGREGATEBLOB C++ User Defined Function is shown at bottom of this post)
but it only concatenates a maximum of two rowids into the VERTICES BLOB for
the MIN(ROWID).
Perhaps, We need to break this into a several steps
1. CREATE TABLE TEMPRESULT(FieldName CHAR(25), PreviousFieldName CHAR(25).
MINROWID INT);
2. INSERT INTO TEMPRESULTS SELECT .
3. C++ code NOT SQL Code
sprintf(InsertStatementinsert or replace into BlobLastNameTest(rowid,
FieldName, Vertices)
select min(rowid), FieldName,
AGGREGATEBLOB(X.Vertices,X.rowid,%d,\'%s\',X.FIELDNAME,Y.PREVIOUSFIELDNAME)
from BlobLastNameTest X, TEMPRESULTS Y WHERE X.FIELDNAME = Y.FIELDNAME group by
X.FieldName,
In this way when the FieldName changes , we could possibly refresh the Standard
Template Library Vector Column3 which stores the rowid's which are embedded in
the VERTICES BLOB for each unique fieldname Thank you for your help.
void cIntersectingGroupCache::AGGREGATEBLOBFunc(sqlite3_context *context, int
argc, sqlite3_value **argv){
char* TableName;
int size;
int* ip2;
long long int iVal;
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column3.clear();
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal 0) ? 1: ( iVal 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
size = sqlite3_value_bytes(argv[3]);
TableName = new char[size + 1];
memcpy(TableName, sqlite3_value_text(argv[3]),size);
TableName[size] = '\x0';
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-RowCountMap[TableName]
+= 1;
delete [] TableName;
break;
}
case SQLITE_BLOB: {
size = sqlite3_value_bytes(argv[0]);
ip2 = (int *)sqlite3_value_blob(argv[0]);
for (int i = 0; i size/sizeof(int); i++){
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column3.push_back(ip2[i]);
}
break;
}
default: {
break;
}
}
switch( sqlite3_value_type(argv[1]) ){
case SQLITE_INTEGER: {
int iVal = sqlite3_value_int(argv[1]);
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column3.push_back(iVal);
char* blob =
reinterpret_castchar*cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column3[0]));
sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column3.size()*sizeof(int),NULL);
break;
}
default: {
break;
}
}
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table
Igor Tandetnik,
The explain query plan for select FieldName, min(rowid) from
BlobLastNameTest group by FieldName shows a full index scan, even after I run
ANALYZE TABLE AND INDEX ON FIELDNAME, which could be problematic when the
number of rows in BlobLastNameTest increases by a factor 10 to 30 million rows.
But since Sqlite does not have a DISTINCT ON clause as Florian Weimer as
pointed out two days ago,we will have to investigate this issue in another
possible thread.
Here is how I might do the update:
1. CREATE TABLE FOO(FIELDNAME CHAR(25), IDROW INT);
2 INSERT INTO FOO select FieldName, min(rowid) from BlobLastNameTest
group by FieldName
3. INSERT OR REPLACE INTO BLOBLASTNAMETEST SELECT t1.FIELDNAME,
UDF(t1.ROWID,t1.FIELDNAME,this,'BLOBLASTNAMETEST') FROM FOO WHERE
BLOBLASTNAMETEST.FIELDNAME = FOO.FIELDNAME AND BLOBLASTNAMETEST.ROWID =
FOO.IDROW.
WHERE THE UDF looks like this:
void cIntersectingGroupCache::UDFFunc(sqlite3_context *context, int argc,
sqlite3_value **argv){
char* TableName;
int size;
int* ip2;
long long int iVal;
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column.clear();
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal 0) ? 1: ( iVal 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
size = sqlite3_value_bytes(argv[3]);
TableName = new char[size + 1];
memcpy(TableName, sqlite3_value_text(argv[3]),size);
TableName[size] = '\x0';
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-RowCountMap[TableName]
+= 1;
delete [] TableName;
break;
}
case SQLITE_BLOB: {
size = sqlite3_value_bytes(argv[0]);
ip2 = (int *)sqlite3_value_blob(argv[0]);
for (int i = 0; i size/sizeof(int); i++){
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column.push_back(ip2[i]);
}
break;
}
default: {
break;
}
}
switch( sqlite3_value_type(argv[1]) ){
case SQLITE_INTEGER: {
int iVal = sqlite3_value_int(argv[1]);
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column.push_back(iVal);
char* blob =
reinterpret_castchar*cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column[0]));
sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))-Column.size()*sizeof(int),NULL);
break;
}
default: {
break;
}
}
}
Thank you for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize a very large database query by avoiding total index scans
Florence Weimar, Igor Tadetnik, Simon Slavin, I ran ANALYZE BLOBLASTNAMETEST in order to get better index statistics. Then I modified my query to: select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1. FIELDNAME) group by t1.FieldName . It appears that this new query is not doing a full index scan while select FieldName, MIN(ROWID) FROM BLOBLASTNAMETEST GROUP BY FIELDNAME is still doing a full index scan. The explain query plans are shown below. Thank you. sqlite explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1. FIELDNAME) group by t1.FieldName; 0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary5 (~2709783 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldName=? ) (~1 rows) sqlite explain query plan select FieldName, min(rowid) from BlobLastNameTest gr oup by FieldName; 0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary5 (~5419566 rows ) sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize a query on a very large database table?
Florian Weimar and Igor Tadetnik, When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by 1; the explain output seems to have 40% less steps. Does this mean order by 1 should be faster than group by t1.FIELDNAME as I scale up the number of rows in the very large database table BLOBLASTNAMETEST. The explain output is ahown below. Thank you. sqlite explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by 1; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Goto|0|32|0||00| 3|OpenRead|3|108789|0|keyinfo(1,BINARY)|00| 4|Rewind|3|30|1|0|00| 5|IdxRowid|3|1|0||00| 6|Null|0|3|0||00| 7|Integer|1|4|0||00| 8|Null|0|6|0||00| 9|Null|0|5|0||00| 10|OpenRead|4|108789|0|keyinfo(1,BINARY)|00| 11|Column|3|0|7||00| 12|IsNull|7|20|0||00| 13|SeekGe|4|20|7|1|00| 14|IdxGE|4|20|7|1|01| 15|IdxRowid|4|9|0||00| 16|CollSeq|0|0|0|collseq(BINARY)|00| 17|AggStep|0|9|5|min(1)|01| 18|Goto|0|20|0||00| 19|Next|4|14|0||00| 20|Close|4|0|0||00| 21|AggFinal|5|1|0|min(1)|00| 22|SCopy|5|10|0||00| 23|Move|10|3|1||00| 24|IfZero|4|25|-1||00| 25|Ne|3|29|1||6c| 26|Column|3|0|11||00| 27|IdxRowid|3|12|0||00| 28|ResultRow|11|2|0||00| 29|Next|3|5|0||00| 30|Close|3|0|0||00| 31|Halt|0|0|0||00| 32|Transaction|0|0|0||00| 33|VerifyCookie|0|7|0||00| 34|TableLock|0|2|0|BlobLastNameTest|00| 35|Goto|0|3|0||00| sqlite sqlite explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) GROUP by t1.FieldName; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Integer|0|4|0||00| 3|Integer|0|3|0||00| 4|Gosub|6|51|0||00| 5|Goto|0|55|0||00| 6|OpenRead|3|108789|0|keyinfo(1,BINARY)|00| 7|Rewind|3|40|9|0|00| 8|IdxRowid|3|9|0||00| 9|Null|0|11|0||00| 10|Integer|1|12|0||00| 11|Null|0|14|0||00| 12|Null|0|13|0||00| 13|OpenRead|4|108789|0|keyinfo(1,BINARY)|00| 14|Column|3|0|15||00| 15|IsNull|15|23|0||00| 16|SeekGe|4|23|15|1|00| 17|IdxGE|4|23|15|1|01| 18|IdxRowid|4|17|0||00| 19|CollSeq|0|0|0|collseq(BINARY)|00| 20|AggStep|0|17|13|min(1)|01| 21|Goto|0|23|0||00| 22|Next|4|17|0||00| 23|Close|4|0|0||00| 24|AggFinal|13|1|0|min(1)|00| 25|SCopy|13|18|0||00| 26|Move|18|11|1||00| 27|IfZero|12|28|-1||00| 28|Ne|11|39|9||6c| 29|Column|3|0|8||00| 30|Compare|7|8|1|keyinfo(1,BINARY)|00| 31|Jump|32|36|32||00| 32|Move|8|7|1||00| 33|Gosub|5|45|0||00| 34|IfPos|4|54|0||00| 35|Gosub|6|51|0||00| 36|Column|3|0|1||00| 37|IdxRowid|3|2|0||00| 38|Integer|1|3|0||00| 39|Next|3|8|0||00| 40|Close|3|0|0||00| 41|Gosub|5|45|0||00| 42|Goto|0|54|0||00| 43|Integer|1|4|0||00| 44|Return|5|0|0||00| 45|IfPos|3|47|0||00| 46|Return|5|0|0||00| 47|Copy|1|19|0||00| 48|Copy|2|20|0||00| 49|ResultRow|19|2|0||00| 50|Return|5|0|0||00| 51|Null|0|1|0||00| 52|Null|0|2|0||00| 53|Return|6|0|0||00| 54|Halt|0|0|0||00| 55|Transaction|0|0|0||00| 56|VerifyCookie|0|7|0||00| 57|TableLock|0|2|0|BlobLastNameTest|00| 58|Goto|0|6|0||00| sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table.
Igor Tandetnik, The fieldname groups in our BlobLastNameTable consist of multiple rows where each pair of columns [FieldName, BLOB[Vertices]] is unique. Therefore, every fieldname group does not just have a single row but instead 1000's or 1's rows. So that is why we use a group by/order by and subselect clause to locate the first/minimum row id row in each fieldname group. Once we know the first/minimum row id of each unique fieldname group, we would lke to write a sqlite UPDATE Statement to accumulate all the BLOB vertices of all the rows with that unique fieldname into the first(i.e MIN(ROWID)) row's BLOB(Vertices)column for each unique fieldname group. Then we would like to discard all the rows in each fieldname group of rows that have an rowid different from the first row(i.e MIN(rowid)). Because we using a C++ WINDOWS/LINUX/Solaris UNIX multithreaded program where each concurrent worker thread has its own sqlite database and sqlite table and sqlite index , we would like each concurrence worker thread to run as fast as possible on a multicore CPU. We have profiled the worker threads and we have found that the sqlite statements are the bottleneck. So, that is why we would like our queries to run as fast as possible by avoiding full index scans as Florian Weimer pointed out in the post about Postgres' DISTINCT ON feature which we are trying to simulate on Sqlite. I hope I have provided you more information. I did try your suggestion: select FieldName, rowid from BlobLastNameTest. However, it generates 5.5 million rows of output which would force our C++ Worker threads to do the BLOB(Vertices) aggregation. Unfortunately , that would would take too long so we were hoping that we could devise a nice query to let the SQLite query processor do all the Blob(Vertices) aggregation in a few minutes or less . Please let me know if you have any questions or suggestions. Thank you for your help. -- -- I take it back - the behavior of your query is well-defined, for the simple reason that FieldName is unique, so every group only has a single row in it (as someone else has kindly pointed out - I missed this detail on the first reading). For that same reason, the whole GROUP BY and sub-select dance is completely pointless. Your query is just a very elaborate and wasteful way to write select FieldName, rowid from BlobLastNameTest; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Re; Is it possible to optimize this query on a very large database table? Oct 10,2011
Simon Slavin, I tried your suggestion about creating the index: So do this: CREATE INDEX BLNTFieldName ON BlobLastNameTest (FieldName, rowid) But sqlite complains that rowid is not a BLobLastNameTest column. So then I tried your repeated selects. Your selects work fine but since we are using Multiple concurrent Windows and Linux/pthread worker threads, these repeated select resulrts would have to be processed in C++ code which would overload each of the cores on the multicore CPU we are using. Instead, we were hoping they we write a nice query to let the SQLite query processor do the aggregation of BLOB(vertices) without incurring the full index scan cost. Florian Weimer told us about this Posttgres feature DISTINCT ON which would allow us to do this. However, SQLITE does not have DISTINCT ON yet, so the Postgres thread recommended we use somerthing like to simulate DISTINCT ON: select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by t1.FieldName; where the query plan looks like this: 0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary5(~2709793 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldN ) (~1 rows) Please let us know if there is a faster or more elegant way to this. Finally, I tried your suggestion about : SELECT rowid,FieldName FROM BlobLastNameTest GROUP BY FieldName ORDER BY rowid but it gives the wrong answer where the right answer is the minimum rowid for a particular FIELDNAME(i.e.LASTNAME). Your query runs very fast . Is it possible that you could tweak it a litle so that it gives the right minimum ROWiD answer. Thank you for all of your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large database table Oct. 10, 2011 13:53:01 GMT
Igor Tandetnik, Explain the problem you are trying to solve, *not* your proposed solution. What we are trying to achieve is to to find the minimum row id for each unique Field Name in BLobLastNameTest where many rows can have the same FIELDNAME but distinct BLOBS(Vertices Column). Once we know the first/minimum row id of each unique fieldname group, we would like to write a sqlite UPDATE Statement to accumulate all the BLOB vertices of all the rows with that unique fieldname into the first(i.e MIN(ROWID)) row's BLOB(Vertices)column for each unique fieldname group. The reason we can't use select FieldName, rowid from BlobLastNameTest is that it would slow our C++ Windows/LINUx/Solaris UNIX worker threads so much that it wouldn;t be worth multithreading this whole process. We were hoping that the SQLITE query processor and the appropriate indexes could accomplish these previous 2 steps in a few minutes for a reasonable number of BLOBLASTNAMETEST rows. Thank you for all of your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is is possible to optimize this query on a very large datatbase table?
Hi, We are using the following schema : CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, [Vertices] BLOB ) index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3| This table could potentially hold 10 to 40 million rows. We are using the following query to obtain the minumum rowid for each unique LastName: sqlite explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1 GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTes t where FieldName = t1.FIELDNAME); 0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX sqlite_autoindex_BlobLastNameTest_1 (~100 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX sqlite_autoindex_BlobLastNameTest_1 (FieldName=?) (~1 rows) This query runs in less than 5 seconds on the lastest version of sqlite with 2,700,000 rows. But what if we have 10 million rows to 40 million rows. Would it possible to optimize this query further? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?
Simon Slavin, Here is the schema which I used. CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25), [Vertices] BLOB ) With this schema it is possible to have multiple rows with the same FieldName. This is intentional since I am writing a Windows and Linux C++ multithreaded application whch uses a unique Database, Database table and database connection on each of 3 worker threads. I wanted to speed up the multithreaded application so I inserted into the table [BlobLastNameTest] without a index on FieldName. When the worker threads are finished inserting, I run create index on FieldName on each of the different tables on each of the database threads. CREATE INDEX claramary ON BlobLastNameTest(FieldName) I tried running my query without the claramary index but the query just hung. So that is why I created CREATE INDEX claramary ON BlobLastNameTest(FieldName) to supplement the rowid index. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large datatabase table
Igor Tandetnik, Here is a comparison of my query plan with your query plan on the latest version of sqlite. sqlite explain query plan select FieldName, min(rowid) from BlobLastNameTest group by FieldName; 0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary (~100 rows) sqlite explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1 GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTes t where FieldName = t1.FIELDNAME); 0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary (~100 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary (FieldName=?) (~1 rows) Your query plan apparently traverses the claramary index(CREATE INDEX claramary ON BlobLastNameTest(FieldName)) on the whole table(which may have side effects as the number of rows on my table grows from 2.7 million to 20 million) while my query plan also tries to execute correlated subquery which exploits the CREATE INDEX claramary ON BlobLastNameTest(FieldName). So, why is my query just working accidently? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible this optimize query on a very large database table
Florian Weimer, A better query needs support for DISTINCT ON, which SQLite lacks AFAIK. But speed will obviously be limited because you cannot avoid traversing the index for the whole table. I read your email and found it to be very interesting. PostgresSQL supports DISTINCT ON. However sqlite does not support it yet as you point you in your post. So, on the PostgresSQL thread http://www.postgresonline.com/journal/archives/4-Using-Distinct-ON-to-return-newest-order-for-each-customer.html , I found that they recommended simulating DISTINCT ON by using a subselect correlated subquery with either MAX(ROWID) OR MIN(ROWID) which is what I tried to do in my query. I was wondering if you knew when sqlite might add the DISTINCT ON clause. Also , is there any way which you can suggest where I can write a query or/and index which only traverses the index (CREATE INDEX claramary on BLOBLASTNAMETEST(FIELDNAME) for each distinct FIELDNAME/last name? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] conversion of MySQL database table to Sqlite database table (Kees Nuyt)
Kees Nuyt, I studied your producer-consumer multithreading further with respect to our proposed application. If the speed of MySQL reads is much faster than the the Speed of SQLite writes, then in the producer-consumer model , the consumer will spend a lot of time waiting for the SQLite write to complete. In our proposed application, each SQL write to the master database table actually writes to 4 other SQLite tables using SQLite User Defined Functions. So that is why the overlapping MySQL reads would be much faster than the SQLite Writes. Is it possible to modify the producer-consumer multithreading model to account for the fact that the SQL writes are much longer than the MySQL reads? In terms of other SQLite Optimizations, during the MySQL to SQLite conversion process, is it possible to deactive the SQLIie mutexes and SQLite locks since we will be writing to the SQLite database tables only and there will no concurrent SQlite readers during the MySQL to SQLite conversion. If that is not possible, could you briefly describe the other SQLite optimization which you mentioned your previous post? Thank you. Hi Frank, On Sat, 3 Sep 2011 02:55:17 -0400, Frank Chang frank_chan...@hotmail.com wrote: Good morning, Is it possible to multithread the conversion of MySQLite database table to the SQLite database? Dr. Richard Hipp said in a post recently that at that time it was not to multithread SQLite database connections to the same database. Dr. Hipp suggested that we use MYSQL to do that. I was wondering if it was possible to multi thread the initial data load of a process using MySQL and temporary tables using the MySQL C/C++ API . Then, in order to preserve the hundreds of SQLite code in our proximity matcher ,could we write a C++ class which allow us to multithread the conversion of the MySQL database B Trees to SQLite database B trees. I realize it may be impossible to do because SQLite does not allow the multithreading of SQLite database connections to the same database. You can multithread SQLite, preferably by using one connection per thread, and may be sharing the cache between them helps a bit in your situation. All connections have to be in the same process to allow that. You will have to handle SQLITE_BUSY and SQLITE_LOCKED. It will not help much, because SQLite only allows one writer at a time without blocking and the proces will probably be I/O bound anyway. However, it does make sense to try to overlap reads from MySQL with writes to SQLite, so having two threads might have some effect indeed, but I expect not much more than a producer/consumer model, where the producer reads MySQL into an in-memory buffer, and the consumer reads the buffer and writes to SQLite. That model is relatively easy to implement using system fifo's. The mbuffer utility allows you to optimize the size of the buffer. However, I realize SQlite has a lot of clever programmers who really understand the Sqlite internals and MYSQL internals and may be able to come up with some kind of workaround to speed up C/C++ MYSQL conversion to SQlite. There is no workaround when using the standard SQLite library. If you need parallel writes you will have to develop a new library using the same database file format. If that was really necessary and relatively easy, it would have been done already. Optimizations using the existing library are possible though. What did you already try to improve performance? How many rows per second can you handle now? Thank you. Hope this helps. -- ( Kees Nuyt ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to multithread the conversion of MySQLite database table to the SQLite database?
Good morning, Is it possible to multithread the conversion of MySQLite database table to the SQLite database? Dr. Richard Hipp said in a post recently that at that time it was not to multithread SQLite database connections to the same database. Dr. Hipp suggested that we use MYSQL to do that. I was wondering if it was possible to multi thread the initial data load of a process using MySQL and temporary tables using the MySQL C/C++ API . Then, in order to preserve the hundreds of SQLite code in our proximity matcher ,could we write a C++ class which allow us to multithread the conversion of the MySQL database B Trees to SQLite database B trees. I realize it may be impossible to do because SQLite does not allow the multithreading of SQLite database connections to the same database. However, I realize SQlite has a lot of clever programmers who really understand the Sqlite internals and MYSQL internals and may be able to come up with some kind of workaround to speed up C/C++ MYSQL conversion to SQlite. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to multithread the conversion of MySQLite database table to the SQLite database
Kees Nuyt, Thank you for very impressive and excellent reply to our question. I will show it to my boss and ask him for the data necessary to answer your question. I will also ask my boss , who is a director of product development, whether he will allow us to try to implement your producer -consumer multithreaded suggestion. Thank you for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the readperformanceofa C++ app using sqlite pragma journal_mode = wal
Simon Slavin, Thank you for your help with the PRAGMA page size question. Sometimes I see C++ programs which first issue the PRAGMA page_size = 4096. Then these C++ programs request a sqlite3_execute(Database,VACUUM,callback,0,Msg) which takes about 4 minutes to complete. We wondering if the sqlite3_execute(Database,VACUUM,callback,0,Msg) is necessary to change the PRAGMA page_size=4096. I know tomorrow is Memorial Day. So , we can wait for your answer if you should choose to answer our question. Thank you for help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the readperformanceof a C++ app using sqlite pragma journal_mode = wal pragma
Michael Stephenson, Thank you for all of your excellent ideas on increasing the speed of the deduper and the speed of the WAL reads, We will try these ideas. We were wondering if you could tell us what sqlite C/C++ API function to use to change the SQLIte page size to 4KB. Thank you for all of your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the readperformanceof a C++ app using sqlite pragma journal_mode = wal
Michael Stephenson, We want to thank you again for your excellent suggestions
regarding how to improve the speed of our Sqlite WAL reads and our deduper
prototype. We looked at the SQlite documentation for increasing the Sqlite page
size to 4K and an excerpt of our code is shown below. If we can implement your
suggestions corrrectly, we try to let you and the sqlite-users group about our
results if they are meaningful. Thank you for taking the time to write all of
your suggestions and ideas.
strcpy(Command,PRAGMA page_size=4096);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
printf(1 ReturnValue %d\n,ReturnValue);
status = sqlite3_step(Statement);
printf(1 status %d\n,status);
status = sqlite3_finalize(Statement);
printf(1 status %d\n,status);
strcpy(Command,VACUUM);
ReturnValue=sqlite3_exec(Database,Command,callback,0,Msg);
if (Msg!=0) {
strcpy(LastError,Msg);
sqlite3_free(Msg);
}
printf(1-1 ReturnValue %d\n,ReturnValue);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the read performance of a C++ app using sqlite pragma journal_mode = wal pragma wal_checkpoint?
Jean-Christophe Deschamps. Thank you for thoughtful reply. I will show it my boss when he return on Tuesday(Monday May 30th is Memorial Day. Thank you. Date: Fri, 27 May 2011 03:27:44 +0200 To: frank_chan...@hotmail.com From: j...@antichoc.net Subject: Re: [sqlite] Is it possible to optimize the read performance of a C++ app using sqlite pragma journal_mode = wal pragma wal_checkpoint? Here's the download link promised. The build is for x86. Take some time to carefully read the explanations at top of the source code. The function you may want to test is typos(). It can be significantly simplified/sped up for Levenshtein-only distance. I use it for instance to identify returning customers registering again with typos or small changes in names, first names, street address, city, etc. Also very useful for validating zip/city couples against a known-good table. -- Jean-Christophe Deschamps eMail: j...@antichoc.net SnailsTo: 1308, route du Vicot 40230 Saint Jean de Marsacq France GSM: +33 (0)6 15 10 19 29 Home:+33 (0)5 58 77 71 79 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal pragma
Roger Binns, Thank you suggesting that we run a benchmark that tests our prototype deduper with and without WAL using different page sizes and different transactions. You never answered the important bit - is your concern about initial population of the database or about runtime later on. I apologize for not answering your question. Our primary concern is about the runtime later on rather than the initial population of the database. Is it possible for you to recommend how we should use the latest sqlite distribution(i.e. pragmas,sqlite function parameters) if we are concerned about the run time later on. Thank you for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal pragmawal_checkpoint?
Simon Slavin, Thank you for your suggestion. Our deduper prototoype uses fuzzy matching methods such as the Levenshtein Distance to detect duplicates. We have found that these fuzzy matching methods are best implemented in C++ for processing time requirements. We would still like to know your experience with SQLite WAL databases compared to SQlite non-WAL databases. Particularly, we are in the sqlite read processing in SQLIte WAL databases. Is possible to SQLiTe WAL databases to have faster read processing than SQLite non-WAL databases. If so, what method to use to gain the read improvement? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal pragma
Roger Binns, Thank you for your reply. I understand what you are saying that we should drop the sqlite3_wal_checkpoint_v2(Database,main,SQLITE_CHECKPOINT_FULL, // number1,number2); after the commit transaction ReturnValue=sqlite3_prepare(Database,COMMIT,-1,Statement,0); status = sqlite3_step(Statement); sqlite3_finalize(Statement); We will try testing our deduper prototype using a sqlite WAL database without the wal checkpoint after commit. Would you expect us to get faster sqlite WAL reads without the wal checkpoint after commit? Is it possible there is another method for obtaining get faster sqlite WAL reads ? Thank you for your help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to optimize the read performance of a C++ app using sqlite pragma journal_mode = wal pragma wal_checkpoint?
Good evening, We are trying to build a C++ deduper application using the latest sqlite release. Our deduper runs in two phases. In the first phase it reads the records to be deduped from a Microsoft DBF file and writes the records into sqlite wal database. While writing the records to to the sqlite database we do a commit every 1 records. We think we understand that we also need to do a sqlite pragma wal_checkpoint everytime we do a sqlite database commit. However, we would like to know whether to use SQLITE_CHECKPOINT_PASSIVE or SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART in our pragma wal_checkpoint. We are using the asumption that now will be no sqlite database readers while we are writing to the sqlite wal database. If we have to write a busy-handler function in C/C++, how do we do it? In the second phase, we read the sqlite WAL database and try to find out the duplicates in our input records. Here, we are only reading the sqlite WAL database. We would like to find out how to optimize the read performance of the sqlite WAL database during the second phase of deduping? Please let us know if you have any suggestions. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is is it possible to close the random rowid gaps in a sqlite table?
Hi, I just ran this sqlite command to randomly delete 20% of the rows in a 15000 row sqlite table. DELETE FROM TABLE WHERE ROWID IN (SELECT ROWID FROM TABLE ORDER BY RANDOM() LIMIT 3000) Now there are gaps in the rowid in the modified table. I was wondering if it was possible to close the row id gaps and make them contiguous again. If it possible to close the gaps, what would be the sqlite statements I would have to run to close the rowid gaps ? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite 3.7.6.2: sqlite3VdeExec says we have a corrupted database
Good afternoon, We are using the latest version of sqlite, 3.7.6.2 in Windows
XP 32bit ( and Centos Linux 5.5). sqlite3_step, sqlite3_reset and
sqlite3VdeExec says we have a corrupted database. We are not using the WAL
mode.
When I using sqlite3.exe to query the database, the queries return
correct values. So I am wondering if our database is really corrupted.
Using the Windows C++ 2008 Debugger, we can isolate the detection of
the corrupted sqlite database to the following lines:
lines 64503 - 64513 of sqlite.c(sqlite3VdbeExec) shown below.
We were wondering how to fix this problem. Is our sqlite database
really corrupted or are we doing something wrong in our application. Below
lines 64504 - 64513 we show an excerpt of our application code.
/* If we have read more header data than was contained in the header,
** or if the end of the last field appears to be past the end of the
** record, or if the end of the last field appears to be before the end
** of the record (when all fields present), then we must be dealing
** with a corrupt database.
*/
if( (u.am.zIdx u.am.zEndHdr) || (u.am.offset u.am.payloadSize)
|| (u.am.zIdx==u.am.zEndHdr u.am.offset!=u.am.payloadSize) ){
rc = SQLITE_CORRUPT_BKPT;
goto op_column_out;
}
}
--APPLICATION CODE---
strcpy(SelectStatement, select [Key] from KeyFile order by rowid);
ReturnValue=sqlite3_prepare(Database,SelectStatement,-1,Statement,0);
int mm(0);
while (true){
status = sqlite3_step(Statement);
if (status == SQLITE_ROW) {
bytes = sqlite3_column_bytes(Statement,0);
Key = new char[bytes+1];
memset(Key,0,54);
memcpy(Key, sqlite3_column_text(Statement, 0),bytes);
char *filestart = KeyArray-operator [](Offset); //Memory Mapped File
strcpy(filestart[Offset],Key); //Memory Mapped File
Offset += Matchcode-KeySize();
delete [] Key;
mm += 1;
}
else{
resetstatus = sqlite3_reset(Statement);
KeyArray-AddFinalRange(); // Memory Mapped File
break;
}
}
status = sqlite3_finalize(Statement);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite WAL mode
Michael D. Black, Thank you for your suggestion use the sqlite 3.7 WAL mode. We
wrote a small test program to open 2 WAL connections to the main database and
insert 5.4 million rows into a table. The code is shown below. We wiil add
sqlite error handling handling code tomorrow.
The program appears to be running okay but we noticed it took 5 minutes for
the sqlite3_open corresponding to the DROP TABLE statement to complete. Is
there anything we can do to speed up the DROP TABLE? Thank you.
sprintf(Path,%s/mdMatchup.dat,ConfigPath);
if (sqlite3_open(Path,Database)!=SQLITE_OK) {
return mdMUBatchUpdate::ErrorConfigFile;
}
sprintf(Path,%s/mdMatchup.dat,ConfigPath);
if (sqlite3_open(Path,Database2)!=SQLITE_OK) {
return mdMUBatchUpdate::ErrorConfigFile;
}
ReturnValue = sqlite3_create_function(Database, msign, 4, SQLITE_UTF8, NULL,
cIntersectingGroupCache::msignFunc, NULL, NULL);
ReturnValue = sqlite3_create_function(Database2, CombineBlob, 3, SQLITE_UTF8,
NULL,
cIntersectingGroupCache::CombineBlobFunc, NULL, NULL);
strcpy(Command,PRAGMA journal_mode=wal);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
ReturnValue=sqlite3_prepare(Database2,Command,-1,Statement2,0);
status = sqlite3_step(Statement2);
status = sqlite3_finalize(Statement2);
sprintf(Command,SELECT COUNT(*) FROM sqlite_master WHERE [Name]=\KeyFile\);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
if (status==SQLITE_ROW){
Count = sqlite3_column_int(Statement,0);
}
status = sqlite3_finalize(Statement);
if (Count == 0){
strcpy(Command,CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR
(256), [DupeGroup] INTEGER) );
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
}
else {
strcpy(Command,DROP TABLE [KeyFile]);
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
strcpy(Command,CREATE TABLE [KeyFile] ([Key] CHAR (256), [UserInfo] CHAR
(256), [DupeGroup] INTEGER) );
ReturnValue=sqlite3_prepare(Database,Command,-1,Statement,0);
status = sqlite3_step(Statement);
status = sqlite3_finalize(Statement);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_busy_handler
I wanted to thank Michael D. Black and Simon Slavin for replying to my question. I was wondering how long the sqlite_busy_handler should sleep for before SQLite tries to access the datbase again. Our chief engineer was wondering whether the writing function could set an event when the write finished. The purpose of this event would be to notify the sqlite_busy_handler that the write finished so that the sqlite_busy_handler could use something like WaitForSingleObject before returning from the callback function. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_busy_handler
Michael D. Black, I will try different batch sizes so that other processes do their thing potentially. I was wondering why other selects who need to only read from a sqlite database need to use the sqlite3_busy_handlers. Is it because database connection which are writing to the sqlite database have a higher priority then database connections which are reading from the sqlite database? Thank you for your suggestions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to determine how many openconnections are active for a sqlite database?
I wanted to thank Roger Binns for solving my problem. Using sqlite3_request, I was able to determine the sqlite database was corrupted when I didn't issue a BEGIN EXCLUSIVE before beginning to insert the 5.4 million rows. Evidently, the use of BEGIN EXCLUSIVE prevents my transaction from being interrupted by another connection from the same process. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] iIs it possible to determine how many open connections are active for a sqlite database?
Good evening, I am trying to insert 5.4 million rows to a sqlite database running in Windows or Unix/Linux. I am using a transaction to try to speed up the insertions. When the sqlite database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. As a result, I periodically get a sqlite return code of 1 from sqlite3_step after inserting one of the 5.4 million rows. I am running Process Explorer on windows to see if another user or process has a connection to the sqlite database table. The Process explorer tells me my executable is the only process with connections to the sqlite database. The database locking problem is causing me to skip insertions. I would like to verify if my executable does indeed have multiple connections to the sqlite database. Is there a sqlite API does can tell me how many open connections my executable is responsible for? Also, it is possible to determine the lines of code(i.e like a gdb backtrace) in my application are responsible for each of the multiple connection? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] iIs it possible to determine how many open connections are active for a sqlite database?
I did some research into this topic . I read the following sqlite-users posts http://www.mail-archive.com/sqlite-users@sqlite.org/msg25752.html and http://www.mail-archive.com/sqlite-users@sqlite.org/msg25762.html . From these posts, its says as of 2007, there is no sqlite public api for querying the number of open connections to a sqlite database. In UNIX and Linux , there is /usr/sbin/lsof name-of-sqlite-database. Is there any way that I can execute lsof from within the program inserting the 5.4 million rows whenever sqlite3_step returns error code 1 after inserting a row. It appears that once the program executes the lsof it much exit. Or is it possible for the parent process to resuming running again Also, I was wondering about windows. Process explorer is a good tool. However, is there any open source program or source code then can print information about each open connection? Thank you. From: frank_chan...@hotmail.com To: sqlite-users@sqlite.org Subject: iIs it possible to determine how many open connections are active for a sqlite database? Date: Fri, 18 Feb 2011 22:24:38 -0500 Good evening, I am trying to insert 5.4 million rows to a sqlite database running in Windows or Unix/Linux. I am using a transaction to try to speed up the insertions. When the sqlite database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. As a result, I periodically get a sqlite return code of 1 from sqlite3_step after inserting one of the 5.4 million rows. I am running Process Explorer on windows to see if another user or process has a connection to the sqlite database table. The Process explorer tells me my executable is the only process with connections to the sqlite database. The database locking problem is causing me to skip insertions. I would like to verify if my executable does indeed have multiple connections to the sqlite database. Is there a sqlite API does can tell me how many open connections my executable is responsible for? Also, it is possible to determine the lines of code(i.e like a gdb backtrace) in my application are responsible for each of the multiple connection? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible to update the same sqlite table using two different Linux pthread threads?
Good morning, If we use the latest version of sqlite, is it possible to
update the same sqlite table using two different Centos/RedHat Linux pthread
threads? We are using the same database connection on both pthreads but we
always update different rows on each of the two threads. We have run
some tests using the following code and occasionally sqlite3_prepare and
sqlite2_step returns error code 21. Here is the thread function which each
thread runs. Below it, we show the main thread which creates and manages the
worker threads. Thank you.
#if defined(__unix)
void cIntersectingGroupCache::BruteForceDistillation(void* param){
char UpdateStatement[256];
char WhereCondition[256];
int bMatch1;
int ReturnValue;
int status;
char* blob2;
int ii,j;
THREADDATA* td;
td = (THREADDATA*)(param);
ReturnValue=sqlite3_prepare(td-This-Database,BEGIN,-1,(td-This-Statement),0);
status = sqlite3_step(td-This-Statement);
status = sqlite3_finalize(td-This-Statement);
for (ii = td-Begin; ii = td-End; ii++){
for (j = ii+1; j = td-TableSize; j++){
if (td-DistillationType == pFuzzyCompare)
switch (td-Fuzzy ) {
case mfAccurateNear:
bMatch1=td-This-AccurateNearCompare(td-FieldNameBlock +
(ii - 1)* td-ComponentSize,
td-FieldNameBlock + (j - 1)*
td-ComponentSize,td-ComponentSize,
td-Near);
break;
default:
bMatch1=(memcmp(td-FieldNameBlock + (ii - 1)* td-ComponentSize,
td-FieldNameBlock + (j - 1)*
td-ComponentSize,td-ComponentSize) == 0);
break;
}
if (bMatch1){
sprintf(UpdateStatement,
update %s set [Vertices] = CombineBlob(Vertices,%d,(select
Vertices from %s where rowid = %d)) ,td-TableName,
td-This,td-TableName,td-RowNumberBlock[j-1]);
sprintf(WhereCondition, where rowid = %d ,
td-RowNumberBlock[ii-1]);
strcat(UpdateStatement,WhereCondition);
td-This-m_cs.Lock(); // critical section
ReturnValue=sqlite3_prepare(td-This-Database,UpdateStatement,-1,(td-This-Statement),0);
status = sqlite3_step(td-This-Statement);
status = sqlite3_finalize(td-This-Statement);
sprintf(UpdateStatement,
update %s set [Vertices] = ?,td-TableName);
sprintf(WhereCondition, where rowid = %d , td-RowNumberBlock[j-1]);
strcat(UpdateStatement,WhereCondition);
ReturnValue=sqlite3_prepare(td-This-Database,UpdateStatement,-1,(td-This-Statement),0);
int image[] = {0};
blob2 = reinterpret_castchar*(image);
status = sqlite3_bind_blob(td-This-Statement, 1, blob2, 0,NULL );
status = sqlite3_step(td-This-Statement);
status = sqlite3_finalize(td-This-Statement);
td-This-m_cs.UnLock();
td-RowNumberBlock[j - 1] = td-RowNumberBlock[ii - 1];
}
}
}
ReturnValue=sqlite3_prepare(td-This-Database,COMMIT,-1,(td-This-Statement),0);
status = sqlite3_step(td-This-Statement);
status = sqlite3_finalize(td-This-Statement);
pthread_t current = pthread_self();
int i;
for (i = 0; i td-This-TotalThreads; i++) {
if (pthread_equal(td-This-ThreadList[i], current)) {
// signal condition variables when thread function
BruteForceDistillation
// is finished
pthread_mutex_lock((td-This-mMutex));
td-This-ThreadList[i] = 0; // thread function is finished
pthread_mutex_unlock((td-This-mMutex));
pthread_cond_signal((td-This-mConditionVariable));
return;
}
}
}
#endif
// main thread which creates threads and manages worker threads
#if defined(__unix)
mCritSect.Lock();
ConstructThread(ThreadList[0], (void *(*)(void
*))BruteForceDistillation, DataOfThread1);
ConstructThread(ThreadList[1], (void *(*)(void
*))BruteForceDistillation, DataOfThread2);
mCritSect.UnLock();
mCritSect.Lock();
pthread_mutex_lock(mMutex);
while (true) {
// check if ThreadList array has unused element
for (nn = 0; nn TotalThreads; nn++) {
if (ThreadList[nn] == 0) {
pthread_mutex_unlock(mMutex);
mCritSect.UnLock();
continue;
}
}
if (ThreadList[0] == 0 ThreadList[1] == 0)
break;
// wait on mConditionVariable for ThreadList array element to become
available
pthread_cond_wait(mConditionVariable, mMutex);
}
#endif
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is locked
Hello, Yesterday, we recognized that we had two concurrent SQL Server
threads reading and writing to the same sqlite database. Furthermore,the reader
thread was not releasing it's lock. So, now we release the lock by commiting
the transaction. As a result, we no longer get the SQLite database is locked
message. Thank you
Retval = Keys-Execute(BEGIN EXCLUSIVE);
sprintf(Command,SELECT [Key], [RowId], [DupeGroup]
FROM [Keys] WHERE [Cluster]=\%*.*s\,BlockSize,BlockSize,_Key);
Keys-Prepare(Command);
while (Keys-Step()==SQLITE_ROW) {
Keys-ColumnText(0,TestKey);
if ((rc=CompareKeys(TestKey,_Key,0))!=0) {
ErrorMask|=rc;
if (DupeCount=DedupeBlockSize)
IncreaseDedupeBlocks();
RowIds[DupeCount]=Keys-ColumnInt(1);
DupeGroups[DupeCount]=Keys-ColumnInt(2);
}
}
Retval = Keys-Execute(COMMIT);
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurantsform=MFESRPpubl=WLHMTAGcrea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database is locked
Jay Kreibich, Thank for your reply about sqlite3_busy_timeout. We
found that the sqlite 'database is locked' error message can be fixed by
updating two different tables in the two SQL Server 2005 client processes.
UPDATE mdMatchUp SET
MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental, Matchcode,
Zip, Last, First, Address, NULL, NULL, NULL, NULL, NULL, NULL),
@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),
Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),
DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)
Perhaps, the problem we were experiencing with the sqlite 'database is locked'
error message is related to SQL Server 2005 locks.
The SQL Server 2005 extended stored procedure
master.dbo.mdMUIncrementalAddRecord(@Incremental) is a wrapper around the C/C++
code:
sprintf(Command,INSERT INTO [Keys] ([Key], [Cluster],
[DupeGroup]) VALUES (\%s\, \%*.*s\, %d),
MCKey,BlockSize,BlockSize,MCKey,DupeGroup);
while (Keys-Execute(Command)==SQLITE_BUSY) {
#if defined(__unix)
sleep(dRETRYDELAY);
#else
Sleep(dRETRYDELAY*1000);
#endif
}
Thank you.
_
Hotmail: Trusted email with powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141665/direct/01/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database is locked
We have an application which uses Microsoft SQL Server 2005 Extended
stored procedures in conjunction with Sqlite databases. We have a C++ DLL which
uses the following code to insert rows into a SQLite database:
sprintf(Command,INSERT INTO [Keys] ([Key], [Cluster],
[DupeGroup]) VALUES (\%s\, \%*.*s\, %d),
MCKey,BlockSize,BlockSize,MCKey,DupeGroup);
while (Keys-Execute(Command)==SQLITE_BUSY) {
#if defined(__unix)
sleep(dRETRYDELAY);
#else
Sleep(dRETRYDELAY*1000);
#endif
}
We use SQL Server 2005 Extended Stored Procedures and User Defined functions
in the following manner in order to insert into the SQLite table using the
above C++ code:
UPDATE MyTestDatabase SET
MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental, @Matchcode,
Column12, Column53, Column52, Address, NULL, NULL, NULL, NULL, NULL, NULL),
@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),
Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),
DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)
When we run this UPDATE statement from two SQL Server 2005 Management
Studio clients concurrently, one of the client processes returns with the error
code Database is locked and the other client process is suspended. Has anyone
seen this problem? Is it a SQL Server 2005 problem or does it have anything to
do with Sqlite? In particular, we are wondering why we get the SQLite error
message database is locked when we are running concurrent SQL Server client
processes? When we run just one SQL Server client process, everything works
fine and we get no SQLite error messages. Thank you.
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurantsform=MFESRPpubl=WLHMTAGcrea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
