I think you can drop the following indexes as well CREATE INDEX FirstNameIndex ON PERSON(FirstName); CREATE INDEX LastNameIndex ON PERSON(LastName); CREATE INDEX CompanyIndex ON PERSON(Company);
They are redundant in that you have compound indexes that start with each of the above. That should improve your insert performance. --- On Mon, 1/5/09, D. Richard Hipp <[email protected]> wrote: > From: D. Richard Hipp <[email protected]> > Subject: Re: [sqlite] Big performance regressions since 3.4.0? > To: "General Discussion of SQLite Database" <[email protected]> > Date: Monday, January 5, 2009, 7:16 PM > Version 3.5.3 made a change to the way DISTINCT is > processed. > Probably that change is making your particular case much > slower. The > change can be seen at: > > http://www.sqlite.org/cvstrac/chngview?cn=4538 > > This change was in response to grumbling on the mailing > list > > > http://www.mail-archive.com/[email protected]/msg28894.html > > It would appear that I need to spend some time improving > this > optimization - enabling it only in cases where it seems > likely to > improve performance and disabling it in cases like yours > were it makes > things much worse. We'll try to have a look at that > for version 3.6.9. > > > On Jan 5, 2009, at 7:13 PM, Jason Freund wrote: > > > Dan, > > > > Thanks, I sent a separate email containing the source > and data for > > my test app since it's too big (5M) to post to the > group. But I'm > > also posting just the code for the test app here, in > case anyone > > wants to take a look. On my target hardware, the > results I got for > > the most egregious regression (doing prepare and step > on a > > selection) were: > > > > 3.4.0 3.6.5 > > 100 records 4ms 20ms > > 500 records 1.4ms 89ms > > 1000 records 1.5ms 173ms > > > > If anyone else is interested in seeing the data to try > to reproduce > > and track the problem, please let me know, and > I'll send you the > > full zip file. > > > > Thanks, > > Jason > > > > > > === > > / > > > ****************************************************************************** > > * This app prepares a statement and steps 50 times in > a database of > > 100, > > * 500 or 1000 records, using a SQL statement that > seems to demonstrate > > * a performance regression between 3.4.0 and 3.6.5 > > * > > * The raw results we got when running this test on > some cellphone > > reference > > * hardware running an ARM5/~600Mhz chip and NAND > storage are: > > * 3.4.0 3.6.5 > > * N=100, Prepare, StepX50 4ms 20ms > > * N=500, Prepare, StepX50 1.4ms 89 > > * N=1000 Prepare, StepX50 1.5ms 173ms > > * > > * While this test demonstrates the worst degradation: > constant > > performance > > * for varying data sizes with 3.4.0 and linear > performance for > > 3.6.5, there > > * were several other test cases that were linear on > both 340 and > > 365, but > > * were consistently 30% slower for 365. These other > cases included a > > * different query, and a couple of INSERT statements, > but we didn't > > * include those results in this test. > > * > > * Any advice or insight on this case would be > appreciated: > > * [email protected] > > > *****************************************************************************/ > > > > #include <string.h> > > #include <stdio.h> > > #include <sqlite3.h> > > #include <wchar.h> > > #include <glib.h> > > #include <sys/time.h> > > > > /* Return delta between oldTime - currentTime in > millisecons */ > > float TimeInterval(struct timeval *oldTvP) > > { > > struct timeval tv; > > gettimeofday(&tv, NULL); > > > > if(oldTvP == NULL) > > return(0); > > > > tv.tv_sec -= oldTvP->tv_sec; > > if (tv.tv_usec < oldTvP->tv_usec) { > > tv.tv_usec += 1000000 - oldTvP->tv_usec; > > tv.tv_sec--; > > } else { > > tv.tv_usec -= oldTvP->tv_usec; > > } > > > > return (((float)tv.tv_sec*1000) + > tv.tv_usec/1000.0f); > > } > > > > static int my_utf8_collator(void* user_data, int > Length1,const void* > > Start1, > > int Length2, const void* Start2) > > { > > int Result = 0; > > > > gchar* Char1 = (gchar*) Start1; > > gchar* Stop1 = Char1 + Length1; > > wchar_t Wide1[2] = {0, 0}; > > > > gchar* Char2 = (gchar*) Start2; > > gchar* Stop2 = Char2 + Length2; > > wchar_t Wide2[2] = {0, 0}; > > > > while (0 == Result && Char1 < Stop1 > && Char2 < Stop2) > > { > > Wide1[0] = (wchar_t) g_utf8_get_char(Char1); > > Wide2[0] = (wchar_t) g_utf8_get_char(Char2); > > > > Result = wcscoll(Wide1, Wide2); > > > > if (0 == Result) > > { > > Char1 = g_utf8_next_char(Char1); > > Char2 = g_utf8_next_char(Char2); > > } > > } > > > > if (Result == 0) { > > Result = Length1 - Length2; > > } > > > > return Result; > > } > > > > void SelectionTest(char *dbName, int category) > > { > > sqlite3 *dbH = NULL; > > struct timeval oldTv; > > float totalTime=0.0; > > const unsigned char *junkStringP = NULL; > > char *dummy = NULL, sqlStrP[512]; > > int sqlErr, loopCount=0, junk; > > sqlite3_stmt *stmtP = NULL; > > char *selectSql = "SELECT DISTINCT > PERSON.PersonLUID, FirstName, > > LastName, Company, EnglishName, Note NOTNULL, > SIMPhoneBook, > > DisplayNameLast FROM PERSON, _CATEGORY_MEMBERSHIP > WHERE > > _CATEGORY_MEMBERSHIP.EntityLUID = PERSON.PersonLUID > AND > > _CATEGORY_MEMBERSHIP.CategoryLUID = %d ORDER BY > LastName, FirstName, > > Company, SIMPhoneBook, PERSON.PersonLUID COLLATE > my_utf8_collator > > LIMIT 100 OFFSET 0;"; > > char *selectSql2 = "SELECT DISTINCT > PERSON.PersonLUID, FirstName, > > LastName, Company, EnglishName, Note NOTNULL, > SIMPhoneBook, > > DisplayNameLast FROM PERSON ORDER BY LastName, > FirstName, Company, > > SIMPhoneBook, PERSON.PersonLUID COLLATE > my_utf8_collator LIMIT 100 > > OFFSET 0;"; > > > > /* Open SQLite database connection */ > > sqlErr = sqlite3_open(dbName, &dbH); > > if (sqlErr) { > > printf("sqlite3_open(%s) = 0x%x\n", > dbName, sqlErr); > > } > > > > /* Install user UTF-8 collate function */ > > sqlErr = sqlite3_create_collation( > > dbH, /* (sqlite3 *) */ > > "my_utf8_collator", /* (const char *)zName > */ > > SQLITE_UTF8, /* int enc */ > > NULL, /* (void *)pCtx */ > > &my_utf8_collator /* int(xCompare) */ > > ); > > if (sqlErr) { > > printf("sqlite3_create_collation = > 0x%x\n", sqlErr); > > } > > > > /* Build first SQL statement, with JOIN */ > > sprintf(sqlStrP, selectSql, category); > > > > /* Start timer, prepare statement and step up to 50 > times */ > > gettimeofday(&oldTv, NULL); > > sqlErr = sqlite3_prepare(dbH, sqlStrP, > strlen(sqlStrP), > > &stmtP, (const char **) &dummy); > > > > while (((sqlErr = sqlite3_step(stmtP)) == SQLITE_ROW) > && loopCount > > < 50) { > > /* Include a few column fetches from each row in the > timing */ > > junk = sqlite3_column_int(stmtP, 0); > > junkStringP = sqlite3_column_text(stmtP, 7); > > junk = sqlite3_column_int(stmtP, 6); > > junk = sqlite3_column_int(stmtP, 5); > > > > loopCount++; > > } > > > > /* Finalize statement, stop timer, and report results > */ > > sqlErr = sqlite3_finalize(stmtP); > > totalTime = TimeInterval (&oldTv); > > printf("A) Create select statement with > Category and iterate %d " > > "times = %3.3f ms\n", loopCount, > totalTime); > > > > /* Start timer, prepare statement and step up to 50 > times */ > > gettimeofday(&oldTv, NULL); > > sqlErr = sqlite3_prepare(dbH, selectSql2, > strlen(selectSql2), > > &stmtP, (const char **) &dummy); > > > > while (((sqlErr = sqlite3_step(stmtP)) == SQLITE_ROW) > && loopCount > > < 50) { > > /* Include a few column fetches from each row in the > timing */ > > junk = sqlite3_column_int(stmtP, 0); > > junkStringP = sqlite3_column_text(stmtP, 7); > > junk = sqlite3_column_int(stmtP, 6); > > junk = sqlite3_column_int(stmtP, 5); > > > > loopCount++; > > } > > > > /* Finalize statement, stop timer, and report results > */ > > sqlErr = sqlite3_finalize(stmtP); > > totalTime = TimeInterval (&oldTv); > > printf("B) Create select statement (no > Category) and iterate %d " > > "times = %3.3f ms\n\n", loopCount, > totalTime); > > > > /* Close DB */ > > sqlErr = sqlite3_close(dbH); > > } > > > > /* Run 3 queries and report timings */ > > int main(int argc, char *argv[]) > > { > > const char *versionP; > > > > versionP = sqlite3_libversion(); > > printf("SQLite %s\n", versionP); > > > > SelectionTest("contacts_100.db", > -1073743423); > > SelectionTest("contacts_500.db", > -1073743576); > > SelectionTest("contacts_1000.db", > -1073741927); > > > > return 0; > > } > > > > / > > *Schema > > > ********************************************************************** > > BEGIN TRANSACTION; > > > > CREATE TABLE PERSON( > > PersonLUID integer not null primary key, > > FirstName text default null collate nocase, > > MiddleName text default null, > > LastName text default null collate nocase, > > Title text default null, > > Company text default null collate nocase, > > Prefix text default null, > > Suffix text default null, > > NickName text default null, > > EnglishName text default null, > > YomiFirstName text default null, > > YomiLastName text default null, > > YomiCompany text default null, > > Ringtone text default null, > > VideoRingtone text default null, > > Note text default null, > > > > FriendlyName text default null, > > DisplayNameLast text default null, > > DisplayNameCompany text default null, > > DisplayNameFirst text default null, > > TwoCharLookup text default null, > > > > Birthday integer default null, > > BirthdayAlarm integer default null, > > Anniversary integer default null, > > AnniversaryAlarm integer default null, > > > > UseCount integer default 0, > > DontBeamNote boolean default false, > > IsBusinessCard boolean default false, > > > > SIMPhoneBook integer default 0, > > SIMEntryID integer default 0 > > ); > > > > CREATE INDEX FirstNameIndex ON PERSON(FirstName); > > CREATE INDEX LastNameIndex ON PERSON(LastName); > > CREATE INDEX CompanyIndex ON PERSON(Company); > > CREATE INDEX UseCountIndex ON PERSON(UseCount); > > CREATE INDEX TwoCharLookupIndex ON > PERSON(TwoCharLookup); > > CREATE INDEX LastFirstCompanyIndex ON > > PERSON(LastName,FirstName,Company,SIMPhoneBook); > > CREATE INDEX FirstLastCompanyIndex ON > > PERSON(FirstName,LastName,Company,SIMPhoneBook); > > CREATE INDEX CompanyLastFirstIndex ON > > PERSON(Company,LastName,FirstName,SIMPhoneBook); > > > > CREATE TABLE ADDRESS( > > AddressLUID integer primary key default 1, > > PersonLUID integer not null, > > Identifier text default null, > > Address text default null, > > City text default null, > > State text default null, > > PostalCode text default null, > > Country text default null, > > VCardSrc text default null > > ); > > > > CREATE INDEX AddressPersonLUIDIndex ON > ADDRESS(PersonLUID); > > > > CREATE TABLE EXTRAFIELDS( > > ExtraFieldLUID integer primary key default 1, > > PersonLUID integer not null, > > Identifier text default null, > > Value text default null, > > VCardSrc text default null > > ); > > > > CREATE INDEX ExtraFieldPersonLUIDIndex ON > EXTRAFIELDS(PersonLUID); > > > > CREATE TABLE CONTACTS( > > ContactLUID integer primary key default 1, > > PersonLUID integer not null, > > Identifier text default null, > > Value text default null, > > ContactType text default null, > > UseCount integer default 0, > > DefaultContact boolean default false, > > SpeedDial integer default 0, > > VCardSrc text default null > > ); > > > > CREATE INDEX ContactPersonLUIDIndex ON > CONTACTS(PersonLUID); > > > > CREATE TABLE THUMBNAIL( > > ThumbnailLUID integer primary key default 1, > > PersonLUID integer not null, > > SmallThumbnail blob not null, > > LargeThumbnail blob not null > > ); > > > > CREATE INDEX ThumbnailPersonLUIDIndex ON > THUMBNAIL(PersonLUID); > > > > CREATE TABLE CUSTOMLABELS( > > CustomLabelLUID integer primary key default 1, > > Label text not null, > > Identifier text not null > > ); > > > > CREATE TABLE VERSION( > > Version integer primary key default 1 > > ); > > > > INSERT INTO VERSION VALUES(3); > > > > CREATE TABLE LOADEDDEFAULTS( > > HasDefaults integer primary key default 0 > > ); > > > > CREATE TABLE CATEGORY_RINGTONES( > > CategoryLUID integer not null, > > Ringtone text default null, > > RingtoneType integer default 0 > > ); > > > > CREATE TABLE RECORD_COUNT_CACHE( > > CategoryLUID integer not null primary key, > > RecordCount integer default null > > ); > > > > CREATE TABLE DISTRIBUTION( > > DistributionLUID integer primary key default 1, > > Value text not null, > > DistributionType text not null > > ); > > > > CREATE TABLE DISTRIBUTION_MEMBERSHIP( > > MembershipLUID integer primary key default 1, > > DistributionLUID integer not null, > > PersonLUID integer not null, > > Identifier text not null > > ); > > > > CREATE INDEX DistMembershipIndex ON > > > DISTRIBUTION_MEMBERSHIP(DistributionLUID,PersonLUID,Identifier); > > > > COMMIT; > > > > BEGIN TRANSACTION; > > > > CREATE TABLE _CATEGORY( > > CategoryLUID integer not null primary key default 1, > > Name text not null, > > ReadOnly int not null > > ); > > > > CREATE TABLE _CATEGORY_MEMBERSHIP( > > CategoryMemberLUID integer not null primary key > default 1, > > CategoryLUID integer not null, > > EntityLUID integer not null > > ); > > > > COMMIT;"; > > > *****************************************************************************/ > > > > > > On Dec 25, 2008, at 5:01 AM, Jason Freund wrote: > > > >> Hi, > >> > >> > >> > >> We recently upgraded from 3.4.0 to 3.6.5, and > noticed that our > >> little, > >> internal sqlite benchmark test program performed > much worse on most > >> operations with the new version. We expected to > see an improvement > >> because 3.6.1 advertises a performance enhancement > with the lookaside > >> buffer. > >> > >> > >> > >> Our small performance test app showed regressions > averaging 25% > >> slowdown > >> for all read and write operations, but one test > case was very > >> striking: > >> going from 2ms in 3.4.0 to 170ms in 3.6.5. This > test prepared a > >> "SELECT > >> DISTINCT from one table with an ORDER BY, COLLATE, > and LIMIT" and > >> then > >> stepped 50 times through that statement. > >> > >> > >> > >> I was wondering if anyone else encountered a > regression, and > >> whether I > >> can interest someone from the dev community in > looking into it, or > >> whether I should file a bug? Also, I may be able > to do more builds > >> to > >> help isolate when the regressions were introduced, > but I'm not sure > >> if > >> there is an easy way to download code for earlier > releases? I > >> haven't > >> used cvs before - is there an easy way to checkout > the code for some > >> kind of "label" that represents an > official release, or is there a > >> depot > >> somewhere that I can just download earlier > distros? > > > > If you post the performance test program I'll take > a look at it. > > > > Dan. > > > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > [email protected] > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
