Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
problem using different data.
- There is only a problem when I index email on the base table.
- The problem goes away when I add "AND b.email IS NOT NULL" to the
inner join condition. It does not help when I add the logic to the
"WHERE" clause.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index icx on base(bk)
create index ix_email on base(email)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index icx on filter (bk)
create index ix_email on filter (email)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Data Profile
--35120500, 35120491, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
--16796199, 16796192, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
WHERE email IS NOT NULL
--250552, 250552, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
--250208, 250208, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
WHERE email IS NOT NULL
18  5003 
Dave wrote:
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
problem using different data.
- There is only a problem when I index email on the base table.
- The problem goes away when I add "AND b.email IS NOT NULL" to the
inner join condition. It does not help when I add the logic to the
"WHERE" clause.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index icx on base(bk)
create index ix_email on base(email)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index icx on filter (bk)
create index ix_email on filter (email)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Data Profile
--35120500, 35120491, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
--16796199, 16796192, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
WHERE email IS NOT NULL
--250552, 250552, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
--250208, 250208, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
WHERE email IS NOT NULL
What does the estimated execution plan look like? Try adding the column
BK as a second key in your email index.
--
Tracy McKibben
MCDBA http://www.realsqlguy.com
bk is clustered so there is no reason to add it to the other index.
Estimated plan does an index scan on each table, then a merge join.
When I remove the index on base.email the plan does a table scan on
base then performs a Hash Match
Tracy McKibben wrote:
Dave wrote:
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
problem using different data.
- There is only a problem when I index email on the base table.
- The problem goes away when I add "AND b.email IS NOT NULL" to the
inner join condition. It does not help when I add the logic to the
"WHERE" clause.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index icx on base(bk)
create index ix_email on base(email)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index icx on filter (bk)
create index ix_email on filter (email)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Data Profile
--35120500, 35120491, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
--16796199, 16796192, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
WHERE email IS NOT NULL
--250552, 250552, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
--250208, 250208, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
WHERE email IS NOT NULL
What does the estimated execution plan look like? Try adding the column
BK as a second key in your email index.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Dave,
Do you have a lot of null emails on both tables? If so, that might be an
influencing factor.
-- Bill
"Dave" <da******@gmail.comwrote in message
news:11**********************@s80g2000cwa.googlegr oups.com...
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
problem using different data.
- There is only a problem when I index email on the base table.
- The problem goes away when I add "AND b.email IS NOT NULL" to the
inner join condition. It does not help when I add the logic to the
"WHERE" clause.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index icx on base(bk)
create index ix_email on base(email)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index icx on filter (bk)
create index ix_email on filter (email)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Data Profile
--35120500, 35120491, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
--16796199, 16796192, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
WHERE email IS NOT NULL
--250552, 250552, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
--250208, 250208, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
WHERE email IS NOT NULL
Dave (da******@gmail.com) writes:
bk is clustered so there is no reason to add it to the other index.
Estimated plan does an index scan on each table, then a merge join.
When I remove the index on base.email the plan does a table scan on
base then performs a Hash Match
And the merge-join plan takes forever? That's indeed very funny, because
that's sounds like a quite decent plan. Your tables looks funny, because
they have no keys, but that would matter.
What happens if you try:
SELECT b.hk, b.email
FROM base b
WHERE EXISTS (SELECT *
FROM filter
WHERE f.email = b.email)
Which version of SQL Server?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Your correlated subquery works.
It added a stream aggregate step to the execution plan right before the
merge join.
This is really weird. Could it be an SQL Server Bug? Like I said
earlier, it is very data specific. I can add 3 million random records
to the filter table and it normally works fine. It is only this
specific dataset that is causing problems.
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
bk is clustered so there is no reason to add it to the other index.
Estimated plan does an index scan on each table, then a merge join.
When I remove the index on base.email the plan does a table scan on
base then performs a Hash Match
And the merge-join plan takes forever? That's indeed very funny, because
that's sounds like a quite decent plan. Your tables looks funny, because
they have no keys, but that would matter.
What happens if you try:
SELECT b.hk, b.email
FROM base b
WHERE EXISTS (SELECT *
FROM filter
WHERE f.email = b.email)
Which version of SQL Server?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi Dave,
I tried to create a situation similar to yours on SQL Server 2000. What
I noticed is:
- Without the extra predicate, the optimizer does not seem smart enough
to chose SEEK:([b].[email] IsNotNull) instead of a full Index Scan
- You are correct that the change in access method is not achieved if
the predicate is added to the WHERE clause. It seems it must be part of
the join ON clause
- If either "b.email IS NOT NULL" or "f.email IS NOT NULL" is added, the
join type remains MERGE JOIN. If both are added to the ON clause I get a
HASH JOIN
- I am not sure if the MERGE JOIN is faster or if the HASH JOIN is, but
in either case you may want to add the two "unnecessary" predicates
There is a thresshold somewhere (depending on your specific situation)
where merge-joining or hashing the two partially scanned indexes is
faster. Up to that point, the fastest plan will be to partially scan the
index of filter and loop join and seek each matching row in base. In all
cases it will be faster if NULL rows are excluded from the index seeks.
HTH,
Gert-Jan
Dave wrote:
>
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
problem using different data.
- There is only a problem when I index email on the base table.
- The problem goes away when I add "AND b.email IS NOT NULL" to the
inner join condition. It does not help when I add the logic to the
"WHERE" clause.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index icx on base(bk)
create index ix_email on base(email)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index icx on filter (bk)
create index ix_email on filter (email)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Data Profile
--35120500, 35120491, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
--16796199, 16796192, 14221553
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM base
WHERE email IS NOT NULL
--250552, 250552, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
--250208, 250208, 250205
SELECT COUNT(*) ,COUNT(DISTINCT bk), COUNT(DISTINCT email)
FROM filter
WHERE email IS NOT NULL
Dave (da******@gmail.com) writes:
Your correlated subquery works.
It added a stream aggregate step to the execution plan right before the
merge join.
This is really weird. Could it be an SQL Server Bug? Like I said
earlier, it is very data specific. I can add 3 million random records
to the filter table and it normally works fine. It is only this
specific dataset that is causing problems.
Note that the two queries are not semantically equivalent. If a certain
email address appears four times in the filter and four times in base, your
original query will return 16 rows, while mine will return four.
With the numbers you posted there are only four duplicates in the filter.
Provided, that is, you are running with ANSI_NULLS ON. If this is in a
stored procedure saved from Enterprise Manager, you are likely to be running
with ANSI_NULLS OFF, in which case NULL = NULL, and you will get an
enormous number of duplicates. Which are filtered away if you add the
IS NOT NULL filter.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
We are using SQL 2K Enterprise SP4.
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
I added this to the test script and it did not help.
SET ANSI_NULLS ON
I think there are two things going on here.
1. SQL Server is not picking the best plan
2. There is an issue with my specific data set that I can not reproduce
with any other dataset except for "some" subsets or supersets.
Thanks for the help guys!!!
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Your correlated subquery works.
It added a stream aggregate step to the execution plan right before the
merge join.
This is really weird. Could it be an SQL Server Bug? Like I said
earlier, it is very data specific. I can add 3 million random records
to the filter table and it normally works fine. It is only this
specific dataset that is causing problems.
Note that the two queries are not semantically equivalent. If a certain
email address appears four times in the filter and four times in base, your
original query will return 16 rows, while mine will return four.
With the numbers you posted there are only four duplicates in the filter.
Provided, that is, you are running with ANSI_NULLS ON. If this is in a
stored procedure saved from Enterprise Manager, you are likely to be running
with ANSI_NULLS OFF, in which case NULL = NULL, and you will get an
enormous number of duplicates. Which are filtered away if you add the
IS NOT NULL filter.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
I added this to the test script and it did not help.
SET ANSI_NULLS ON
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.
Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...
2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...
3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):
4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.
Let me know how it goes.
-Dave Markle http://www.markleconsulting.com
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
I added this to the test script and it did not help.
SET ANSI_NULLS ON
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
I like your explanation!
The table does not currently have a primary key.
I basically have options 2 and 4 available; 1 and 3 are out of my
control.
I will lobby for adding an identity column to this table. It happens
to be very wide with 12 single column indexes so I really don't want
to make the clustered super wide.
Just for clarities sake, in order for the optimizer to properly
understand the tables, do I have to add a PK constraint to the identity
column?
>Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.
Thanks!
dmarkle wrote:
I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.
Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...
2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...
3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):
4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.
Let me know how it goes.
-Dave Markle
http://www.markleconsulting.com
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
>
I added this to the test script and it did not help.
SET ANSI_NULLS ON
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Well, it comes mostly from relational and set theory. I like to
explain the need for PK's like this:
If your wife makes you go to the grocery store and she asks you what
you bought, you'd give a set back as your answer:
"Peas, carrots, potatoes, and Coca-Cola"
You wouldn't say:
"Peas, carrots, potatoes, peas, and Coca-Cola".
The two answers aren't equivalent. One simply makes sense, and one
does not. For example, in set theory, you can express a set of numbers
as:
{1,2,3,4}
but not:
{1,2,2,3,4}. The second representation is invalid.
Like giving your grocery list or giving a set of numbers, the engine's
theoretical foundation is based on relational theory, which is closely
tied to set theory.
If you want a book with a recommendation, "Inside SQL Server" as well
as a lot of other DB books out there will tell you the same thing --
always have a PK on each table you create.
I'm sorry, I'll admit that I don't have the hard evidence to prove to
you that not having a PK is going to screw up the optimizer. I do try
to stay away from "cargo cult" type "I saw this once..." sort of
recommendations, but in this case, since not having a PK on each table
is such a no-no, I'd try this first. I like #2 best. If "email" is
distinct in the table by itself, that's going to be even better...
Maybe copy your data over to a development database and try these out
and see how it goes.
Good luck.
-Dave
Dave wrote:
I like your explanation!
The table does not currently have a primary key.
I basically have options 2 and 4 available; 1 and 3 are out of my
control.
I will lobby for adding an identity column to this table. It happens
to be very wide with 12 single column indexes so I really don't want
to make the clustered super wide.
Just for clarities sake, in order for the optimizer to properly
understand the tables, do I have to add a PK constraint to the identity
column?
>Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.
Thanks!
dmarkle wrote:
I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.
Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...
2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...
3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):
4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.
Let me know how it goes.
-Dave Markle
http://www.markleconsulting.com
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
I added this to the test script and it did not help.
SET ANSI_NULLS ON
>
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
>
>
>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanks for the explanation.
I copied the tables to a test db. I created an identity column on each
table and created a unique clustered in the identity columns. I then
created a non-clustered on bk and a non clustered on email.
This did not help the query performance.
I will keep checking back here to see if anyone else has any
suggestions on how to trouble shoot this. It is no longer a critical
issue due to the work around. I just don't like the fact that we can
not explain this behavior.
dmarkle wrote:
Well, it comes mostly from relational and set theory. I like to
explain the need for PK's like this:
If your wife makes you go to the grocery store and she asks you what
you bought, you'd give a set back as your answer:
"Peas, carrots, potatoes, and Coca-Cola"
You wouldn't say:
"Peas, carrots, potatoes, peas, and Coca-Cola".
The two answers aren't equivalent. One simply makes sense, and one
does not. For example, in set theory, you can express a set of numbers
as:
{1,2,3,4}
but not:
{1,2,2,3,4}. The second representation is invalid.
Like giving your grocery list or giving a set of numbers, the engine's
theoretical foundation is based on relational theory, which is closely
tied to set theory.
If you want a book with a recommendation, "Inside SQL Server" as well
as a lot of other DB books out there will tell you the same thing --
always have a PK on each table you create.
I'm sorry, I'll admit that I don't have the hard evidence to prove to
you that not having a PK is going to screw up the optimizer. I do try
to stay away from "cargo cult" type "I saw this once..." sort of
recommendations, but in this case, since not having a PK on each table
is such a no-no, I'd try this first. I like #2 best. If "email" is
distinct in the table by itself, that's going to be even better...
Maybe copy your data over to a development database and try these out
and see how it goes.
Good luck.
-Dave
Dave wrote:
I like your explanation!
The table does not currently have a primary key.
I basically have options 2 and 4 available; 1 and 3 are out of my
control.
I will lobby for adding an identity column to this table. It happens
to be very wide with 12 single column indexes so I really don't want
to make the clustered super wide.
Just for clarities sake, in order for the optimizer to properly
understand the tables, do I have to add a PK constraint to the identity
column?
>Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.
Thanks!
dmarkle wrote:
I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.
>
Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
>
You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...
>
2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...
>
3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):
>
4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.
>
Let me know how it goes.
-Dave Markle
http://www.markleconsulting.com
>
>
>
>
>
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
>
I added this to the test script and it did not help.
SET ANSI_NULLS ON
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dave (da******@gmail.com) writes:
Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.
It's not really a matter of the optimizer needing a key. Adding an
IDENTITY column is not going to change this particular problem. It's
more about database design in general. Every table in a database should
have a primary key. Preferrably this should be a natural key, but this
is not always possible. In a relational database, you access data through
data, that is the primary key.
By defining good primary keys, you can avoid duplicate data. With further
normalisation, there are more anomalies you can avoid.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Interesting problem. It seems that the optimizer likes the "not null"
hint and is treating it differently than other predicates. I see you
do have a lot of nulls in your database.
I've run into a number of situations with minimally indexed tables,
where adding additional indexes slows down execution (of selects).
This is always weird, since one would hope the optimizer could judge
and ignore the index in those cases!
The size of your data can count, too. In fact, I'm working on
something along those lines these days myself. Once the optimizer
realizes your data is a lot larger than your RAM, it has to switch to
multipass sorts and merges which are MUCH slower. I'm not sure these
even show on the exection plans, preview nor afterwards. Maybe it
shows on set statistics profile.
I don't have any specific suggestions. If you kept the null-valued
records in a separate table, ... something along those lines, is
somewhat extreme but might help out, except you have your
"work-around", so what the heck! Maybe SQL2005 partitioned tables ...
Josh
On 8 Jan 2007 13:00:17 -0800, "Dave" <da******@gmail.comwrote:
>Thanks for the explanation.
I copied the tables to a test db. I created an identity column on each
table and created a unique clustered in the identity columns. I then
created a non-clustered on bk and a non clustered on email.
This did not help the query performance.
I will keep checking back here to see if anyone else has any
suggestions on how to trouble shoot this. It is no longer a critical
issue due to the work around. I just don't like the fact that we can
not explain this behavior.
dmarkle wrote:
>Well, it comes mostly from relational and set theory. I like to
explain the need for PK's like this:
If your wife makes you go to the grocery store and she asks you what
you bought, you'd give a set back as your answer:
"Peas, carrots, potatoes, and Coca-Cola"
You wouldn't say:
"Peas, carrots, potatoes, peas, and Coca-Cola".
The two answers aren't equivalent. One simply makes sense, and one
does not. For example, in set theory, you can express a set of numbers
as:
{1,2,3,4}
but not:
{1,2,2,3,4}. The second representation is invalid.
Like giving your grocery list or giving a set of numbers, the engine's
theoretical foundation is based on relational theory, which is closely
tied to set theory.
If you want a book with a recommendation, "Inside SQL Server" as well
as a lot of other DB books out there will tell you the same thing --
always have a PK on each table you create.
I'm sorry, I'll admit that I don't have the hard evidence to prove to
you that not having a PK is going to screw up the optimizer. I do try
to stay away from "cargo cult" type "I saw this once..." sort of
recommendations, but in this case, since not having a PK on each table
is such a no-no, I'd try this first. I like #2 best. If "email" is
distinct in the table by itself, that's going to be even better...
Maybe copy your data over to a development database and try these out
and see how it goes.
Good luck.
-Dave
Dave wrote:
I like your explanation!
The table does not currently have a primary key.
I basically have options 2 and 4 available; 1 and 3 are out of my
control.
I will lobby for adding an identity column to this table. It happens
to be very wide with 12 single column indexes so I really don't want
to make the clustered super wide.
Just for clarities sake, in order for the optimizer to properly
understand the tables, do I have to add a PK constraint to the identity
column?
>Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.
Thanks!
dmarkle wrote:
I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.
Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.
You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...
2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...
3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):
4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.
Let me know how it goes.
-Dave Markle
http://www.markleconsulting.com
Erland Sommarskog wrote:
Dave (da******@gmail.com) writes:
Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.
>
I added this to the test script and it did not help.
SET ANSI_NULLS ON
As long it's a loose script, ANSI_BULLS would be on by default. I will
have to admit that I was clutching a straws. Without access to the
database seeing it, it is very difficult to analyse the problem accurately.
It's not alwyas that easy even if you have full acecss.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi,
I have modified your query a little bit and hope this will help.
DDL
CREATE TABLE base (bk char(25), email varchar(100))
create clustered index ix_email on base(email)
create index icx on base(bk)
CREATE TABLE filter (bk char(25), email varchar(100))
create clustered index ix_email on filter (email)
create index icx on filter (bk)
Query
SELECT b.bk, b.email
FROM base b WITH(NOLOCK)
INNER JOIN filter f ON f.email = b.email
--and f.email is not null
Dave wrote:
Thanks for the explanation.
I copied the tables to a test db. I created an identity column on each
table and created a unique clustered in the identity columns. I then
created a non-clustered on bk and a non clustered on email.
This did not help the query performance.
I will keep checking back here to see if anyone else has any
suggestions on how to trouble shoot this. It is no longer a critical
issue due to the work around. I just don't like the fact that we can
not explain this behavior.
dmarkle wrote:
Well, it comes mostly from relational and set theory. I like to
explain the need for PK's like this:
If your wife makes you go to the grocery store and she asks you what
you bought, you'd give a set back as your answer:
"Peas, carrots, potatoes, and Coca-Cola"
You wouldn't say:
"Peas, carrots, potatoes, peas, and Coca-Cola".
On Fri, 5 Jan 2007 23:18:30 +0000 (UTC), Erland Sommarskog wrote:
ANSI_BULLS
Hi Erland,
Is that your secret nickname for Joe Celko? <g>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName,...
|
by: DCM Fan |
last post by:
{CREATE TABLEs and INSERTs follow...}
Gents,
I have a main table that is in ONE-MANY with many other tables. For example, if
the main table is named A, there are these realtionships:
A-->B...
|
by: news-east.earthlink.net |
last post by:
The scenario:
two tables
CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
|
by: Thomas Beutin |
last post by:
Hi,
i've a speed problem withe the following statement:
SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
FROM ot_adresse AS a, ot_produkt AS p
LEFT OUTER JOIN ot_kat_prod AS pz ON (...
|
by: PASQUALE |
last post by:
Hi
I have a question:
do the both statements below give the same result?
If yes then does somebody know something about preformance differencies
using these joins?
SELECT A.*
FROM Table1 A...
|
by: gkellymail |
last post by:
the following query works fine:
select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid,
b.strandid
from link_detail, link, strand A, strand B
where link_detail.x_table =...
|
by: moldster |
last post by:
Hi,
I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained...
|
by: dmonroe |
last post by:
hi group --
Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:
...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
