Why would adding dbo. cause my function to run forever?

[serge]

Lately I did a mass update on all our scripts and added
dbo. in front of all tables and other objects.

There is a function that returns a table and the function is
I think 300 lines. There are a lot of UNIONs, EXISTS,
NOT EXISTS, etc...

Anyhow there's a single place where if i add dbo. in front
of the table this function which is being called from an SP,
when i run the SP from the QA or from the ASP application
it runs forever. As soon as i remove the dbo. again it runs
in 6-8 seconds. Here's the thing this same table reference exists
a few lines below in the 2nd UNION for example and
having a dbo. in front doesn't cause any problems.

Even more confusing if i add Databasename.dbo.TableName
and run it again, i don't run into any problem.
So it's almost like if i specify dbo. in front of the table, somehow
SQL Server or our code is getting lost and searching for this
table in other databases?

Has anyone run or seen such a problem?

I am sure I can make changes to the code and end up
writing a different code but before I make changes I would
like to find out more about my mystery problem.

I am running SQL 2000 SP4 and the same problem occured
on two different machines. Win 2000 Pro and Win 2000 Server.

Any ideas, suggestions?

Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Anyhow there's a single place where if i add dbo. in front
of the table this function which is being called from an SP,
when i run the SP from the QA or from the ASP application
it runs forever. As soon as i remove the dbo. again it runs
in 6-8 seconds. Here's the thing this same table reference exists
a few lines below in the 2nd UNION for example and
having a dbo. in front doesn't cause any problems.
I assume that we can rule out that there are two tables with different
owners, and you are logged in as that owner when you try this? Of course,
since:
Even more confusing if i add Databasename.dbo.TableName
and run it again, i don't run into any problem.

it's not very likely at all.

But I'm afraid I don't have very many ideas. Did you study the query
plan to see if they were identical in both cases?

--
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

[serge]

> But I'm afraid I don't have very many ideas.

I wanted to find out if someone knew or had encountered this
weird problem or may have heard it as a SQL bug or something.
Basically I was hoping that this was a known issue and there
was a simple explanation to it.

Did you study the query plan to see if they were identical in
both cases?

It was a big plan that I had waited for around 35 minutes before I
got it. There were I think 2 statements of INSERT to a #temp table
taking about 32% each so that seemed to be the big overhead in the
execution plan.

This problem is very intriguing. Yesterday I ran your version of
sp_lock but I don't really know how to interpret sp_lock etc...
I'll have to read a couple of chapters on locking and spend a
number of hours to learn this stuff, experiment and see what
I'll manage to learn out of this.

I am sure it will be painful but rewarding as I'll get a chance
to learn about locking and much more.

If I do end up finding any interesting information I'll post it here.

Thanks Erland.

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

I wanted to find out if someone knew or had encountered this
weird problem or may have heard it as a SQL bug or something.
Basically I was hoping that this was a known issue and there
was a simple explanation to it.
Unfortunately, I have never heard of this before. It sonds very strange.
It was a big plan that I had waited for around 35 minutes before I
got it. There were I think 2 statements of INSERT to a #temp table
taking about 32% each so that seemed to be the big overhead in the
execution plan.

A tip is to use SET STATISTICS_PROFILE ON. This gives you output in
text form, that you save to disk. Run this on both versions, and then
use Beyond Compare (http://www.scootersoftware.com) to compare the
output. Beyond Compare is good for this, becausee it produces diffs
on character level.


--
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

[serge]

> Unfortunately, I have never heard of this before. It sonds very strange.

Today when I went to work I saw the developer of this file had
updated the code and I noticed the tablename that was causing
my problem was removed. The statement was part of a
EXISTS or NOT EXISTS statement and when I quickly looked
at what was removed and replaced with, I believe the table that
was causing the problem was in the original SELECT statement
before the EXISTS or NOT EXISTS condition.
Anyhow I have kept the database and the problem as a copy
and if I do find the time in the near future I will try to troubleshoot
it further.

It was a big plan that I had waited for around 35 minutes before I
got it. There were I think 2 statements of INSERT to a #temp table
taking about 32% each so that seemed to be the big overhead in the
execution plan.

A tip is to use SET STATISTICS_PROFILE ON. This gives you output in
text form, that you save to disk. Run this on both versions, and then
use Beyond Compare (http://www.scootersoftware.com) to compare the
output. Beyond Compare is good for this, becausee it produces diffs
on character level.

Very good idea I'll keep this mind to compare execution plans instead
of going through long graphical execution plans.

Thanks again.