April 28, 2005 at 7:45 am
Can't post execution plan - too big!
NB: I can't do anything to this SQL because it is 3rd party. I believe the slow running proc to be proc_NavStructAddNewNodeByUrl which is called multiple times in this batch.
Also, remember all I really want to know is there more than one way to carry out an execution plan?
declare @P1 int
set @P1=2000
declare @P2 int
set @P2=0
declare @P3 int
set @P3=0
declare @P4 int
set @P4=0
declare @P5 int
set @P5=0
declare @P6 int
set @P6=0
declare @P7 int
set @P7=0
declare @P8 int
set @P8=0
declare @P9 int
set @P9=0
declare @P10 int
set @P10=0
declare @P11 int
set @P11=0
declare @P12 int
set @P12=0
exec sp_executesql N'DECLARE @abort int
SET @abort = 0
DECLARE @EidBase int,@EidHome int
SET @EidBase = 0
SET @EidHome = NULL
BEGIN TRAN
EXEC @abort = proc_NavStructAllocateEidBlockWebId ''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',12,1007,@EidBase OUTPUT
SELECT @P1 = @EidBase, @P2 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1000,0,-2,0,N''qmh/cases/0042/case042237/default.aspx'',N''Home'',NULL,0,0,NULL,@EidBase,@EidHome OUTPUT
SELECT @P3 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1002,0,-2,0,N'''',N''SharePoint Top Navbar'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P4 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',6,1002,-2,0,N''qmh/cases/0042/case042237/default.aspx'',N''Home'',NULL,0,0,NULL,@EidBase,@EidHome OUTPUT
SELECT @P5 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',8,1002,-2,1,N''javascript:HelpWindowKey("NavBarHelpHome")'',N''Help'',NULL,0,0,NULL,@EidBase,@EidHome OUTPUT
SELECT @P6 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1004,0,-2,0,N'''',N''Documents'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P7 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',12,1004,-2,1,N''http://paper'',N''PaPER'',NULL,0,0,NULL,@EidBase,@EidHome OUTPUT
SELECT @P8 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1005,0,-2,0,N'''',N''Pictures'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P9 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1003,0,-2,0,N'''',N''Lists'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P10 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1006,0,-2,0,N'''',N''Discussions'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P11 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
BEGIN TRAN
EXEC @abort = proc_NavStructAddNewNodeByUrl ''37F905B1-2175-4141-8186-B77B61D2195F'',''1DE5E1C2-38F1-4739-A703-906B6CEA3BEB'',1007,0,-2,0,N'''',N''Surveys'',NULL,0,1,NULL,@EidBase,@EidHome OUTPUT
SELECT @P12 = @abort
IF @abort <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
', N'@P1 int OUTPUT,@P2 int OUTPUT,@P3 int OUTPUT,@P4 int OUTPUT,@P5 int OUTPUT,@P6 int OUTPUT,@P7 int OUTPUT,@P8 int OUTPUT,@P9 int OUTPUT,@P10 int OUTPUT,@P11 int OUTPUT,@P12 int OUTPUT', @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, @P12 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12
April 28, 2005 at 8:00 am
You can't carry out same execution plan in different way as it is controlled by optimizer. But what you can do is to force optimizer to generate different execution plan and run it by using "hints"
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply