CTEs solution is slower than cursor & recursion

Question

CTEs solution is slower than cursor & recursion

Matjaz Justin

Mr or Mrs. 500

Points: 568
More actions
March 29, 2010 at 7:23 am

#219921

Environment
CREATE TABLE [dbo].[tPA_SetAuthoFunc](
[anCounter] [int] NULL,
[acFuncAbove] [varchar](50) NULL,
[anNo] [int] NOT NULL,
[acFunction] [varchar](50) NOT NULL,
[acName] [varchar](100) NULL,
[acUserFunc] [char](1) NULL,
[acIsParent] [char](1) NULL,
[anUserIns] [int] NULL,
[adTimeIns] [datetime] NULL,
[adTimeChg] [datetime] NULL,
[anUserChg] [int] NULL
CONSTRAINT [kPA_SetAuthoFunc_0] PRIMARY KEY CLUSTERED
(
[acFunction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_anCounter] DEFAULT ((0)) FOR [anCounter]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_acFuncAbove] DEFAULT ('HERMES') FOR [acFuncAbove]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_anNo] DEFAULT ((0)) FOR [anNo]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_acFunction] DEFAULT ('') FOR [acFunction]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_acName] DEFAULT ('') FOR [acName]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_acUserFunc] DEFAULT ('F') FOR [acUserFunc]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_acIsParent] DEFAULT ('T') FOR [acIsParent]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_anUserIns] DEFAULT ((0)) FOR [anUserIns]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_adTimeIns] DEFAULT (getdate()) FOR [adTimeIns]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_adTimeChg] DEFAULT (getdate()) FOR [adTimeChg]
GO
ALTER TABLE [dbo].[tPA_SetAuthoFunc] ADD CONSTRAINT [DF_tPA_SetAuthoFunc_anUserChg] DEFAULT ((0)) FOR [anUserChg]
GO
CREATE TABLE [dbo].[tPA_UserAutho](
[anUserId] [int] NOT NULL,
[acFunction] [varchar](50) NOT NULL,
[acPermit] [char](1) NULL,
[anDaysForCha] [int] NOT NULL,
[anUserIns] [int] NULL,
[adTimeIns] [datetime] NULL,
[anUserChg] [int] NULL,
[adTimeChg] [datetime] NULL,
CONSTRAINT [kPA_UserAutho_0] PRIMARY KEY CLUSTERED
(
[anUserId] ASC,
[acFunction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tPA_UserAutho] CHECK CONSTRAINT [rtPA_UserAutho_tHE_SetSubjContact_1]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_anUserId] DEFAULT ((0)) FOR [anUserId]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_acFunction] DEFAULT ('') FOR [acFunction]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_acPermit] DEFAULT ('0') FOR [acPermit]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_anDaysForCha] DEFAULT ((0)) FOR [anDaysForCha]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_anUserIns] DEFAULT ((0)) FOR [anUserIns]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_adTimeIns] DEFAULT (getdate()) FOR [adTimeIns]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_anUserChg] DEFAULT ((0)) FOR [anUserChg]
GO
ALTER TABLE [dbo].[tPA_UserAutho] ADD CONSTRAINT [DF_tPA_UserAutho_adTimeChg] DEFAULT (getdate()) FOR [adTimeChg]
GO
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(0,'APPLDATALAB',1,'APPL','DataLab Aplikacije','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(1,'APPL',1,'PANTHEON','DataLab PANTHEON','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(2,'PANTHEON',1,'SIF','Nastavitve','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(51,'SIF',2,'SIFART','Šifrant identi','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(77,'SIFART',4,'SIFARTEM','Merske enote','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(84,'SIFART',11,'SIFARTEMBAL','Vrste embalaže / odpadkov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(83,'SIFART',10,'SIFARTEMBALVRSTA','Šifre vrst odpadnega materiala','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(78,'SIFART',5,'SIFARTFORMULA','Formule izračuna cen','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(73,'SIFART',3,'SIFARTKLASIF','Klasifikacija','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(74,'SIFARTKLASIF',2,'SIFARTKLASIFKALK','Kalkulacija','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(75,'SIFARTKLASIF',4,'SIFARTKLASIFPOLPOL','Poljubna polja','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(76,'SIFARTKLASIF',5,'SIFARTKLASIFRAZMN','Razmnoži','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(52,'SIFART',1,'SIFARTMS','Identi','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(53,'SIFARTMS',1,'SIFARTMSCHGPK','Spreminjanje ključa','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(66,'SIFARTMS',16,'SIFARTMSEMBAL','Embalaža','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(57,'SIFARTMS',5,'SIFARTMSKALK','Kalkulacija nabavne cene','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(71,'SIFARTMS',21,'SIFARTMSKLASIF','Avtorizacija klasifikacij','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(68,'SIFARTMS',18,'SIFARTMSKODA','Subjektove kode','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(65,'SIFARTMS',15,'SIFARTMSKOS','Kosovnica','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(59,'SIFARTMS',7,'SIFARTMSOPIS','Opis','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(54,'SIFARTMS',2,'SIFARTMSOSNPOD','Osnovni podatki','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(55,'SIFARTMS',3,'SIFARTMSOSNPODKNJ','Spreminjanje knjigovodskih podatkov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(56,'SIFARTMS',4,'SIFARTMSOSNPODSN','Spreminjanje nač. vodenja serijskih št.','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(62,'SIFARTMS',10,'SIFARTMSPOGCEN','Pogodbeni cenik','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(67,'SIFARTMS',17,'SIFARTMSPOLPOL','Poljubna polja','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(69,'SIFARTMS',19,'SIFARTMSPREGDOK','Pregled dokumentov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(70,'SIFARTMS',20,'SIFARTMSPRENOSPROM','Prenos podatkov med identi','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(58,'SIFARTMS',6,'SIFARTMSPRODCEN','Kalkulacija prodajne cene','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(64,'SIFARTMS',14,'SIFARTMSSERIALNO','Serijske številke','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(61,'SIFARTMS',9,'SIFARTMSSLIKA','Slika','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(60,'SIFARTMS',8,'SIFARTMSTEHPOST','Tehnološki postopek','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(63,'SIFARTMS',11,'SIFARTMSZALOGA','Zaloga','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(82,'SIFART',9,'SIFARTPC','Vnos stalnih cen','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(81,'SIFART',8,'SIFARTPREGPRODCEN','Pregled prodajne cene','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(79,'SIFART',6,'SIFARTSTOLPEC','Predloge serijskih številk','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(80,'SIFART',7,'SIFARTUVOZCEN','Uvoz cenika','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(72,'SIFART',2,'SIFARTVRSTAMS','Vrsta identa','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(3,'SIF',1,'SIFPART','Šifrant subjekti','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(47,'SIFPART',5,'SIFPARTDRZAVA','Države','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(50,'SIFPART',8,'SIFPARTPERINV','Zbirno periodično fakturiranje','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(45,'SIFPART',3,'SIFPARTPOSTA','Poštne številke','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(48,'SIFPART',6,'SIFPARTSIFDEJAVNOST','Šifre dejavnosti','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(49,'SIFPART',7,'SIFPARTSIFSKIS','Šifrant SKIS','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(4,'SIFPART',1,'SIFPARTSUBJ','Subjekti','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(14,'SIFPARTSUBJ',6,'SIFPARTSUBJBANKA','Banka','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(38,'SIFPARTSUBJ',24,'SIFPARTSUBJBLOK','Dovoljena izdaja pri blokiranih subj.','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(5,'SIFPARTSUBJ',1,'SIFPARTSUBJCHGPK','Spreminjanje ključa','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(29,'SIFPARTSUBJ',10,'SIFPARTSUBJDEL','Delavec','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(37,'SIFPARTSUBJ',23,'SIFPARTSUBJDELBLOK','Dovolj.izdaja pri delno blokiranih subj.','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(11,'SIFPARTSUBJ',5,'SIFPARTSUBJDOBAV','Dobavitelj','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(12,'SIFPARTSUBJDOBAV',2,'SIFPARTSUBJDOBAVFINPOD','Finančni podatki','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(13,'SIFPARTSUBJDOBAV',3,'SIFPARTSUBJDOBAVFINPODMENICE','Knjiga menic','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(34,'SIFPARTSUBJ',15,'SIFPARTSUBJKNJSTANJE','Pregled knjigovodskega stanja','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(6,'SIFPARTSUBJ',2,'SIFPARTSUBJKONTOS','Kontaktne osebe','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(8,'SIFPARTSUBJ',4,'SIFPARTSUBJKUPEC','Kupec','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(9,'SIFPARTSUBJKUPEC',2,'SIFPARTSUBJKUPECFINPOD','Finančni podatki','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(10,'SIFPARTSUBJKUPEC',3,'SIFPARTSUBJKUPECFINPODMENICE','Kniga menic','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(15,'SIFPARTSUBJ',7,'SIFPARTSUBJOBCINA','Občina','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(17,'SIFPARTSUBJ',9,'SIFPARTSUBJODD','Oddelek','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(18,'SIFPARTSUBJODD',1,'SIFPARTSUBJODDAdministracija','Administracija','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(19,'SIFPARTSUBJODD',2,'SIFPARTSUBJODDODDELEK 1','ODDELEK 1','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(20,'SIFPARTSUBJODD',3,'SIFPARTSUBJODDODDELEK 2','ODDELEK 2','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(21,'SIFPARTSUBJODD',4,'SIFPARTSUBJODDOsnovna sredstva','Osnovna sredstva','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(22,'SIFPARTSUBJODD',5,'SIFPARTSUBJODDProdaja','Prodaja','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(23,'SIFPARTSUBJODD',6,'SIFPARTSUBJODDProfitni center 1','Profitni center 1','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(24,'SIFPARTSUBJODD',7,'SIFPARTSUBJODDProfitni center 2','Profitni center 2','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(25,'SIFPARTSUBJODD',8,'SIFPARTSUBJODDServis','Servis','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(26,'SIFPARTSUBJODD',9,'SIFPARTSUBJODDSkladiSCe reklamacije','SkladiSCe reklamacije','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(27,'SIFPARTSUBJODD',10,'SIFPARTSUBJODDTajniStvo direktorja','TajniStvo direktorja','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(28,'SIFPARTSUBJODD',11,'SIFPARTSUBJODDUprava','Uprava','T','F',1,'20060125','20100316',0)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(32,'SIFPARTSUBJ',13,'SIFPARTSUBJOPOMBA','Opomba','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(33,'SIFPARTSUBJ',14,'SIFPARTSUBJPOLPOL','Poljubna polja','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(35,'SIFPARTSUBJ',16,'SIFPARTSUBJPREGDOK','Pregled dokumentov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(36,'SIFPARTSUBJ',19,'SIFPARTSUBJPRENOSPROM','Prenos prometov med subjekti','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(7,'SIFPARTSUBJ',3,'SIFPARTSUBJRACUN','Računi pri banki','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(31,'SIFPARTSUBJ',12,'SIFPARTSUBJSINDIKAT','Sindikat','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(16,'SIFPARTSUBJ',8,'SIFPARTSUBJSKLAD','Skladišče','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(39,'SIFPARTSUBJ',25,'SIFPARTSUBJSTEC','Dovoljena izdaja pri subjektih v stečaju','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(40,'SIFPARTSUBJ',26,'SIFPARTSUBJTIP','Avtorizacija tipov subjektov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(30,'SIFPARTSUBJ',11,'SIFPARTSUBJUSTANOVA','Ustanova','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(41,'SIFPART',2,'SIFPARTTIPPART','Tipi subjektov','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(44,'SIFPARTTIPPART',6,'SIFPARTTIPPARTBankDraft','Menice','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(42,'SIFPARTTIPPART',4,'SIFPARTTIPPARTCASSAS','Cassasconto','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(43,'SIFPARTTIPPART',5,'SIFPARTTIPPARTRAZMN','Razmnoži','F','T',1,'20100329','20100329',1)
INSERT INTO dbo.tPA_SetAuthoFunc(anCounter,acFuncAbove,anNo,acFunction,acName,acUserFunc,acIsParent,anUserIns,adTimeIns,adTimeChg,anUserChg) VALUES(46,'SIFPART',4,'SIFPARTVALUTA','Valute','F','T',1,'20100329','20100329',1)
go
INSERT INTO dbo.tPA_UserAutho(anUserId,acFunction,acPermit,anDaysForCha,anUserIns,adTimeIns,anUserChg,adTimeChg) VALUES(999,'SIFARTMS','D',-1,1,'20080416',0,'20100312')
INSERT INTO dbo.tPA_UserAutho(anUserId,acFunction,acPermit,anDaysForCha,anUserIns,adTimeIns,anUserChg,adTimeChg) VALUES(999,'SIFPARTSUBJODD','D',-1,1,'20080416',0,'20100312')
INSERT INTO dbo.tPA_UserAutho(anUserId,acFunction,acPermit,anDaysForCha,anUserIns,adTimeIns,anUserChg,adTimeChg) VALUES(1,'APPL','D',-1,0,'20040113',0,'20100312')
go
CREATE procedure [dbo].[pPA_FillUserAuthorLogOnFill]
@cParent Char(50),
@cMainAvto Char(1),
@nMainDni integer,
@cParentAvto Char(1),
@nParentDni integer,
@cFunkcija Char(50) output,
@cFunkcijaNad Char(50) output,
@cDovoljeno Char(1) output,
@nDniZaSpremembo integer output,
@cUserFunc Char(1) output,
@cNadDovoljeno Char(1) output,
@nNadDniZaSpremembo integer output,
@nFetch Int output
as
declare
@nCounter Int
fetch next from crAvtor into @nCounter, @cFunkcija, @cFunkcijaNad, @cDovoljeno, @nDniZaSpremembo, @cUserFunc, @cNadDovoljeno, @nNadDniZaSpremembo
set @nFetch = @@FETCH_STATUS
while (@nFetch = 0) and (@cParent = @cFunkcijaNad) begin
if (@cFunkcija = 'APPL') and @cMainAvto is null begin
set @cMainAvto = '0'
set @nMainDni = -1
set @cParentAvto = '0'
set @nParentDni = -1
end
if @cNadDovoljeno is null begin
set @cNadDovoljeno = @cParentAvto
set @nNadDniZaSpremembo = @nParentDni
end
if @cDovoljeno is null begin
set @cDovoljeno = @cParentAvto
set @nDniZaSpremembo = @nParentDni
end
insert into #AvtorUser (acFunction, acPermit, anDaysForCha)
values (@cFunkcija, @cDovoljeno, @nDniZaSpremembo)
exec pPA_FillUserAuthorLogOnFill @cFunkcija, @cMainAvto, @nMainDni, @cDovoljeno, @nDniZaSpremembo,
@cFunkcija output, @cFunkcijaNad output, @cDovoljeno output, @nDniZaSpremembo output, @cUserFunc output,
@cNadDovoljeno output, @nNadDniZaSpremembo output, @nFetch output
end
GO
CREATE procedure [dbo].[pPA_FillUserAuthorLogOn]
@nUserId integer,
@cIsLT Char(1)
as
set nocount on
declare
@nFetch Int,
@cFunkcija Char(50),
@cFunkcijaNad Char(50),
@cDovoljeno Char(1),
@nDniZaSpremembo integer,
@cUserFunc Char(1),
@cNadDovoljeno Char(1),
@nNadDniZaSpremembo integer
create table #AvtorUser (acFunction VarChar(50),
acPermit Char(1),
anDaysForCha integer)
if @cIsLT = 'T'
begin
insert into #AvtorUser (acFunction, acPermit, anDaysForCha)
select acFunction, acPermit, anDaysForCha
from tPA_UserAutho
where anUserId = @nUserId and acFunction = 'APPL'
end
else
begin
select @cDovoljeno = acPermit, @nDniZaSpremembo = anDaysForCha
from tPA_UserAutho
where anUserId = @nUserId and acFunction = 'APPL'
declare crAvtor cursor fast_forward for select distinct A.anCounter, A.acFunction, A.acFuncAbove, U.acPermit, U.anDaysForCha, A.acUserFunc, N.acPermit, N.anDaysForCha
from tPA_SetAuthoFunc A left join tPA_UserAutho U on U.acFunction = A.acFunction and U.anUserId = @nUserId
left join tPA_UserAutho N on A.acFuncAbove = N.acFunction and N.anUserId = @nUserId
order by A.anCounter
open crAvtor
set @nFetch = 0
exec pPA_FillUserAuthorLogOnFill 'APPLDATALAB', @cDovoljeno, @nDniZaSpremembo, @cDovoljeno, @nDniZaSpremembo,
@cFunkcija output, @cFunkcijaNad output, @cDovoljeno output, @nDniZaSpremembo output, @cUserFunc output,
@cNadDovoljeno output, @nNadDniZaSpremembo output, @nFetch output
close crAvtor
deallocate crAvtor
end
select acFunction, acPermit, anDaysForCha
from #AvtorUser
GO
create procedure dbo.pPA_FillUserAuthorLogOn6
@nUserIDsmallint,
@cIsLTchar(1) = 'F'
as
set nocount on
if @cIsLT = 'T'
begin
select acFunction, acPermit, anDaysForCha
from tPA_UserAutho
where anUserId = @nUserId and acFunction = 'APPL'
return
end
create table #tmp_menu(
acFuncAbove varchar(50) not null,
acFunction varchar(50) not null,
acPermit char(1) null,
anDaysForCha smallint null
)
insert #tmp_menu(acFuncAbove, acFunction, acPermit, anDaysForCha)
select f.acFuncAbove, f.acFunction, ua.acPermit, ua.anDaysForCha
from tPA_SetAuthoFunc f left join tPA_UserAutho ua on
ua.acFunction = f.acFunction and
ua.anUserId = @nUserID;
create unique index #idx_temp_menu on #tmp_menu(acFunction, acFuncAbove) include(acPermit, anDaysForCha)
--CTE Query
;WITH MenuCTE(acFunction, acFuncAbove, acPermit, anDaysForCha)
AS
(
select acFunction, acFuncAbove, cast(isnull(acPermit, '0') as char(1)), cast(isnull(anDaysForCha, -1) as smallint)
from #tmp_menu
where acFuncAbove = 'APPLDATALAB'
union all
select F.acFunction, F.acFuncAbove,
cast(coalesce(F.acPermit, m.acPermit, '0') as char(1)),
cast(coalesce(F.anDaysForCha, m.anDaysForCha, -1) as smallint)
from #tmp_menu F join MenuCTE m on
m.acFunction = F.acFuncAbove
)
select m.acFunction, m.acPermit, m.anDaysForCha
from MenuCTE m
go
--------------------------------------------------------------------------------------------------------------------------------------
exec pPA_FillUserAuthorLogOn6 999, 'F'
exec pPA_FillUserAuthorLogOn 999, 'F'
Execution procedures above give an unexpected result. CTEs solution is slower than cursor & recursion. Look picture from profile. How is that possible?
Background: table tPA_SetAuthoFunc contains a tree of authorizations. tPA_UserAutho contains permissions for a user from a leaf under the tree down. User #1 has all possible permissions from a top of the tree of authorizations. User #999 has only permissions for ‘SIFARTMS' and 'SIFPARTSUBJODD' and permissions bellow.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

Unfortunatly, I don't have much time to look at this right now. But, one quick thing that might help to improve the CTE is to get rid of the temp table and make that a CTE also. for example: WITH tmpMenu

AS

(

--insert #tmp_menu(acFuncAbove, acFunction, acPermit, anDaysForCha)

select f.acFuncAbove, f.acFunction, ua.acPermit, ua.anDaysForCha

from tPA_SetAuthoFunc f left join tPA_UserAutho ua on

ua.acFunction = f.acFunction and

ua.anUserId = @user-id

),

MenuCTE(acFunction, acFuncAbove, acPermit, anDaysForCha)

AS

(

select

acFunction,

acFuncAbove,

cast(isnull(acPermit, '0') as char(1)),

cast(isnull(anDaysForCha, -1) as smallint)

from tmpMenu

where acFuncAbove = 'APPLDATALAB'

union all

select F.acFunction, F.acFuncAbove,

cast(coalesce(F.acPermit, m.acPermit, '0') as char(1)),

cast(coalesce(F.anDaysForCha, m.anDaysForCha, -1) as smallint)

from tmpMenu F join MenuCTE m on

m.acFunction = F.acFuncAbove

)

select m.acFunction, m.acPermit, m.anDaysForCha

from MenuCTE m

WayneS SSC Guru Points: 95409 More actions · Answer 2

I added this index: CREATE INDEX [IX_tPA_SetAuthoFunc_acFuncAbove] ON dbo.tPA_SetAuthoFunc (acFuncAbove)

I changed the CTE procedure to this:create procedure dbo.pPA_FillUserAuthorLogOn6

@nUserID smallint,

@cIsLT char(1) = 'F'

as

set nocount on

if @cIsLT = 'T'

begin

select acFunction, acPermit, anDaysForCha

from tPA_UserAutho

where anUserId = @nUserId and acFunction = 'APPL'

return

end

--create table #tmp_menu(

-- acFuncAbove varchar(50) not null,

-- acFunction varchar(50) not null,

-- acPermit char(1) null,

-- anDaysForCha smallint null

--)

--insert #tmp_menu(acFuncAbove, acFunction, acPermit, anDaysForCha)

-- select f.acFuncAbove, f.acFunction, ua.acPermit, ua.anDaysForCha

-- from tPA_SetAuthoFunc f left join tPA_UserAutho ua on

-- ua.acFunction = f.acFunction and

-- ua.anUserId = @nUserID;

--create unique index #idx_temp_menu on #tmp_menu(acFunction, acFuncAbove) include(acPermit, anDaysForCha)

--CTE Query

;WITH TempTblCTE (acFuncAbove, acFunction, acPermit, anDaysForCha)

AS

(

select f.acFuncAbove, f.acFunction, ua.acPermit, ua.anDaysForCha

from tPA_SetAuthoFunc f left join tPA_UserAutho ua on

ua.acFunction = f.acFunction and

ua.anUserId = @nUserID

)

,MenuCTE(acFunction, acFuncAbove, acPermit, anDaysForCha)

AS

(

select acFunction, acFuncAbove, cast(isnull(acPermit, '0') as char(1)), cast(isnull(anDaysForCha, -1) as smallint)

from TempTblCTE --#tmp_menu

where acFuncAbove = 'APPLDATALAB'

union all

select F.acFunction, F.acFuncAbove,

cast(coalesce(F.acPermit, m.acPermit, '0') as char(1)),

cast(coalesce(F.anDaysForCha, m.anDaysForCha, -1) as smallint)

from TempTblCTE F --#tmp_menu F

join MenuCTE m on

m.acFunction = F.acFuncAbove

)

select m.acFunction, m.acPermit, m.anDaysForCha

from MenuCTE m

go

I changed the execution of the procs as follows to send the results to a temp table, and to store the times of execution to another temp table. The results were sent to a temp table to keep everything within sql; sending output to a screen now involves other parts of your system.

if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (acFunction varchar(50), acPermit char(1), anDaysForCha smallint)

if object_id('tempdb..#times') IS NOT NULL DROP TABLE #times

CREATE TABLE #Times (RowID INT IDENTITY PRIMARY KEY CLUSTERED, StartDate datetime, ProcName varchar(50))

GO

insert into #Times select GetDate(), 'pPA_FillUserAuthorLogOn6'

insert into #Temp exec pPA_FillUserAuthorLogOn6 999, 'F'

insert into #Times select GetDate(), 'pPA_FillUserAuthorLogOn'

insert into #Temp exec pPA_FillUserAuthorLogOn 999, 'F'

insert into #Times select GetDate(), NULL

GO 50 -- <<<< run this block of code 50 times

-- display results

;with CTE AS

(

select RowNbr = ROW_NUMBER() OVER (ORDER BY t1.RowID),

t1.RowID,

t1.StartDate,

[Elapsed]= DateDiff(ms, t1.StartDate, t2.StartDate),

t1.ProcName

from #Times t1

LEFT JOIN #Times t2 ON t2.RowID = t1.RowID + 1

where t1.ProcName IS NOT NULL

)

select t1.StartDate,

t1.[Elapsed],

t1.ProcName,

[Delta (ms)] = case when t1.RowNbr % 2 = 0 then t2.Elapsed - t1.Elapsed else NULL end

from CTE t1

LEFT JOIN CTE t2 ON t1.RowNbr = t2.RowNbr + 1

order by t1.RowID

The results that I get show that the worst the CTE ever did was the same as the cursor, and this was 14 out of 50 runs. The rest of the time the cursor was slower; on average the cursor took 3 times as long.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Matjaz Justin Mr or Mrs. 500 Points: 568 More actions · Answer 3

Matjaz Justin

Mr or Mrs. 500

Points: 568

March 31, 2010 at 12:24 am

#1142855

Thank you. Another CTEs makes query 3 times faster.