dbaccess/qa/unit/embeddeddb_performancetest.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  */
   9
  10 #include "dbtest_base.cxx"
  11
  12 #include <boost/scoped_ptr.hpp>
  13 #include <osl/file.hxx>
  14 #include <osl/process.h>
  15 #include <osl/time.h>
  16 #include <rtl/ustrbuf.hxx>
  17 #include <tools/stream.hxx>
  18 #include <unotools/tempfile.hxx>
  19
  20 #include <com/sun/star/beans/XPropertySet.hpp>
  21 #include <com/sun/star/frame/XStorable.hpp>
  22 #include <com/sun/star/lang/XComponent.hpp>
  23 #include <com/sun/star/sdb/XOfficeDatabaseDocument.hpp>
  24 #include <com/sun/star/sdbc/XColumnLocate.hpp>
  25 #include <com/sun/star/sdbc/XConnection.hpp>
  26 #include <com/sun/star/sdbc/XParameters.hpp>
  27 #include <com/sun/star/sdbc/XPreparedStatement.hpp>
  28 #include <com/sun/star/sdbc/XResultSet.hpp>
  29 #include <com/sun/star/sdbc/XRow.hpp>
  30 #include <com/sun/star/sdbc/XStatement.hpp>
  31 #include <com/sun/star/util/XCloseable.hpp>
  32
  33 using namespace ::com::sun::star;
  34 using namespace ::com::sun::star::beans;
  35 using namespace ::com::sun::star::frame;
  36 using namespace ::com::sun::star::lang;
  37 using namespace ::com::sun::star::sdb;
  38 using namespace ::com::sun::star::sdbc;
  39 using namespace ::com::sun::star::uno;
  40
  41 void normaliseTimeValue(TimeValue* pVal)
  42 {
  43     pVal->Seconds += pVal->Nanosec / 1000000000;
  44     pVal->Nanosec %= 1000000000;
  45 }
  46
  47 void getTimeDifference(const TimeValue* pTimeStart,
  48                        const TimeValue* pTimeEnd,
  49                        TimeValue* pTimeDifference)
  50 {
  51     // We add 1 second to the nanoseconds to ensure that we get a positive number
  52     // We have to normalise anyway so this doesn't cause any harm.
  53     // (Seconds/Nanosec are both unsigned)
  54     pTimeDifference->Seconds = pTimeEnd->Seconds - pTimeStart->Seconds - 1;
  55     pTimeDifference->Nanosec = 1000000000 + pTimeEnd->Nanosec - pTimeStart->Nanosec;
  56     normaliseTimeValue(pTimeDifference);
  57 }
  58
  59 OUString getPrintableTimeValue(const TimeValue* pTimeValue)
  60 {
  61     return OUString::number(
  62         (sal_uInt64(pTimeValue->Seconds) * SAL_CONST_UINT64(1000000000)
  63         + sal_uInt64(pTimeValue->Nanosec))/ 1000000
  64     );
  65 }
  66
  67 /*
  68  * The reccomended way to run this test is:
  69  * 'SAL_LOG="" DBA_PERFTEST=YES make CppunitTest_dbaccess_embeddeddb_performancetest'
  70  * This blocks the unnecessary exception output and show only the performance data.
  71  *
  72  * You also need to create the file dbacess/qa/unit/data/wordlist, this list cannot
  73  * contain any unescaped apostrophes (since the words are used directly to assemble
  74  * sql statement), apostrophes are escaped using a double apostrophe, i.e. ''.
  75  * one easy way of generating a list is using:
  76  * 'for WORD in $(aspell dump master); do echo ${WORD//\'/\'\'}; done > dbaccess/qa/unit/data/wordlist'
  77  *
  78  * Note that wordlist cannot have more than 220580 lines, this is due to a hard
  79  * limit in our hsqldb version.
  80  *
  81  * Also note that this unit test "fails" when doing performance testing, this is
  82  * since by default unit test output is hidden, and thus there is no way of
  83  * reading the results.
  84  */
  85 class EmbeddedDBPerformanceTest
  86     : public DBTestBase
  87 {
  88 private:
  89     const static OUString our_sEnableTestEnvVar;
  90
  91     // We store the results and print them at the end due to the amount of warning
  92     // noise present which otherwise obscures the results.
  93     OUStringBuffer m_aOutputBuffer;
  94
  95     void printTimes(const TimeValue* pTime1, const TimeValue* pTime2, const TimeValue* pTime3);
  96
  97     void doPerformanceTestOnODB(const OUString& rDriverURL,
  98                                 const OUString& rDBName,
  99                                 const bool bUsePreparedStatement);
 100
 101     void setupTestTable(uno::Reference< XConnection >& xConnection);
 102
 103     SvFileStream *getWordListStream();
 104
 105     // Individual Tests
 106     void performPreparedStatementInsertTest(
 107         uno::Reference< XConnection >& xConnection,
 108         const OUString& rDBName);
 109     void performStatementInsertTest(
 110         uno::Reference< XConnection >& xConnection,
 111         const OUString& rDBName);
 112     void performReadTest(
 113         uno::Reference< XConnection >& xConnection,
 114         const OUString& rDBName);
 115
 116     // Perform all tests on a given DB.
 117     void testFirebird();
 118     void testHSQLDB();
 119
 120 public:
 121     void testPerformance();
 122
 123     CPPUNIT_TEST_SUITE(EmbeddedDBPerformanceTest);
 124     CPPUNIT_TEST(testPerformance);
 125     CPPUNIT_TEST_SUITE_END();
 126 };
 127
 128 SvFileStream* EmbeddedDBPerformanceTest::getWordListStream()
 129 {
 130     OUString wlPath;
 131     createFileURL("wordlist", wlPath);
 132
 133     SvFileStream *pFile(new SvFileStream(wlPath, STREAM_READ));
 134
 135     if (!pFile)
 136     {
 137         fprintf(stderr, "Please ensure the wordlist is present\n");
 138         CPPUNIT_ASSERT(false);
 139     }
 140
 141     return pFile;
 142 }
 143
 144 void EmbeddedDBPerformanceTest::printTimes(
 145     const TimeValue* pTime1,
 146     const TimeValue* pTime2,
 147     const TimeValue* pTime3)
 148 {
 149     m_aOutputBuffer.append(
 150         getPrintableTimeValue(pTime1) + "\t" +
 151         getPrintableTimeValue(pTime2) + "\t" +
 152         getPrintableTimeValue(pTime3) + "\t"
 153         "\n"
 154    );
 155 }
 156
 157 const OUString EmbeddedDBPerformanceTest::our_sEnableTestEnvVar("DBA_PERFTEST");
 158
 159 // TODO: we probably should create a document from scratch instead?
 160
 161 void EmbeddedDBPerformanceTest::testPerformance()
 162 {
 163     OUString sEnabled;
 164     osl_getEnvironment(our_sEnableTestEnvVar.pData, &sEnabled.pData);
 165
 166     if (sEnabled.isEmpty())
 167         return;
 168
 169     m_aOutputBuffer.append("---------------------\n");
 170     testFirebird();
 171     m_aOutputBuffer.append("---------------------\n");
 172     testHSQLDB();
 173     m_aOutputBuffer.append("---------------------\n");
 174
 175     fprintf(stdout, "Performance Test Results:\n");
 176     fprintf(stdout, "%s",
 177             OUStringToOString(m_aOutputBuffer.makeStringAndClear(),
 178                               RTL_TEXTENCODING_UTF8)
 179                 .getStr()
 180     );
 181
 182     // We want the results printed, but unit test output is only printed on failure
 183     // Hence we deliberately fail the test.
 184     CPPUNIT_ASSERT(false);
 185 }
 186
 187 void EmbeddedDBPerformanceTest::testFirebird()
 188 {
 189
 190     m_aOutputBuffer.append("Standard Insert\n");
 191     doPerformanceTestOnODB("sdbc:embedded:firebird", "Firebird", false);
 192     m_aOutputBuffer.append("PreparedStatement Insert\n");
 193     doPerformanceTestOnODB("sdbc:embedded:firebird", "Firebird", true);
 194 }
 195
 196 void EmbeddedDBPerformanceTest::testHSQLDB()
 197 {
 198     m_aOutputBuffer.append("Standard Insert\n");
 199     doPerformanceTestOnODB("sdbc:embedded:hsqldb", "HSQLDB", false);
 200     m_aOutputBuffer.append("PreparedStatement Insert\n");
 201     doPerformanceTestOnODB("sdbc:embedded:hsqldb", "HSQLDB", true);
 202 }
 203
 204 /**
 205  * Use an existing .odb to do performance tests on. The database cannot have
 206  * a table of the name PFTESTTABLE.
 207  */
 208 void EmbeddedDBPerformanceTest::doPerformanceTestOnODB(
 209     const OUString& rDriverURL,
 210     const OUString& rDBName,
 211     const bool bUsePreparedStatement)
 212 {
 213     ::utl::TempFile aFile;
 214     aFile.EnableKillingFile();
 215
 216     {
 217         uno::Reference< XOfficeDatabaseDocument > xDocument(
 218             m_xSFactory->createInstance("com.sun.star.sdb.OfficeDatabaseDocument"),
 219             UNO_QUERY_THROW);
 220         uno::Reference< XStorable > xStorable(xDocument, UNO_QUERY_THROW);
 221
 222         uno::Reference< XDataSource > xDataSource = xDocument->getDataSource();
 223         uno::Reference< XPropertySet > xPropertySet(xDataSource, UNO_QUERY_THROW);
 224         xPropertySet->setPropertyValue("URL", Any(rDriverURL));
 225
 226         xStorable->storeAsURL(aFile.GetURL(), uno::Sequence< beans::PropertyValue >());
 227     }
 228
 229     uno::Reference< XOfficeDatabaseDocument > xDocument(
 230         loadFromDesktop(aFile.GetURL()), UNO_QUERY_THROW);
 231
 232     uno::Reference< XConnection > xConnection =
 233         getConnectionForDocument(xDocument);
 234
 235     setupTestTable(xConnection);
 236
 237     if (bUsePreparedStatement)
 238         performPreparedStatementInsertTest(xConnection, rDBName);
 239     else
 240         performStatementInsertTest(xConnection, rDBName);
 241
 242     performReadTest(xConnection, rDBName);
 243 }
 244
 245 void EmbeddedDBPerformanceTest::setupTestTable(
 246     uno::Reference< XConnection >& xConnection)
 247 {
 248     uno::Reference< XStatement > xStatement = xConnection->createStatement();
 249
 250     // Although not strictly necessary we use quoted identifiers to reflect
 251     // the fact that Base always uses quoted identifiers.
 252     xStatement->execute(
 253         "CREATE TABLE \"PFTESTTABLE\" ( \"ID\" INTEGER NOT NULL PRIMARY KEY "
 254         ", \"STRINGCOLUMNA\" VARCHAR (50) "
 255     ")");
 256
 257     xConnection->commit();
 258 }
 259
 260 void EmbeddedDBPerformanceTest::performPreparedStatementInsertTest(
 261     uno::Reference< XConnection >& xConnection,
 262     const OUString& rDBName)
 263 {
 264     uno::Reference< XPreparedStatement > xPreparedStatement =
 265         xConnection->prepareStatement(
 266             "INSERT INTO \"PFTESTTABLE\" ( \"ID\", "
 267             "\"STRINGCOLUMNA\" "
 268             ") VALUES ( ?, ? )"
 269         );
 270
 271     uno::Reference< XParameters > xParameters(xPreparedStatement, UNO_QUERY_THROW);
 272
 273     ::boost::scoped_ptr< SvFileStream > pFile(getWordListStream());
 274
 275     OUString aWord;
 276     sal_Int32 aID = 0;
 277
 278     TimeValue aStart, aMiddle, aEnd;
 279     osl_getSystemTime(&aStart);
 280
 281     while (pFile->ReadByteStringLine(aWord, RTL_TEXTENCODING_UTF8))
 282     {
 283         xParameters->setInt(1, aID++);
 284         xParameters->setString(2, aWord);
 285         xPreparedStatement->execute();
 286     }
 287     osl_getSystemTime(&aMiddle);
 288     xConnection->commit();
 289     osl_getSystemTime(&aEnd);
 290
 291
 292     TimeValue aTimeInsert, aTimeCommit, aTimeTotal;
 293     getTimeDifference(&aStart, &aMiddle, &aTimeInsert);
 294     getTimeDifference(&aMiddle, &aEnd, &aTimeCommit);
 295     getTimeDifference(&aStart, &aEnd, &aTimeTotal);
 296     m_aOutputBuffer.append("Insert: " + rDBName + "\n");
 297     printTimes(&aTimeInsert, &aTimeCommit, &aTimeTotal);
 298
 299     pFile->Close();
 300 }
 301
 302 void EmbeddedDBPerformanceTest::performStatementInsertTest(
 303     uno::Reference< XConnection >& xConnection,
 304     const OUString& rDBName)
 305 {
 306     uno::Reference< XStatement > xStatement =
 307         xConnection->createStatement();
 308
 309     ::boost::scoped_ptr< SvFileStream > pFile(getWordListStream());
 310
 311     OUString aWord;
 312     sal_Int32 aID = 0;
 313
 314     TimeValue aStart, aMiddle, aEnd;
 315     osl_getSystemTime(&aStart);
 316
 317     while (pFile->ReadByteStringLine(aWord, RTL_TEXTENCODING_UTF8))
 318     {
 319         xStatement->execute(
 320             "INSERT INTO \"PFTESTTABLE\" ( \"ID\", "
 321             "\"STRINGCOLUMNA\" "
 322             ") VALUES ( "
 323             + OUString::number(aID++) + ", '" + aWord + "' )"
 324                     );
 325     }
 326     osl_getSystemTime(&aMiddle);
 327     xConnection->commit();
 328     osl_getSystemTime(&aEnd);
 329
 330     TimeValue aTimeInsert, aTimeCommit, aTimeTotal;
 331     getTimeDifference(&aStart, &aMiddle, &aTimeInsert);
 332     getTimeDifference(&aMiddle, &aEnd, &aTimeCommit);
 333     getTimeDifference(&aStart, &aEnd, &aTimeTotal);
 334     m_aOutputBuffer.append("Insert: " + rDBName + "\n");
 335     printTimes(&aTimeInsert, &aTimeCommit, &aTimeTotal);
 336
 337     pFile->Close();
 338 }
 339
 340 void EmbeddedDBPerformanceTest::performReadTest(
 341     uno::Reference< XConnection >& xConnection,
 342     const OUString& rDBName)
 343 {
 344     uno::Reference< XStatement > xStatement = xConnection->createStatement();
 345
 346     TimeValue aStart, aMiddle, aEnd;
 347     osl_getSystemTime(&aStart);
 348
 349     uno::Reference< XResultSet > xResults = xStatement->executeQuery("SELECT * FROM PFTESTTABLE");
 350
 351     osl_getSystemTime(&aMiddle);
 352
 353     uno::Reference< XRow > xRow(xResults, UNO_QUERY_THROW);
 354
 355     while (xResults->next())
 356     {
 357         xRow->getString(2);
 358     }
 359     osl_getSystemTime(&aEnd);
 360
 361     TimeValue aTimeSelect, aTimeIterate, aTimeTotal;
 362     getTimeDifference(&aStart, &aMiddle, &aTimeSelect);
 363     getTimeDifference(&aMiddle, &aEnd, &aTimeIterate);
 364     getTimeDifference(&aStart, &aEnd, &aTimeTotal);
 365     m_aOutputBuffer.append("Read from: " + rDBName + "\n");
 366     printTimes(&aTimeSelect, &aTimeIterate, &aTimeTotal);
 367 }
 368
 369 CPPUNIT_TEST_SUITE_REGISTRATION(EmbeddedDBPerformanceTest);
 370
 371 CPPUNIT_PLUGIN_IMPLEMENT();
 372
 373 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */