This is a working proof of concept patch against sqlite 3.5.1 that
can improve query speed by as much as 50% when floating point or
64 bit integer constants are used. It does so by eliminating repeated
parsing of numbers from strings in OP_Real and OP_Int64 by storing
the parsed binary numbers in the otherwise unused P1 and P2 opcode
arguments. The string form of the number is still retained in P3.
Although "make test" passes with no errors, the patch does not attempt
to check for out of memory conditions. Comments or bug reports are
welcome.
Example:
CREATE TABLE n1(a integer primary key);
INSERT INTO "n1" VALUES(1);
INSERT INTO "n1" VALUES(2);
INSERT INTO "n1" VALUES(3);
INSERT INTO "n1" VALUES(4);
INSERT INTO "n1" VALUES(5);
INSERT INTO "n1" VALUES(6);
INSERT INTO "n1" VALUES(7);
INSERT INTO "n1" VALUES(8);
INSERT INTO "n1" VALUES(9);
INSERT INTO "n1" VALUES(10);
CREATE VIEW vu as select v3.a a from n1 v1,n1 v2,n1 v3,n1 v4,n1 v5,n1 v6,n1 v7;
select sum(a-1234567890.12345678+123456789012345) from vu where a>1;
Without the patch, the SQL above completes in 15.1 seconds on my machine.
With the patch it takes just 10.4 seconds.
I hereby place this code in the public domain.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Index: src/expr.c
===================================================================
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.313
diff -u -3 -p -r1.313 expr.c
--- src/expr.c 18 Sep 2007 15:55:07 -0000 1.313
+++ src/expr.c 17 Oct 2007 23:01:01 -0000
@@ -1680,6 +1680,32 @@ void sqlite3CodeSubselect(Parse *pParse,
}
#endif /* SQLITE_OMIT_SUBQUERY */
+static void codeReal(Vdbe *v, const char *z, int n){
+ double r;
+ int p1, p2, rc;
+ char *buf = sqlite3_malloc(n+1);
+ memcpy(buf, z, n);
+ buf[n] = 0;
+ rc = sqlite3AtoF(buf, &r); // XXX check rc
+ sqlite3_free(buf);
+ memcpy((char*)&p1, (char*)&r, 4);
+ memcpy((char*)&p2, 4+(char*)&r, 4);
+ sqlite3VdbeOp3(v, OP_Real, p1, p2, z, n);
+}
+
+static void codeInt64(Vdbe *v, const char *z, int n){
+ i64 r;
+ int p1, p2, rc;
+ char *buf = sqlite3_malloc(n+1);
+ memcpy(buf, z, n);
+ buf[n] = 0;
+ rc = sqlite3Atoi64(buf, &r); // XXX check rc
+ sqlite3_free(buf);
+ memcpy((char*)&p1, (char*)&r, 4);
+ memcpy((char*)&p2, 4+(char*)&r, 4);
+ sqlite3VdbeOp3(v, OP_Int64, p1, p2, z, n);
+}
+
/*
** Generate an instruction that will put the integer describe by
** text z[0..n-1] on the stack.
@@ -1691,9 +1717,9 @@ static void codeInteger(Vdbe *v, const c
if( sqlite3GetInt32(z, &i) ){
sqlite3VdbeAddOp(v, OP_Integer, i, 0);
}else if( sqlite3FitsIn64Bits(z) ){
- sqlite3VdbeOp3(v, OP_Int64, 0, 0, z, n);
+ codeInt64(v, z, n);
}else{
- sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
+ codeReal(v, z, n);
}
}
}
@@ -1772,14 +1798,18 @@ void sqlite3ExprCode(Parse *pParse, Expr
codeInteger(v, (char*)pExpr->token.z, pExpr->token.n);
break;
}
- case TK_FLOAT:
case TK_STRING: {
- assert( TK_FLOAT==OP_Real );
assert( TK_STRING==OP_String8 );
sqlite3DequoteExpr(pParse->db, pExpr);
sqlite3VdbeOp3(v, op, 0, 0, (char*)pExpr->token.z, pExpr->token.n);
break;
}
+ case TK_FLOAT: {
+ assert( TK_FLOAT==OP_Real );
+ sqlite3DequoteExpr(pParse->db, pExpr);
+ codeReal(v, (char*)pExpr->token.z, pExpr->token.n);
+ break;
+ }
case TK_NULL: {
sqlite3VdbeAddOp(v, OP_Null, 0, 0);
break;
@@ -1879,9 +1909,12 @@ void sqlite3ExprCode(Parse *pParse, Expr
assert( pLeft );
if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
Token *p = &pLeft->token;
- char *z = sqlite3MPrintf(pParse->db, "-%.*s", p->n, p->z);
+ char *z = sqlite3_malloc(p->n+2);
+ z[0] = '-';
+ memcpy(z+1, p->z, p->n);
+ z[p->n+1] = 0;
if( pLeft->op==TK_FLOAT ){
- sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1);
+ codeReal(v, z, p->n+1);
}else{
codeInteger(v, z, p->n+1);
}
Index: src/vdbe.c
===================================================================
RCS file: /sqlite/sqlite/src/vdbe.c,v
retrieving revision 1.651
diff -u -3 -p -r1.651 vdbe.c
--- src/vdbe.c 5 Oct 2007 16:23:55 -0000 1.651
+++ src/vdbe.c 17 Oct 2007 23:01:02 -0000
@@ -713,9 +713,10 @@ case OP_Int64: {
assert( pOp->p3!=0 );
pTos->flags = MEM_Str|MEM_Static|MEM_Term;
pTos->z = pOp->p3;
- pTos->n = strlen(pTos->z);
+ pTos->n = strlen(pTos->z); /* should probably put strlen in pOp */
pTos->enc = SQLITE_UTF8;
- pTos->u.i = sqlite3VdbeIntValue(pTos);
+ memcpy( (char*)&pTos->u.i, (char*)&pOp->p1, 4);
+ memcpy(4+(char*)&pTos->u.i, (char*)&pOp->p2, 4);
pTos->flags |= MEM_Int;
break;
}
@@ -728,9 +729,10 @@ case OP_Real: { /* same as TK
pTos++;
pTos->flags = MEM_Str|MEM_Static|MEM_Term;
pTos->z = pOp->p3;
- pTos->n = strlen(pTos->z);
+ pTos->n = strlen(pTos->z); /* should probably put strlen in pOp */
pTos->enc = SQLITE_UTF8;
- pTos->r = sqlite3VdbeRealValue(pTos);
+ memcpy( (char*)&pTos->r, (char*)&pOp->p1, 4);
+ memcpy(4+(char*)&pTos->r, (char*)&pOp->p2, 4);
pTos->flags |= MEM_Real;
sqlite3VdbeChangeEncoding(pTos, encoding);
break;
Index: src/vdbeaux.c
===================================================================
RCS file: /sqlite/sqlite/src/vdbeaux.c,v
retrieving revision 1.320
diff -u -3 -p -r1.320 vdbeaux.c
--- src/vdbeaux.c 3 Oct 2007 18:45:04 -0000 1.320
+++ src/vdbeaux.c 17 Oct 2007 23:01:03 -0000
@@ -321,7 +321,7 @@ static void resolveP2Values(Vdbe *p, int
nMaxStack--;
}
- if( pOp->p2>=0 ) continue;
+ if( opcode==OP_Real || opcode==OP_Int64 || pOp->p2>=0 ) continue;
assert( -1-pOp->p2<p->nLabel );
pOp->p2 = aLabel[-1-pOp->p2];
}
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
