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