Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlg_1
Starting Member
7 Posts |
 Posted - 2002-03-13 : 16:42:33
|
I'm still new to the microsoft side of things so I need much help!!!I'm co-developing a system where real-time information is dumped into a SQL Server every 3 seconds (about 4-10 sets of dumbs ranging from 4 rows up to 800 rows each). also every 3 seconds this dump first deletes the previous rows...so the information is "real-time".anyhow, I need to have a web interface that will create triggers (based on business rules) that will fire when certain columns reach a certain number or pass a threshold.at that point the SQL Server should send a "message" to the VB app which will then digest the message and do a couple of things.How do I accomplish this?at this point the message from the triggers can simply be a 1 or 0. but I would like to later increase the complexity. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 16:55:20
|
| Well, just to clarify the term "triggers": in SQL Server, a trigger is a piece of code that runs whenever an INSERT, UPDATE, or DELETE operation occurs. You can have separate triggers for each operation. Since you are anticipating an INSERT and DELETE operation every 3 seconds, this is gonna be a pretty busy table! Depending on what you need these triggers to do, it may not be a good idea to have SQL Server triggers handle it.As far as a "web interface" to CREATE triggers, I think it would be better that the trigger code either be kept entirely on the web side, or that the triggers are created on the SQL Server tables, and they fire normally. It'll definitely have a negative impact on performance if you actually have an ASP app manipulating table triggers. The other concern is having SQL Server push a message out to your VB app; it can be done, but in all likelihood the overhead involved will become pretty high, and it can block SQL Server from continuing execution, while it waits for acknowledgement from your VB app.Can you give us more detail on what your app needs to do? Be as specific as you can (if you've got code or table structures, post them, all of it), err on the side of giving too much information. Define the business rules, and describe the triggers/actions that must enforce them. It's hard to tell from the outline you gave which technique would be best suited for this. How many people will be accessing the web site? Will each user request require database access? What kind of hardware are you running this on? (hopefully you are maintaining separate web and database servers) Where does the real-time information come from? |
 |
