big update problem!

I have a mini .net application that I have created to make some pretty
complex transformationsdatabase.
All the code, .net datatables and logic etc takes 3 minutes to run on the
entire database. However, when I put in the update statements the same
process takes
3 1/2 hours!
So I know its not becuase I am using datatables or that my select statements
are poor etc etc etc. Its 100% the update statements.
Any clues? is this normal? are updates statements the most expensive in time?ADDED:
I am also able to create and populate a fairly large table in a matter of
minutes however updating to this one table is costing a lot of time.
Now granted this table in question is much larger then most tables in the DB
and larger then the tables I create but not 10 times larger!
Also, this table gets a lot of activity. Could our problem be the table
itself?
"Sean" wrote:
> I have a mini .net application that I have created to make some pretty
> complex transformationsdatabase.
> All the code, .net datatables and logic etc takes 3 minutes to run on the
> entire database. However, when I put in the update statements the same
> process takes
> 3 1/2 hours!
> So I know its not becuase I am using datatables or that my select statements
> are poor etc etc etc. Its 100% the update statements.
> Any clues? is this normal? are updates statements the most expensive in time?|||Yes, and it could be a hardware problem or the phase of the moon. With no
information about what the table looks like, what you are trying to do and
what else is running, there's no way to tell. This is like calling your
mechanic and saying it takes to long for me to get to work, what should I
do? The main difference between update statements and select statements is
they take update locks and write to disk. I would look at blocking waiting
to lock something that's in use first but that's just idle speculation.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...
> ADDED:
> I am also able to create and populate a fairly large table in a matter of
> minutes however updating to this one table is costing a lot of time.
> Now granted this table in question is much larger then most tables in the
> DB
> and larger then the tables I create but not 10 times larger!
> Also, this table gets a lot of activity. Could our problem be the table
> itself?
> "Sean" wrote:
>> I have a mini .net application that I have created to make some pretty
>> complex transformationsdatabase.
>> All the code, .net datatables and logic etc takes 3 minutes to run on the
>> entire database. However, when I put in the update statements the same
>> process takes
>> 3 1/2 hours!
>> So I know its not becuase I am using datatables or that my select
>> statements
>> are poor etc etc etc. Its 100% the update statements.
>> Any clues? is this normal? are updates statements the most expensive in
>> time?|||Well there is currently only 3 people accessing the server and only one (me)
accessing this particular database.
by my estimations it is taking about 1-3 seconds to update a row and in this
context each row has to be updated seperately.
Having said all that I think the functionality we are gaining might not be
worth the dev time which is a subject I will have for our meeting.
"Roger Wolter[MSFT]" wrote:
> Yes, and it could be a hardware problem or the phase of the moon. With no
> information about what the table looks like, what you are trying to do and
> what else is running, there's no way to tell. This is like calling your
> mechanic and saying it takes to long for me to get to work, what should I
> do? The main difference between update statements and select statements is
> they take update locks and write to disk. I would look at blocking waiting
> to lock something that's in use first but that's just idle speculation.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...
> > ADDED:
> >
> > I am also able to create and populate a fairly large table in a matter of
> > minutes however updating to this one table is costing a lot of time.
> >
> > Now granted this table in question is much larger then most tables in the
> > DB
> > and larger then the tables I create but not 10 times larger!
> >
> > Also, this table gets a lot of activity. Could our problem be the table
> > itself?
> >
> > "Sean" wrote:
> >
> >> I have a mini .net application that I have created to make some pretty
> >> complex transformationsdatabase.
> >> All the code, .net datatables and logic etc takes 3 minutes to run on the
> >> entire database. However, when I put in the update statements the same
> >> process takes
> >> 3 1/2 hours!
> >> So I know its not becuase I am using datatables or that my select
> >> statements
> >> are poor etc etc etc. Its 100% the update statements.
> >>
> >> Any clues? is this normal? are updates statements the most expensive in
> >> time?
>
>|||Sean wrote:
> Well there is currently only 3 people accessing the server and only one (me)
> accessing this particular database.
> by my estimations it is taking about 1-3 seconds to update a row and in this
> context each row has to be updated seperately.
> Having said all that I think the functionality we are gaining might not be
> worth the dev time which is a subject I will have for our meeting.
>
1-3 seconds to update a record? How many indexes are on this table? Is
there a clustered index? Is the column you're updating part of the
clustered index key? Are there update triggers on the table?|||Basically none of the above and its safe to assume no optimization strategies
have been done for this database.
We have a primary key on this table but that is it really.
I have studied some on clustering because I wanted to go from MCAD to MCSD
but I dropped that database study so in short I am database stupid outside of
basic T-SQL statements.
If you guys think clustering etc will help a lot I will look into it because
this table is getting big.
Thanks
"Tracy McKibben" wrote:
> Sean wrote:
> > Well there is currently only 3 people accessing the server and only one (me)
> > accessing this particular database.
> > by my estimations it is taking about 1-3 seconds to update a row and in this
> > context each row has to be updated seperately.
> >
> > Having said all that I think the functionality we are gaining might not be
> > worth the dev time which is a subject I will have for our meeting.
> >
> 1-3 seconds to update a record? How many indexes are on this table? Is
> there a clustered index? Is the column you're updating part of the
> clustered index key? Are there update triggers on the table?
>|||and correction, its only .4 a second for updates per record.
Its just that at the point of production we will have
157,000 records which if this was all we were doing it would be fine. but
this one item is taking up more then 50% of the time for our data conversion.
"Sean" wrote:
> Basically none of the above and its safe to assume no optimization strategies
> have been done for this database.
> We have a primary key on this table but that is it really.
> I have studied some on clustering because I wanted to go from MCAD to MCSD
> but I dropped that database study so in short I am database stupid outside of
> basic T-SQL statements.
> If you guys think clustering etc will help a lot I will look into it because
> this table is getting big.
> Thanks
>
>
>
> "Tracy McKibben" wrote:
> > Sean wrote:
> > > Well there is currently only 3 people accessing the server and only one (me)
> > > accessing this particular database.
> > > by my estimations it is taking about 1-3 seconds to update a row and in this
> > > context each row has to be updated seperately.
> > >
> > > Having said all that I think the functionality we are gaining might not be
> > > worth the dev time which is a subject I will have for our meeting.
> > >
> >
> > 1-3 seconds to update a record? How many indexes are on this table? Is
> > there a clustered index? Is the column you're updating part of the
> > clustered index key? Are there update triggers on the table?
> >