مقایسه سرعت جدول موقت و جدول متغیری

[mehdis2]

سلام میخواستم بدونم کدومشون سرعتش بهتره و کلا سرعت کرسر ها هم چطوره؟ مرسی

[esmit61]

متغیر نوع جدول سریعتر است.
استفاده از کرسر به شدت سرعت رو کاهش میده. حتی الامکان ازش استفاده نکنید

[mehdis2]

مرسی ولی من بجای کرسر از همون بقول بچه ها set base استفاده کردم بدون کرسر که سرعت خیلی بدتر شد .من دو تا کرسر تودر تو باید بنویسم بانکم 1میلیون رکورد داره
اینو براش نوشتم
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Rep_Day_Result]
@EXPDate_Start nvarchar(10),
@EXPDate_End nvarchar(10),
@PGMyKey INT,
@EXPGMyKey INT,
@InsMyKey INT ,
@Rows_Inst INT OUTPUT
AS
BEGIN

SET NOCOUNT ON;
declare @EXPTime_master nchar(10);

declare @EXPInsName_master nchar(10);
declare @EXPTime nchar(10);
declare @EXPValue nchar(10);
declare @EXPTName nchar(10);
declare @EXPInsName nchar(10);
DECLARE @F1 nchar(10);
DECLARE @F2 nchar(10);
DECLARE @F3 nchar(10);
DECLARE @F4 nchar(10);

DECLARE @F5 nchar(10);
DECLARE @F6 nchar(10);
DECLARE @F7 nchar(10);
DECLARE @F8 nchar(10);
DECLARE @F9 nchar(10);
DECLARE @F10 nchar(10);
DECLARE @F11 nchar(10);
DECLARE @F12 nchar(10);
DECLARE @F13 nchar(10);
DECLARE @F14 nchar(10);

DECLARE @F15 nchar(10);

DECLARE @test INT;


DECLARE @P nchar(10);
DECLARE @Row int;
DECLARE @Row_Number int;
DECLARE @EXPGName1 nchar(20);
DECLARE @EXPGName2 nchar(20);
DECLARE @EXPGName3 nchar(20);
DECLARE @EXPGName4 nchar(20);

DECLARE @EXPGName5 nchar(20);
DECLARE @EXPGName6 nchar(20);
DECLARE @EXPGName7 nchar(20);
DECLARE @EXPGName8 nchar(20);
DECLARE @EXPGName9 nchar(20);
DECLARE @EXPGName10 nchar(20);
DECLARE @Name nchar(20);
DECLARE @EXPGName11 nchar(20);
DECLARE @EXPGName12 nchar(20);
DECLARE @EXPGName13 nchar(20);
DECLARE @EXPGName14 nchar(20);

DECLARE @EXPGName15 nchar(20);IF EXISTS (SELECT * FROM tem_rep )
BEGIN
DELETE FROM ExpRep_Day_Result_Tittle_Tmp
DELETE FROM tem_rep
END

SET @test=0;
Create table #All_EXPTName
([InsMyKey] INT PRIMARY KEY ,
[F] [nchar](5) ,
)
DECLARE @EXPTMyKey_cr nchar(5);
DECLARE @EXPTName_cr nchar(20);
DECLARE @Number_Filed INT;
SET @Number_Filed=1
DECLARE cr_All_EXPTName CURSOR FOR SELECT DISTINCT EXPTMyKey, EXPTName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start AND
EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPValue IS NOT NULL
ORDER BY EXPTName
OPEN cr_All_EXPTName


FETCH NEXT FROM cr_All_EXPTName INTO @EXPTMyKey_cr,@EXPTName_cr
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTo #All_EXPTName
(InsMyKey, F) VALUES ( @EXPTMyKey_cr,@Number_Filed)

