1

I have a few constraints (to default the value of the column) on a table that is being updated. The update is really slow, and I was wondering if it could be the constraints fault?

The constraint in question is: 

ALTER TABLE [dbo].[OrderCustomers] ADD  CONSTRAINT [DF_OrderCustomers_AmountTotal]  DEFAULT ((0.00)) FOR [AmountTotal]

The update statement is just changing a few columns one of which is the column in the Constraint above and the a few other columns that don't have FKs on them.

FYI: I disabled all triggers to isolate the problem.

John Egbert
  • 5,496
  • 8
  • 32
  • 44
  • 1
    Can you provide some details on the definition of the constraints as well as the update statement itself. – Joe Stefanelli Sep 27 '11 at 18:20
  • 1
    SQL Performance is like the weather - there are about a billion factors involved and it's impossible to give any insight without a lot of information. You are basically asking "Is it hot here because of the wind?" – JNK Sep 27 '11 at 18:20
  • A default value on a column? Unlikely, unless that default value's computation is expensive. Can we see the constraint? – Clinton Pierce Sep 27 '11 at 18:21
  • A default is not a constraint. – Larry Lustig Sep 27 '11 at 18:27
  • @Larry Lustig: debatable. It constrains the value to be NOT NULL when no value is supplied eg SomeCol int NOT NULL CONSTRAINT DF_Table_SomeCol DEFAULT 0 – gbn Sep 27 '11 at 18:32
  • Hmm, SQL server may implement DEFAULTs as some kind of constraint, most engines treat them differently (but the question *is* tagged sql-server). – Larry Lustig Sep 27 '11 at 18:34
  • @gbn: can you explain this in a bit more detail to me pls : "It constrains the value to be NOT NULL when no value is supplied" ? Does it mean that there is a constraint to the effect that if a user doesn't supply a value, then this value that he hasn't supplied must not be null ? If the user doesn't supply a value, then how does that magical system check whether the constraint is satisfied ??? – Erwin Smout Sep 27 '11 at 23:08
  • @Erwin Smout: NULL or NOT NULL is actually a constraint. If you don't supply a value then you'd get an error if NOT NULL. Arguably (I said "debatable" above) the a default constraint prevents errors for the NOT NULL constraint. A nullable column with a default would be a bit odd by these rules. – gbn Sep 28 '11 at 03:58
  • A default is not a constraint - not in standard SQL and certainly not according to the usual sense of a data integrity rule espressed as a Boolean expression. It just happens to be a peculiar, non-standard, non-portable "feature" of Transact-SQL that it uses the CONSTRAINT keyword in front of default definitions. I have never seen the logic of that. – nvogel Sep 28 '11 at 13:11

2 Answers2

5

It's highly unlikely a default constraint on a column is going to even be noticeable. There are so many things that could cause a slow update. However, the first place I would look is any triggers on the table being updated. This could cause a whole slew of performance issues.

One of the best ways to diagnose this is to fire up SQL Profiler and see what's happening on your SQL Server when you do an update. You might be quite surprised at what's happening.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • +1 for having the guts to answer a question with so little info and yet having great possibility of being right. I like when **I** do that, wish I had done it again :) – Adriano Carneiro Sep 27 '11 at 18:29
  • +1 for almost definitely being right. There's only an infinitesimally small chance that default value will slow anything down. If however, someone's decided to put a `wait 3 seconds` ( I'm serious ) or `select from my table but don't bother with indexes` in a trigger then that'll be the cause. – Ben Sep 27 '11 at 19:27
4

Unless you've coded them really badly then no.

By badly, I mean a things like a UDF to access a table to generate a value. Or sending an email RBAR in a trigger.

A slow write can be caused by many things. I doubt constraints. See Why does an UPDATE take much longer than a SELECT?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676