473,883 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.db o.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
Jan 13 '06 #1
4 1711
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.db o.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****@sommarsk og.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
Jan 13 '06 #2
> 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.

Jan 14 '06 #3
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_PROF ILE 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****@sommarsk og.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
Jan 14 '06 #4
> 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_PROF ILE 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.

Jan 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3733
by: Gabe | last post by:
Please see: http://www.showorders.com/test3.asp The code generating the page is as follows: testdate = "1/29/2003 1:00:00 PM" while count <> 5 testdate = dateadd("n",15,testdate) response.write testdate & "<br>"
4
5903
by: OJ | last post by:
Hi, This works to maximize the window, but wants to load yahoo locally : C:\WINDOWS\Desktop\www.yahoo.com <html> <script type="text/javaScript"> <!-- function test() { qwe = window.open("www.yahoo.com","");
20
12907
by: David | last post by:
I have a one-line script to add an onunload event handler to the body of the document. The script is as follows: document.getElementsByTagName("BODY").onunload=function s() {alert("s")} Now obviously, I put the alert("s") part in for debugging purposes, just to make sure the error wasn't in any code I was going to be running. This line works just fine in IE6 but in Firefox it doesn't. However, if I replace that line with the...
2
3646
by: Clayton Hamilton | last post by:
I have a DataGrid on a webform bound to a Datasource and can successfully use <ItemTemplate> to create edit/update/cancel functionality for user maintenance of data. I use separate logic to delete a row. Everything works just fine. BUT I would like to add a button to (for example) the DataGrid header, which when pressed will add a new row to the datagrid. This should then allow the user to enter information into text boxes (in some...
3
2351
by: Ankit Aneja | last post by:
I have a strange situation and I have no idea how to solve this. Its a Recruitment Search Page,in the Admin Page, for every button click event the Admin Person has to create a checkbox on the users page. So whenever the Admin person comes to know about the new category in the market he will be adding as different Sub-Categories for example ABAP, BDC etc..etc.. on every click event as Checkboxes. And these controls(checkboxes) should remain...
6
3139
by: Juan Pedro Gonzalez | last post by:
I wanted to add a Combobox to a toolbar... Kind of the look you get on VisualStudio's toolbar. I've been able to find some VB 6 samples, but the placeholder option is no longer available for ToolbarButtons... Does anyone know how this could be done? Looking forward to hear from you,
2
2442
by: Martin Eckart | last post by:
Hello, I have built a Hello World Web Service using Visual Studio 2005 and .NET 2.0 on machine A. Setting up a Web Site on machine B and adding a web reference to that WS works fine, without any delays. Setting up the same Web Site on machine C it takes forever after I select the WS and hit the "Add Reference" button in the wizard. Every time I updated the web reference the same. In addition the executing of the application takes...
2
15083
by: ChrisCicc | last post by:
Hi All, I got a real doozy here. I have read hundreds upon hundreds of forum posts and found numerous others who have replicated this problem, but have yet to find a solution. Through testing I have been able to find the cause of the problem, and will describe it here first textually and then through a code example. The purpose of what I am trying to do is to create a postback-free web application through the use of ASP.net AJAX UpdatePanels...
8
2051
by: Hugh Oxford | last post by:
I know I can force a variable passed to a function to be an object of class bar eg function foo(bar $bar_object) { //code }
0
9933
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11128
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10408
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7964
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.