IF (@Number_Filed=1)
set @EXPGName1=@EXPTName_cr
ELSE IF (@Number_Filed=2)
set @EXPGName2=@EXPTName_cr
IF (@Number_Filed=3)
set @EXPGName3=@EXPTName_cr
IF (@Number_Filed=4)
set @EXPGName4=@EXPTName_cr
IF (@Number_Filed=5)
set @EXPGName5=@EXPTName_cr
IF (@Number_Filed=6)
set @EXPGName6=@EXPTName_cr
IF (@Number_Filed=7)
set @EXPGName7=@EXPTName_cr
IF (@Number_Filed=8)
set @EXPGName8=@EXPTName_cr

IF (@Number_Filed=9)
set @EXPGName9=@EXPTName_cr
IF (@Number_Filed=10)
set @EXPGName10=@EXPTName_cr
IF (@Number_Filed=11)
set @EXPGName11=@EXPTName_cr
IF (@Number_Filed=12)
set @EXPGName12=@EXPTName_cr
IF (@Number_Filed=13)
set @EXPGName13=@EXPTName_cr
IF (@Number_Filed=14)
set @EXPGName14=@EXPTName_cr
IF (@Number_Filed=15)
set @EXPGName15=@EXPTName_cr



FETCH NEXT FROM cr_All_EXPTName INTO @EXPTMyKey_cr,@EXPTName_cr
SET @Number_Filed=@Number_Filed+1
END
CLOSE cr_All_EXPTName
DEALLOCATE cr_All_EXPTName

DECLARE @EXPDate nchar(10);
SET @EXPDate='تاريخ'
INSERT INTO ExpRep_Day_Result_Tittle_Tmp
(
F1,F2,F3,F4,F5, F6,F7,F8,F9,F10, F11, F12, F13, F14, F15

)
VALUES
(
@EXPDate,'ساعت',@EXPGName1,@EXPGName2,@EXPGNam e3,@EXPGName4,@EXPGName5,@EXPGName6,@EXPGName7,@EX PGName8,@EXPGName9,@EXPGName10,@EXPGName11,@EXPGNa me12,@EXPGName13
)



iF (@InsMyKey IS NULL)
BEGIN
DECLARE cr_Time CURSOR FORWARD_ONLY FOR SELECT DISTINCT EXPDate,EXPTime,EXPInsName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start
AND EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey IS NULL AND EXPValue IS NOT NULL ORDER BY EXPDate,EXPTime
SET @test=1;
END
ELSE IF (1=1)
BEGIN
SET @test=2;
DECLARE cr_Time CURSOR FORWARD_ONLY FOR SELECT DISTINCT EXPDate,EXPTime,EXPInsName FROM TempRep1 WHERE EXPDate>=@EXPDate_Start
AND EXPDate<=@EXPDate_end and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey ORDER BY EXPDate,EXPTime
END


OPEN cr_Time
FETCH NEXT FROM cr_Time INTO @EXPDate,@EXPTime_master,@EXPInsName_master
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Row=1
SET SET @Row=1
-- detal
IF (@InsMyKey IS NULL)
BEGIN

DECLARE cr CURSOR FORWARD_ONLY FOR SELECT [EXPTime],[EXPValue],[EXPTName] ,[EXPTMyKey]
FROM TempRep1 WHERE EXPDate=@EXPDate and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey IS NULL AND EXPTime=@EXPTime_master
ORDER BY EXPTName

END
ELSE IF (1=1)
BEGIN
DECLARE @EXPTMyKey_cr_detail int;

DECLARE cr CURSOR FOR SELECT [EXPTime],[EXPValue],[EXPTName] ,[EXPTMyKey]
FROM TempRep1 WHERE EXPDate=@EXPDate and
pgmykey=@PGMyKey AND EXPGMyKey=@EXPGMyKey AND EXPInsMyKey=@InsMyKey AND EXPTime=@EXPTime_master AND EXPValue IS NOT NULL
ORDER BY EXPTName

END

SET @F1=NULL SET @F2=NULL SET @F3=NULL SET @F4=NULL SET @F5=NULL SET @F6=NULL SET @F7=NULL SET @F8=NULL SET @F9=NULL SET @F10=NULL SET @F11=NULL SET @F12=NULL SET @F13=NULL SET @F14=NULL SET @F15=NULL
OPEN cr
FETCH NEXT FROM cr INTO @EXPTime,@EXPValue,@Name,@EXPTMyKey_cr_detail
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Row=ae.F FROM #All_EXPTName ae WHERE ae.InsMyKey=@EXPTMyKey_cr_detail

