My opinion is that partitioning your data into groups of 3000 records is
unnecessary complexity and a source of potential problems. Especially if
you are trying to walk through a dataset in exact increments. It's really
difficult to pin records in a SQL database to numerically referenced items.
Making the kind of assumptions about your data to make this work sounds to
me like hearing a motor with rods knocking. It may work at first, but at
some point its going to come apart or not have the muscle to do what you
need.

My advice is to write a service application that is queue based and it
simply works on whatever is in the (potentially open neded) queue(s). If
there are multiple tasks/processes/queues needing concurrent attention then
have a cursor object for each queue and then have the thread simply visit
each queue and do a single unit of work from each and just keep purring
through all the tasks at hand. It's possible too that you can customize the
priority servicing of the queues.

I have written single service applications that maintain real-time
synchronization of multiple replication targets and full text search
indexes. Doing this as I have allows the service to quietly sleep and only
wake up and do whatever needs to be done and then go back to sleep.

I don't know what your budget and timeframe for this project is, but short
of purchasing a major support instance to get me directly involved with this
aspect of your project to show you what I'm talking about, it may be hard
for me to coach you from the sidelines. I don't think I've ever released my
service app example code, but it's somthing I really should do at some
point.

Jason

> -----Original Message-----
> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]On
> Behalf Of aalexander
> Sent: Friday, December 08, 2006 1:42 AM
> To: IBObjects@yahoogroups.com
> Subject: RE: [IBO] Which is faster? Select First 3000 or
> MaxRows = 3000
> ?
>
>
> Hi Jason,
>
>
>
> We have written our own Replication application to enable our
> organisation
> to replicate from one Master to in infinite number of Targets
> (usually about
> 4).
>
>
>
> The Log Table is populated via Triggers on the master database and our
> application retrieves this data and replicates to the target
> databases via a
> range of stored procedures.
>
>
>
> We have Low and High priority replication tables which are
> set depending
> upon how important it is that the data is kept up to date. As
> a result we
> replicate a set number of records per replication in the
> example given this
> is 3000. The Sequence field is set the priority of the data
> records, and are
> generated by one of two generators.
>
>
>
> The SQL we currently use is:
>
>
>
> SELECT ToDB, Sequence, TableName, TableID, RepType, KeyValue, Status
>
> FROM TD_REPLICATIONLOG
>
> WHERE TODB = 0
>
> AND (STATUS is NULL) or (Status = "")
>
> ORDER BY Sequence
>
>
>
> And the Fields within the Log Table are:
>
> FromDB
>
> ToDB
>
> Sequence
>
> TableName
>
> TableID
>
> RepType
>
> KeyValue
>
> Status
>
>
>
>
>
> I set the MaxRows Value on the query at present.
>
>
>
> Thanks
>
>
>
> Andrew
>
>
>
>
>
>
>
>
>
>
>
> _____
>
> From: IBObjects@yahoogroups.com
> [mailto:IBObjects@yahoogroups.com] On Behalf
> Of Jason Wharton
> Sent: 07 December 2006 22:53
> To: IBObjects@yahoogroups.com
> Subject: RE: [IBO] Which is faster? Select First 3000 or
> MaxRows = 3000 ?
>
>
>
> > I have a table containing just 8 fields, however there may be upto
> > several million data records within this table.
> >
> > I need to bring the record set back in batches of approx
> 3000 records.
> >
> > I'm using an IB_Query and was hoping someone could advise if it is
> > quicker/more efficient to use the following SQL statement or set the
> > MaxRows property on the query.
> >
> > Select First 3000 * From MYTABLENAME
> > WHERE ......
>
> It highly depends on what you are doing in the where clause.
>
> Tell us more about what you are doing. So far I'm inclined to
> think you are
> not approaching things in the best way.
>
> Jason
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ______________________________________________________________
> _____________
> IB Objects - direct, complete, custom connectivity to
> Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ______________________________________________________________
> _____________
> http://www.ibobjects.com - your IBO community resource for
> Tech Info papers,
> keyword-searchable FAQ, community code contributions and more
> !
> Yahoo! Groups Links
>
>
>