|
|
dlg_1
Starting Member
7 Posts |
Posted - 2002-03-13 : 17:02:53
|
| == real-time information: data feed seperate from the SQL Server, this is a vb app that processes this information and then inserts the data into SQL Server every 3 seconds or so.== web site: this will be for the business units to maintain their business rules and adapt to their new environment. This will be an entirely new system and will require frequent usage at first. There will be no more than maybe 3 people on this at one time. There will be security implemented via asp/sql server.== trigger: The triggers will not be firing every 3 seconds. lets say that the biz unit wants to be alerted with a 1 when column A in Table C reaches a count(*) of x >= 234 and would like a 0 alert on the same column when the count(*) drops x <= 75. so in reality triggers may not fire any more than every 20min - 3 hours.== vb app: this vb app will receive a message from sql server and depending on 1 or 0 will perform some tasks. anything else?Thanks for your help! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 17:46:54
|
Yeah. Uh, don't take this the wrong way, but that's not any more information than what you originally provided; it doesn't DESCRIBE what you want to do.For instance, WHAT kind of data is being fed in "real time"...stock quotes, input from a sensor, streaming audio/video, radio astronomy? WHERE exactly does it come from?When you say "this will be for business units...to adapt to their new environment", how does their environment change? Do they work in an office one day, then on the road the next, then overseas the day after, then a whorehouse? Does the weather affect how they use the application? Is this part of the Homeland Security Office, and they need to change how they work depending on the kind of threats they receive? WHO THE HELL ARE THESE PEOPLE ANYWAY???I'm being facetious, but it's for a good reason...I have no idea what you mean by these terms you're using, and unless you tell me otherwise these interpretations could be correct.The terminology "...the biz unit wants to be alerted with a 1 when column A in Table C reaches a count(*) of x >= 234..." is meaningless in a database. Columns don't have a "count", only rows can be counted. A column can have a VALUE that exceeds 234. Is that what you mean? Or do you mean "I need to know if there are more than 234 rows in this table"?Do you have any tables designed yet? Do you have any code for this? Do you mind if I ask what your programming/development background is? Believe me, it's vital that we have some idea where you are in the project before we can make suggestions. There's no point in someone giving you an entire solution if you're stuck with 100,000 lines of legacy code that you CAN'T get rid of and WON'T be compatible with it. Please don't simplify or generalize, it ALWAYS leaves out crucial information.When I asked to err on the side of TOO MUCH information, I MEANT IT, I was expecting "Well, the vice president, who is sleeping with his secretary, well, he wants a web-based golf reservation system, one that even his caddy can use (even though his caddy is a dork, he can't even tie his shoes much less carry a golf bag) and of course he needs to have his secretary use it too, because sometimes they go off for a nooner on the golf course, then they get arrested for lewd behavior, and I have to bail them out..."  I'm not kidding though, I'd much prefer to have an excess of information than not enough.PLEASE PLEASE PLEASE if you post again, put in everything, including the kitchen sink, don't skimp on info. I DO want to help you, but I need these questions answered. |
|
|
|
dlg_1
Starting Member
7 Posts |
Posted - 2002-03-13 : 17:56:20
|
ay ya yay. :)understood.okay.I have an Automated Call Center that handles incoming phone calls. This has a real-time datafeed that I can access using a dll. APP 1 -> this app builds a bridge from the call center and dumps data into sql server.SQL Server -> I need SQL server to fire a trigger when the number of rows in the call_queue table reach 250. This trigger alert needs to interface with a vb app that only needs one of two messages: 1 or 0 ... true or false.the vb app then does other processing...but only needs to know true or false.i have a stronger java/oracle/jsp/servlet background and therefore am jumping into this arena not knowing how to do several things that could be done in the other environment.any more info?thanks, |
|
|
|
dlg_1
Starting Member
7 Posts |
Posted - 2002-03-13 : 17:57:18
|
| one table. with hundreds of "fresh" rows every 3 seconds. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 22:05:25
|
| I think it might be better to keep the entire process separate from SQL Server. Here's the idea:1. DLL gets data from call center;2. INSERT data into SQL Server table;3. Check the # of rows in the table after the INSERT is done;4. If greater than 250, call VB app with a 1The reason is that this will avoid the overhead of the trigger in SQL Server. Normally I wouldn't be too concerned, but making COM calls is not something I feel belongs in a SQL trigger, especially with this level of activity. It will be a lot easier to manage it asynchronously from a VB app, and it'll be nicely encapsulated. In fact, you could put this into it's own COM object and recycle it in other apps (VB, ASP, etc.)The thing is, if you are inserting the rows in batches, you may come across a situation where the table has 249 rows, then another 250 get inserted. Well, it won't fire the trigger twice, because each INSERT fires the trigger only once no matter how many rows are inserted (nor will the trigger fire on the 250th row, it will fire when the entire operation completes). There's no way for you to know this from the SQL Server side until after it's done. You *might* be able to check the table and the number of new rows on the VB side before the INSERT, but it'll be difficult.I don't know if that's an issue for you, but you should be aware of it.HTH |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-03-13 : 22:39:36
|
| MSMQ ... Microsoft Message Queue would be put to good use in an environment like this... instead of the call center dumping constantly it should pack up a nice say XML document and place it into the receiving queue and have a COM object read the queue and insert it into the database. Then the database can have a trigger... and the call center isn't reliant upon SQL Server doing the delete and the insert and the trigger operation before it succeeds. Once the queue consume delivers the data into SQL Server ... SQL Server could either run a job or have a trigger process the events and see if CLIENT_X need notified. If CLIENT_X needs notified insert a row into another AlertQueue table say with the CLIENT_X info you need to alert the client with. Have a job run every 2-5 seconds that looks at the queue and starts processing them... place them into another message queue and have the client check the message queue every so often for alerts... you could probably just have the trigger insert into the queue but it would be far more scalable if you just inserted into AlertQueue and have another thread process those entries however you like. The good news is it can be done. The bad news is that you can to make sure you handle the MSMQ's properly, for instance what do you want to do if the MSMQ for incoming data from the call center gets backed up? Do you want to just skip through all the message until you have the most recent message and ignore the others and not fire alerts for those or do you want it to go along processing alerts for each one. Whatever route you choose, good luck. |
|
|
|
dlg_1
Starting Member
7 Posts |
Posted - 2002-03-14 : 08:17:29
|
| wonderful responses, each with it's own advantage. I like the idea about the alert queue in that it would keep the other table from being heavily used. I will think about that.The reason that I want a trigger to be fired is that 8000 or eventually 15,000 users will be at risk here. And I don't want their vb app to query the SQL Server every 3 - 12 seconds or so. --> network traffic, heavy load, etc.I would still like the sql server to at least alert a 3rd vb app, maybe a listener? and this "listener" would then alert the bigger vb app. But I would still like to bridge the two apps.Unfortunately, I don't have MSMQ at my disposition.Any other ideas? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-14 : 08:50:35
|
In thinking about the MSMQ solution, which IMHO is much better, I'm wondering what the database is even needed for? This is a little fuzzy (not your fault at all, I didn't ask) What concerns are there besides traffic? It's a stumper for me considering that you'll be throwing data into a table and (from what I've gathered) deleting it in relatively short order (pour water in a bucket, when it's full, empty it and tell someone?) I'm not seeing any practical benefit...not saying there isn't any, but I need more background (what a shocker!)  Thanks. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-03-14 : 12:52:24
|
yes that is a good point, I can see maybe 3 clients getting this done with minor problems but if 10k users are going to be alerted based on the data ... by the time they go to get the data it could already be gone! Maybe having an insertion ID in the table that you can use for each INSERT chunk ... for instance .. say you have a column called DataSet, for each insert done for the data why not just say DataSet = 1 ... then for the next set of rows DataSet = 2 ... etc... that way you don't have to delete the records which in my opinion would create a huge mess. This way you can process each chunk and maybe delete the old chunks later on. BIGINT column should handle quite a few chunks and maybe (i'm sure your call center can't have incoming calls 24/7) Find some time to delete all entries and start at 1 again. but any way ... say you have an insert every 3 seconds ... that means you can have about 6,148,914,691,236,517,205 sets of data ... enough for a life time ... :-) then you could also group on this column to get total data sets ... On MSMQ, it is free. Why isn't it at your disposal if you are using VB? I assume you are on NT? Would it be safe to assume that this represents the structure you are trying to get to?| CALL CENTER | ---> | MESSAGE QUEUE | ---> | DATABASE | ---> | ALERT JOB | ---> | CLIENT | | CALL CENTER ( CC VB DLL --> MESSAGE QUEUE ) || MESSAGE QUEUE ( DB VB DLL ---> DATABASE ) || DATABASE ( CallData TABLE ---> INSERT TRIGGER ---> AlertQueue TABLE ) || ALERT JOB ( ALERT VB DLL ---> CLIENT ) || CLIENT ( VB APPLICATION ) | The one part you may want to consider ... you said the trigger will determine if the client is alerted? What are the requirements for determine if the client is alerted? Is it just the message count? If so you may not want to make each CLIENTs trigger an ACTUAL database trigger. You would want to have the logic in the trigger so that is could process all the ALERT TRIGGERS very VERY fast so as not to hold up execution of the insert. having a few triggers is ok but if your talking eventually 10k! that may kill it. If the logic is always (row count) (operand) (value) where [row count] is the number of rows inserted, [operand] is >=, <=, <, >, =, !=, and [value] is the value they want to compare to then that's easy. Just have the trigger look at all alerts and find the ones that need alerted... Table ClientAlerts - (Client Information? IP?) - Operand - Value Table AlertQueue - (Client Information? IP?) - DataSet Table CallData - (Call Data Information?) - DataSet Insert Trigger for CallData Get number of rows from inserted Get DataSet number from inserted Select all Client Information from ClientAlerts where (NumberofRows) (Operand) (Value) Inserted selected Client Information into AlertQueue with ClientInformation and DataSet that's pretty generic eh? well just trying to get a feel for what you are actually trying to do since it seems pretty interesting.... Maybe using a case statement like this would be efficient ....INSERT INTO AlertQueue (ClientInformation, DataSet) SELECT ClientInformation, @DataSet -- The current dataset from inserted FROM ClientAlerts WHERE 1 = CASE Operand WHEN '=' THEN CASE WHEN @Rows = Value THEN 1 ELSE 0 END WHEN '!=' THEN CASE WHEN @Rows <> Value THEN 1 ELSE 0 END WHEN '>' THEN CASE WHEN @Rows > Value THEN 1 ELSE 0 END WHEN '<' THEN CASE WHEN @Rows < Value THEN 1 ELSE 0 END WHEN '>=' THEN CASE WHEN @Rows >= Value THEN 1 ELSE 0 END WHEN '<=' THEN CASE WHEN @Rows <= Value THEN 1 ELSE 0 END ELSE 0 -- UNKNOWN OPERAND END I'm pretty sure something like this would work well and pretty fast I would hope so that your application could run right a long without any bottle necks... Ok I was just bored and decided to do all this :-p<edit>One thing I forget to ask is how are you going to handle sending the alerts to the clients? If the server has to alert the clients you are going to suffer a severe bottle neck. Specially with 10k clients. Say that even 1000 clients need alerted by the time the 1000th client gets alerted it could be too late or something... you could multithread the alerts, but with VB this is not a good idea, you'd have to use c/c++ or java, java could handle it I do believe. Spawn 255 threads that all work off a queue ... each thread gets the next item and that item is locked until the thread says it's clean or just split the total number of items into arrays and send each array to each thread and let all the threads do their thing. As you can see this part is the most important part of the application, all the others are pretty simple it seems. If you mess up the whole ALERT server then you are in for it. Specially at the significant rate you expect data to be processed.</edit>Edited by - onamuji on 03/14/2002 12:58:32 |
|
|
|
|
|
|
|
|
Interacting with another APP using Triggers