May 10, 2012 at 3:48 am
WayneS (5/9/2012)
Tim Walker. (5/9/2012)
riya_dave, I've not been here for a couple of months, but you have at least three of the best experts giving you free help here. You haven't given them any information as to what you want to do, as far as I can seeSo I have a tip on that basis. Replace the whole SP with SELECT GETDATE(). This is quick and might return the results you want based on the information supplied.
Best of luck.
Tim
Hi Tim.
SELECT 1 might be a wee bit faster.
Gentlemen, you really don't know how to write efficient and fast stored procs. Here is the one (including create proc statement):
CREATE PROC p_GetMeEverythingIWant AS RETURN;
😛
No loops, no cursors, Nothing!
Very fast and and stylish! Easy to maintain!
:hehe:
Huh, found even shorter version of it:
CREATE PROC p_GetMeEverythingIWant_ExtraShort AS;
:w00t:
Both above procs are very useful!
They do return integer zero as return value in no time!
You can use them like that:
DECLARE @ValueINeedWhichHelpsMeToSolveEverything INT
EXEC @ValueINeedWhichHelpsMeToSolveEverything = p_GetMeEverythingIWant
--OR
EXEC @ValueINeedWhichHelpsMeToSolveEverything = p_GetMeEverythingIWant_ExtraShort
May 10, 2012 at 4:59 am
Dear Riya,
I saw a lot of posts and questions asked by you without much information and when you reply back, they read as if you just want people to email you and request for more information. There are a lot of people trying to volunteer and help you out even during their normal working hours so please show them some respect by replying properly even if you are not getting the output.
Else like one of the guys suggested here 'select 1 is the fastest query with the best execution plan.
Thanks
Chandan
May 10, 2012 at 5:13 am
Eugene Elutin (5/10/2012)
WayneS (5/9/2012)
Tim Walker. (5/9/2012)
riya_dave, I've not been here for a couple of months, but you have at least three of the best experts giving you free help here. You haven't given them any information as to what you want to do, as far as I can seeSo I have a tip on that basis. Replace the whole SP with SELECT GETDATE(). This is quick and might return the results you want based on the information supplied.
Best of luck.
Tim
Hi Tim.
SELECT 1 might be a wee bit faster.
Gentlemen, you really don't know how to write efficient and fast stored procs. Here is the one (including create proc statement):
CREATE PROC p_GetMeEverythingIWant AS RETURN;
😛
No loops, no cursors, Nothing!
Very fast and and stylish! Easy to maintain!
:hehe:
Huh, found even shorter version of it:
CREATE PROC p_GetMeEverythingIWant_ExtraShort AS;
:w00t:
Both above procs are very useful!
They do return integer zero as return value in no time!
You can use them like that:
DECLARE @ValueINeedWhichHelpsMeToSolveEverything INT
EXEC @ValueINeedWhichHelpsMeToSolveEverything = p_GetMeEverythingIWant
--OR
EXEC @ValueINeedWhichHelpsMeToSolveEverything = p_GetMeEverythingIWant_ExtraShort
I humbly submit:
CREATE PROC p_Answer AS RETURN 42;
🙂
May 10, 2012 at 6:19 am
...
I humbly submit:
CREATE PROC p_Answer AS RETURN 42;
🙂
It can be few yocto-seconds slower. Can be a great impact if you call it just few septillion times.
Serious performance degradation...
:w00t:
May 10, 2012 at 6:47 am
I have amended my original proposal to a genuinely useful one:
CREATE PROC WhenCanIGoHome AS
SELECT 'You can go home at '+CONVERT(varchar, DATEADD(s,5+(3595*RAND(CHECKSUM(NEWID()))),GETDATE()),108)
:hehe:
Tim
.
May 10, 2012 at 6:50 am
Tim Walker. (5/10/2012)
I have amended my original proposal to a genuinely useful one:CREATE PROC WhenCanIGoHome AS
SELECT 'You can go home at '+CONVERT(varchar, DATEADD(s,5+(3595*RAND(CHECKSUM(NEWID()))),GETDATE()),108)
:hehe:
Tim
Late again! Your dinner's in the dog.
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
May 10, 2012 at 7:36 am
STOP POSTING USELESS CODE!!!
Here is the must have one!
CREATE PROC dbo.p_ShowMeWhenICanGoHome (@TargetTime TIME(0), @TellMeEvery TIME(0))
AS
BEGIN
DECLARE @TimeNow TIME
DECLARE @h INT, @m INT, @S INT
DECLARE @msg NVARCHAR(1000)
DECLARE @WaitFor VARCHAR(8)
SET @WaitFor =CAST(@TellMeEvery AS VARCHAR)
SET @TimeNow = CAST(GETDATE() AS TIME)
WHILE @TimeNow < @TargetTime
BEGIN
SELECT @h = DATEDIFF(SECOND, @TimeNow, @TargetTime)/3600%60
,@m = DATEDIFF(SECOND, @TimeNow, @TargetTime)/60%60
,@s = DATEDIFF(SECOND, @TimeNow, @TargetTime)%60
SET @msg = 'You must be here for another ' +
CASE WHEN @h=1 THEN CAST(@h as varchar) + ' hour '
WHEN @h>1 THEN CAST(@h as varchar) + ' hours '
ELSE ''
END +
CASE WHEN @m=1 THEN CAST(@m as varchar) + ' minute '
WHEN @m>1 THEN CAST(@m as varchar) + ' minutes '
ELSE ''
END +
CASE WHEN @S=1 THEN CAST(@s as varchar) + ' second'
WHEN @S>1 THEN CAST(@s as varchar) + ' seconds'
ELSE ''
END
RAISERROR (@msg,10,1) WITH NOWAIT;
WAITFOR DELAY @WaitFor
SET @TimeNow = CAST(GETDATE() AS TIME)
END
PRINT 'You can pack and go home. NOW!';
END
Now, go into SSMS, menu Tools/Options...
Select in the option hierarchy Query Results/SQL Server/Results to Text
Tick option "Scroll as results are received"
Open the new Query window (change of option does only apply to the new one)
And execute it with wanted parameters eg.:
EXEC dbo.p_ShowMeWhenICanGoHome @TargetTime='16:30', @TellMeEvery = '00:00:01'
You can change frequency it yells, by changing the second parameter!
Enjoy!
:hehe::hehe::hehe::hehe::hehe:
Augh, make sure you switch SSMS to get "results in text"...
May 10, 2012 at 8:14 am
riya_dave, Here is the code I wrote. To make it work with Waynes test suite either modify it to create a table named dbo.Emps or change the references in the code below from dbo.Emps to @Emps.
I apologize for the piling on that has occurred.
Hopefully between the Wayne's setup and the code below you will see the difference between cursor-based and set-based solutions.
-- 1st cursor-based solution, loops through all 2135 employee records and updates them all, some with a new salary:
DECLARE empCursor CURSOR FOR
SELECT
EmpId,
IsActive,
Salary,
Class
FROM
dbo.Emps;
OPEN empCursor;
DECLARE @EmpId INT,
@IsActive BIT,
@Salary DECIMAL(10,2),
@Class INT;
FETCH NEXT FROM empCursor INTO @EmpId, @IsActive, @Salary, @Class;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsActive = 1
BEGIN
IF @Class = 1
SET @Salary = @Salary * 1.1
IF @Class = 2
SET @Salary = @Salary * 1.08
IF @Class = 3
SET @Salary = @Salary * 1.06
IF @Class = 4
SET @Salary = @Salary * 1.04
END
UPDATE dbo.Emps SET
Salary = @Salary
WHERE
EmpId = @EmpId;
FETCH NEXT FROM empCursor INTO @EmpId, @IsActive, @Salary, @Class;
END
CLOSE empCursor;
DEALLOCATE empCursor;
GO
-- 2nd cursor-based solution, loops through all 2135 employee records and updates only the active employess:
DECLARE empCursor CURSOR FOR
SELECT
EmpId,
IsActive,
Salary,
Class
FROM
dbo.Emps;
OPEN empCursor;
DECLARE @EmpId INT,
@IsActive BIT,
@Salary DECIMAL(10,2),
@Class INT;
FETCH NEXT FROM empCursor INTO @EmpId, @IsActive, @Salary, @Class;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsActive = 1
BEGIN
IF @Class = 1
SET @Salary = @Salary * 1.1
IF @Class = 2
SET @Salary = @Salary * 1.08
IF @Class = 3
SET @Salary = @Salary * 1.06
IF @Class = 4
SET @Salary = @Salary * 1.04
UPDATE dbo.Emps SET
Salary = @Salary
WHERE
EmpId = @EmpId;
END
FETCH NEXT FROM empCursor INTO @EmpId, @IsActive, @Salary, @Class;
END
CLOSE empCursor;
DEALLOCATE empCursor;
GO
-- 3rd cursor-based solution, only loops through the active 176 employees and updates them:
DECLARE empCursor CURSOR FOR
SELECT
EmpId,
Salary,
Class
FROM
dbo.Emps
WHERE
IsActive = 1;
OPEN empCursor;
DECLARE @EmpId INT,
@Salary DECIMAL(10,2),
@Class INT;
FETCH NEXT FROM empCursor INTO @EmpId, @Salary, @Class;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Class = 1
SET @Salary = @Salary * 1.1
IF @Class = 2
SET @Salary = @Salary * 1.08
IF @Class = 3
SET @Salary = @Salary * 1.06
IF @Class = 4
SET @Salary = @Salary * 1.04
UPDATE dbo.Emps SET
Salary = @Salary
WHERE
EmpId = @EmpId;
FETCH NEXT FROM empCursor INTO @EmpId, @Salary, @Class;
END
CLOSE empCursor;
DEALLOCATE empCursor;
GO
-- Set-based solution, updates all active employees:
UPDATE dbo.Emps SET
Salary = Salary * (CASE Class WHEN 1 THEN 1.1 WHEN 2 THEN 1.08 WHEN 3 THEN 1.06 WHEN 4 THEN 1.04 END)
WHERE
IsActive = 1;
GO
May 10, 2012 at 6:22 pm
So much time wasted.
--Jeff Moden
May 10, 2012 at 6:45 pm
Jeff Moden (5/10/2012)
So much time wasted.
Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
May 10, 2012 at 7:06 pm
Lynn Pettis (5/10/2012)
Jeff Moden (5/10/2012)
So much time wasted.Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
I hope I am not slighting Jeff in any way, shape or form by my comment, so I apologize in advance if it comes out wrong as I tend to agree with his comment by the way. But Lynn, people like you are a major reason why I love this site and this community in general. I applaud your efforts.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 10, 2012 at 7:13 pm
opc.three (5/10/2012)
Lynn Pettis (5/10/2012)
Jeff Moden (5/10/2012)
So much time wasted.Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
I hope I am not slighting Jeff in any way, shape or form by my comment, so I apologize in advance if it comes out wrong as I tend to agree with his comment by the way. But Lynn, people like you are a major reason why I love this site and this community in general. I applaud your efforts.
x1000! Lynn- thank you for, well, being you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 11, 2012 at 8:43 am
opc.three (5/10/2012)
Lynn Pettis (5/10/2012)
Jeff Moden (5/10/2012)
So much time wasted.Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
I hope I am not slighting Jeff in any way, shape or form by my comment, so I apologize in advance if it comes out wrong as I tend to agree with his comment by the way. But Lynn, people like you are a major reason why I love this site and this community in general. I applaud your efforts.
Nope... not slighted in the least because I wasn't talking about Lynn being dedicated. Lynn (and others) wouldn't have to spend so much time if the OP would simply cough up a bit of data and DDL at the beginning.
--Jeff Moden
May 11, 2012 at 8:44 am
Lynn Pettis (5/10/2012)
Jeff Moden (5/10/2012)
So much time wasted.Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
See my post just previous to this one.
--Jeff Moden
May 11, 2012 at 8:51 am
Jeff Moden (5/11/2012)
Lynn Pettis (5/10/2012)
Jeff Moden (5/10/2012)
So much time wasted.Maybe, but it is my time to waste. I'm not going to apologize for hoping against hope for the best. I guess that is what I am when it comes to this site, the eternal optimist. I may get disgruntled, angry, frustrated, upset, disillusioned at times and rant about it. But I will always try and give the other person a chance to change.
See my post just previous to this one.
I guess your comment could be taken several ways. Considering people kept telling me to give up, I guess I took it the wrong way.
Sorry.
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply