RPC takes forever - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

RPC takes forever

Author

Topic

sabtheleo
Starting Member

10 Posts

Posted - 2011-02-28 : 15:46:08

Hi all,
I have two servers. Server-1 is running sql 2005 and Server-2 running sql 2008. I have linked both servers on both sides. Tested the connection which is succeeded. I am trying to execute a Stored Procedure which is residing on server-1.

On server-2 when I tried to execute that stored procedure, it gave me the result in no time. This is the query I have executed.

EXEC [server-1].geolab_mdt2_cape.dbo.sp_SelectAVL_PL

GeolabID	Datetime	Latitude	Longitude	LatLonAccuracy	PositionSpeed	PositionHeading	SatelliteNumber	SignalStrength	BatteryLevel	Froutename	BusID	TimetoGrey	CustomIcon	DirectionName
PB11330	2011-02-28 15:27:56.000	42.34847744	-71.05697777	31000	0	104	4	4	4	Boston->Hyannis (231)	11330	300	images/map_vehicles/intercity_bus.gif	NULL
PB11352	2011-02-28 15:30:20.000	42.34459966	-71.06445277	31000	0	296	4	4	4	Logan->Hyannis (241)	11352	300	images/map_vehicles/intercity_bus.gif	NULL
PB11372	2011-02-28 15:12:39.000	41.96360833	-70.69711355	31000	0	112	4	4	4		11372	300	images/map_vehicles/intercity_bus.gif	NULL
PB11380	2011-02-28 15:35:37.000	42.349283	-71.05661111	31000	0	18	4	4	4	Boston->Hyannis (237)	11380	300	images/map_vehicles/intercity_bus.gif	NULL
PB11383	2011-02-28 15:35:39.000	42.38929133	-71.01492777	31000	12	210	4	4	4	McGinn (3000)	11383	300	images/map_vehicles/intercity_bus.gif	NULL
PB11391	2011-02-28 15:12:40.000	42.199308	-71.06608055	31000	0	0	4	4	4	Boston->Plymouth (119)	11391	300	images/map_vehicles/intercity_bus.gif	NULL
PB11399	2011-02-28 15:35:20.000	41.67068266	-70.299508	31000	0	300	4	4	4	Hyannis->Logan (270)	11399	300	images/map_vehicles/intercity_bus.gif	NULL
PB11401	2011-02-28 15:12:34.000	42.34449411	-71.06435833	31000	0	172	4	4	4		11401	300	images/map_vehicles/intercity_bus.gif	NULL
PB11402	2011-02-28 15:35:35.000	41.68706633	-70.335533	31000	0	102	4	4	4	Logan->Hyannis (227)	11402	300	images/map_vehicles/intercity_bus.gif	NULL
PB11404	2011-02-28 15:12:34.000	42.34591633	-71.03784966	31000	0	140	4	4	4		11404	300	images/map_vehicles/intercity_bus.gif	NULL
PB11405	2011-02-28 15:31:29.000	41.96399166	-70.69723022	31000	0	306	4	4	4		11405	300	images/map_vehicles/intercity_bus.gif	NULL

Now the problem is if I want to execute this procedure inside another stored procedure. It is querying forever.
The stored procedure I am trying to execute is

USE [geolab_mdt2_cape]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectAVL_CapeCodMultiModal]    Script Date: 02/28/2011 15:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------------------------------------------------------------------
-- Date Created:  05/09/2007 22:05:53
-- Created By:   
------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_SelectAVL_CCM]
AS
BEGIN
	SET NOCOUNT ON; 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	-- Memory Table
	DECLARE @tableBusData table
	(
		GeolabID char(10),
		[Datetime] datetime,
		Latitude float,
		Longitude float,
		LatLonAccuracy int,
		PositionSpeed tinyint,
		PositionHeading smallint,
		SatelliteNumber tinyint, 
		SignalStrength tinyint,
		BatteryLevel tinyint,
		Froutename varchar(30),
		BusID varchar(10),
		TimetoGrey int,
		CustomIcon varchar(100),
		DirectionName varchar(100) NULL
	)

	INSERT INTO @tableBusData EXEC dbo.sp_SelectAVL_CCP
	INSERT INTO @tableBusData EXEC dbo.sp_SelectAVL_CC
	INSERT INTO @tableBusData EXEC [server-1].geolab_mdt2_cape.dbo.sp_SelectAVL_PL

	DECLARE @time AS Datetime
	SET @time = GETDATE()
	SELECT * FROM @tableBusData WHERE ([Latitude] IS NOT NUll AND [Longitude] IS NOT NUll AND  ([Datetime] BETWEEN  @time-0.025 AND @time+10.0)  )

END

one more thing If remove "INSERT INTO @tableBusData" from that line the stored procedure is running perfectly. But I need all that data in one single table.

The Result Looks like this.

GeolabID	Datetime	Latitude	Longitude	LatLonAccuracy	PositionSpeed	PositionHeading	SatelliteNumber	SignalStrength	BatteryLevel	Froutename	BusID	TimetoGrey	CustomIcon	DirectionName
PB11330	2011-02-28 15:27:56.000	42.34847744	-71.05697777	31000	0	104	4	4	4	Boston->Hyannis (231)	11330	300	images/map_vehicles/intercity_bus.gif	NULL
PB11351	2011-02-28 15:50:02.000	42.36788544	-71.02683544	31000	0	182	4	4	4	Logan->Hyannis (241)	11351	300	images/map_vehicles/intercity_bus.gif	NULL
PB11352	2011-02-28 15:49:40.000	42.344633	-71.06429722	31000	0	296	4	4	4	Logan->Hyannis (241)	11352	300	images/map_vehicles/intercity_bus.gif	NULL
PB11372	2011-02-28 15:12:39.000	41.96360833	-70.69711355	31000	0	112	4	4	4		11372	300	images/map_vehicles/intercity_bus.gif	NULL
PB11380	2011-02-28 15:50:10.000	42.35080833	-71.05510555	31000	0	34	4	4	4	Boston->Hyannis (237)	11380	300	images/map_vehicles/intercity_bus.gif	NULL
PB11383	2011-02-28 15:50:00.000	42.37928544	-71.02141322	31000	0	84	4	4	4	McGinn (3000)	11383	300	images/map_vehicles/intercity_bus.gif	NULL
PB11391	2011-02-28 15:12:40.000	42.199308	-71.06608055	31000	0	0	4	4	4	Boston->Plymouth (119)	11391	300	images/map_vehicles/intercity_bus.gif	NULL
PB11399	2011-02-28 15:49:53.000	41.69818855	-70.38747744	31000	19	302	4	4	4	Hyannis->Logan (270)	11399	300	images/map_vehicles/intercity_bus.gif	NULL
PB11401	2011-02-28 15:12:34.000	42.34449411	-71.06435833	31000	0	172	4	4	4		11401	300	images/map_vehicles/intercity_bus.gif	NULL
PB11402	2011-02-28 15:50:07.000	41.65827222	-70.279466	31000	10	169	4	4	4	Logan->Hyannis (227)	11402	300	images/map_vehicles/intercity_bus.gif	NULL
PB11404	2011-02-28 15:12:34.000	42.34591633	-71.03784966	31000	0	140	4	4	4		11404	300	images/map_vehicles/intercity_bus.gif	NULL
PB11405	2011-02-28 15:31:29.000	41.96399166	-70.69723022	31000	0	306	4	4	4		11405	300	images/map_vehicles/intercity_bus.gif	NULL