IF (@Row=1)
BEGIN
SET @F1=@EXPValue

END
ELSE IF (@Row=2)
BEGIN
SET @F2=@EXPValue
END
ELSE IF (@Row=3)
BEGIN
SET @F3=@EXPValue
END
ELSE IF (@Row=4)
BEGIN
SET @F4=@EXPValue
END
ELSE IF (@Row=5)
BEGIN
SET @F5=@EXPValue
END
ELSE IF (@Row=6)
BEGIN
SET @F6=@EXPValue
END
ELSE IF (@Row=7)
BEGIN
SET @F7=@EXPValue
END
ELSE IF (@Row=8)
BEGIN
SET @F8=@EXPValue
END
ELSE IF (@Row=9)
BEGIN
SET @F9=@EXPValue
END
ELSE IF (@Row=10)
BEGIN
SET @F10=@EXPValue
END
ELSE IF (@Row=11)
BEGIN
SET @F11=@EXPValue
END
ELSE IF (@Row=12)
BEGIN
SET @F12=@EXPValue
END
ELSE IF (@Row=13)
BEGIN
SET @F13=@EXPValue
END
ELSE IF (@Row=14)
BEGIN
SET @F14=@EXPValue
END
ELSE IF (@Row=15)
BEGIN
SET @F15=@EXPValue
END


FETCH NEXT FROM cr INTO @EXPTime,@EXPValue,@Name,@EXPTMyKey_cr_detail
END
CLOSE cr
DEALLOCATE cr

SET @Row_Number=0;
INSERT INTO tem_rep
(
F1,F2,F3,F4,F5,F6,F7,F8,F9, F10, F11, F12, F13, F14, F15
)
VALUES
(
@EXPDate,@EXPTime_master,@F1,@F2,@F3,@F4,@F5,@F6,@ F7,@F8,@F9,@F10,@F11,@F12,@F13
)

FETCH NEXT FROM cr_Time INTO @EXPDate,@EXPTime_master,@EXPInsName_master
END
CLOSE cr_Time
DEALLOCATE cr_Time




DECLARE @cout_Fild_Exp INT ;
SELECT @cout_Fild_Exp=COUNT(*) FROM #All_EXPTName
IF (@cout_Fild_Exp=1)
SELECT F1,F2 FROM tem_rep
ELSE IF (@cout_Fild_Exp=2)
SELECT F1,F2,F3 FROM tem_rep
ELSE IF (@cout_Fild_Exp=3)
SELECT F1,F2,F3,F4 FROM tem_rep
IF (@cout_Fild_Exp=4)
SELECT F1,F2,F3,F4,F5 FROM tem_rep
ELSE IF (@cout_Fild_Exp=5)
SELECT F1,F2,F3,F4,F5,F6 FROM tem_rep
ELSE IF (@cout_Fild_Exp=6)
SELECT F1,F2,F3,F4,F5,F6,F7 FROM tem_rep
ELSE IF (@cout_Fild_Exp=7)
SELECT F1,F2,F3,F4,F5,F6,F7,F8 FROM tem_rep
ELSE IF (@cout_Fild_Exp=8)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9 FROM tem_rep
ELSE IF (@cout_Fild_Exp=9)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 FROM tem_rep
ELSE IF (@cout_Fild_Exp=10)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11 FROM tem_rep
ELSE IF (@cout_Fild_Exp=11)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12 FROM tem_rep
ELSE IF (@cout_Fild_Exp=12)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13 FROM tem_rep
ELSE IF (@cout_Fild_Exp=13)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13,F14 FROM tem_rep
ELSE IF (@cout_Fild_Exp=14)
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13,F14, F15 FROM tem_rep
ELSE
SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11, F12, F13,F14, F15 FROM tem_rep
SET @Rows_Inst=@Row


END