HI, Does this go back to a few other threads - where other users have picked up on slow response times? Drew might remember better than me? >From what I have experienced doing anything with the HSQLDB engine (not sure >correct term but what ever is the backend nuts and bolts) where there are more >than a few hundred records is slow. By doing 'anything' - I have only needed to run simple SQL (join and summarise multiple tables) which do go slow when there are more than a few hundred records - but I have not tested properly and have not tested the report builder. Oh -yes I am confident that the SQL is fine (e.g. the same query, against same tables runs in under a second in MySQL and takes 3 - 5 minutes in Base(v 2.3, using windows vista business)). So I have started to use other free databases (MySQL, Postgre etc) to hold and manipulate data and occasionally use Base as a front-end to allow integration with spreadsheets, documents etc which is really great by the way! I am not suggesting this approach is suitable for the 'average user' but it is just a description of what I had to do. Darren
> Date: Thu, 11 Oct 2007 11:45:32 -0300> From: [EMAIL PROTECTED]> To: > [email protected]> Subject: [dba-users] Report Builder Performance> > > 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]> _________________________________________________________________ Feel like a local wherever you go. http://www.backofmyhand.com
