July 31, 2008 at 1:20 am
Hello,
I'm a newbie in developing stored procedures. Therefor this question: Can the script below be more optimized?
I wrote it yesterday and I'm not sure that this is the correct way to do so.
The view: View vw_VBR
VBR_ID int
VDR_ID int
Cor1, ..., Cor13 BIT (TRUE=Correction Needed)
Tempr1, ...., Tempr13 Float (Value that needs a correction if CorX = TRUE and TemprX <> 0 )
Temps1, ..., Temps13 float (Value that needs a correction if CorX = TRUE and TempsX <> 0 )
Tempreti float (Correction value for TemprX)
Tempsti float (Correction value for TempsX)
(Other not needed columns for this script)
ALTER PROCEDURE [dbo].[ups_VBR]
-- Add the parameters for the stored procedure here
@VBR_ID int = 0,
@VDR_ID int = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Teller int;
declare @TempRetour nchar(7);
declare @TempSteek nchar(7);
declare @RequireCorrection bit;
declare @temps float;
declare @Tempr float;
-- Insert statements for procedure here
SELECT * INTO #TMP_VBR FROM vw_vbr where VBR_ID = @VBR_ID AND VDR_ID = @VDR_ID;
-- the right row is in #TMP_VBR
-- loop through all corrections 1->13
set @teller = 1;
while @teller <= 13 begin
print '--START--';
print @teller;
-- Getting the right columns by the help of @teller
if @teller = 1 declare Cor_Cursor CURSOR FOR SELECT cor1, tempr1, temps1 FROM #TMP_VBR
if @teller = 2 declare Cor_Cursor CURSOR FOR SELECT cor2, tempr2, temps2 FROM #TMP_VBR
if @teller = 3 declare Cor_Cursor CURSOR FOR SELECT cor3, tempr3, temps3 FROM #TMP_VBR
if @teller = 4 declare Cor_Cursor CURSOR FOR SELECT cor4, tempr4, temps4 FROM #TMP_VBR
if @teller = 5 declare Cor_Cursor CURSOR FOR SELECT cor5, tempr5, temps5 FROM #TMP_VBR
if @teller = 6 declare Cor_Cursor CURSOR FOR SELECT cor6, tempr6, temps6 FROM #TMP_VBR
if @teller = 7 declare Cor_Cursor CURSOR FOR SELECT cor7, tempr7, temps7 FROM #TMP_VBR
if @teller = 8 declare Cor_Cursor CURSOR FOR SELECT cor8, tempr8, temps8 FROM #TMP_VBR
if @teller = 9 declare Cor_Cursor CURSOR FOR SELECT cor9, tempr9, temps9 FROM #TMP_VBR
if @teller = 10 declare Cor_Cursor CURSOR FOR SELECT cor10, tempr10, temps10 FROM #TMP_VBR
if @teller = 11 declare Cor_Cursor CURSOR FOR SELECT cor11, tempr11, temps11 FROM #TMP_VBR
if @teller = 12 declare Cor_Cursor CURSOR FOR SELECT cor12, tempr12, temps12 FROM #TMP_VBR
if @teller = 13 declare Cor_Cursor CURSOR FOR SELECT cor13, tempr13, temps13 FROM #TMP_VBR
-- opening cursor (pointer)
open cor_cursor
-- move to first record and dump the values in variables
fetch next from cor_cursor into @requirecorrection, @tempr, @temps
-- Is CorX = TRUE
if @RequireCorrection = 1
BEGIN
print 'Correction needed'
-- preparing columnnames
SET @TempRetour = N'tempr' + CONVERT(nchar(3), @teller )
set @TempSteek = N'temps' + CONVERT(nchar(3), @teller )
-- Is temprX not 0
if @tempr != 0
Begin
-- Add tempreti to temprX
execute ('UPDATE #TMP_VBR SET ' + @tempretour + ' = ' + @tempretour + ' + tempreti')
Print 'Temp Retour corrected'
end
else
print 'Temp retour = 0';
-- Is tempsX not 0
if @temps != 0
begin
-- Add tempsti to tempsX
execute ('UPDATE #TMP_VBR SET ' + @tempsteek + ' = ' + @tempsteek + ' + tempsti')
print 'Temp Steek corrected'
end
else
print 'Temp steek = 0';
END
else
print 'No correction needed'
-- closing cursor
close cor_cursor
-- release the memory
deallocate cor_cursor
print '--EINDE--';
set @teller = @teller + 1;
end
-- send the result to the caller
select * from #TMP_VBR;
-- cleaning up
drop table #TMP_VBR;
END
Friendly regards
Jo
July 31, 2008 at 1:55 am
Hi Jo
You could convert the cursor-based update of the temp table to a set-based update like this:
UPDATE #TMP_VBR
SET
tempr1 = CASE WHEN tempr1 <> 0 THEN tempr1 + tempreti ELSE tempr1 END,
temps1 = CASE WHEN tempr1 <> 0 THEN
CASE WHEN temps1 <> 0 THEN temps1 + tempsti ELSE temps1 END
ELSE temps1 END
WHERE cor1 = 1
- of course, this is only for teller #1, you would need 13 such statements or use dynamic sql within a WHILE loop.
Personally, I'd code all of the adjustments into the SELECT for the temp table. It will generate a long statement but that doesn't mean it will run slowly. If you provide a full column list for the temp table #TMP_VBR I'll start you off.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 2:16 am
Hi Chris,
I'm using a view on two tables:
ALTER VIEW [dbo].[vw_VBR]
AS
SELECT dbo.VBR.ID AS VBR_ID, dbo.VDR.ID AS VDR_ID, dbo.VBR.ret1, dbo.VBR.ret2, dbo.VBR.ret3, dbo.VBR.ret4, dbo.VBR.ret5, dbo.VBR.ret6, dbo.VBR.ret7,
dbo.VBR.ret8, dbo.VBR.ret9, dbo.VBR.ret10, dbo.VBR.ret11, dbo.VBR.ret12, dbo.VBR.ret13, dbo.VBR.cor1, dbo.VBR.cor2, dbo.VBR.cor3, dbo.VBR.cor4,
dbo.VBR.cor5, dbo.VBR.cor6, dbo.VBR.cor7, dbo.VBR.cor8, dbo.VBR.cor9, dbo.VBR.cor10, dbo.VBR.cor11, dbo.VBR.cor12, dbo.VBR.cor13,
dbo.VBR.pauze1, dbo.VBR.pauze2, dbo.VBR.pauze3, dbo.VBR.pauze4, dbo.VBR.pauze5, dbo.VBR.pauze6, dbo.VBR.pauze7, dbo.VBR.pauze8,
dbo.VBR.pauze9, dbo.VBR.pauze10, dbo.VBR.pauze11, dbo.VBR.pauze12, dbo.VBR.pauze13, dbo.VBR.bron1, dbo.VBR.bron2, dbo.VBR.bron3,
dbo.VBR.bron4, dbo.VBR.bron5, dbo.VBR.bron6, dbo.VBR.bron7, dbo.VBR.bron8, dbo.VBR.bron9, dbo.VBR.bron10, dbo.VBR.bron11, dbo.VBR.bron12,
dbo.VBR.bron13, dbo.VBR.tijd1, dbo.VBR.tijd2, dbo.VBR.tijd3, dbo.VBR.tijd4, dbo.VBR.tijd5, dbo.VBR.tijd6, dbo.VBR.tijd7, dbo.VBR.tijd8, dbo.VBR.tijd9,
dbo.VBR.tijd10, dbo.VBR.tijd11, dbo.VBR.tijd12, dbo.VBR.tijd13, dbo.VBR.cs1 * 10 AS cs1, dbo.VBR.cs2 * 10 AS cs2, dbo.VBR.cs3 * 10 AS cs3,
dbo.VBR.cs4 * 10 AS cs4, dbo.VBR.cs5 * 10 AS cs5, dbo.VBR.cs6 * 10 AS cs6, dbo.VBR.cs7 * 10 AS cs7, dbo.VBR.cs8 * 10 AS cs8,
dbo.VBR.cs9 * 10 AS cs9, dbo.VBR.cs10 * 10 AS cs10, dbo.VBR.cs11 * 10 AS cs11, dbo.VBR.cs12 * 10 AS cs12, dbo.VBR.cs13 * 10 AS cs13,
dbo.VBR.tempr1 * 10 AS tempr1, dbo.VBR.tempr2 * 10 AS tempr2, dbo.VBR.tempr3 * 10 AS tempr3, dbo.VBR.tempr4 * 10 AS tempr4,
dbo.VBR.tempr5 * 10 AS tempr5, dbo.VBR.tempr6 * 10 AS tempr6, dbo.VBR.tempr7 * 10 AS tempr7, dbo.VBR.tempr8 * 10 AS tempr8,
dbo.VBR.tempr9 * 10 AS tempr9, dbo.VBR.tempr10 * 10 AS tempr10, dbo.VBR.tempr11 * 10 AS tempr11, dbo.VBR.tempr12 * 10 AS tempr12,
dbo.VBR.tempr13 * 10 AS tempr13, dbo.VBR.temps1 * 10 AS temps1, dbo.VBR.temps2 * 10 AS temps2, dbo.VBR.temps3 * 10 AS temps3,
dbo.VBR.temps4 * 10 AS temps4, dbo.VBR.temps5 * 10 AS temps5, dbo.VBR.temps6 * 10 AS temps6, dbo.VBR.temps7 * 10 AS temps7,
dbo.VBR.temps8 * 10 AS temps8, dbo.VBR.temps9 * 10 AS temps9, dbo.VBR.temps10 * 10 AS temps10, dbo.VBR.temps11 * 10 AS temps11,
dbo.VBR.temps12 * 10 AS temps12, dbo.VBR.temps13 * 10 AS temps13, dbo.VDR.vdrtijd, dbo.VDR.tempsti * 10 AS tempsti,
dbo.VDR.tempreti * 10 AS tempreti
FROM dbo.VBR CROSS JOIN
dbo.VDR
Table VDR:
CREATE TABLE [dbo].[VDR](
[ID] [int] IDENTITY(1,1) NOT NULL,
[vdrnaam] [nvarchar](50) NOT NULL,
[vdrtijd] [int] NOT NULL,
[tempreti] [float] NOT NULL,
[tempsti] [float] NOT NULL,
[Aanmaak] [datetime] NOT NULL CONSTRAINT [DF_VDR_Aanmaak] DEFAULT (getdate()),
[Gewijzigd] [datetime] NOT NULL CONSTRAINT [DF_VDR_Gewijzigd] DEFAULT (getdate()),
CONSTRAINT [PK_VDR] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table VBR:
CREATE TABLE [dbo].[VBR](
[ID] [int] IDENTITY(1,1) NOT NULL,
[vbrnaam] [nvarchar](16) NOT NULL,
[ret1] [bit] NOT NULL,
[ret2] [bit] NOT NULL,
[ret3] [bit] NOT NULL,
[ret4] [bit] NOT NULL,
[ret5] [bit] NOT NULL,
[ret6] [bit] NOT NULL,
[ret7] [bit] NOT NULL,
[ret8] [bit] NOT NULL,
[ret9] [bit] NOT NULL,
[ret10] [bit] NOT NULL,
[ret11] [bit] NOT NULL,
[ret12] [bit] NOT NULL,
[ret13] [bit] NOT NULL,
[cor1] [bit] NOT NULL,
[cor2] [bit] NOT NULL,
[cor3] [bit] NOT NULL,
[cor4] [bit] NOT NULL,
[cor5] [bit] NOT NULL,
[cor6] [bit] NOT NULL,
[cor7] [bit] NOT NULL,
[cor8] [bit] NOT NULL,
[cor9] [bit] NOT NULL,
[cor10] [bit] NOT NULL,
[cor11] [bit] NOT NULL,
[cor12] [bit] NOT NULL,
[cor13] [bit] NOT NULL,
[pauze1] [int] NOT NULL,
[pauze2] [int] NOT NULL,
[pauze3] [int] NOT NULL,
[pauze4] [int] NOT NULL,
[pauze5] [int] NOT NULL,
[pauze6] [int] NOT NULL,
[pauze7] [int] NOT NULL,
[pauze8] [int] NOT NULL,
[pauze9] [int] NOT NULL,
[pauze10] [int] NOT NULL,
[pauze11] [int] NOT NULL,
[pauze12] [int] NOT NULL,
[pauze13] [int] NOT NULL,
[bron1] [int] NOT NULL,
[bron2] [int] NOT NULL,
[bron3] [int] NOT NULL,
[bron4] [int] NOT NULL,
[bron5] [int] NOT NULL,
[bron6] [int] NOT NULL,
[bron7] [int] NOT NULL,
[bron8] [int] NOT NULL,
[bron9] [int] NOT NULL,
[bron10] [int] NOT NULL,
[bron11] [int] NOT NULL,
[bron12] [int] NOT NULL,
[bron13] [int] NOT NULL,
[tijd1] [int] NOT NULL,
[tijd2] [int] NOT NULL,
[tijd3] [int] NOT NULL,
[tijd4] [int] NOT NULL,
[tijd5] [int] NOT NULL,
[tijd6] [int] NOT NULL,
[tijd7] [int] NOT NULL,
[tijd8] [int] NOT NULL,
[tijd9] [int] NOT NULL,
[tijd10] [int] NOT NULL,
[tijd11] [int] NOT NULL,
[tijd12] [int] NOT NULL,
[tijd13] [int] NOT NULL,
[cs1] [float] NOT NULL,
[cs2] [float] NOT NULL,
[cs3] [float] NOT NULL,
[cs4] [float] NOT NULL,
[cs5] [float] NOT NULL,
[cs6] [float] NOT NULL,
[cs7] [float] NOT NULL,
[cs8] [float] NOT NULL,
[cs9] [float] NOT NULL,
[cs10] [float] NOT NULL,
[cs11] [float] NOT NULL,
[cs12] [float] NOT NULL,
[cs13] [float] NOT NULL,
[tempr1] [float] NOT NULL,
[tempr2] [float] NOT NULL,
[tempr3] [float] NOT NULL,
[tempr4] [float] NOT NULL,
[tempr5] [float] NOT NULL,
[tempr6] [float] NOT NULL,
[tempr7] [float] NOT NULL,
[tempr8] [float] NOT NULL,
[tempr9] [float] NOT NULL,
[tempr10] [float] NOT NULL,
[tempr11] [float] NOT NULL,
[tempr12] [float] NOT NULL,
[tempr13] [float] NOT NULL,
[temps1] [float] NOT NULL,
[temps2] [float] NOT NULL,
[temps3] [float] NOT NULL,
[temps4] [float] NOT NULL,
[temps5] [float] NOT NULL,
[temps6] [float] NOT NULL,
[temps7] [float] NOT NULL,
[temps8] [float] NOT NULL,
[temps9] [float] NOT NULL,
[temps10] [float] NOT NULL,
[temps11] [float] NOT NULL,
[temps12] [float] NOT NULL,
[temps13] [float] NOT NULL,
[Aanmaak] [datetime] NOT NULL CONSTRAINT [DF_VBR_Aanmaak] DEFAULT (getdate()),
[Gewijzigd] [datetime] NOT NULL CONSTRAINT [DF_VBR_Gewijzigd] DEFAULT (getdate()),
CONSTRAINT [PK_VBR] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I assume that this is what you need?
July 31, 2008 at 2:23 am
Hi Jo, yes it is, thanks.
I'll use the view, and select from it to create the temp table with the calculations incorporated into it. Ideally you would query from the base tables because it would be quicker - whether or not you can do this will really depend upon your development policy.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 2:29 am
Hi Chris,
I'll make it easy for you. We have no development policy for SQL yet. I'm working on it.:P
July 31, 2008 at 3:18 am
Hi Jo
This is intended to replace your stored procedure. Please note that it's untested because I don't have your data, you should test the output against your sproc.
SELECT VBR_ID, VDR_ID,
ret1, ret2, ret3, ret4, ret5, ret6, ret7, ret8, ret9, ret10, ret11, ret12, ret13,
cor1, cor2, cor3, cor4, cor5, cor6, cor7, cor8, cor9, cor10, cor11, cor12, cor13,
pauze1, pauze2, pauze3, pauze4, pauze5, pauze6, pauze7, pauze8, pauze9, pauze10, pauze11, pauze12, pauze13,
bron1, bron2, bron3, bron4, bron5, bron6, bron7, bron8, bron9, bron10, bron11, bron12, bron13,
tijd1, tijd2, tijd3, tijd4, tijd5, tijd6, tijd7, tijd8, tijd9, tijd10, tijd11, tijd12, tijd13,
cs1, cs2, cs3, cs4, cs5, cs6, cs7, cs8, cs9, cs10, cs11, cs12, cs13,
tempr1 = CASE WHEN cor1 = 1 AND tempr1 <> 0 THEN tempr1 + tempreti ELSE tempr1 END,
tempr2 = CASE WHEN cor2 = 1 AND tempr2 <> 0 THEN tempr2 + tempreti ELSE tempr2 END,
tempr3 = CASE WHEN cor3 = 1 AND tempr3 <> 0 THEN tempr3 + tempreti ELSE tempr3 END,
tempr4 = CASE WHEN cor4 = 1 AND tempr4 <> 0 THEN tempr4 + tempreti ELSE tempr4 END,
tempr5 = CASE WHEN cor5 = 1 AND tempr5 <> 0 THEN tempr5 + tempreti ELSE tempr5 END,
tempr6 = CASE WHEN cor6 = 1 AND tempr6 <> 0 THEN tempr6 + tempreti ELSE tempr6 END,
tempr7 = CASE WHEN cor7 = 1 AND tempr7 <> 0 THEN tempr7 + tempreti ELSE tempr7 END,
tempr8 = CASE WHEN cor8 = 1 AND tempr8 <> 0 THEN tempr8 + tempreti ELSE tempr8 END,
tempr9 = CASE WHEN cor9 = 1 AND tempr9 <> 0 THEN tempr9 + tempreti ELSE tempr9 END,
tempr10 = CASE WHEN cor10 = 1 AND tempr10 <> 0 THEN tempr10 + tempreti ELSE tempr10 END,
tempr11 = CASE WHEN cor11 = 1 AND tempr11 <> 0 THEN tempr11 + tempreti ELSE tempr11 END,
tempr12 = CASE WHEN cor12 = 1 AND tempr12 <> 0 THEN tempr12 + tempreti ELSE tempr12 END,
tempr13 = CASE WHEN cor13 = 1 AND tempr13 <> 0 THEN tempr13 + tempreti ELSE tempr13 END,
temps1 = CASE WHEN cor1 = 1 AND tempr1 <> 0 AND temps1 <> 0 THEN temps1 + tempsti ELSE temps1 END,
temps2 = CASE WHEN cor2 = 1 AND tempr2 <> 0 AND temps2 <> 0 THEN temps2 + tempsti ELSE temps2 END,
temps3 = CASE WHEN cor3 = 1 AND tempr3 <> 0 AND temps3 <> 0 THEN temps3 + tempsti ELSE temps3 END,
temps4 = CASE WHEN cor4 = 1 AND tempr4 <> 0 AND temps4 <> 0 THEN temps4 + tempsti ELSE temps4 END,
temps5 = CASE WHEN cor5 = 1 AND tempr5 <> 0 AND temps5 <> 0 THEN temps5 + tempsti ELSE temps5 END,
temps6 = CASE WHEN cor6 = 1 AND tempr6 <> 0 AND temps6 <> 0 THEN temps6 + tempsti ELSE temps6 END,
temps7 = CASE WHEN cor7 = 1 AND tempr7 <> 0 AND temps7 <> 0 THEN temps7 + tempsti ELSE temps7 END,
temps8 = CASE WHEN cor8 = 1 AND tempr8 <> 0 AND temps8 <> 0 THEN temps8 + tempsti ELSE temps8 END,
temps9 = CASE WHEN cor9 = 1 AND tempr9 <> 0 AND temps9 <> 0 THEN temps9 + tempsti ELSE temps9 END,
temps10 = CASE WHEN cor10 = 1 AND tempr10 <> 0 AND temps10 <> 0 THEN temps10 + tempsti ELSE temps10 END,
temps11 = CASE WHEN cor11 = 1 AND tempr11 <> 0 AND temps11 <> 0 THEN temps11 + tempsti ELSE temps11 END,
temps12 = CASE WHEN cor12 = 1 AND tempr12 <> 0 AND temps12 <> 0 THEN temps12 + tempsti ELSE temps12 END,
temps13 = CASE WHEN cor13 = 1 AND tempr13 <> 0 AND temps13 <> 0 THEN temps13 + tempsti ELSE temps13 END,
vdrtijd, tempsti, tempreti
INTO #TMP_VBR
FROM vw_vbr
WHERE VBR_ID = @VBR_ID AND VDR_ID = @VDR_ID
Just as a matter of interest, why isn't tellerID another column in your tables giving you 13 rows per existing row, rather than having column1...column13? Is there a good reason why the data is denormalized this way?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 3:30 am
Hi,
No, it's denormalisez because of me. (one of my mistakes).
But it's to much work to modify it in the current project.
A lesson for the future projects. 🙂
Now testing your query...
July 31, 2008 at 3:51 am
Hi Jo
If this is a training exercise then you are going to learn loads about normalisation and database layout, at least until you throw your computer out of the window in frustration. If it's paid work then you owe it to your employer and yourself to do it properly. Mistakes will always come back to bite you in the a$$.
cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 3:59 am
Hi Chris,
Your query works fine.
This is for a paid project. But before I wrote it all in (scripting-)code and I wanted to go more to a multi-level development with this scada-system.
Most of the queries are concentrated in views now. But this one was no so easy for me. So I tried a stored procedure.
Thanks for the help and simplification of my stored procedure.
July 31, 2008 at 4:04 am
You're welcome Jo, thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply