Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


4 Ways to Increase Your Salary (Using UPDATE)


4 Ways to Increase Your Salary (Using UPDATE)

Author
Message
arup chakraborty
arup chakraborty
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 365
Comments posted to this topic are about the item 4 Ways to Increase Your Salary (Using UPDATE)
Jochen Vleming
Jochen Vleming
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 69
Hi Arup,

I'm fairly new to SQL so maybe it's a stupid question, but why do you use a temp-table in the last example? I think there's no use for that since both the emp-table and the temp-table share the same identity. The way the query is built I think it wouldn't even work if the id's in the emp-table would be different (for example 1,2,5,6,7 (after delete)).

Jochen
Kevin Gill
Kevin Gill
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 356
You say that salary * 115 / 100 and (salary * 115) / 100 return different results due to operator precedence - I can't see how operator precedence makes any difference in this scenario. Could you elaborate or provide an example?

Basically :

salary x (115/100) = (salary x 115)/100 so operator precedence should make no difference...

Actually - thinking about it, is it the integer division that's the issue? What I say above is true in a pure mathematical sense, but if you divide 115/100 as integers in SQLServer, you get 1. If however you divide 115 as a decimal /100, then you get 1.150000, and the calculation will work in any layout. So technically it's the operator precedence causing the integer divide to happen first which is the issue I suppose... though it's the integer divide in its own right that means there is any confusion to be had in the first place.

declare @percentage decimal(3,0)
set @percentage = 115 -- Even works with an 'integer' decimal.
select 3000 * @percentage / 100
select (3000 * @percentage) / 100
select 3000 * (@percentage / 100.0)

Cheers

-- Kev

-------------------------------
Oh no!
stuart.jeffery
stuart.jeffery
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 40
I have to agree, there is no real difference with the calculations except in the interpretation by the system. Int divide by Int = Int which causes incorrect values. Simpler would be to change one of the Int's or even remove one altogether.

What is wrong with using Salary * 1.15? After all Salary is a float.

Remember the key to performance is simplicity. Every level of complexity will exponentially affect your query performance.
Martin Hill-258755
Martin Hill-258755
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 65
There is actually another way to archive a row by row processing. I am using it in different scenarios since quite a few years and it turned out to be very efficient.


BEGIN

Declare @ID int
Select @ID = Min(ID) from emp

WHILE @ID Is Not Null
BEGIN

UPDATE emp SET salary = (salary * 115) /100 WHERE emp.id = @ID

Select @ID = Min(ID) from emp where ID > @ID

END
END



In relation to the actual performance of this method I listed the times/cost on my server below. Please note that I also included the cost calculated by the execution plan:


READ ESTIMATED EXECUTION COST
Direct SQL 5 0.0132935 100%
Cursor 69 0.1462060 1099%
Temp table with TOP 254 0.2531007 1904%
Temp table with IDENTITY column 121 0.0994881 748%
Min Loop 39 0.0861955 648%




:-)
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1346 Visits: 3229
Martins answer is a lot sweeter and more robust if you need to loop through a data set, as there is no guarantee that the numbers are consecutive eg record number 4 is deleted for some reason (yes it can happen), so the last record will never get updated, assuming a 1% row deletion count, then in an organisation with 1000 members, 10 wont get pay rises.

As always the advice is use set based queries rather than Loops and Cursors, though in some cases you have to revert to them they should be rare.

As an aside, one of my colleagues ran some internal tests on a somewhat larger data set (1000 rows, same number of columns) and found that the Cursor ran significantly faster than the last option.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Jochen Vleming
Jochen Vleming
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 69
nice one Martin,

That's a nice and easy way to do the updates.
...And independent of identity values in the table.
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 1323
The last example uses assumption on the identity column values in the main table.
You can just add these values to the temp table.
But Martin's solution is better



See, understand, learn, try, use efficient
© Dr.Plch
jarred.nicholls
jarred.nicholls
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 14
Jason Lees-299789 (11/26/2009)
As an aside, one of my colleagues ran some internal tests on a somewhat larger data set (1000 rows, same number of columns) and found that the Cursor ran significantly faster than the last option.


Cursors and temp tables are very similar, since a cursor creates a temp table in tempdb (spooling all data to disk before being read back)...just like a temp table does. A cursor has an upfront hit when allocating itself, but that overhead doesn't occur with each row fetch and each row fetch is quite speedy.

The CPU cycles is much greater on each row's operation in the identity temp table method (it is doing a COUNT(*) on the temp table each iteration). With a large data set, a cursor will (always) startup slower but finish faster and with less CPU. On a small data set, a cursor will still startup slower but finish slower as well.

Bottom line is, the last method makes certain assumptions about the identity column that can't always be made and thus isn't a universal solution. And of course, 99% of the time there is a set-based solution that will run circles around any procedural solution. I once tuned a stored procedure that took over 2 hours to complete by replacing a cursor with a set-based solution and brought the execution time down to just around 15 seconds. If you are forced into a procedural situation, first post your situation onto a forum and let someone find a set-based solution to the problem (there's a great chance there is one), and if none is found, use a method that suits the size of the data set. Martin's solution is the appropriate one here, no matter the size of the data set - it doesn't use a temp table.
juancvaz
juancvaz
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
Hi All,

I´m test for 24200 rows, the cursor done in 35 seconds and the tabla with identity in 84 seconds.

But when i modify the WHILE control for an constant the final time is 35 seconds, equal to cursor.

BEGIN
CREATE TABLE #temp(id INT IDENTITY(1,1), name VARCHAR(32), salary float)
INSERT INTO #temp
SELECT name, salary FROM #emp
DECLARE @i INT, @last INT
SELECT @last = COUNT(id) FROM #temp
SET @i = 1
WHILE (@i <= @last )
BEGIN
UPDATE #emp
SET salary = (salary * 115)/100
WHERE #emp.id = @i
SET @i = @i + 1
END
END
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search