Re: How do I connect from iSeries to MS SQL Server 2000? -- MIDRANGE-L

Could you be more specific about how current search is working?
When you say "over tens of millions..."
    are you doing a SELECT with operators = or <>
   or are you doing SELECT with operator "like"
There is a huge difference in performance.
Are you returning a set of a few records or perhaps thousands.
Are you returning page at a time or all at once.
I do have a web search over 5 - 7 million records, with all of the above.
The exact match operators are very fast, even with 20 simultaneous searches
on a low end i5 with 2 gig memory. It was getting slow on the previous S10
at 73 cpw.
When someone does a "like" search, I do require at least one  "=" selection
and
 first in statement - Select * from ABC where County='BR' and NAME like
'%FRED %'
instead of Select * from ABC where NAME like '%FRED %'
(this site has millions of construction jobs in Florida).
When someone does a very generic search, we return the first 1000 with a msg
to be more specific.   This is all in SQLRPGLE.
btw - the database engine at v4r3 much slower than current v5r3. many
improvements..
I think the i5 cost them $40k  with enough disk to be at 40% utilization.
I did spend some time analizing access paths and it paid off.
jim


----- Original Message ----- 
From: "Tom" <tomh5480@xxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, June 07, 2005 3:40 PM
Subject: Re: How do I connect from iSeries to MS SQL Server 2000?


> Sure, with a small number of searches we have no problems at all. Or with
> lots of searches, done serially.  We get a big performance hit when we
have
> lots of simultaneous searches over tens of millions of records.
>
> So will our v4r3 170. But not for the load that we put on our 270 (maxed
out
> on disk, RAM, processor).
>
> Our database is designed such that it is a  pretty straight-forward
process
> of segmenting it.  The queries we get are pretty segmented too, with just
a
> handful of users hitting any particular subset of the database at the same
> time.  We've written lots of logicals, so that the queries are hitting
only
> that portion of the db that they're interested in.
>
> Our plan is to increase the database by A LOT over the next couple of
years,
> A LOT more over the next five years. We will also increase our user base
> accordingly (and the searches as well).  We're looking at as many
> alternatives as we can to handle the search load, we're not married to
> anything at this point.  Whatever we choose, it must be scaleable -
without
> requiring us to sell everything to make it happen.  And it must be robust;
> if a particular remote server goes down, we should be able to recover from
> that failure quickly.
>
> 3rd party connectors, JDBC, bigger-faster iron - all options are open.  If
> we go to Windows, believe me - the design is such that each PC will only
> have a subset of the data to search (a few hundred thousand records or
so).
>
> Tom
>
>
> "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> wrote in message news:000601c56b85$b913eb80$1901010a@xxxxxxxxx
> > And what alternative platform are you going to get acceptable
> > performance from?  Most PC-based databases barely handle a single query
> > on a file of that size.  Are you thinking of running this on
> > Windows?!?!?!  Perhaps an industrial-strength Unix database, but that's
> > not going to be any cheaper.
> >
> > Also, I don't understand the high price of the iSeries solution.  I can
> > do searches on millions of records on my little model 270.  Why do you
> > think you need $1.5M dollars worth of hardware?  Are you saying a model
> > 870 with 5 processors and 7700 CPW isn't enough????  That processor is a
> > base price under $300K.  Load up disk and memory, and you might break
> > $500K.  And you can upgrade to 11500 CPW for another $100K.
> >
> > I think you might want to revisit your figures.
> >
> > Joe
> >
> >> From: Tom
> >>
> >> Our problem is performance; we're having scores of simultaneous
> > searches
> >> on
> >> 10s of millions of db records.  An upgrade to an iSeries system
> > capable of
> >> handling our projected needs over the next 3 years (hundres of
> >> simultaneous
> >> searches on 100s of millions of records) will cost at about $500K,
> > another
> >> $1M+ after that.  My gawd.
> >
> > -- 
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email:
> > MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>