Re: Another angle on this....

With clobs you are just getting a ref which I think is rather small so it would be like retrieving what 16 bytes.
How fast you can get rows/second is going to depend on a lot of things. (rhetorical questions)
How cached is the IO (reading from db block buffers or does the data have to be put in the cache first.)?
What protocol (my guess is TCP/IP - nothing wrong with that, but different ones can perform differently)?
Also what are the sdu and tdu settings on client and server? (controls the session data unit and transmit data unit sizes) What packet size will the network handle?
How fast is the network?
How fast are the disks and what config and what data distribution? (are redo logs contending for data etc?)
Is the query have an order by and if the data is larger than a certain size (configerable) are we sorting to disk? ( that will make a huge difference.) Does the query use the old MS Access select distinct...forcing Oracle to compare every row with every other row before sending the data back? The Nagle algorithium can cause you speed problems especially if a large amount of data is retrieved in once shot. (this is an OS problem) It really isn't possible to say in general you should get x rows per second.(Just like on sqlserver it really isn't either.) If you use host variables etc. on subsequent queries you will get better than x. etc.

I wasn't calling your application design crap; frankly I know basically nothing about it. And I am not looking for a consulting job. I have seen enough people with extensive MS SQLServer experience and other application development experience that that there are a set of common errors that they might make. From that I stated POSSIBLE things that might be wrong. It is quite common in the SQLServer world to retrieve all the results at once and close the result set and then do things locally on the client. This usually done because MS consultants say that is the way to do it. (or write everything in stored procedures and do basically the same thing) What they are doing is trying to get around a lock problem. (in Oracle readers don't block writers and writers don't block readers - so if I only need to display 20 rows on the screen I would only get 20 rows and fetch more later as I need it. Okay, if my array fetch size is 50 rows then yes I would fetch 50, but not the whole result set of lets say 200 or 300 rows. Why? Wire time is much slower than other things in the application. Also why retrieve more than I need right now. You said you only retrieve the columns you need. I was surprised you said that only because I thought that was a given; but no big deal - it is just ensuring clarity - a good thing.

Jim

"Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message news:gxQc8.122434$H37.15800676_at_news2-win.server.ntlworld.com...
> Sorry Jim, I've been thinking about your response....
>
> Some of the thrust of what you are saying amounts to "retrieve less data",
> "are you sure you need it all?" "are you simulating client side cursor",
> "just retrieve what you need when you need it" <more bad paraphrasing
> deleted><G>
>
> There is a kind of implication here that you think I'm retrieving too much
> data and expecting Oracle to give it to me faster than it can; so the
> performance is normal....just my app design is crap. Might that be right?
> 150 records/second or so is normal? That would be fine by me. I'd shut up
> (loud cheering from Oracle NGroups),because my Oracle users would be
getting
> the performance that they are accustomed to in other apps using their
choice
> of db tech. OTOH it's likely that I've read too much into the first
> paragraph of your response just because it seems fundamentally unlikely to
> me that Oracle should be that slow and also you've made sensible
performance
> suggestions in the following pgraphs.
>
> To put it another way, do you have any mental benchmark of *broadly* what
> kind of retrieval speed optimised ODBC routines should achieve against
> Oracle? 150? 300?1000?5000?10000?20000+(!) records per second on typical
20
> column tables of mixed type with maybe a couple of CLOBS?
>
> Regards
>
> Tom/Heinz
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:7dOc8.25995$UT6.53933_at_rwcrnsc52.ops.asp.att.net...
> > My guess at your poor performance on the row retrieval is that you are
> > probably trying to simulate the client side cursor concept that
sqlserver
> > uses (bring all results to client and close cursor). They do this
because
> > of the locking problems sqlserver has. They don't complain about not
> having
> > multiversioning because they haven't heard of it in most cases. Since
> > sqlserver doesn't work that way and the MS consultants will never answer
> the
> > question "I have a complex batch process that needs to process a large
set
> > of rows (eg millions) how do I do this without bringing the entire
result
> > set back at once? Once I got past the retrieve entire result set mantra
> the
> > MS consultant (from MS) said he would get back to me. Never did. Try
> > server side cursors, just rtetrieve what you need when you need it.
Also
> > use array fetches and bind variables and don't close the cursor, keep it
> > open, change the bind variables and reexecute, much faster. (probably
not
> > doing that)
> >
> > Really, if creating an index is that much of a problem then you have
> larger
> > problems. Since function based indexes are good for other uses besides
> > upper or lower on a text column. I would rather have them implimented
> this
> > way than not be able to search case sensitive or use function based
> indexes
> > for other purposes.
> >
> > I've ported to 9 different back ends and sqlserver was the most
> difficult -
> > took 6 rewrites of the driver layer and 9 months, all the other
databases
> > took 30 days to add the functionality including qa time (1 engineer
doing
> > all the development). In the end it worked, but in comparison to
> everything
> > else it was not as stable. Granted this was 6.5, but everything is
> > realative.
> >
> > Jim
> >
> > "Heinz Kiosk" <no.spam_at_ntlworld.com> wrote in message
> > news:yiMc8.91752$as2.14794568_at_news6-win.server.ntlworld.com...
> > >
> > > "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> > > news:3C72A014.C8DCFDDC_at_ci.seattle.wa.us...
> > > > 1. Defending the indefensible? Bug? Let me quote you from page 38 of
> Tom
> > > Kyte's book expert one-on-one
> > > > Oracle.
> > > >
> > > > "The developer doing the conversion (from TransactSQL to Oracle) was
> > > complaining that the SQL queries in
> > > > Oracle returned the 'wrong' answer. The goal here was to find al of
> the
> > > rows in T where X was Null if
> > > > some condition was not met or where x equaled a specific value if
some
> > > condition was met.
> > > >
> > > > The complaint was that, in Oracle, this query would return no data
hen
> > > L_SOME_VARIABLE was not set to a
> > > > specific value (when it was left as Null). In Sybase and SQL Server,
> > this
> > > was not the case - the query
> > > > would find the rows where X was set to a Null value. I see this on
> > almost
> > > every conversion from Sybase
> > > > or SQL Server to Oracle. SQL is supposed to operate under tri-valued
> > logic
> > > and Oracle implements Null
> > > > values the way ANSI SQL requires them to be implemented. Under those
> > > rules, comparing x to Null is
> > > > neither True or False - it is, in fact, unknown."
> > > >
> > > > So if complying with ANSI SQL is an indefensible bug I'll stand in
for
> > > Larry Ellison. >>Fire when ready!
> > > Straw man again.... You do like them. I hope you didn't pay much for
> that
> > > book.<G>
> > > I'm not complaining about Oracle returning no rows from comparisons
with
> > > Null value fields. Nor am I complaining that I can't have Nulls in
> unique
> > > keys. No db that I am aware of returns rows from null comparisons and
> the
> > > reference above to SQL Server doing it is mistaken. It definitely
> doesn't.
> > > Reading the snippet Tom Kyte has missed the point (deliberately?). He
> > didn't
> > > notice that all other db platforms actually distinguish between blank
> and
> > > null (correctly). Presumably he is some Oracle high-priest and I'll
now
> be
> > > burnt at the stake for heresy.
> > >
> > > To repeat; I am complaining that Oracle turns blank strings into
Nulls.
> > That
> > > is the indefensible behaviour. All of the rest is collateral damage
> > > resulting from the fact that Oracle doesn't appear to understand that
""
> > is
> > > a valid value for a string field distinct from NULL. Lets put it
another
> > > way, would you still defend Oracle if integer zeroes were considered
as
> > > nulls and failed to match any query predicate other than "is null" and
> > > integer zeroes couldn't be in a key and appeared last in sorted lists
> etc.
> > > Come on.
> > >
> > > You and Tom Kyte are in a hole thats so deep I can't see either of you
> any
> > > more, just the frenzied earth flying out the top. Stop Digging.
> > > > ----------------------------------------------
> > > > 2. BFD = Big ..... Deal
> > > Thank you, I'll remember that.
> > > However, its a big deal for me when porting a large app from another
> > > platform. Did the developer who implemented quoted identifiers really
> > think
> > > that users would want to distinguish between columns named "Comment"
and
> > > "COMMENT" and "cOMMENT" and "CoMMENT"......? Not useful
> > > > ----------------------------------------------
> > > > 3. I've been using identity columns for years.
> > > > What difference can't be implemented in 29 keystrokes?
> > > > Be specific.
> > > See my boring and pointlessly detailed rebuttal in another thread.
> > > > ----------------------------------------------
> > > > 4. Creating an index is a pain? Really? No doubt you'll never touch
C
> or
> > > Java.
> > > My main development is done entirely in C++. Its only a minor pain.
> > > > ----------------------------------------------
> > > > 5. Had you given an example I'd gladly comment. But, like CASE, I
> > suspect
> > > > you are expressing more your lack of knowledge of Oracle rather than
> any
> > > > inability of Oracle to perform some function.
> > > Try decode (fieldvalue>4,0,otherfieldvalue,0) As it happens, most of
my
> > > gripe about this goes away as I can use CASE when fieldvalue>4 then 0
> else
> > > otherfieldvalue END
> > > > ----------------------------------------------
> > > > 6. Didn't appear in 9i. I use it in 8i.
> > > > You stand doubly corrected.
> > > Another Oracle user has told me that their documentation for 8i does
not
> > > mention it either. Out of interest, could you definitely create views
> > using
> > > CASE in 8i using PL/SQL? Thats what I wanted to do and failed for some
> > > reason. Probably incompetence.
> > > > ----------------------------------------------
> > > > 8. No one uses Oracle "out of the box" any more than one builds a
> house
> > > with 2x4s.
> > > > Nails and carpentry are required. The advantages come with a cost.
The
> > > cost of
> > > > having to do real work and reading the manuals.
> > > I've been pampered, the other db manufacturers built my house for me,
> its
> > a
> > > bummer to have to build my own. <G>
> > > So, optimised ODBC routines only managing to return 2.5% of the
> > performance
> > > of 4 other platforms (out of the box performance) is acceptable
> > performance
> > > for Oracle out of the box, cos "no-one uses it that way". As a
clueless
> > > whining stranger to the real work that you are familiar with any
guesses
> > at
> > > how long it'll take me to solve this one? I need to know how long I'll
> be
> > > doing real work and when I can revert to my normal slothful condition.
> <G>
> > > However there is a serious point here. The performance of multi-record
> > > retrieval is dreadful (despite terrific performance in returning the
> > *first*
> > > row even of complex queries). I accept that its my responsibility to
> sort
> > it
> > > out, and Oracle is very tunable etc etc etc , but I have no idea how
> much
> > > work I'll have to do, and how much knowledge I'll have to acquire to
fix
> > it.
> > > The commitment is completely open ended as no-one has even given the
> > vaguest
> > > hint as to what the nature of the problem might be and where I should
> > start
> > > looking other than to recommend that I start reading some weighty
tomes.
> > > > ----------------------------------------------
> > > > Finally: I'm not upset. It takes a lot more than the usenet to upset
> me.
> > > Then why do so many of your posts in these groups come across as upset
> > > put-downs?
> > > >
> > > > But you don't see Oracle developers in the SQL Server usenet groups
> > > complaining about the lack of
> > > > multiversioning and the fact that row level locks are a finite
> resource
> > do
> > > you?
> > > Of course you don't, they're all too busy doing "real work" trying to
> get
> > > usable performance out of the shipped version of Oracle <G>. Meanwhile
> all
> > > the SQL Server/DB2/Sybase bunnies whose apps are just singing along
> > without
> > > tuning so they've got no work to do come in here and post specifically
> to
> > > wind you up. Its that or yet another game of Hearts. ;)
> > > Seriously, because Oracle *is* a higher pitched and more advanced
> product
> > > than SQL Server there probably aren't as many people trying to do the
> port
> > > in that direction. You can bet your life that if there were, and they
> > needed
> > > those features, they'd let the NG know about it.
> > > >
> > > > I am just tired of what I perceive to be the kind of whining I would
> > > expect from a toddler coming from
> > > > people that think that whatever they know is better than what they
> don't
> > > know. That are reluctant to
> > > > learn new things. That think change is bad. And post in the Oracle
> > usenet
> > > groups, day after day, week
> > > > after week, the same nonsense. Software is just a tool not a
religion.
> > At
> > > Boeing they don't put
> > > > airplanes together with hammers. And my house was not built with a
> rivet
> > > gun.
> > > Another straw man. I've ported my app to 5 different db platforms. I
> love
> > > learning new things. I approached Oracle with excitement. However, it
is
> a
> > > fact that despite Oracle's many nice features, most of which are
> > > unfortunately of little use to me because I develop cross-platform,
the
> > > Oracle port was considerably more painful than all of the others put
> > > together, by a long margin.
> > >
> > > Its not me who has a religious attitude to one particular technology.
> Pots
> > > and Black Kettles I think.....
> > >
> > >
> > > > ----------------------------------------------
> > > > Daniel Morgan
> > > >
> > > >
> > > >
> > > > Tom McClelland wrote:
> > > >
> > > > > damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message
> > > news:<3C7144EB.7CA04F3A_at_ci.seattle.wa.us>...
> > > > > > My comments, barbs, interspersed below.
> > > > > >
> > > > > > Daniel Morgan
> > > > > >
> > > > > >
> > > > > >
> > > > > > Tom McClelland wrote:
> > > > > >
> > > > > > > Your points are valid, I don't contest them. However as
someone
> > > coming
> > > > > > > from SQL Server to Oracle I have faced the following
significant
> > (to
> > > > > > > me) irritations:
> > > > > > >
> > > > > > > 1. Empty strings turned into nulls. Empty strings per se are
not
> > > > > > > supported
> > > > > >
> > > > > > What's your point? Those going from Oracle to SQL Server can
> > complain
> > > > > > about the exact opposite.
> > > > > No they can't, those going from SQL Server to Oracle acquire the
> > > > > remarkable ability to distinguish between blank values and unknown
> > > > > values. They aren't required to use it. To me there is a big
> > > > > difference between someone not having a middle name and someone's
> > > > > middle name being unknown.
> > > > > > So they are different ... so what? Did you think
> > > > > This is not merely a difference. It is a bug in the way Oracle
> treats
> > > > > blank strings. I don't understand how you can defend it. I can't
> > > > > include a blank field in a primary key, ever, in Oracle. I can't
get
> > > > > sensible results from select * from table where field>"". You are
> > > > > defending the indefensible in your zeal to consider Oracle equal
or
> > > > > superior in every way.
> > > > > > Oracle was SQL Server with another company name on the CD? Do
you
> > > think
> > > > > > that everything Microsoft does is the "standard" to which
everyone
> > > else
> > > > > > must adhere?
> > > > > No, there are lots of things that I like better about Oracle, and
> lots
> > > > > of inadequacies in SQL Server.
> > > > > > Do you think that because you learned SQL Server first
> > > > > > somehow that makes it better?
> > > > > No. You are wrong. I didn't learn SQL Server first (though I did
> learn
> > > > > it before Oracle), and I don't think that it is better in every
> > > > > situation.
> > > > > > Good grief. If I followed that logic I'd be
> > > > > > whining about things being different than they were in Fortran
IV.
> > > > > You are fighting a straw man with this one. You've set me up as
> > > > > someone who thinks a particular way, and then you are knocking
that
> > > > > way of thinking over. Nothing to do with me.
> > > > >
> > > > > > >
> > > > > > > 2. Quoted identifiers are case sensitive, just what you
need...
> > > > > >
> > > > > > BFD
> > > > > You'll have to explain that one to me.
> > > > > >
> > > > > > >
> > > > > > > 3. No support for identity columns, instead requiring
> programmers
> > to
> > > > > > > create sequences and triggers to achieve effects that are
> trivial
> > in
> > > > > > > other databases
> > > > > >
> > > > > > Implementing a seqence takes 29 keystrokes. Count them ... 29 to
> > > create
> > > > > > the sequence and add it to an insert statement. This is a lot of
> > > whining
> > > > > > over 29 keystrokes.
> > > > > It takes a lot more than that to duplicate the full functionality
of
> > > > > an identity column. You need to study the difference more closely.
> > > > >
> > > > > > But go the other way ... I'd like to see you make your
> > > > > > identity column count backwards by every third number and reset
> > itself
> > > > > > after every 300 inserts.
> > > > > But I've never needed to do anything like that in 20 years. I just
> > > > > want column identities. Its a common requirement, sensibly
supplied
> by
> > > > > all other platforms that I've used.
> > > > >
> > > > > >
> > > > > > >
> > > > > > > 4. No option to ignore case on indexes or character-string
> > > comparison
> > > > > >
> > > > > > BFD. It is that bloody painful to type UPPER()?
> > > > > Of course not, but for it to be efficient you also have to create
> the
> > > > > upper case functional index too. These little details add to the
> pain
> > > > > of porting generic code. I need to go through all my code looking
> for
> > > > > places where this may be necessary.
> > > > > >
> > > > > > >
> > > > > > > 5. No support for comparison operators in arithmetic
expressions
> > > > > >
> > > > > > I'm not clear on what you mean by comparison operators. But I
> > strongly
> > > > > > suspect you are otally incorrect.
> > > > > How do you suspect that I'm incorrect when you don't know what I'm
> > > > > talking about? What do you think it can do? Look at the definition
> of
> > > > > expressions in the BN diagrams, and you'll see the difference
> between
> > > > > the expressions permitted for comparisons and the expressions
> > > > > permitted for arithmetic operations. (unless of course like CASE
> this
> > > > > has been added, in which case I apologise)
> > > > >
> > > > > >
> > > > > > >
> > > > > > > 6. No support for "Case" notation
> > > > > >
> > > > > > Wrong! I use CASE WHEN in much of what I write. Try RTFM.
> > > > > I stand corrected. I didn't notice that this had appeared in 9i,
due
> > > > > to my stupid inability to find the 9i manual pages on Oracle's
> website
> > > > > (thank you DStevens for the URL). I am also told that it works in
at
> > > > > least some versions of 8i despite not apparently appearing in the
> > > > > documentation. Curious.
> > > > > >
> > > > > > >
> > > > > > > 7. Bugs in the ODBC driver which (in my case) make CLOBS
> unusable,
> > > so
> > > > > > > I have to rely on varchar2(4000)
> > > > > >
> > > > > > Try an ODBC driver from another company.
> > > > > Which one do you recommend? It's Oracle's ODBC driver that I am
> using.
> > > > > I've got 2 CLOB bugs.
> > > > > >
> > > > > > > 8. Out-of-the-box retrieval of multi-record datasets at 1/40
the
> > > speed
> > > > > > > of SQL Server in my (highly optimised) ODBC usage (SQLBindCol,
> > > > > > > multi-record Chunked retrievals, etc)
> > > > > >
> > > > > > Then you don't have a clue what you are doing.
> > > > > That told me, I'm an idiot. So sorry for troubling you.
> > > > >
> > > > > I said, "Out of the box". My routines retrieve records at around
> > > > > 4000/second on 4 other platforms as they install out of the box
> > > > > (MSSQL,Sybase,MSAccess(!), and DB2), and around 100/second on
> Oracle.
> > > > > I am sure I am doing something very wrong, but despite posting
> > > > > detailed expositions of this problem in these forums no-one has
> > > > > managed to suggest what it might be beyond suggesting that I read
> > > > > books on tuning Oracle, in order to achieve the same performance
> that
> > > > > I get for free in 4 rival technologies.
> > > > > >
> > > > > > > I know that there are work-arounds for some of these
"features".
> > And
> > > I
> > > > > > > understand that Oracle is a great tool for those wanting to
> store
> > > vast
> > > > > > > quantities of data. A competitor to SQL Server for those just
> > > wanting
> > > > > > > to store a few million records and have a thousands of
> > transactions
> > > > > > > per day, with less than 50 permanent users, it ain't, IMHO.
> > > > > > >
> > > > > > > Ducks back into large concrete bunker and dons flameproof suit
> <G>
> > > > > > >
> > > > > > > Regards
> > > > > >
> > > > > > What your list clearly indicates to me is that you are totally
> > > clueless as
> > > > > > to what Oracle is.
> > > > > Its not a database then? Damn.
> > > > >
> > > > > > Let me give you the reverse complaint list. One for
> > > > > > which there is no work-around in SQL Server.
> > > > > >
> > > > > > 1. No multiversioning.
> > > > > Sadly in my ignorance I don't know what this is. In my other
> platforms
> > > > > I am presumably suffering because of its absence. You didn't name
> lots
> > > > > of other great features of Oracle, that I would love if it were
the
> > > > > *only* platform that I were trying to develop against, eg
> materialised
> > > > > views, the ability to use a sub query where a table-name goes in a
> > > > > query without building a view first, function-based indexes, and
> many
> > > > > others. Lots of great features don't make a product perfect
however,
> > > > > and my experience of porting to Oracle has been around 100 times
> more
> > > > > painful than the other ports of my product that I've done.
> > > > > >
> > > > > > Didn't need any others. That one is more than enough. When you
> > figure
> > > out
> > > > > > how to code around that in TransactSQL let Bill Gates know.
> > > > > Why should I, I don't like Microsoft particularly above Oracle.
> > > > > >
> > > > > > BTW: When the developer of the Jet engine left Microsoft and
> started
> > > his
> > > > > > own company two or three years back his database platform of
> choice
> > > was
> > > > > > Oracle on Linux. I guess he didn't know as much about it as you.
> > > > > >
> > > > > > Daniel Morgan
> > > > >
> > > > > Daniel, what's your problem? You seem very upset and defensive
that
> I
> > > > > don't love Oracle as much as you do. Do you not think that at
least
> > > > > some of my complaints could be legitimate criticisms?
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed Feb 20 2002 - 23:30:10 CST