Re: how does trigger works?

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 09:49:50 -0500

The problem is that we don't know enough about what you are doing. A few
possibilites:

1. Improve your validation process and use a trigger. It is likely that
you can improve upon a 2-3 second time by indexing, or other strategies.
Without knowing the details, cannot even guess.

2. Multi-thread your inserts. If the different inserts will not block one
another in doing the checks, and you are not hitting the upper bounds of
your machines capacities (disk, cpu, etc,) and the inserts do not have to
be be done sequentially, then split your inserts into multiple queues and
start inserting rows like that.

3. The suggestion that Daniel P makes is valid for sure. Insert the data
into a different table, clean it, and then insert it.

How are you doing it now? You say it goes for 10 minutes an hour 5 items
per second. So we have 10 (minutes/hour) * 60 (seconds/minute) * 5
(inserts/second) = 3000 (inserts/hour).

If each takes 3 seconds, that is 9000 seconds. There are 60
(seconds/minute) * 60 (minutes/hour) = 3600 seconds per hour, so you are
going to have a very hard time.

You might be able to shave some time off if you can do suggestion 3, but
unless it works out really nice, it is still going to take some doing to get
it done. Multi-threading it may help, as long as there is no blocking
between the processes. A lot of the question of how to do it depends on how
this table is used, and how this will affect other users. Like will any of
these block other users, tax the server such that others have to wait, etc.

There are probably others, but I would start with improving the validation
performance. Since any insert into this table will have to go through the
same thing, it can only improve things. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Guy Brom" <guy_brom@yahoo.com> wrote in message
news:O9GdgvSLEHA.2456@TK2MSFTNGP12.phx.gbl...
> I see..
> any other suggestion then?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:%23zKOfmSLEHA.1484@tk2msftngp13.phx.gbl...
> > Triggers are not queued, as they might have ROLLBACK in them and the
> calling code need to be notified with
> > errors if a ROLLBACK is performed. What will happen is that you will get
> (potentially) regular blocking during
> > your insert time period.
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Guy Brom" <guy_brom@yahoo.com> wrote in message
> news:utvG0YSLEHA.3892@TK2MSFTNGP11.phx.gbl...
> > > Hi there,
> > >
> > > I have an application that INSERT new records to a database at a rate
of
> 5
> > > items per second (for a period of 10 minutes each hour).
> > >
> > > The new data arriving to the database should be verified and
> cross-checked
> > > with a different table (a 2-3 seconds process for each new record).
> > >
> > > Is it wise to use a TRIGGER (for insert) to perform this method? will
> each
> > > INSERT be delayed by 2-3 seconds because of the trigger? or does the
> trigger
> > > gets sent and queued automatically to a different "thread"?
> > >
> > > Thanks!
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: How to limited number of rows in a table?
    ... If this is a log table, database blocking should ... Pro SQL Server 2000 Database Design ... > I'll bend on the trigger, but I think a nightly job may be too infrequent ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger: To fill another Database with using Stored Procedures of the other Database
    ... I fill from Database A with triggers Database B, ... add additional information accordingly, this Stored Procedures is ... trigger does not work anymore, even if I do a try catch over the whole ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • call to xp_cmdshell from trigger problem
    ... To any SQL Server MVP: ... database that is used to alert and calculate required amounts of assistance ... At first it failed because the trigger called the EXE but the tables were ...
    (microsoft.public.sqlserver.server)
  • Re: implement Save button
    ... When we use a disconnected recordset, i.e. we do not use the databinding. ... you want to make a few steps in database side ... For SQL server we can use the Trigger, which is a special kind of stored ...
    (microsoft.public.vb.enterprise)
  • Re: Error 2950 - Reserved Error on form refresh/requery
    ... and I can't leave the client with an unusable database while I try to ... The requery is on the form (so the data displayed in the sub-forms refreshes ... The triggering event is a listbox after update that does 2 things only: ... can trigger others, and so the problem may occur only when a specific ...
    (microsoft.public.access.formscoding)