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:
* jason.fre...@access-company.com
*****************************************************************************/
#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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
