"o 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." i don't know if i'm an average user or not, but i do know that the 14 of us who use MSAc at work have constant problems with it crashing in our "multiuser" environment and being pretty slow. Our director has been looking for something more robust and secure? and our Tech. Dept. has recommended we move away from MSAc for the above same reasons (not made for multiple users, security, etc.).
So, since we had made up our our program under MSAc, although none of us are programmers, we decided to do some research and came up with something free, OpenSource and which worked on multiple OSs -- it's called Postgresql. However we can't use it because there is no OpenSource front-end robust enough (until we came across Base) which could even begin to help us reprogram our MSAc program on a gui level. We have watched with pleasure on the one hand and sadness on the other the development of Base, especially its reporting ability. Pleasure because one day non-programmers may have enough tools to put together their own programs with Base and hopefully Pg, and sadness that we these tools weren't available in time for us to have the pleasure of "doing it ourselves" once again. It's now gonna cost our taxpayer funded program $20,000 for one that was made by a company, plus $2,000 a year for "maintenance". Hopefully this will add something positive to the discussion. Have a great day! :) ___________________________________________________________________ Darren Norris <[EMAIL PROTECTED]> wrote: 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 "En el jardin de tu corazón, no plantes sino la rosa del amor." Bahá´u´lláh Fundación Anisa, A.C. www.fundacionanisa.org ; [EMAIL PROTECTED]
