December 1, 2010 at 8:26 am
Hi,
For my below points i have 2 questions. whether it will boost performance of SQL server? whether it would be good design?
1) Instead of having Default value on column, insert explicitly default value.
2) Keep column constraint as NULL(Eventhough it ll not have NULL at any time) & explicitly take care in coding that NULL value should not get inserted.)
December 1, 2010 at 8:27 am
If the column cannot contain nulls, make it NOT NULL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2010 at 8:29 am
By keeping NOT NULL, sql server has to check for NOT NULL before insertion of each row. so that's my question whether it would increase performance & would be it good desing?
December 1, 2010 at 8:31 am
the constraint checks are very small and don't impact performance in a significant way. Adding them in there is a way of ensuring that your data is correct and accurate, which ultimately results in better performance.
December 1, 2010 at 8:36 am
It would be a very bad design. Constraints belong in the database and data should be constrained as much as possible (data types, foreign key + check constraints)
If you're worrying about the microseconds of constraint checks, you're worrying about the wrong thing. Poor indexing decision or poor queries will have far, far, far more of an impact than this.
If the column is nullable then sQL has to consider that it is possible for a null value and has to do more work in allowing for that possibility in a number of cases. IN being the one that comes to mind immediately.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2010 at 11:58 am
Hi,
I agree with all the above answers, especially Gails.
It is a misconception that constraints always hurt performance. Maybe it was so a long time ago. Constraints can actually be used by the optimizer to speed up a query.
One interesting aspect is when there is a need to add a new not null column to a huge table. Not null constraint in this case requires default value so the table is locked for a while during the operation. There are several ways to solve this, one is to allow null, fill the default values in batches and then set the not null constraint.
Cheers
Istvan
December 3, 2010 at 2:24 pm
Brigadur (12/3/2010)
Constraints can actually be used by the optimizer to speed up a query.
Hope you are referring to Referential Integrity constraints.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 4, 2010 at 2:38 am
PaulB-TheOneAndOnly (12/3/2010)
Brigadur (12/3/2010)
Constraints can actually be used by the optimizer to speed up a query.Hope you are referring to Referential Integrity constraints.
Check and not null constraints can too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2010 at 3:57 pm
GilaMonster (12/4/2010)
PaulB-TheOneAndOnly (12/3/2010)
Brigadur (12/3/2010)
Constraints can actually be used by the optimizer to speed up a query.Hope you are referring to Referential Integrity constraints.
Check and not null constraints can too.
mmmhhh... Please correct me if I'm wrong. I understand optimizer doesn't look at Check Constraints themselves but at performance statistics - which I agree will reflect the set of values allowed by Check Constraints but will also include cardinality / data distribution related info. I think same applies to Null constraint.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 4, 2010 at 5:03 pm
PaulB-TheOneAndOnly (12/4/2010)
GilaMonster (12/4/2010)
PaulB-TheOneAndOnly (12/3/2010)
Brigadur (12/3/2010)
Constraints can actually be used by the optimizer to speed up a query.Hope you are referring to Referential Integrity constraints.
Check and not null constraints can too.
mmmhhh... Please correct me if I'm wrong. I understand optimizer doesn't look at Check Constraints themselves but at performance statistics - which I agree will reflect the set of values allowed by Check Constraints but will also include cardinality / data distribution related info. I think same applies to Null constraint.
Pablo, I don't think that is the whole truth, actually the optimizer are using the constraint definitions also (the constraints have to be trusted). This can easily be verified. If there is a check constraint defined on a column, and you search on a value that is outside the constraint definition, the optimizer immidiately detects this. This can be verified by looking at the execution plan.
Cheers
Istvan
December 5, 2010 at 8:37 am
Brigadur (12/4/2010)
PaulB-TheOneAndOnly (12/4/2010)
GilaMonster (12/4/2010)
PaulB-TheOneAndOnly (12/3/2010)
Brigadur (12/3/2010)
Constraints can actually be used by the optimizer to speed up a query.Hope you are referring to Referential Integrity constraints.
Check and not null constraints can too.
mmmhhh... Please correct me if I'm wrong. I understand optimizer doesn't look at Check Constraints themselves but at performance statistics - which I agree will reflect the set of values allowed by Check Constraints but will also include cardinality / data distribution related info. I think same applies to Null constraint.
Pablo, I don't think that is the whole truth, actually the optimizer are using the constraint definitions also (the constraints have to be trusted). This can easily be verified. If there is a check constraint defined on a column, and you search on a value that is outside the constraint definition, the optimizer immidiately detects this. This can be verified by looking at the execution plan.
Do you know this for a fact? How do you know optimizer is looking at constraint definition and not to table statistics? table statistics are telling optimizer range value, cardinality and data distribution.
Moreover, example below doesn't show any difference in explain plan meaning, no differences for select against constrained and non-contrained version of the table.
USE Northwind
go
CREATE TABLE dbo.table_a
(
a numeric(38,0) NULL
)
go
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (1);
insert into dbo.table_a values (2);
insert into dbo.table_a values (2);
insert into dbo.table_a values (2);
insert into dbo.table_a values (2);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
insert into dbo.table_a values (3);
go
ALTER TABLE dbo.table_a
ADD CONSTRAINT my_const
CHECK ([a] < 4)
go
USE Northwind
go
CREATE TABLE dbo.table_b
(
a numeric(38,0) NULL
)
go
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (1);
insert into dbo.table_b values (2);
insert into dbo.table_b values (2);
insert into dbo.table_b values (2);
insert into dbo.table_b values (2);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
insert into dbo.table_b values (3);
go
select *
from dbo.table_a
where a = 5
go
select *
from dbo.table_b
where a = 5
go
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 5, 2010 at 9:12 am
PaulB-TheOneAndOnly (12/4/2010)
mmmhhh... Please correct me if I'm wrong. I understand optimizer doesn't look at Check Constraints themselves but at performance statistics - which I agree will reflect the set of values allowed by Check Constraints but will also include cardinality / data distribution related info. I think same applies to Null constraint.
It looks at both.
The example I can think of immediately is the behaviour of NOT IN when the column is nullable vs when it's not (regardless of the presence of nulls). The performance difference there is very pronounced. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Also, the optimiser understands that the stats are estimates. If the histogram says that there are no rows with a NULL value less than zero it estimates one row (not zero) and if there's a check for NOT is will still do that. It has to, the histogram's an estimate (and could easily be out of date) and it cannot assume that it is 100% correct, plus the plan if cached must be safe for reuse at a future time by which time data changes could have occurred.
If however there's a check constraint that says NOT NULL, there cannot be any rows in the table with such values (if the constraint is trusted) and hence if there's a check for that in a query, SQL can ignore it safely.
Depends on simplicity of query of course, parameterisation messes the whole thing up.
CREATE TABLE TestingConstraints (
id INT IDENTITY PRIMARY KEY,
status CHAR(2),
SomeInt int,
filler CHAR(200) DEFAULT ' '
)
INSERT INTO TestingConstraints (status, someint)
SELECT CHAR(65+NTILE(5) OVER (ORDER BY b.column_id)) + CHAR(65+NTILE(7) OVER (ORDER BY a.column_id)), NTILE(50) OVER (ORDER BY a.object_id)
FROM master.sys.columns a CROSS JOIN master.sys.columns b
GO
CREATE INDEX idx_1 ON TestingConstraints (status)
CREATE INDEX idx_2 ON TestingConstraints (SomeInt)
SELECT id FROM TestingConstraints WHERE Status = 'BB' AND someInt IS NOT NULL
DROP INDEX idx_2 ON TestingConstraints
ALTER TABLE TestingConstraints ALTER COLUMN someint INT NOT NULL
CREATE INDEX idx_2 ON TestingConstraints (SomeInt)
SELECT id FROM TestingConstraints WHERE Status = 'BB' AND someInt IS NOT NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2010 at 9:15 am
PaulB-TheOneAndOnly (12/5/2010)
Moreover, example below doesn't show any difference in explain plan meaning, no differences for select against constrained and non-contrained version of the table.
Two points.
That query is simple enough to likely have got the Trivial Plan treatment. If it did, it didn't go through more than the absolute basics of optimisation - table scan and filter.
It is simple enough to have been auto-parameterised. If it was what the optimiser got was not
select *
from dbo.table_a
where a = 5
It was
select *
from dbo.table_a
where a = @P1
and that cannot have the condition eliminated because the plan matches any query of that form, no matter what value is passed for @P1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2010 at 5:17 pm
Thank you Gail.
I got the Null constraint, what you say makes sense.
In regards to Check constraint, still not sure how can a check contraint have an impact on execution plan when optimizer gets a bind-variable therefore optimizer doesn't know the actual value of the parameter.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 5, 2010 at 6:12 pm
Will see if I can dig up an example. Will need to be a query with hardcoded values and one that's not so simple it gets auto-parameterised.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply