Re: CLSQL - feature request?
From: Dmitri Ivanov (divanov_NOSP_at_M_.aha.ru)
Date: 05/19/04
- Next message: David Steuber: "Re: Mapping tables"
- Previous message: Kenny Tilton: "Re: macro-biology"
- In reply to: Will Hartung: "Re: CLSQL - feature request?"
- Next in thread: D. Richard Hipp: "Re: CLSQL - feature request?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 19 May 2004 10:20:58 +0400
Hello Will,
"Will Hartung" <willh@msoft.com> wrote:
WH> | ...snip...|
WH> For ODBC, it's not really important as the actual back end ODBC
WH> driver does all of the work for the DB anyway, so who know what
WH> it's actually doing.
WH>
WH> Also, most of the Lisp SQL interfaces do the same thing,
WH> essentially recreating the complete SQL ASCII statement for each
WH> request.
Recreating an SQL statement is not always possible due to the maximum length
limitation imposed by the DBMS or driver. The pathological case is when the
SQL statement for passing binary data cannot be constructed at all due to
the lack of the corresponding data type text representation.
WH> On the other hand, for an actual binary SQL protocol (ala Oracle
WH> OCI), then you need to marshall the binary data into and out of the
WH> Lisp process anyway, so I dunno if even then we'd get much of a
WH> performance benefit.
WH>
WH> Not to say that there isn't a semantic benefit to being able to
WH> simply build a SQL statement and set parameters over and over,
WH> making it easier on the developer. But one of the primary
WH> motivations behing prepared statements was simply performance, not
WH> necessarily semanatics, so I'm curious if a CL based SQL layer will
WH> actually be able to benefit from prepared statements at the
WH> performance level.
The answer is 'Yes'. The following rude benchmarks come from testing YSQL on
LW over the Access ODBC driver:
;; Without preparing
(time
(dotimes (i 1000)
(sql:with-transaction ()
(sql:insert-records :into [tab2]
:attributes '([id] [text2_])
:values (list i (format nil "~A" (random 1000)))))))
; user time = 5.015
; system time = 0.703
; Elapsed time = 0:00:06
; Allocation = 3887584 bytes standard / 5264677 bytes fixlen
; 0 Page faults
;; With preparing
(time
(sql:with-prepared-statement (stmt [insert :into [tab2]
:attributes '([id] [text2_])
:values '([? :type integer] [? :type string])])
(sql:with-transaction ()
(dotimes (i 1000)
(sql:execute-command stmt
:values (list i (format nil "~A" (random 1000))))))))
; user time = 0.890
; system time = 0.093
; Elapsed time = 0:00:01
; Allocation = 647984 bytes standard / 1573990 bytes fixlen
; 0 Page faults
FWIW, the example below demonstrates the syntactical sugar for dealing with
parameters in YSQL.
(setq pa (sql:prepare-statement
[insert :into [tab]
:attributes '([Text_] [Boolean_] [Currency_] [DateTime_]
[Byte_] [Short_] [Long_] [Single_] [Double_])
:values '([? :name text_ :type string]
[? :name boolean_ :type :boolean]
[? :name Currency_ :type :numeric]
[? :name DateTime_ :type :timestamp]
[? :name Byte_ :type unsigned-byte]
[? :name Short_ :type (integer -32767 32767)]
[? :name Long_ :type integer]
[? :name Single_ :type single-float]
[? :name Double_ :type double-float]) ]
:database a))
(sql:execute-command pa
:values `("From prepared" 0 111.11 ,(get-universal-time)
255 222 3333 4.44 55.5555))
-- Sincerely, Dmitri Ivanov lisp.ystok.ru
- Next message: David Steuber: "Re: Mapping tables"
- Previous message: Kenny Tilton: "Re: macro-biology"
- In reply to: Will Hartung: "Re: CLSQL - feature request?"
- Next in thread: D. Richard Hipp: "Re: CLSQL - feature request?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
