NREL/EnergyPlus — pull request #4610 by mbadams5 (2014-11-29 21:09:50+00:00)
Title: Update SQLite

######TL;DR;
From my test runs using SQLite:SimpleAndTabular, this decreases overall runtime ~8-15%, decreases database file size ~55-65%, and decreases SQLite functions ~35-99%.

@kbenne @Myoldmopar @sanyalj
Please review and make sure i've maintained backwards compatibility .

######Updates

First, this updates the sqlite.[h,c] from 3.6.18 -> 3.8.7.2, which improves SQLite performance ~10-30%. Next, are many code updates to SQLiteProcedures to improve SQL performance and table design.

- Removed some existing indexes since they were not being used in view queries (using EXPLAIN QUERY PLAN). This dramatically reduced the database file size.
- Consolidated ReportVariableData and ReportMeterData into ReportData to reduce data duplication
- Removed ExtendedDataIndex from ReportData and added ReportDataIndex to ReportExtendedData to reduce number of NULLs in database
- Changed createSQLiteStringTableRecord to reduce number of lookups dramatically
- Add more INTEGER PRIMARY KEYs to aid in queries
- Added views to replicate legacy tables
- Fixed errors in code adding extra data or null data to database tables
- And more...

###Medium Office
| Medium Office | baseline | code | baseline => code | library | code => library | baseline => library |
| ----------: |:--------:|:----:|:----------------:|:--------:|:----------------:|:----------------:|
| initializeIndexes | 1646 | 1 | 99.94% | 1 | 0.00% | 99.94% |
| sqliteCommit | 63 | 37 | 41.27% | 35 | 5.41% | 44.44% |
| createSQLiteTabularDataRecords | 4984 | 292 | 94.14% | 254 | 13.01% | 94.90% |
| createSQLiteStringTableRecord | 4946 | 235 | 95.25% | 216 | 8.09% | 95.63% |
| createSQLiteReportDataRecord | 1454 | 1128 | 22.42% | 766 | 32.09% | 47.32% |
| WriteRealData | 625 | 796 | -27.36% | 541 | 32.04% | 13.44% |
| WriteIntegerData | 109 | 131 | -20.18% | 81 | 38.17% | 25.69% |
| ReportHRMeters | 719 | 197 | 72.60% | 144 | 26.90% | 79.97% |
| createSQLiteTimeIndexRecord | 129 | 82 | 36.43% | 78 | 4.88% | 39.53% |
| sqliteStepCommand | 5991 | 1171 | 80.45% | 847 | 27.67% | 85.86% |
| sqliteBindInteger | 307 | 169 | 44.95% | 121 | 28.40% | 60.59% |
| sqliteBindText | 42 | 14 | 66.67% | 18 | -28.57% | 57.14% |
| run time | 43.83 | 37.66 | 14.08% | 36.89 | 2.04% | 15.83% |
| database size | 34,193,408 | 11,955,200 | 65.04% | 11,734,016 | 1.85% | 65.68% |

###Outpatient
| Outpatient | baseline | code | baseline => code | library | code => library | baseline => library |
| -------: |:--------:|:----:|:----------------:|:--------:|:----------------:|:----------------:|
| initializeIndexes | 4497 | 1 | 99.98% | 1 | 0.00% | 99.98% |
| sqliteCommit | 129 | 102 | 20.93% | 85 | 16.67% | 34.11% |
| createSQLiteTabularDataRecords | 36923 | 1115 | 96.98% | 932 | 16.41% | 97.48% |
| createSQLiteStringTableRecord | 36755 | 931 | 97.47% | 785 | 15.68% | 97.86% |
| createSQLiteReportDataRecord | 3117 | 2785 | 10.65% | 2013 | 27.72% | 35.42% |
| WriteRealData | 2123 | 2454 | -15.59% | 1792 | 26.98% | 15.59% |
| WriteIntegerData | 95 | 108 | -13.68% | 76 | 29.63% | 20.00% |
| ReportHRMeters | 897 | 222 | 75.25% | 144 | 35.14% | 83.95% |
| createSQLiteTimeIndexRecord | 204 | 148 | 27.45% | 116 | 21.62% | 43.14% |
| sqliteStepCommand | 38873 | 3119 | 91.98% | 2318 | 25.68% | 94.04% |
| sqliteBindInteger | 611 | 527 | 13.75% | 356 | 32.45% | 41.73% |
| sqliteBindText | 135 | 67 | 50.37% | 44 | 34.33% | 67.41% |
| run time | 430.8 | 402.16 | 6.65% | 393.7 | 2.10% | 8.61% |
| database size | 83,494,912 | 37,134,336 | 55.53% | 36,961,280 | 0.47% | 55.73% |