Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: Re: odbc batching


From   "Joseph Coveney" <[email protected]>
To   <[email protected]>
Subject   st: Re: odbc batching
Date   Fri, 11 Mar 2011 01:50:47 +0900

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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index