November 17, 2004 at 11:26 pm
Feel free to send a copy of your response to mhaque4@comcast.net
1. Does SQL server do multitasking in query processing?
2. Can one assign priority on SQL queries?
Most Important Question
3. Is it possible to pause a query and start another query? Let the second query finish first and then resume the first query? How this can be done in SQL server, or in any data base?
Example:
Query 1 takes 2 hours to finish. Query 2 takes 5 minutes to finish.
Query 1 matches non-urgent job to nurse-availability.
Query 2 matches urgent jobs to nurse-availability. (High priority query)
This means both queries uses same nurse-availability table, and mark availability open slot to "filled" once the job is found for her.
Both query has different set of jobs to process.
Let's say: Query 1 is under process; query 2 came in.
I want query 1 to pause and query 2 to start.
Once query 2 is finished then query 1 should resume where it left off.
Obviously, once query 1 resume it work, it will find less availability slot to fill because some of the availability slot might have been filled by the query 2 (urgent) job.
Munzer
November 18, 2004 at 6:32 am
With reqrads to the queries themselves the answer is no.
However you could potentially write Query 1 with a while loop in it and each step thru the while check a flag table which Query 2 could set as marker on which would cause query 1 to stay in the loop (which you could add a "WAIT FOR" in so it is a slow loop), btu have query 1 write a different marker in the table as well to signal back to query 2 all is clear.
Now query 2 on seeing the marker from 1 set could run then it's process and update the flag to all clear for query 1 to continue.
Query 1 then in turn sets it's flag to running also and continues until complete.
Or in query 1 do a cursor or loop which during the process has the code for query 2 in it and montiors whatever would be the trigger for query 2 to need to occurr, and you just continue based on your rules thru the cursor or loop setting those thatmeet query 2 rules to where they need to be and those that don't continue thru query 1.
But overall this kind of logic probably would be better for a C or VB application which could handle live singnalling unlike queries within sql server.
Lastly I bet you could even write Query 1 to occurr much faster in the realm of a few mintues and thus be out of the way for 2 but without all the details of you rules and code I cannot be certain that is what you would even want.
November 18, 2004 at 1:40 pm
Antares686:
I want to thank you for the excellent and quick response. I think, you are correct. I was already leaning toward what you wrote.
I guess problem with data bases is that, dbs are not very good for truely distributed db, distributed query, dynamic data, and near real time processing for web based millions of custemers interaction, where what one customers does affects all others.
Munzer
November 19, 2004 at 6:09 am
That truely does of factors of depends. But I would say that many times you will need to add a controller somewhere to ensure transactions can get priority as needed in the middle tier when dealing with your situation.
November 19, 2004 at 2:41 pm
Antares686:
I have another question.
Is it possible to update or replicate by means of broadcasting in sql server 2000? My servers are in the same LAN. Objective is to reduce the network traffic. Instead of replicating to one server at a time I want to update all servers at once.
As I understand, at Eithernet level, IP Address, aaa.bbb.ccc.255 is a broadcast message to all servers. Can SQL server use this feature to broadcast the updates?
Munzer
November 19, 2004 at 4:00 pm
No you technically cannot. However you could build a middle tier service to update to multiple locations at one time with transactions so you can recover from a failure. Or I have seen a few products that say they can do this for you. That is nothing inside SQL that will directly do this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply