[dba-SQLServer] Constant Poll: Approaches

Hi Mark,

The db in question concerns stock-trading. A couple of hundred users
scattered around the world enter "ideas", which are instructions to buy or
sell some particular stock. These ideas go into the tblFiction table. Then a
complex chunk of code looks at the idea and may or may not act on it. For
example, there might be a limit on how much stock a person can buy, either
absolutely or within an industry or even of a particular company. Suppose a
person's limit on buying apples is 10 and she already owns 6. She submits an
idea to buy 3 and the idea goes into the tblFiction table. The process of
analyzing the idea begins and while it is still running, she submits another
idea to buy 6 more apples. So the first buy must process completely before
the second one gets analyzed. Now these "ideas" come in at any time of day,
so we have to poll the table in some way to see if there's any unprocessed
records to deal with, and if so then deal with them. This may or may not
result in an actual order to buy or sell some apples.

To answer your question, the only rows that I have to look at in tblFiction
are rows entered by the same trader, and depending on various flags on the
trader's record, I may have to go a a bit finer-grained (i.e. same trader,
same industry, maybe same stock). In most cases, though, it's just "same
trader".

Anyway, that stuff is all sort of beside the point I was asking, which is
what is the best (i.e. least resource-hog) approach to constantly poll  the
table.

On Thu, Jun 12, 2008 at 11:27 AM, Mark A Matte <markamatte at hotmail.com>
wrote:

>
> Arthur,
>
> Just to simplify the question ( for my benifit)...
>
> "For each new record in tblFiction, you want to analyze EVERY existing
> record in tblFiction, do something, and mark that record in tblFiction
> complete?
>
> Thanks,
>
> Mark A. Matte
>
>
> > Date: Wed, 11 Jun 2008 07:56:26 -0300
> > From: fuller.artful at gmail.com
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Constant Poll: Approaches
> >
> > Thanks for the input, Stuart. Currently I'm thinking that I should try
> what
> > I need using SSIS (small footprint, external process), but I'm still open
> on
> > the subject.
> >
> > Arthur
> >
> > On Tue, Jun 10, 2008 at 7:40 PM, Stuart McLachlan
> > wrote:
> >
> >> Triggers are probably out of the question because you need to process
> the
> >> rows
> >> sequentially.
> >>
> >> Procs with Infinite loops tend to be resource hogs and it's not so easy
> to
> >> program complex
> >> business logic in TSQL.
> >>
> >> I'd go with an external agent.
> >>
> >> I've got a couple of similar things running. Both use ODBC for the
> >> connection and a front
> >> end application or service which sits in the background and periodically
> >> polls the data base,
> >> applies the logic and updates where required.
> >>
> >> I generally write this sort of thing in PowerBasic with the SQL Tools
> ODBC
> >> package but any
> >> environment which can generate small footprint executables, can access
> >> ODBC, can
> >> "sleep" and can spawn threads will do it.
> >>
> >>
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
> _________________________________________________________________
> Instantly invite friends from Facebook and other social networks to join
> you on Windows Live™ Messenger.
> https://www.invite2messenger.net/im/?source=TXT_EML_WLH_InviteFriends
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>