Hello people,
I have received this mail from a user in the Spanish mailing list (to
the list and my private mail)
[http://es.openoffice.org/servlets/ReadMsg?list=users&msgNo=5651]
A short explanation:
on a base with ± 20,000 records, on a Celeron PC with 256 MB RAM, it
took up to 5 minutes to build a simple report with one grouping.
On a Pentium 4 with 1GB RAM, the time was reduced to 2 minutes (but the
user still finds this as a performance issue)
Before answering anything, I'd like to ask you if you had experienced
something like that.
Bye the way, this makes me face once again a question for which I've
found no answer: who is the common Base user? == is a table with 20,000
records a "normal" testing case?
If you what to do your own testings, and you don't have such a table at
home (I didn't so I wrote this just a few minutes ago) the following
code sample will create a new ODB in your temp. dir. with an embedded
HSQL database, and a table with 20,000 records
I'm quite sure it won't crash your OOo installation!!! (although the
code is very awful - sorry programmers, I was to busy and harry to be
clean :-( )
Bye and thanks for your opinions!
'***********************************************************************
REM ***** BASIC *****
Option Explicit
Sub Run_Test
On Error GoTo Run_Test_ErrorHandler
Dim oDataSource as Object, oConnection as Object
'create an HSQLDB embeddded data source on the temporal directory
oDataSource = createDataSource(_
"sdbc:embedded:hsqldb",FALSE, "", "", _
getTempDirURL()& "/OOoBase_TEST.odb")
If NOT IsNull( oDataSource ) Then
'create a connection
oConnection = oDataSource.getConnection("","")
If IsNull( oConnection ) Then Exit Sub
'create the test table named CLIENTES
createTable( oConnection, "CLIENTES" )
'populate it with a number of records
populateTable( oConnection, 20000 )
'store the doc.
oDataSource.DatabaseDocument.store()
Dim oDoc as Object
'load it
oDoc = StarDesktop.loadComponentFromURL(_
oDataSource.DatabaseDocument.URL,
"_default", 0, Array())
End If
Run_Test_ErrorHandler:
If NOT ISNull(oConnection) Then
oConnection.close()
oConnection.dispose()
End If
End Sub
Function getTempDirURL() as String
Dim oPathSubstitution as Object
oPathSubstitution =
createUnoService("com.sun.star.util.PathSubstitution")
getTempDirURL() = oPathSubstitution.getSubstituteVariableValue(
"$(temp)" )
End Function
Function createDataSource( sDataSourceURL$,_
bPassword as Boolean,_
sUser$, _
sPassword$,_
sDatabaseDocumentURL$)_
as com.sun.star.sdb.DataSource
On Error Resume Next
Dim oDBContext as Object, oDataSource as Object, oDatabaseDocument as
Object
Dim sStoreURL$
oDBContext = createUNOService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDBContext.createInstance()
With oDataSource
.URL = sDataSourceURL
.IsPasswordRequired = bPassword
.User = sUser
.Password = sPassword
End With
sStoreURL = convertToURL(sDatabaseDocumentURL)
oDataSource.DatabaseDocument.storeAsURL( sDatabaseDocumentURL, Array() )
createDataSource() = oDataSource
End Function
Sub createTable( oConnection as com.sun.star.sdb.Connection, sTableName$)
On Error Resume Next
If IsNull(oConnection) Then Exit Sub
Dim oTables, oNewTable as Object
oTables = oConnection.getTables()
If oTables.hasByName(sTableName) Then Exit Sub
oNewTable = oTables.createDataDescriptor()
oNewTable.setPropertyValue("Name", sTableName)
'agregar las COLUMNAS (antes de insertar la tabla en el contenedor)
DIm oColumns as Object, oColumnDescriptor as Object
oColumns = oNewTable.getColumns()
'sólo se necesita un DataDescriptor,
'cambiando cada vez aquellas propiedades que difieran de la columna
anterior
oColumnDescriptor = oColumns.createDataDescriptor()
'crear la PRIMER columna: llave primaria, auto-incremento
oColumnDescriptor.setPropertyValue("Name", "ID")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.INTEGER)
oColumnDescriptor.setPropertyValue("IsAutoIncrement", TRUE)
oColumnDescriptor.setPropertyValue("IsNullable",
com.sun.star.sdbc.ColumnValue.NO_NULLS)
oColumns.appendByDescriptor(oColumnDescriptor)
'SEGUNDA columna
oColumnDescriptor.setPropertyValue("Name", "NOMBRE")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.VARCHAR)
oColumnDescriptor.setPropertyValue("IsAutoIncrement", FALSE)
oColumnDescriptor.setPropertyValue("IsNullable",
com.sun.star.sdbc.ColumnValue.NULLABLE)
oColumnDescriptor.setPropertyValue("Precision", 50)
oColumns.appendByDescriptor(oColumnDescriptor)
'TERCERA columna
oColumnDescriptor.setPropertyValue("Name", "APELLIDO")
oColumnDescriptor.setPropertyValue("Precision", 100)
oColumns.appendByDescriptor(oColumnDescriptor)
'CUARTA columna
oColumnDescriptor.setPropertyValue("Name", "DIRECCION")
oColumnDescriptor.setPropertyValue("Precision",50)
oColumns.appendByDescriptor(oColumnDescriptor)
'QUINTA columna
oColumnDescriptor.setPropertyValue("Name", "CIUDAD")
oColumns.appendByDescriptor(oColumnDescriptor)
'SEXTA columna
oColumnDescriptor.setPropertyValue("Name", "EMAIL")
oColumnDescriptor.setPropertyValue("Precision", 100)
oColumns.appendByDescriptor(oColumnDescriptor)
'SÉPTIMA columna
oColumnDescriptor.setPropertyValue("Name", "FECHA_NACIMIENTO")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.DATE)
oColumnDescriptor.setPropertyValue("Precision", 10)'1970/01/02
oColumns.appendByDescriptor(oColumnDescriptor)
'OCTAVA columna
oColumnDescriptor.setPropertyValue("Name", "FECHA_INGRESO")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.TIMESTAMP)
'oColumnDescriptor.setPropertyValue("Precision", 10)'1970/01/02
oColumns.appendByDescriptor(oColumnDescriptor)
'NOVENA columna
oColumnDescriptor.setPropertyValue("Name", "SALARIO")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.DECIMAL)
'oColumnDescriptor.setPropertyValue("Precision", 10)
oColumnDescriptor.setPropertyValue("Scale", 2)
oColumns.appendByDescriptor(oColumnDescriptor)
'DÉCIMA columna
oColumnDescriptor.setPropertyValue("Name", "NEWSLETTER")
oColumnDescriptor.setPropertyValue("Type",
com.sun.star.sdbc.DataType.BOOLEAN)
oColumnDescriptor.setPropertyValue("DefaultValue", "TRUE")
'oColumnDescriptor.setPropertyValue("ControlDefault", "1")
oColumns.appendByDescriptor(oColumnDescriptor)
'*******************************************************************************
'crear la CLAVA/LLAVE PRIMARIA
Dim oKeys as Object, oKeyDescriptor as Object
oKeys = oNewTable.getKeys()
oKeyDescriptor = oKeys.createDataDescriptor()
oKeyDescriptor.setPropertyValue("Type",
com.sun.star.sdbcx.KeyType.PRIMARY)
'agregar la/s columna/s a la clave primaria
Dim oKeyColumns as Object, oKeyColumnsDescriptor as Object
oKeyColumns = oKeyDescriptor.getColumns()
oKeyColumnsDescriptor = oKeyColumns.createDataDescriptor()
oKeyColumnsDescriptor.setPropertyValue("Name", "ID")
oKeyColumns.appendByDescriptor(oKeyColumnsDescriptor)
'agregar la CLAVA PRIMARIA
oKeys.appendByDescriptor(oKeyDescriptor)
'**********************************************************************************
'por último, agragar la nueva tabla a la colección de tablas
oTables.appendByDescriptor(oNewTable)
End Sub
Sub populateTable( oConnection as com.sun.star.sdb.Connection,
nNumberOfRecords&)
On Error GoTo PopTable_ErrorHandler
If IsNull(oConnection) Then Exit Sub
Dim oStatement as Object
oStatement = oConnection.createStatement()
Dim NOMBRE(), sNombre$
Dim APELLIDO(), sApellido$
Dim CIUDAD(), sCiudad$
NOMBRE = ARRAY( "Juan", "Pedro", "Luis", "Miguel", "José", "Ángel",
"Andrés", "Alberto", "Alejandro", "Martín",_
"Laura", "Paula", "Alicia", "Andrea", "Liliana", "Verónica",
"Carolina", "Ana", "Eva", "Roxana", "Julia")
APELLIDO =
ARRAY("Pérez","López","Gómez","Gonzales","Rodriguez","Prado","García","Belgrano","Sarmiento","Aguirrez","Acasuso",_
"Giovanni","Porcel","Olmedo","Casanova","Liporace","Casella","Ventura","Rinaldi","Piazzola","Cortázar")
CIUDAD = ARRAY("La Plata","Berisso","Ensenada","Gonnet","City
Bell","Villa Elisa")
Dim n&
For n = 0 To nNumberOfRecords-1
'NOMBRE
sNombre = NOMBRE( CInt( 20 * RND ) )
'APELLIDO
sApellido = APELLIDO( CInt( 20 * RND ) )
'DIRECCION
Dim DIR$ : DIR = "Calle " & CInt( 70 * RND ) & " Nº "
& CInt( 700 * RND )
'CIUDAD
sCiudad = CIUDAD( CInt( 5* RND ) )
'EMAIL - ToDo: quitar todos los acentos!!!
Dim EMAIL$
EMAIL = ReplaceNoASCIIChars( LCase(sNombre & "_" & sApellido) &
"@gmail.com" )
Dim NACIM$, AGNO$, MES$, DIA$
AGNO = "19" & CStr( 70 + ( CInt( 20 * RND ) ) )
MES = Format(CInt( 11 * RND ) + 1, "00")
DIA = Format(CInt( 27 * RND ) + 1, "00")
NACIM = Format(AGNO & "-" & MES & "-" & DIA)
'FECHA INGRESO
'Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff].
Dim HORA, MINUTO, SEGUNDO, INGRESO
AGNO = "19" & CStr( 70 + ( CInt( 20 * RND ) ) )
MES = Format( CInt( 11 * RND ) + 1, "00")
DIA = Format( CInt( 27 * RND ) + 1, "00")
HORA = Format( CInt( 24 * RND ), "00")
MINUTO = Format( CInt( 59 * RND ), "00")
SEGUNDO = Format( CInt( 59 * RND ), "00")
INGRESO = AGNO & "-" & MES & "-" & DIA & " " & HORA & ":" & MINUTO &
":" & SEGUNDO
'SALARIO
Dim SALARIO
SALARIO = CStr( 1000 + CInt(999 * RND) ) & "." & CStr( CInt(
99*RND ) )
'NEWS
Dim NEWS as Boolean
NEWS = CBool( CInt( RND ) )
Dim sSQL$
sSQL = "INSERT INTO ""CLIENTES"" VALUES(" &_
"NULL, " &_
"'" & sNombre & "', " &_
"'" & sApellido & "', " &_
"'" & DIR & "', " &_
"'" & sCiudad & "', " &_
"'" & EMAIL & "', " &_
"'" & NACIM & "', " &_
"'" & INGRESO & "', " &_
SALARIO & ", " &_
UCase( CStr( NEWS ) ) & " )"
oStatement.execute( sSQL )
Next
PopTable_ErrorHandler:
Dim ERRs
ERRs = ERROR
If NOT IsNull(oStatement) Then oStatement.dispose()
End Sub
Sub ReplaceNoASCIIChars(sString as String) as String
Dim sTempString$
sTempString = sString
Dim aStringPair(4) as New com.sun.star.beans.StringPair
aStringPair(0).First = "á" : aStringPair(0).Second = "a"
aStringPair(1).First = "é" : aStringPair(1).Second = "e"
aStringPair(2).First = "í" : aStringPair(2).Second = "i"
aStringPair(3).First = "ó" : aStringPair(3).Second = "o"
aStringPair(4).First = "ú" : aStringPair(4).Second = "u"
Dim n%
For n = 0 To UBound(aStringPair)
If InStr(sTempString, aStringPair(n).First) Then
sTempString = JOIN(_
SPLIT(sTempString,
aStringPair(n).First), _
aStringPair(n).Second )
End If
Next
ReplaceNoASCIIChars() = sTempString
End Sub
'*********************************************************************
--
Ariel Constenla-Haile
La Plata, Argentina
[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.arielconstenlahaile.com.ar/ooo/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
