patch: improve oracle performance on sbcl and cmucl
James Bielman <[email protected]> Sat, 12 Nov 2005 23:58:04 -0800
| Newsgroups | gmane.lisp.clsql.devel |
|---|---|
| Organization | Hayton Systems, Inc. |
| Message-ID | <[email protected]> |
Hi,
Due to a lack of inlining and type declarations, the Oracle
backend currently conses like it's going out of style on SBCL
and CMUCL.
The following patch improves the situation quite a bit, by
inlining all the OCI functions (both the raw ones and the error
checking wrappers). Also, the SHORT-ARRAY type was missing a
NIL dimension---after fixing the type definition I was able to
uncomment the type declaration in FETCH-ROW; this change alone
reduced the time for my test query by about a factor of six!
The number of compiler notes in oracle-sql.lisp under SBCL has been
reduced from around 100 to about 25. I will probably try to get this
lower, as I think there is still room for further improvement.
Here are the timings for my simple test query:
Before (SBCL/x86 0.9.6):
* (time (length (clsql:query "select * from user_objects")))
Evaluation took:
6.091 seconds of real time
5.512162 seconds of user run time
0.280957 seconds of system run time
0 page faults and
81,987,208 bytes consed.
68
After (SBCL/x86 0.9.6):
CLSQL-USER> (time (length (query "select * from user_objects")))
Evaluation took:
1.016 seconds of real time
0.736888 seconds of user run time
0.032995 seconds of system run time
0 page faults and
10,598,192 bytes consed.
68
It hasn't quite caught up to OpenMCL yet, but it's getting closer:
? (time (length (clsql:query "select * from user_objects")))
(LENGTH (CLSQL-SYS:QUERY "select * from user_objects")) took 292
milliseconds (0.292 seconds) to run.
Of that, 5 milliseconds (0.005 seconds) were spent in user mode
1 milliseconds (0.001 seconds) were spent in system mode
286 milliseconds (0.286 seconds) were spent executing other OS
processes.
34,200 bytes of memory allocated.
68
I've tested the patch on SBCL x86 and x86-64 Linux, SBCL darwinppc,
LispWorks 4.3.7 x86, and OpenMCL/darwinppc, and the testsuite doesn't
appear to fail any additional tests. I am going to try to fix the
Oracle tests that fail as well in another patch.
All of my test machines have either the full or instant Oracle 10i
client (some symlink hackery is currently necessary for the instant
client).
James
_______________________________________________
CLSQL-Devel mailing list
[email protected]
http://lists.b9.com/mailman/listinfo/clsql-devel
clsql-oracle-performance.diff
(text/x-patch, 5.7 KB)
diff -rN -u old-clsql-devel/db-oracle/oracle-api.lisp new-clsql-devel/db-oracle/oracle-api.lisp
--- old-clsql-devel/db-oracle/oracle-api.lisp 2005-11-12 23:17:53.000000000 -0800
+++ new-clsql-devel/db-oracle/oracle-api.lisp 2005-11-12 23:18:02.000000000 -0800
@@ -53,36 +53,18 @@
;;; unless NULLS-OK is set.
(defmacro def-oci-routine ((c-oci-symbol lisp-oci-fn) c-return &rest c-parms)
- (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms)))
- `(let ((%lisp-oci-fn (uffi:def-function
- (,c-oci-symbol ,(intern (concatenate 'string "%" (symbol-name lisp-oci-fn))))
- ,c-parms
- :returning ,c-return)))
- (defun ,lisp-oci-fn (,@ll &key database nulls-ok)
- (let ((result (funcall %lisp-oci-fn ,@ll)))
- (case result
- (#.+oci-success+
- +oci-success+)
- (#.+oci-error+
- (handle-oci-error :database database :nulls-ok nulls-ok))
- (#.+oci-no-data+
- (error 'sql-database-error :message "OCI No Data Found"))
- (#.+oci-success-with-info+
- (error 'sql-database-error :message "internal error: unexpected +oci-success-with-info"))
- (#.+oci-invalid-handle+
- (error 'sql-database-error :message "OCI Invalid Handle"))
- (#.+oci-need-data+
- (error 'sql-database-error :message "OCI Need Data"))
- (#.+oci-still-executing+
- (error 'sql-temporary-error :message "OCI Still Executing"))
- (#.+oci-continue+
- (error 'sql-database-error :message "OCI Continue"))
- (1804
- (error 'sql-database-error :message "Check ORACLE_HOME and NLS settings."))
- (t
- (error 'sql-database-error
- :message
- (format nil "OCI unknown error, code=~A" result)))))))))
+ (let ((ll (mapcar (lambda (x) (declare (ignore x)) (gensym)) c-parms))
+ (c-oci-fn (intern (concatenate 'string "%" (symbol-name lisp-oci-fn)))))
+ `(progn
+ (declaim (inline ,c-oci-fn ,lisp-oci-fn))
+ (uffi:def-function (,c-oci-symbol ,c-oci-fn)
+ ,c-parms
+ :returning ,c-return)
+ (defun ,lisp-oci-fn (,@ll &key database nulls-ok)
+ (let ((result (,c-oci-fn ,@ll)))
+ (if (= result #.+oci-success+)
+ +oci-success+
+ (handle-oci-result result database nulls-ok)))))))
(defmacro def-raw-oci-routine
@@ -162,6 +144,7 @@
(p0 :pointer-void) ; svc
(p1 :pointer-void)) ; err
+(declaim (inline oci-error-get))
(uffi:def-function ("OCIErrorGet" oci-error-get)
((handlp :pointer-void)
(recordno ub4)
diff -rN -u old-clsql-devel/db-oracle/oracle-sql.lisp new-clsql-devel/db-oracle/oracle-sql.lisp
--- old-clsql-devel/db-oracle/oracle-sql.lisp 2005-11-12 23:17:53.000000000 -0800
+++ new-clsql-devel/db-oracle/oracle-sql.lisp 2005-11-12 23:20:45.000000000 -0800
@@ -120,6 +120,31 @@
:documentation
"The major version number of the Oracle server, should be 8, 9, or 10")))
+;;; Handle a non-successful result from an OCI function.
+(defun handle-oci-result (result database nulls-ok)
+ (case result
+ (#.+oci-success+
+ +oci-success+)
+ (#.+oci-error+
+ (handle-oci-error :database database :nulls-ok nulls-ok))
+ (#.+oci-no-data+
+ (error 'sql-database-error :message "OCI No Data Found"))
+ (#.+oci-success-with-info+
+ (error 'sql-database-error :message "internal error: unexpected +oci-success-with-info"))
+ (#.+oci-invalid-handle+
+ (error 'sql-database-error :message "OCI Invalid Handle"))
+ (#.+oci-need-data+
+ (error 'sql-database-error :message "OCI Need Data"))
+ (#.+oci-still-executing+
+ (error 'sql-temporary-error :message "OCI Still Executing"))
+ (#.+oci-continue+
+ (error 'sql-database-error :message "OCI Continue"))
+ (1804
+ (error 'sql-database-error :message "Check ORACLE_HOME and NLS settings."))
+ (t
+ (error 'sql-database-error
+ :message
+ (format nil "OCI unknown error, code=~A" result)))))
;;; Handle the messy case of return code=+oci-error+, querying the
;;; system for subcodes and reporting them as appropriate. ERRHP and
@@ -129,8 +154,8 @@
(cond
(database
(with-slots (errhp) database
- (let ((errcode (uffi:allocate-foreign-object 'sb4))
- (errbuf (uffi:allocate-foreign-string #.+errbuf-len+)))
+ (uffi:with-foreign-objects ((errcode 'sb4)
+ (errbuf '(array :unsigned-char #.+errbuf-len+)))
;; ensure errbuf empty string
(setf (uffi:deref-array errbuf '(:array :unsigned-char) 0)
(uffi:ensure-char-storable (code-char 0)))
@@ -144,8 +169,6 @@
+errbuf-len+ +oci-htype-error+))
(let ((subcode (uffi:deref-pointer errcode 'sb4))
(errstr (uffi:convert-from-foreign-string errbuf)))
- (uffi:free-foreign-object errcode)
- (uffi:free-foreign-object errbuf)
(unless (and nulls-ok (= subcode +null-value-returned+))
(error 'sql-database-error
:database database
@@ -341,7 +364,7 @@
;; STREAM which has no more data, and QC is not a STREAM, we signal
;; DBI-ERROR instead.
-(uffi:def-type short-array (:array :short))
+(uffi:def-type short-array (:array :short nil))
(uffi:def-type int-pointer (* :int))
(uffi:def-type double-pointer (* :double))
@@ -378,7 +401,7 @@
(defun fetch-row (qc &optional (eof-errorp t) eof-value)
- ;;(declare (optimize (speed 3)))
+ (declare (optimize (speed 3)))
(cond ((zerop (qc-n-from-oci qc))
(if eof-errorp
(error 'sql-database-error :message
@@ -398,7 +421,7 @@
(value
(let* ((arb (foreign-resource-buffer (cd-indicators cd)))
(indicator (uffi:deref-array arb '(:array :short) irow)))
- ;;(declare (type short-array arb))
+ (declare (type short-array arb))
(unless (= indicator -1)
(ecase (cd-oci-data-type cd)
(#.SQLT-STR