[Openmcl-devel] Faster Formatting for fun (the OpenMCL version)

[Gary King]

I've posted a similar question to MCL's list but this is the slightly 
different OpenMCL version.

I'm generating a whole bunch of SQL statements mostly of the form 
"INSERT INTO FOO (...) VALUES (...);". Format is taking up about a 
third of my processing time so I was hoping to speed things up by using 
lower level calls. My attempts to do this, however, have failed 
miserably. Below I show a format statement that does what I want and 
then another bit of code that does the "same" thing using princ 
instead. The trouble is that the princ method is actually slower 
(though it appears not to cons at all). Note that the MCL data showed a 
much larger slow down and lots and lots of consing. Any ideas / help / 
commiseration would be appreciated.

? (declaim (optimize (speed 3) (safety 0) (debug 0)))
NIL
? (progn
   (when (probe-file "ccl:foo.temp")
     (delete-file "ccl:foo.temp"))
   (with-open-file (s "ccl:foo.temp" :direction :output 
:if-does-not-exist :create)
     (time (loop repeat 3000 do
                 (format s "~%INSERT INTO ~A (~{~A~^, ~}) ~
                            VALUES (~{'~A'~^, ~});"
                         'HATS
                         '(a b c d e)
                         (list 1 2 3 4 5))))))
(LOOP REPEAT 3000 DO (FORMAT S "~%INSERT INTO ~A (~{~A~^, ~}) ~
                            VALUES (~{'~A'~^, ~});" 'HATS '(A B C D E) 
(LIST 1 2 3 4 5))) took 1,088 milliseconds (1.088 seconds) to run.
Of that, 620 milliseconds (0.620 seconds) were spent in user mode
          30 milliseconds (0.030 seconds) were spent in system mode
          438 milliseconds (0.438 seconds) were spent executing other OS 
processes.
  120,288 bytes of memory allocated.
NIL
? (progn
   (when (probe-file "ccl:foo.temp")
     (delete-file "ccl:foo.temp"))

   (with-open-file (s "ccl:foo.temp" :direction :output 
:if-does-not-exist :create)
     (let* ((vars '(a b c d e))
            (vals (list 1 2 3 4 5)))
       (time (loop repeat 3000 do
                     (terpri s)
                     (princ "INSERT INTO " s)
                     (princ 'hats s)
                     (princ " (" s)
                     (loop for var = vars then (rest var)
                           while var
                           do (princ (first var) s)
                           when (rest vars) do (princ ", " s))
                     (princ ") VALUES (" s)
                     (loop for var = vals then (rest var)
                           while var
                           do (princ "'" s) (princ (first var) s) (princ 
"'" s)
                           when (rest vars) do (princ ", " s))
                     (princ ");" s))))))
(LOOP REPEAT 3000 DO (TERPRI S) (PRINC "INSERT INTO " S) (PRINC 'HATS 
S) (PRINC " (" S) (LOOP FOR VAR = VARS THEN (REST VAR) WHILE VAR DO 
(PRINC (FIRST VAR) S) WHEN (REST VARS) DO (PRINC ", " S)) (PRINC ") 
VALUES (" S) (LOOP FOR VAR = VALS THEN (REST VAR) WHILE VAR DO (PRINC 
"'" S) (PRINC (FIRST VAR) S) (PRINC "'" S) WHEN (REST VARS) DO (PRINC 
", " S)) (PRINC ");" S)) took 1,674 milliseconds (1.674 seconds) to 
run.
Of that, 1,170 milliseconds (1.170 seconds) were spent in user mode
          30 milliseconds (0.030 seconds) were spent in system mode
          474 milliseconds (0.474 seconds) were spent executing other OS 
processes.
NIL
?


-- 
Gary Warren King, Lab Manager
EKSL East, University of Massachusetts * 413 577 0176

Power is actualized only where word and deed have not parted company, 
where words are not empty and deeds not brutal, where words are not 
used to veil intentions but to disclose realities, and deeds are not 
used to violate and destroy but to establish relations and create new 
realities.
-- Hannah Arendt