Query never ends execution | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query never ends execution

Hello I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete. Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @@rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT. TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows. What could be wrong?
Run the profiler and see what statement exactly cuasing performance degradation.
Are you using synonyms or four part name to call remote table? And also check if your spid is being blocked on remote server (2000). MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Thanks for your reply. I am using synonyms for tables and remote stored procedures; does it affect on performance? if so, which is better: using synonyms of four-part names?
Yes, it should effect the peformance when you use synonyms… try using four-part name and see if it improves.. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Synonyms may affect performance theoratically, but I dont think there will be a noticable difference. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
They are introduced in SQL 2005 and may not work properly with SQL 2000 instnace as you have a linked server attached (if so). As suggested you might check the performance for query exeuction plan for both of them. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Synonyms are most handy when linked servers are involved, and since its just a runtime replacing mechanism, it should work flawlessly, regardless where the linked server is pointitng to. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Here is the general structure of the problematic stored procedure (Consider that there are 2 remote tables and 2 local tables instead of just one, and that the operations are made for both of them in a similar fashion):
declare @LastUpdate datetime
declare @Workstation varchar(250) set @Workstation=host_name() SET NOCOUNT ON
execute spGetLastUpdate @LastUpdate output insert into Synonym_MyRemoteTable1Temp
select
@Workstation,
ID,
Value1,
Value2,
Value3
from myLocalTable1
where UpdateTimeStamp>@LastUpdate execute Synonym_spMyRemoteProcedure @Workstation — Explained below
/*
This remote procedure makes an update and an insert as follows: update MyRemoteTable1
set
Value1=MyRemoteTable1Temp.Value1,
Value2=MyRemoteTable1Temp.Value2,
Value3=MyRemoteTable1Temp.Value3
from MyRemoteTable1
inner join MyRemoteTable1Temp on MyRemoteTable1Temp.ID=MyRemoteTable1.ID and [email protected] insert into MyRemoteTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from MyRemoteTable1Temp
where
[email protected] and ID not in (select ID from MyRemoteTable1)
*/ update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join
(
select
ID,
Value1,
Value2,
Value3
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@LastUpdate and
WorkStation<>@Workstation ) as T on T.ID=MyLocalTable1.ID insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from Synonym_MyRemoteTable1
where
UpdateTimeStamp>@LastUpdate and
Workstation<>@WorkStation and
ID not in (select ID from MyLocalTable1)
Did you tested with four part name? If yes, what is the performance difference?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

I tried and it has not improved 🙁
Copy the data into a temp table locally then use the temp table in your query… select
ID,
Value1,
Value2,
Value3
into #temp
from Synonym_MyRemoteTable1
where UpdateTimeStamp>@LastUpdate and
WorkStation<>@Workstation update MyLocalTable1
set
Value1=T.Value1,
Value2=T.Value2,
Value3=T.Value3,
UpdateTimeStamp=GetDate()
from MyLocalTable1
inner join #temp T on T.ID=MyLocalTable1.ID insert into MyLocalTable1
(
ID,
Value1,
Value2,
Value3,
UpdateTimeStamp
)
select
ID,
Value1,
Value2,
Value3,
GetDate()
from #temp
where
UpdateTimeStamp>@LastUpdate and
Workstation<>@WorkStation and
ID not in (select ID from MyLocalTable1) MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Just a thought that, you are continuing the database optimization tasks regularly to keepup the performance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>