st: Re: odbc batching

["Joseph Coveney" <jcoveney@bigplanet.com>]

Argyn Kuketayev wrote:

does odbc command use batching?
it seems to be awfully slow.

--------------------------------------------------------------------------------

It does as of Release 11, as I understand things.  

Are you reading (-odbc load-) or writing (-odbc insert-)?  The latter is slower
than the former, especially if you don't temporarily turn off indexes in the 
target database.

Regardless, reading moderately large datasets into Stata isn't supposed to be 
excruciating:  in the admittedly unscientific test below, for a quarter-gigabyte
dataset, -odbc- is about half as fast as reading the file directly from the disc
in Stata format.  The test used Microsoft Access on my dinosauric laptop.  
There might be some variation between ODBC drivers, but it shouldn't be 
day-versus-night.

Joseph Coveney
 
. version 11.1

. 
. clear *

. set more off

. set memory 500M
[redacted]
. 
. timer clear

. timer on 1

. use C:\Temp\Test

. timer off 1

. 
. local dsn MS Access Database;DBQ=Test.accdb;DefaultDir=C:\Temp\;

. timer on 2

. odbc load, table(Test) dsn("`dsn'") clear

. timer off 2

. 
. timer list
   1:     29.32 /        1 =      29.3220
   2:     62.39 /        1 =      62.3900

. describe

Contains data
  obs:       500,000                          
 vars:            11                          
 size:   259,000,000 (50.6% of memory free)

--------------------------------------------------------------------------------
              storage  display     value
variable name   type   format      label      variable label

--------------------------------------------------------------------------------
row_nr          long   %12.0g                 
alphabet1       str51  %51s                   
alphabet2       str51  %51s                   
alphabet3       str51  %51s                   
alphabet4       str51  %51s                   
alphabet5       str51  %51s                   
alphabet6       str51  %51s                   
alphabet7       str51  %51s                   
alphabet8       str51  %51s                   
alphabet9       str51  %51s                   
alphabet10      str51  %51s                   
--------------------------------------------------------------------------------
Sorted by:  
     Note:  dataset has changed since last saved

. 
. exit

end of do-file


.


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/