GeolabID	Datetime	Latitude	Longitude	LatLonAccuracy	PositionSpeed	PositionHeading	SatelliteNumber	SignalStrength	BatteryLevel	Froutename	BusID	TimetoGrey	CustomIcon	DirectionName
BAK0322   	2011-02-28 15:49:05.000	41.7398	-70.50041	31000	14	104	4	4	4	NULL	322	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0326   	2011-02-28 15:49:48.000	41.6548	-70.2853	31000	0	0	4	4	4	NULL	326	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0329   	2011-02-28 15:38:16.000	41.62024	-70.5437	31000	10	304	4	4	4	NULL	329	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0336   	2011-02-28 15:46:18.000	41.65778	-70.27933	31000	0	0	4	4	4	NULL	336	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0343   	2011-02-28 15:30:37.000	41.70896	-70.48839	31000	0	0	4	4	4	NULL	343	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0347   	2011-02-28 15:49:54.000	41.69124	-69.96423	31000	0	0	4	4	4	NULL	347	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0350   	2011-02-28 15:49:22.000	41.74688	-70.04996	31000	9	214	4	4	4	NULL	350	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0351   	2011-02-28 15:49:29.000	41.70813	-70.22968	31000	7	340	4	4	4	NULL	351	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0353   	2011-02-28 15:49:23.000	41.69028	-70.25648	31000	0	0	4	4	4	NULL	353	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0355   	2011-02-28 15:49:02.000	41.6875	-70.21148	31000	5	134	4	4	4	NULL	355	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0356   	2011-02-28 15:49:24.000	41.68976	-70.14426	31000	1	90	4	4	4	NULL	356	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0357   	2011-02-28 15:48:37.000	41.66866	-70.29981	31000	0	0	4	4	4	NULL	357	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0358   	2011-02-28 15:49:11.000	41.69851	-70.23056	31000	0	0	4	4	4	NULL	358	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0363   	2011-02-28 15:48:23.000	41.65116	-70.31718	31000	0	0	4	4	4	NULL	363	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0364   	2011-02-28 15:49:29.000	41.68038	-70.17446	31000	12	70	4	4	4	NULL	364	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0375   	2011-02-28 15:40:32.000	41.68974	-70.14363	31000	1	30	4	4	4	NULL	375	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0379   	2011-02-28 15:26:32.000	41.65785	-70.27934	31000	0	0	4	4	4	NULL	379	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0381   	2011-02-28 15:49:16.000	41.68984	-70.21644	31000	16	84	4	4	4	NULL	381	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0382   	2011-02-28 15:48:39.000	41.67048	-70.2274	31000	11	200	4	4	4	NULL	382	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0383   	2011-02-28 15:48:40.000	41.69033	-70.14358	31000	0	0	4	4	4	NULL	383	300	images/map_vehicles/Bus_20_Blue.png	NULL
BAK0384   	2011-02-28 15:47:19.000	41.66299	-70.286	31000	2	74	4	4	4	NULL	384	300	images/map_vehicles/Bus_20_Blue.png	NULL
GEO0337   	2011-02-28 15:48:04.000	41.68986	-70.14376	31000	0	0	4	4	4	NULL	23	300	images/map_vehicles/bus_024.gif	NULL
GEO0338   	2011-02-28 15:49:54.000	41.6736	-70.30219	31000	0	0	4	4	4	NULL	24	300	images/map_vehicles/bus_024.gif	NULL
GEO0339   	2011-02-28 15:34:10.000	41.68986	-70.14376	31000	0	0	4	4	4	NULL	26	300	images/map_vehicles/bus_024.gif	NULL
GEO0340   	2011-02-28 15:49:00.000	41.68986	-70.14385	31000	0	0	4	4	4	Villager	28	300	images/map_vehicles/bus_024.gif	in
GEO0341   	2011-02-28 15:49:37.000	41.68986	-70.14376	31000	0	0	4	4	4	Villager	29	300	images/map_vehicles/bus_024.gif	out
GEO0389   	2011-02-28 15:49:46.000	41.67546	-70.30151	31000	13	225	4	4	4	Villager	42	300	images/map_vehicles/bus_024.gif	in
GEO1034   	2011-02-28 15:49:48.000	41.87919	-69.98826	31000	44	180	4	4	4	H2O	39	300	images/map_vehicles/bus_024.gif	in
GEO1035   	2011-02-28 15:36:41.000	41.69011	-70.14366	31000	0	0	4	4	4	H2O	40	300	images/map_vehicles/bus_024.gif	out
GEO1036   	2011-02-28 15:49:52.000	41.68426	-70.2583	31000	31	180	4	4	4	Flex	41	300	images/map_vehicles/bus_024.gif	in
GEO6117   	2011-02-28 15:49:54.000	42.0536	-70.19616	31000	0	0	4	4	4	H2O	2	300	images/map_vehicles/bus_024.gif	out
GEO6120   	2011-02-28 15:49:43.000	41.74799	-69.9928	31000	38	225	4	4	4	Sealine	9	300	images/map_vehicles/bus_024.gif	in
GEO6281   	2011-02-28 15:49:42.000	41.7064	-70.02141	31000	17	135	4	4	4	NULL	11	300	images/map_vehicles/bus_024.gif	NULL
GEO6282   	2011-02-28 15:49:51.000	41.86051	-69.97844	31000	35	135	4	4	4	H2O	10	300	images/map_vehicles/bus_024.gif	in
GEO6283   	2011-02-28 15:49:36.000	41.68371	-69.96446	31000	26	135	4	4	4	Flex	4	300	images/map_vehicles/bus_024.gif	in
GEO6284   	2011-02-28 15:49:47.000	41.65786	-70.26481	31000	17	90	4	4	4	Sealine	1	300	images/map_vehicles/bus_024.gif	in
GEO6285   	2011-02-28 15:49:34.000	41.61891	-70.48976	31000	6	45	4	4	4	H2O	3	300	images/map_vehicles/bus_024.gif	in
GEO6297   	2011-02-28 15:24:31.000	41.68959	-70.14355	31000	0	0	4	4	4	NULL	12	300	images/map_vehicles/bus_024.gif	NULL
GEO6298   	2011-02-28 15:49:12.000	41.69011	-70.14341	31000	0	0	4	4	4	Sealine	13	300	images/map_vehicles/bus_024.gif	out
GEO6300   	2011-02-28 15:49:49.000	41.52266	-70.66996	31000	0	0	4	4	4	H2O	15	300	images/map_vehicles/bus_024.gif	out
GEO8226   	2011-02-28 15:49:51.000	41.69039	-70.14284	31000	0	0	4	4	4	NULL	17	300	images/map_vehicles/bus_024.gif	NULL
GEO8227   	2011-02-28 15:49:31.000	41.664	-70.29474	31000	2	180	4	4	4	Sealine	16	300	images/map_vehicles/bus_024.gif	out
GEO8229   	2011-02-28 15:49:29.000	41.66426	-70.29476	31000	0	0	4	4	4	NULL	19	300	images/map_vehicles/bus_024.gif	NULL
GEO8230   	2011-02-28 15:49:37.000	41.66666	-70.18514	31000	2	270	4	4	4	Flex	20	300	images/map_vehicles/bus_024.gif	out

