Solved

Looping through records returned in SQL server 2005

Posted on 2007-03-17
5
191 Views
Last Modified: 2010-03-19
Dear Experts,

Just as I thought everything was going really well, I ran into a problem when developing my application...  This part of the application deals with telemarketers and they are constantly updating and retrieving new contact's to call.  Who they call is setup through what we call a Queue.  Here is the meat of the problem:

Users are assigned to queues and each queue for the user to call has a priority.  We want the queues with 1 to be called first, and the higher number to be called last.  I don't want to try to Preload the buffer for the highest queue every time the telemarketer pushes the button to get a record if there are no contacts which meet the queue's criteria.  For this reason, the columns [LastQueryRun], [CurrentPoolSize] and [CurrentBufferSize] come into play.  Each time [AppMarketing].INS_PreloadQueueBuffer is called, it will update [LastQueryRun] and [CurrentPoolSize].  If the poolsize is 0 and the last query ran was less than 45 minutes ago, we should skip this queue.  However, if the poolsize is 0 (or any number for that matter) and the lastqueryrun field was MORE than 45 minutes ago, we should execute [AppMarketing].INS_PreloadQueueBuffer.  This will update LastQueryRun and CurrentPoolSize.  So the problem is, when that happens, we don't know what the values are...we don't know if we should just return a contactID for this Queue or go to the next.  How can we go BACK on a select?!  This isn't a dataset or anything!  What should happen is that we pull the [CurrentBufferSize] field and if it's <> 0 then we return a contactID from it...  If not, we do the same thing to the next queue.

[AppMarketing].QueuesBuffer has a trigger which counts it's records for the FK_Queue_ID which was inserted or deleted and updates that count in [AppMarketing].Queues.CurrentBufferSize.  This helps us to always have an accurate count of what is in the Buffer to know whether or not we should preload (populate) the buffer by calling [AppMarketing].INS_PreloadQueueBuffer

At this point, you are probably saying to yourself 'why does he have a QueuesBuffer table anyway?'  Well, the reason I added this is because I want a quick pool of ContactID's to grab from when a telemarketer needs someone to call.  Coming up with this information has quite a bit of overhead as the stored procedure [AppMarketing].INS_PreloadBuffer will have to search through anywhere between 3 to 15 tables depending on the criteria the administrative user has specified in the Queue.  

In summary, I already have the most of this done, all I can't do is figure out how to loop through the assigned Queues for the selected user...  I have started over on this portion of this stored procedure, but the big stored procedure [AppMarketing].INS_PreloadQueueBuffer is already completed!  yay!

The only parameter this stored procedure (the one that loops through the queues) should accept is @UserID.  it should determine the @QueueID parameter based on the QueueID of the current record.

Please see the screen shot at http://75.33.104.230/public/db_queues.png for a clear picture of this.

Thank you 21 times,

StankStank
0
Comment
Question by:stankstank
  • 4
5 Comments
 

Author Comment

by:stankstank
ID: 18739666
By the way, a user will never be assigned to more than four or five queues at a time...  When a queue has no more contacts, they should go to the queue with the next highest priority.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18739762
sorry its not clear why you "loop" through anything for the user...

surely you just select the highest priority item available for them each time?

you only have 1 queue table?
with the queues identified on that...?

you need to ensure that your transactions are of an appropriate length...
i'd have expected you to have an indcator on the queue to lock/ignore it when it was being re-built...


can you give some example data to illustrate the problem ...
0
 

Author Comment

by:stankstank
ID: 18741172
>> surely you just select the highest priority item available for them each time? <<
I am assuming you mean the highest priority queue.  If so, the yes, I want to choose the highest priority queue, but I don't want it being ran every single time the user calls for a record if there are no more records available in this queue.

>> you only have 1 queue table? with the queues identified on that...? <<
I don't understand this question - can you re-ask it?

>> can you give some example data to illustrate the problem ... <<
Absolutely!  Let me give it a shot here:

----------------------------------------------------------------
Queue 1 was added and has a pool of 375 contacts (LastQueryRun < 45 Minutes ago)
Queue 2 was added and has a pool of 0 contacts (LastQueryRun > 45 minutes ago)
Queue 3 was added and has a pool of 217 contacts (LastQueryRun < 45 Minutes ago)
----------------------------------------------------------------

User1 was assigned to the following queues/priorities
----------------------------------------------------------------
Queue 2/Priority 1
Queue 3/Priority 2

When User1 logs into the application, the procedure will check and see if there are any records in Queue2.  It finds that the LastQueryRun date is greater than 45 minutes ago.  Because of this, it executes [AppMarketing].INS_PreloadQueueBuffer @QueueID which preloads the buffer.  When this procedure is called, it also updates the currentpoolsize column and the LastQueryRun column.  

Because of this, I need to check the SAME queueID and see if there is a record to pull.  If it is STILL zero, then we need to go to the next QueueID which the user is assigned and do the same thing.  

That's where I am having the problem...  Calling [AppMarketing].INS_PreloadQueueBuffer, then returning to the Queue record to see what the updated fields are.




>> i'd have expected you to have an indcator on the queue to lock/ignore it when it was being re-built... <<
That is an excellent idea, and I can put it in.  Maybe a bit column called IsLocked or something.
0
 

Author Comment

by:stankstank
ID: 18742310
I think I have made this question unnecessarily complicated.  I am going to close it, award you the points and open a new one.  I will post the link to the new question - I would appreciate your help with it.

Thanks,

StankStank
0
 

Author Comment

by:stankstank
ID: 18746714
Hey LowFatSpread - I have added the new question which I think is more to the point... I basically cut out all of the things which weren't necessary.  Please take a look at my question - I would love to hear your input!

I am awarding the points to this question to you - you have helped me on past questions and I appreciate it.

New question: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22457492.html
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now