Re: JDBC / Oracle / MS SQL Server performance anomalies

in article aa6b31e0.0301241036.1995999d_at_posting.google.com, Omi Chandiramani at omi_at_chandiramani.org wrote on 1/24/03 10:36 AM:

> is showing that SQL Server is twice as fast as Oracle.

<snip>

> PS: Please dont take my comments to be about some form of Oracle
> bashing. Believe me I'd rather be going the other way - but my data
> simply doesn't justify that at all.

Problem is that your data doesn't show anything of the sort. What you should have said was

'A very simple, non real-world test, on a 1 CPU machine, with a single user, against two databases with the different install parameters, no tuning, different memory caches, table structures and indexing strategies, via a coding standard that requires less than optimal usage of specific database features, showed that the two databases may have two different performance characteristics'

And then the question should have been not

'How can I make database X go faster ? '

but instead

'Is this a valid test for any performance comparison ? '

This reminds me of a recent posting in comp.databases.informix. A long time Informix user was testing Oracle at the bequest of his management, and proclaimed that Oracle was 10 times slower than Informix at extracting data. The reality was that the script he was running on Informix spooled the results straight to a file without terminal output, whereas the same script on Oracle was spooling via SQLPlus, and he was quite happily watching it do so on the terminal at the same time - all 100,000 rows or so. As soon as it was pointed out to him how to set up his SQLPlus script so that it didn't actually write to the terminal as well, he was much happier with the results.

Omi - as a suggestion, why don't you solicit advice as follows

1. Outline the performance characteristics your application needs to see by describing the workload, for some levels of user banding (i.e my application needs to support x transactions per minute, with a 3:1 ratio between query and update, for potential user bandings at 10, 50, 100 and 1000 users. Dirty reads are allowed, and 1 particular transaction must only take xyz milliseconds. Only the following hardware platforms (OS, nbr CPU's, memory) need to be supported)
2. Get advice on how to best use Oracle features and tune to get the results you want against 1) above
3. Same as 2), but this time for SQLServer
4. Build a test harness to test 1) above
5. Build and test a test-to-scale system deploying 2) and 3), measure and understand what the issues are
6. Once you have validated that the workload can be done by the specific databases you want to support with your app, ask specific questions about how to 'generalize' between the two set of features - and what each tradeoff would imply.