long insert time

I have a table with about half a million records.
Select queries run fine.
This test query I ran in QA took over 1 minute to run.
insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
What are some possible causes?Two possible causes are blocking and insert trigger(s) on the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:OpY%23$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Two possible causes are blocking and insert trigger(s) on the table.
>
I don't have any triggers. Funny thing, I re-ran the query about 5
times. The first 4 times it took over a minute to perform. The 5th
time, it took 2 seconds. I performed it again and it only took 2 seconds.
Any idea for the change in performance?|||Blocking is still a possible reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > Two possible causes are blocking and insert trigger(s) on the table.
> >
> I don't have any triggers. Funny thing, I re-ran the query about 5
> times. The first 4 times it took over a minute to perform. The 5th
> time, it took 2 seconds. I performed it again and it only took 2 seconds.
> Any idea for the change in performance?
>|||Another reason could be an autogrow kicking in...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u5gzVvYQEHA.3596@.tk2msftngp13.phx.gbl...
> Blocking is still a possible reason.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Won Lee" <noemail@.nospam.com> wrote in message news:%237B4JrYQEHA.1348@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi wrote:
> >
> > > Two possible causes are blocking and insert trigger(s) on the table.
> > >
> >
> > I don't have any triggers. Funny thing, I re-ran the query about 5
> > times. The first 4 times it took over a minute to perform. The 5th
> > time, it took 2 seconds. I performed it again and it only took 2 seconds.
> >
> > Any idea for the change in performance?
> >
>|||Hi,
As Tiber pointed out this execution time diffence while inserting a record
will be due to Blocking. So to see the blocking:-
While you execute the insert statement, if it is delaying open a new query
analyzer window and execute SP_WHO command.
In the result look for the column BLK, In the normal case all the values
will be "0". If there is a block then a SPID will displayed in BLK field.
If blocked understand the statement that user( SPID - blocked ) is doing by
using the below command:-
DBCC INPUTBUFFER(SPID) -- Replace the SPID with the SPID blocked.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OpY#$dYQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I have a table with about half a million records.
> Select queries run fine.
> This test query I ran in QA took over 1 minute to run.
> insert into tradeData values (0, '5/23/04', '20:00:00', 0, 0.00)
> What are some possible causes?
>|||Tibor Karaszi wrote:
> Another reason could be an autogrow kicking in...
>
OK. Looking up Blocking in Books Online.
Next time I have this issue, I will be write down the DB size to see if
the autogrow is part of the problem. Thanks.sql