Return Value
0

Please help me someone.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-28 : 16:10:11

Compare the execution plans (between the one with the INSERT and without). Is there a difference?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sabtheleo
Starting Member

10 Posts

Posted - 2011-02-28 : 19:21:55

Hi
These are Execution plan screenshots for with and without INSERT statements

Without INSERT Statement
[url]https://picasaweb.google.com/thelegendoflinas/SQL#5578899167779100754[/url]

With INSERT Statement
[url]https://picasaweb.google.com/thelegendoflinas/SQL#5578899168356464610[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-01 : 12:34:07

We can't see enough of the execution plans to be able to help. The screenshots are cut off due to the scroll bars.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sabtheleo
Starting Member

10 Posts

Posted - 2011-03-01 : 12:42:08

hi tkizer.
thanks for your response..

All the remaining part in execution plan is same in both of them. Thats why I did not mention it. Is there any other way that I can insert this data to that table other than INSERT EXEC

Thanks,
Sabtheleo

sabtheleo
Starting Member

10 Posts

Posted - 2011-03-01 : 12:51:53

hi tkizer,

I have an article about the temporary table and table variable..

[url]http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx[/url]

In this article, the second point says that table variables are not
they are not visible to the INSERT EXEC statements.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-01 : 13:09:15

But have you tested it with a temporary table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sabtheleo
Starting Member

10 Posts

Posted - 2011-03-01 : 13:57:05

Yes I have tested with temporary table It was working fine with other procedures. (Excluding RPC)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-01 : 14:14:45

What is the best performance difference between a temporary table and a table variable for this stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sabtheleo
Starting Member

10 Posts

Posted - 2011-03-01 : 14:27:51

Both have the same performance... I dont see much difference.

sabtheleo
Starting Member

10 Posts

Posted - 2011-03-01 : 14:50:04

Hi tkizer,

I have figured it out the problem is with the direct calling of EXEC function.
I tried using

INSERT INTO @tableBusData SELECT * FROM OPENQUERY([Server-1], 'geolab_mdt2_cape.dbo.sp_SelectAVL_PP')

It gave me the result as I expected.

Thanks for your help
sabtheleo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources