Re: how does trigger works?
From: joe (pearl_77_at_hotmail.com)
Date: 04/28/04
- Next message: Don Peterson: "Re: Installing mdf/ldf database files from C#"
- Previous message: Anith Sen: "Re: inserting SP output into table"
- In reply to: Louis Davidson: "Re: how does trigger works?"
- Next in thread: Louis Davidson: "Re: how does trigger works?"
- Reply: Louis Davidson: "Re: how does trigger works?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 28 Apr 2004 15:19:02 -0400
store procedure would be the best choice.
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:uMxZQATLEHA.3852@TK2MSFTNGP10.phx.gbl...
> 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! > > > > > > > > > > > > > > > > > > > >
- Next message: Don Peterson: "Re: Installing mdf/ldf database files from C#"
- Previous message: Anith Sen: "Re: inserting SP output into table"
- In reply to: Louis Davidson: "Re: how does trigger works?"
- Next in thread: Louis Davidson: "Re: how does trigger works?"
- Reply: Louis Davidson: "Re: how does trigger works?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
