Tuninig LIANJA SQL SERVER

[tabacaru]

Any doc/tips on tuning LIANJA SQL SERVER ? I'm interested on the velocity for query, update , and so on
This week I make a lot of test using LIANJA / POSTGRES same hardware , same OS (Windows XP) same database (the most large table is 12 milion records). There is a non indiferent gap between these two SERVER . So I want to know if there is a problem with my HW/OS
Thanks a lot

[barrymavin]

As I mentioned to you in the ticket you submitted the RC has not been tuned, we released it for testing/QA. In RC4 it will be configured with better defaults. I will put together a blog article on how to tune Lianja SQL Server based on your personal requirements in due course. Please be patient. I am looking into the tickets that our outstanding. With almost 14k downloads everyone has a different view of what is important and we have to prioritize what we are doing without jumping all over the map. As I said earlier, everything reported will be looked at in due course. Performance can be affected by many different things. Lianja SQL Server for example is currently more tuned for large numbers of concurrent users than single user testing, but that can be adjusted as required. As an example, it uses row level locking based on isolation level whereas some other SQL databases use table locking which makes them faster in single user tests but extremely poor in real life applications with large amounts of concurrent users updating data.

[tabacaru]

Thanks a lot . Wait for new RC

[barrymavin]

As stated in my email to you:

I ran VFP against the table on my machine and the query is 1-2 seconds only using the Lianja ODBC driver which is twice the performance of the postgres times you emailed to me. I anticipate this will be approximately twice that speed in RC4 as I have done some cache tuning in the distribution. Make sure you have no ODBC logging turned on in your ODBC DSN.


Note that In native Lianja the same code runs in about 74ms.


See attached screenshot.

[barrymavin]

Something I feel is worth pointing out here also is that the Lianja SQL Server only has an 8MB footprint of which 6MB is a shared library. What this means is that you can utilize smaller servers to handle a good workload of users/connections so the TCO is significantly less than its heavyweight cousins. It contains the complete Lianja/VFP scripting language as well as the SQL engine.

If you have 1000's+ of users you can load balance the server connections as well so it scales very well.

Additionally, you can write stored procedures in the Lianja/VFP scripting language that will run across all supported platforms, not just Windows. This allows you to use the full power of the navigational database commands (NoSQL) to query, clean and manipulate data in tables and present the results back to your client apps. I think this is an important feature which should not be overlooked.

Apart from the above, the Lianja SQL Server uses row level locking which improves transaction throughput when lots of users are hitting on the system at the same time, it also has huge table support as it uses full 64-bit file access on all platforms.

Full support for the storage and retrieval of documents, images and other media is also well supported.

The SQL query optimizer is a cost based optimizer which will use VFP-style filtered indexes if it can -- this is something most other SQL databases cannot do -- they typically just use index restriction on columns.

[barrymavin]

See this post in the forums.
http://www.lianja.com/community/show...ver-RC3-tuning