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

Almost all of our searches are a combination of '=' and 'LIKE' searches:

SELECT * FROM DATABASE WHERE STATE = 'TX' AND STATUS = 'ACTIVE' AND COUNTY 
LIKE  'HF%' AND LNAM LIKE '%SMITH%'.

First we return a count of all records matching the search criteria, then we 
display 5 at a time in a browser window with next,previous,first and last 
page buttons.

Tom

"Jim Franz" <franz400@xxxxxxxxxxxx> wrote in 
message news:00d101c56b9e$b1bebf40$7706be41@xxxxxxxx
> 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.
>>
>
> -- 
> 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.
>
>