You are not logged in.
Hello,
I´m only an Oracle DBA that was researching a lot in the few spare time that I have, only to be possible to know where are the causes of bad performance problems that could happen in some Oracle databases, caused by no enough optimized application. Currently, I´m not a Delphi/VB developer, but I´m trying to to my best in building some examples to compare some technologies (ADO, dbExpress, ODBC) to be possible to show to the developers how should be the applications being developed, regarding to obtain the desired performance, too. This was the main reason of trying to use what was offered by mORMot, after reading about what the mORMot framework offered (through 'SynDBOracle: Open Source native Oracle access', as like as was mentioned in this link: http://blog.synopse.info/post/2011/07/0 … le-access):
"
Direct access to the Oracle Call Interface (OCI) client, with no BDE, Midas, DBExpress, nor OleDB or ODBC provider necessary;
.......
"
Than, I downloaded it, unpacked it as suggested and mentioned below:
Well, I´ve done all steps until the step 4, when the problem mentioned in the subject of this post happened. Please note that I´m not a developer and I´m using the standard (as-is) Delphi 7 environment, having SP1 installed, too.
Please let me know how could this problem be solved and if there is more complete and maybe ready-to-run examples available, for being possible to a newcomer to this technology easily understand how to use it.
Regards,
Sven
Offline
FastMM4 is a fast heap memory manager, provided by http://sourceforge.net/projects/fastmm
It is included since Delphi 2006.
But for Delphi 7, you need to download the file and include it in your Delphi libraries (e.g. D:\Dev\Synopse).
I've modified the REadMe.txt file to reflect explicitly this requirement.
See http://synopse.info/fossil/info/51a781c561
Thanks for the feedback and interest.
Online
If you check out http://blog.synopse.info/post/2012/07/1 … erformance and http://blog.synopse.info/post/2012/09/1 … figuration you can see that you can achieve more than 50,000 inserts per seconds with array binding with our SynDBOracle unit.
This technology is also available in JDBC, and probably some .Net direct providers (not OleDB).
Oracle is amazing, even if difficult to master.
I'm a developer since years, but have very rude notions of DBA.
From the programming point of view, the Oracle client is huge if you need all the options, but the Oracle Instant Client is much smaller, do not need any installation, and is very efficient. Directly using it, as mORMot layer does, is a huge benefit for deployment, and even speed.
With our classes, CPU and memory use at client level is also very low. Thanks to direct access to the Oracle Client.
Also check out SynDBExplorer.
See http://blog.synopse.info/?q=syndbexplorer
Online
FastMM4 is a fast heap memory manager, provided by http://sourceforge.net/projects/fastmm
It is included since Delphi 2006.
But for Delphi 7, you need to download the file and include it in your Delphi libraries (e.g. D:\Dev\Synopse).I've modified the REadMe.txt file to reflect explicitly this requirement.
See http://synopse.info/fossil/info/51a781c561Thanks for the feedback and interest.
Hello,
Thanks for your comments, but before wasn´t clear enough for a newcomer how to deal with all these requirements, having a standard environment. Than, I tried a lot of try-and-errors to be possible to find a way to put all these things to work, properly. Even for downloading all the requirements wasn´t so obvious, too.
What I´ve done was:
1) Readed again the steps mentioned before, to see of some detail was not paid enough attention;
2) Tried to find the mentioned RTL7 and LVCL, as was mentioned in another post (' RTL7 and LVCL directories added': http://synopse.info/forum/viewtopic.php?id=30);
3) Downloaded the entire package, as was mentioned behind a lot of other links: http://synopse.info/fossil/zip/mORMot%2 … 966ea9ba46
4) Unpacked again, in the place that I have put at first time: C:\Synopse
5) Paid attention to put togheter the other files, inside that folder (C:\Synopse);
6) Changed the Library path (in Tools->Environment Options->Library TAB) to reflect the latest changes: C:\Synopse\mORMotOpenSource;C:\Synopse\mORMotOpenSource\RTL7;C:\Synopse\mORMot\SQLite3...
Than and only than was possible to see some things run, as expected:
"
Synopse mORMot Framework Automated tests
------------------------------------------
1. Synopse libraries
1.1. Low level common:
- System copy record: 22 assertions passed 13.79ms
- TDynArray: 959,438 assertions passed 319.21ms
- TDynArrayHashed: 1,200,629 assertions passed 242.53ms
- Fast string compare: 7 assertions passed 235us
- IdemPropName: 10 assertions passed 277us
- Url encoding: 105 assertions passed 2.06ms
- IsMatch: 599 assertions passed 306us
- Soundex: 35 assertions passed 100us
- Numerical conversions: 784,912 assertions passed 394.22ms
- Curr64: 20,053 assertions passed 2.95ms
- CamelCase: 5 assertions passed 81us
- Bits: 4,614 assertions passed 135us
- Ini files: 7,004 assertions passed 108.89ms
- Unicode - Utf8: 61,082 assertions passed 1.37s
- Iso8601 date and time: 24,000 assertions passed 10.16ms
- Url decoding: 1,100 assertions passed 414us
- TSynTable: 41 assertions passed 13.25ms
- TSynCache: 404 assertions passed 283us
- TSynFilter: 1,005 assertions passed 3.67ms
- TSynValidate: 677 assertions passed 1.01ms
- TSynLogFile: 42 assertions passed 769us
Total failed: 0 / 3,065,784 - Low level common PASSED 2.49s
1.2. Low level types:
- RTTI: 34 assertions passed 11.13ms
- Url encoding: 200 assertions passed 1.30ms
- Encode decode JSON: 251,032 assertions passed 242.21ms
Total failed: 0 / 251,266 - Low level types PASSED 258.61ms
1.3. Big table:
- TSynBigTable: 19,181 assertions passed 321.55ms
- TSynBigTableString: 15,996 assertions passed 149.70ms
- TSynBigTableMetaData: 384,060 assertions passed 1.75s
- TSynBigTableRecord: 452,185 assertions passed 4.21s
Total failed: 0 / 871,422 - Big table PASSED 6.44s
1.4. Cryptographic routines:
- Adler32: 1 assertion passed 158us
- MD5: 1 assertion passed 163us
- SHA1: 5 assertions passed 163us
- SHA256: 5 assertions passed 179us
- AES256: 6,372 assertions passed 293.45ms
- Base64: 11,994 assertions passed 110.35ms
Total failed: 0 / 18,378 - Cryptographic routines PASSED 417.45ms
1.5. Compression:
- In memory compression: 12 assertions passed 266.39ms
- Gzip format: 19 assertions passed 535.24ms
- Zip format: 36 assertions passed 2.25s
- SynLZO: 3,006 assertions passed 120.74ms
- SynLZ: 13,016 assertions passed 596.39ms
Total failed: 0 / 16,089 - Compression PASSED 3.78s
1.6. Synopse PDF:
- TPdfDocument: 4 assertions passed 28.03ms
- TPdfDocumentGDI: 6 assertions passed 694.21ms
Total failed: 0 / 10 - Synopse PDF PASSED 725.96ms
2. mORMot
2.1. Basic classes:
- TSQLRecord: 52 assertions passed 614us
- TSQLRecordSigned: 200 assertions passed 5.48ms
- TSQLModel: 3 assertions passed 332us
Total failed: 0 / 255 - Basic classes PASSED 12.23ms
2.2. File based:
- Database direct access: 10,138 assertions passed 905.73ms
- Virtual table direct access: 12 assertions passed 47.33ms
- TSQLTableJSON: 19,030 assertions passed 155.40ms
- TSQLRestClientDB: 593,518 assertions passed 7.14s
Total failed: 0 / 622,698 - File based PASSED 8.26s
2.3. File based WAL:
- Database direct access: 10,138 assertions passed 762.87ms
- Virtual table direct access: 12 assertions passed 15.12ms
- TSQLTableJSON: 19,030 assertions passed 110.33ms
- TSQLRestClientDB: 593,518 assertions passed 6.82s
Total failed: 0 / 622,698 - File based WAL PASSED 7.71s
2.4. Memory based:
- Database direct access: 10,136 assertions passed 568.51ms
- Virtual table direct access: 12 assertions passed 15.29ms
- TSQLTableJSON: 19,030 assertions passed 104.18ms
- TSQLRestClientDB: 661,811 assertions passed 7.56s
Total failed: 0 / 690,989 - Memory based PASSED 8.26s
2.5. Client server access:
- TSQLHttpServer: 21 assertions passed 657.14ms
using THttpApiServer
- TSQLHttpClient: 3 assertions passed 278.03ms
- Http client keep alive: 3,084 assertions passed 834.86ms
4803 B, first 12.60ms, done 799.53ms i.e. 1250/s, aver. 799us, 5.8 MB/s
- Http client multi connect: 3,084 assertions passed 628.20ms
4803 B, first 830us, done 607.34ms i.e. 1646/s, aver. 607us, 7.7 MB/s
- Named pipe access: 3,086 assertions passed 661.39ms
4803 B, first 130.99ms, done 330.20ms i.e. 3028/s, aver. 330us, 14.1 MB/s
- Local window messages: 3,085 assertions passed 141.34ms
4803 B, first 177us, done 137.74ms i.e. 7259/s, aver. 137us, 33.9 MB/s
- Direct in process access: 3,053 assertions passed 84.21ms
4803 B, first 114us, done 80.03ms i.e. 12495/s, aver. 80us, 58.4 MB/s
Total failed: 0 / 15,416 - Client server access PASSED 3.31s
2.6. Service oriented architecture:
- Weak interfaces: 56 assertions passed 16.81ms
- Service initialization: 127 assertions passed 2.37ms
- Direct call: 602,472 assertions passed 90.73ms
- Server side: 602,482 assertions passed 86.21ms
- Client side REST: 602,484 assertions passed 1.42s
- Client side JSONRPC: 602,482 assertions passed 1.52s
- Client side synchronized REST: 602,482 assertions passed 3.23s
- Security: 135 assertions passed 2.72ms
- Custom record layout: 602,482 assertions passed 1.43s
- Mocks and stubs: 30,029 assertions passed 108.51ms
Total failed: 0 / 3,645,231 - Service oriented architecture PASSED 7.94s
2.7. External database:
- External records: 1 assertion passed 353us
- Auto adapt SQL: 168 assertions passed 24.54ms
- Crypted database: 253,275 assertions passed 697.68ms
- External via REST: 243,436 assertions passed 2.44s
- External via virtual table: 243,436 assertions passed 3.92s
Total failed: 0 / 740,316 - External database PASSED 7.10s
Synopse framework used: 1.18
SQlite3 engine used: 3.7.14
Generated with: Delphi 7 compiler
Time elapsed for all tests: 56.80s
Tests performed at 02/01/2013 14:25:44
Total assertions failed for all test suits: 0 / 10,560,552
! All tests passed successfully.
Done - Press ENTER to Exit
"
...
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>Cd\"Synopse\mORMotOpenSource\SQLite3"
C:\Synopse\mORMotOpenSource\SQLite3>dir
Volume in drive C is Server
Volume Serial Number is A8B4-4854
Directory of C:\Synopse\mORMotOpenSource\SQLite3
02/01/2013 14:25 <DIR> .
02/01/2013 14:25 <DIR> ..
02/01/2013 15:26 260 c.bat
02/01/2013 14:25 27.905 Dali1.json
02/01/2013 14:25 5.859 Dali2.data
02/01/2013 14:14 <DIR> Documentation
02/01/2013 14:33 604.066 mORMot.dcu
02/01/2013 15:26 1.278.610 mORMot.pas
02/01/2013 15:26 9.036 mORMotBigTable.pas
02/01/2013 14:33 38.107 mORMotDB.dcu
02/01/2013 15:26 60.870 mORMotDB.pas
02/01/2013 14:33 12.522 mORMotFastCgiServer.dcu
02/01/2013 15:26 30.182 mORMotFastCgiServer.pas
02/01/2013 14:33 12.109 mORMotHttpClient.dcu
02/01/2013 15:26 18.398 mORMotHttpClient.pas
02/01/2013 14:33 9.950 mORMotHttpServer.dcu
02/01/2013 15:26 22.034 mORMotHttpServer.pas
02/01/2013 15:26 97.955 mORMoti18n.pas
02/01/2013 15:26 184.743 mORMotReport.pas
02/01/2013 14:33 4.514 mORMotSelfTests.dcu
02/01/2013 15:26 6.739 mORMotSelfTests.pas
02/01/2013 14:33 14.974 mORMotService.dcu
02/01/2013 15:26 28.212 mORMotService.pas
02/01/2013 14:33 41.408 mORMotSQLite3.dcu
02/01/2013 15:26 69.340 mORMotSQLite3.pas
02/01/2013 15:26 111.552 mORMotToolBar.pas
02/01/2013 15:26 71.661 mORMotUI.pas
02/01/2013 15:26 709 mORMotUIEdit.dfm
02/01/2013 15:26 28.147 mORMotUIEdit.pas
02/01/2013 15:26 1.547 mORMotUILogin.dfm
02/01/2013 15:26 25.913 mORMotUILogin.pas
02/01/2013 15:26 874 mORMotUIOptions.dfm
02/01/2013 15:26 18.479 mORMotUIOptions.pas
02/01/2013 15:26 2.086 mORMotUIQuery.dfm
02/01/2013 15:26 12.493 mORMotUIQuery.pas
02/01/2013 15:26 6.010 mORMotVCL.pas
02/01/2013 14:25 86.991 People.data
02/01/2013 14:25 372.204 People.json
02/01/2013 14:14 <DIR> Samples
02/01/2013 15:26 1.717 ServiceTestForm.dfm
02/01/2013 15:26 3.013 ServiceTestForm.pas
02/01/2013 15:26 308 ServiceTestSQL3.dpr
02/01/2013 15:26 876 ServiceTestSQL3.res
02/01/2013 15:26 4.858.385 sqlite3.c
02/01/2013 15:26 246 SQLite3BtnArrow.bmp
02/01/2013 15:26 206 SQLite3BtnClose.bmp
02/01/2013 15:26 214 SQLite3BtnMax.bmp
02/01/2013 15:26 206 SQLite3BtnMin.bmp
02/01/2013 15:26 406 SQLite3BtnOk.bmp
02/01/2013 15:26 88 SQLite3UI.rc
02/01/2013 15:26 752 SQLite3UI.RES
02/01/2013 15:26 5.987 SQLite3UILogin.png
02/01/2013 15:26 39 SQLite3UILogin.rc
02/01/2013 15:26 6.064 SQLite3UILogin.RES
02/01/2013 14:25 323 temptest.log
02/01/2013 14:25 2.432.000 test.db3
02/01/2013 14:24 4.941 test.ini
02/01/2013 14:24 3.307 test.ini.synlz
02/01/2013 14:25 71.044 Test1.json
02/01/2013 14:24 4.941 test2.ini
02/01/2013 15:26 2.016 TestOleDB.dpr
02/01/2013 14:25 400.384 testpass.db3
02/01/2013 15:26 3.648 TestSQL3.dpr
02/01/2013 14:33 1.404.416 TestSQL3.exe
02/01/2013 14:24 20.260.616 TestSQL3.MetaData
02/01/2013 14:25 6.785 TestSQL3.pdf
02/01/2013 14:24 274.970 TestSQL3.Record
02/01/2013 14:24 2.213.766 TestSQL3.syn1
02/01/2013 14:24 1.587.156 TestSQL3.syn2
02/01/2013 14:24 62 TestSQL3.syntable
02/01/2013 15:26 992 TestSQL3Register.dpr
67 File(s) 36.866.333 bytes
4 Dir(s) 97.022.914.560 bytes free
C:\Synopse\mORMotOpenSource\SQLite3>dir *.exe
Volume in drive C is Server
Volume Serial Number is A8B4-4854
Directory of C:\Synopse\mORMotOpenSource\SQLite3
02/01/2013 14:33 1.404.416 TestSQL3.exe
1 File(s) 1.404.416 bytes
0 Dir(s) 97.022.914.560 bytes free
C:\Synopse\mORMotOpenSource\SQLite3>dir *.db
Volume in drive C is Server
Volume Serial Number is A8B4-4854
Directory of C:\Synopse\mORMotOpenSource\SQLite3
File Not Found
C:\Synopse\mORMotOpenSource\SQLite3>dir *.exe
Volume in drive C is Server
Volume Serial Number is A8B4-4854
Directory of C:\Synopse\mORMotOpenSource\SQLite3
02/01/2013 14:33 1.404.416 TestSQL3.exe
1 File(s) 1.404.416 bytes
0 Dir(s) 97.022.914.560 bytes free
C:\Synopse\mORMotOpenSource\SQLite3>dir *.db?
Volume in drive C is Server
Volume Serial Number is A8B4-4854
Directory of C:\Synopse\mORMotOpenSource\SQLite3
02/01/2013 14:25 2.432.000 test.db3
02/01/2013 14:25 400.384 testpass.db3
2 File(s) 2.832.384 bytes
0 Dir(s) 97.022.914.560 bytes free
C:\Synopse\mORMotOpenSource\SQLite3>testsql3
...
HTH other newcomers, like me. Now, I wanted to know it there is available and example for showing how to connect and issue SQL statements, in Oracle databases, too...
Sven
Offline
Congratulations!
Note that both RTL7 and LVCL folders are not needed at all.
In the SAD pdf you have a section named "Source code" in which all the needed units and folders are discussed.
Take a look at the SynDB articles, and all that is said in the SAD pdf corresponding to it.
See http://blog.synopse.info/tag/SynDB
and http://synopse.info/files/pdf/Synopse%2 … 201.18.pdf
See also sample #12, #13 and #15.
Online
If you check out http://blog.synopse.info/post/2012/07/1 … erformance and http://blog.synopse.info/post/2012/09/1 … figuration you can see that you can achieve more than 50,000 inserts per seconds with array binding with our SynDBOracle unit.
This technology is also available in JDBC, and probably some .Net direct providers (not OleDB).
Oracle is amazing, even if difficult to master.
I'm a developer since years, but have very rude notions of DBA.
From the programming point of view, the Oracle client is huge if you need all the options, but the Oracle Instant Client is much smaller, do not need any installation, and is very efficient. Directly using it, as mORMot layer does, is a huge benefit for deployment, and even speed.
With our classes, CPU and memory use at client level is also very low. Thanks to direct access to the Oracle Client.Also check out SynDBExplorer.
See http://blog.synopse.info/?q=syndbexplorer
Hi,
I know that currently Oracle is a big player in the market, but sometimes isn´t easy to find some benchmark examples to check and follow. Thanks for sharing these examples and links, because I´m checking it.
And because mORMot offered a way to have direct access to the Oracle Instant Client was one of the reasons for trying it, too.
But noted that no more examples were offered, regarding to MySQL, also. I knowed only recently that dbExpress have support for MySQL, but only for the releases up to 4.x, and not for the releases 5.x and up (http://www.justsoftwaresolutions.co.uk/ … sql_5.html). Maybe could be another good opportunity to mORMot, to offer a way to cover this gap, because some of the offered libraries (like the mentioned) don´t work as expected...
Regards,
Sven
Offline
Congratulations!
Note that both RTL7 and LVCL folders are not needed at all.In the SAD pdf you have a section named "Source code" in which all the needed units and folders are discussed.
Take a look at the SynDB articles, and all that is said in the SAD pdf corresponding to it.
See http://blog.synopse.info/tag/SynDB
and http://synopse.info/files/pdf/Synopse%2 … 201.18.pdfSee also sample #12, #13 and #15.
Hi,
Please note that I needed to put RTL7 and LVCL in the library path (like I tried to comment, before) because when trying to compile that sample, the problem (mentioned as the subject of this post) happened every time. Only after putting that folders in the library path was possible to compile the example, as was suggested...
Regards,
Sven
Offline
If you check out http://blog.synopse.info/post/2012/07/1 … erformance and http://blog.synopse.info/post/2012/09/1 … figuration you can see that you can achieve more than 50,000 inserts per seconds with array binding with our SynDBOracle unit.
Hi,
Thanks for sending these links, because was possible to see these reports being generated in my own environment. But please note that when testing the Oracle database (local), a problem appeared after all other benchmarks were done (for SQLite3 and others):
"
Assertion failure (C:\Synopse\mORMotOpenSource\SQLite3\Samples\15 - External DB performance\PerfMain.pas, line 280)
"
I tested the connection to the Oracle local database and was Ok, of course too. Please advice about what could be done, when possible too.
"
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Beta on Qua Jan 2 18:37:14 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect system/*******@xe
Connected.
SQL> show sga
Total System Global Area 598437888 bytes
Fixed Size 1385180 bytes
Variable Size 343936292 bytes
Database Buffers 247463936 bytes
Redo Buffers 5652480 bytes
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
"
Regards,
Sven
Offline
You did get FastMm4 from the Rtl7 folder in fact.
Hello,
Could be exist any other dependencies that could be the origin of the problem, mentioned previously (and below) too ?
"
Assertion failure (C:\Synopse\mORMotOpenSource\SQLite3\Samples\15 - External DB performance\PerfMain.pas, line 280)
"
I really wanted to know how to solve this, if possible. Liked very much the example that showed the comparison and benchmark of all these types of solutions, but currently only when doing these steps against Oracle database, this problem happened (right after the third pass, I guess)...
Please advice,
Sven
Offline
No, there is no other dependency.
I just checked that PerfTest.dpr works as expected with Oracle 11g here.
Did you try with SynDBExplorer to access the DB and make some SELECT?
The assertion failure is about the fact that the record with ID was not found, or have an an incorrect Amount column value.
Online
No, there is no other dependency.
I just checked that PerfTest.dpr works as expected with Oracle 11g here.
Did you try with SynDBExplorer to access the DB and make some SELECT?
The assertion failure is about the fact that the record with ID was not found, or have an an incorrect Amount column value.
Hello,
Thanks for answering, but I tried to connect to the same Oracle instance using SynDBExplorer and was Ok. Was this the reason in requesting some help, to understand where could be the problem, in my own environment...
Is there any other way to check what is happening, through the framework ? Could you give some advices about how to debug this ? Any help should be greatly appreciated, because I wanted to use that benchmark to capture a before and after situations, in one Oracle RAC environment. This is planned to happen, this month...
Regards,
Sven
Offline
Perhaps the table is not void at program startup.
Please drop first the table.
It is only deleted at the end of the tests, so it may remain if you stopped the tests.
Online
I just checked that PerfTest.dpr works as expected with Oracle 11g here.
Did you try with SynDBExplorer to access the DB and make some SELECT?
The assertion failure is about the fact that the record with ID was not found, or have an an incorrect Amount column value.
...
Perhaps the table is not void at program startup.
Please drop first the table.
It is only deleted at the end of the tests, so it may remain if you stopped the tests.
Hello,
Thanks for sending your comments, but I tried to follow SAD pdf document and the Perftest/SynDBOracle source codes, but wasn´t clear (to me) where was the specified table name, to drop it.
I tried to see more verbose results by unmarking the commented code (i.e., "SynDBLog.Family.Level := LOG_VERBOSE; // for debugging") but was only possible to see that an error happened:
"Project PerfTest.exe raised exception class ESQLOracle with message 'ORA-01722: invalid number'.
Process stopped. Use Step or Run to continue."
Now, even remarking that line this problem remain.
Being possible to have these issues with existing tables, maybe could be useful to drop these tables before doing these testings. If possible, could you mention the table name (an the correlated schema), to be dropped in the Oracle database ?
Regards,
Sven
Offline