Re: Another angle on this....

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:CB%c8.2090$2Q1.6177_at_rwcrnsc54...
> <Lots of reasons why rows/second can't be even hinted at snipped>
Yeah, it would just be nice to have a hint though.
> etc.
>
> I wasn't calling your application design crap;
You didn't. It was me who suggested that possibility.
> 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.
I just got an implication that you thought I might daft enough to retrieve stuff I don't need and I wanted to clarify. My app uses almost exactly the type of algorithm you describe above for instance when displaying scrollable entity-search dialogs or editable scrollable recordsheets of top-level entities, but not for the small c. <100 record recordsets which typically might be displayed as entity sub-information once you've selected something you're interested in. My 50k reference was rather misleading. the average is more like 20-50 records and the retrieval performance on these little sets is proportionately dreadful. But not the query performance, that's great, even when the query is super-complex the first array-set comes flying back at me very quickly. I'll give your driver optimisation suggestions in your other response some thought (those of them that I don't do already). They are all good practice regardless of platform and I've considered some of the ones that I don't do before, but have never felt the need to do them because performance is more than satisfactory on all my other db platforms.

Part of my problem is that in the production environment (as opposed to the test environment) I have to turn off ODBC array-fetches because of an obscure bug in the Oracle ODBC driver. Turning them back on improves performance by a factor of 5 so I may risk coding round the bug, which I've identified precisely, in the hope that it is as obscure as it seems and not systemic or indicative that the feature is not used by others.

Regards Received on Thu Feb 21 2002 - 04:24:35 CST