July 12, 2005 at 7:15 am
All,
I just yesterday used the techique at a contract client with similar results although not quite as dramatic as the one I wrote about. To be fair though, there is a limit to how well this may work. After I wrote the article I came across another update that was being handled with a cursor. Within the cursor 18 columns had to be updated while pivoting data. I found I only needed 7 functions to perform the 18 updates but by the time all thoses calls were made for 2.2 milllion records, the new process clock out at the same speed as the old one. I tested individual calls and they were very short but that many had an accumulative affect. The good side though is that the code is so much simpler and easier to read that I plan to replace the old code anyway.
Have fun building your functions.
July 25, 2005 at 12:14 am
Happend to see this while browsing . thought it might be relevant here. have a look
http://www.sqlteam.com/item.asp?ItemID=2368
Jeswanth
--------------------------------
April 7, 2006 at 5:32 am
Just a couple of things I've found useful. If you have to loop generally it's quicker to use a while loop selecting each record individually rather than a cursor. Also if you find you have very large SQL statement it often runs much quicker broken up into smaller ones. If each calculation can be broken up into individual set based queries I'm sure you'll improve the performance.
Phil Nicholas
April 7, 2006 at 8:21 am
I hope there are not many people hitting this table. This may be OK as long as it is a nightly run that you know you are the only code running against it.
However long it runs, it will be locked during this process. Although fast, for databases with a lot of people expecting to update it, it would be unacceptable unless you can get it to run in less than 4 seconds (you last said 26 seconds I believe) and lock timeout's are set well above that. Speed is not always the only consideration unless you are running exclusively! In which case I would put a table lock hint on it to speed it up even more. I would recommend taking a little longer time to make sure you don't have a table lock for a half minute.
Mark
April 7, 2006 at 11:01 am
Here is how a highly available database would look. I work on databases with hundreds of thousands of users on databases unconceivable large to most. At my job location, we don't even have a nightly processing time (global app).
Whenever I read posts on this and most other sites, I have to keep in mind that they have a low concurrency of users. As a user base grows, most will have a an increasing number of problems they can't identify over time.
The following code is focused on removing cursors, actually a lot of code should be added for error checking, but for simplicity and keeping to the topic it is left out. While loops in our environment are a necessary evil to keep the number of rows being updated down to a small number so they can complete and release locks within 4 seconds (our apps rule; should actually be lower; perhaps 3). Think availablity first, then speed. At my work location, we even have variations of this that loop through rows in blocks of 5, 10, or whatever higher number still allows us to complete in less than 4 seconds. As you can see, it is a completely different mind set.
--------------------
Declare @tmp_id int
Declare @prev_id int
Declare @tmp_values varchar(20)
Set LOCK_TIMEOUT = 12000 -- assuming all code in app is tested to have
-- no update take longer than 4 seconds
Set @prev_id = -1 -- assumes all ID's are positive in nature
While Exists (
SELECT TOP 1 @tmp_id = id from DB.dbo.OutPut_tbl WITH(NOLOCK)
Where id > @prev_id -- retrieve next row > than the previous
order by id -- key data ; preferable clustered ;
-- order by id to maintain
-- a reference point
)
BEGIN
--Direct column pivot into a variable
-- I don't know enough about the column_out field; but lets hope we never
-- increase in size greater than 20; declarations and table field would
-- need to be changed if that
-- was possible.
SELECT @tmp_values = @tmp_values + convert(varchar(20), column_data) + ','
FROM DB.dbo.many_tbl WITH(NOLOCK)
WHERE id = @tmp_id
UPDATE DB.dbo.OutPut_tbl WITH(ROWLOCK) -- assuming that this will take
-- less than 4 seconds
-- otherwise create another
-- while exists loop
SET column_out = Left(@tmp_values,20)
WHERE id = @tmp_id
SET @prev_id = @tmp_id -- move our pointer to the next row
END -- While Exists
April 10, 2006 at 6:43 am
It's just worth pointing out that by default how a cursor and while loop work are different from a transaction view point. A while will execute as a complete transaction - which might bring about its own problems. ( this assumes no explicit transactions defined )
Just an observation - I like replacing cursors with while loops but you do have to be careful some times.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 11, 2006 at 6:58 am
Good thoughts Mark. Thanks for your input.
May 7, 2006 at 12:08 am
Hi Herring,
I read with interest on this topic of the 'replacing cursor and while loop'.
I faced the same problem on having lot of cursor which I replace with loop, but didn't solve my execution time.
Your article really help me to look at different way to replace cursor with select statment. I know how to use
select for updating data using select method from your
article.
But I cannot think of any good way to insert new data
using select statement. Do you have any good way to
insert new data using the select statement which I replace
from cursor.
Please advise. thank you
May 7, 2006 at 12:44 am
You can of course use the Values clause but if you want to call functions to generate some new data then use the select statement...insert into >tablecolumnlistfunctionetc. until column list is matched<
May 7, 2006 at 10:48 am
Hi Herring,
Below is my example to illustrate my issue.
From your article, I know how to use function to
update using select statement.
But I can't think on how to insert new values
using select statement. Please illustrate with
example to guide me. How to convert my example
into using select statement inserting and updating..
Thank you.
declare cur_insert CURSOR FOR
select ..... FROM
where xxxx
While(xxx)
begin
if(xxx)
Insert into values(xxxx)
else
update
end
April 9, 2007 at 2:00 pm
Perhaps this has already been asked (or scoffed at for being 'undocumented') but have you considered a function using COALESCE to return your pivoted series?
CREATE TABLE Test (
id INT,
val VARCHAR(10))
INSERT Test (id, val) VALUES(56, 'run')
INSERT Test (id, val) VALUES(56, 'jump')
INSERT Test (id, val) VALUES(56, 'scale')
INSERT Test (id, val) VALUES(57, 'fly')
INSERT Test (id, val) VALUES(57, 'swim')
INSERT Test (id, val) VALUES(57, 'crawl')
GO
CREATE FUNCTION uf_pivot_series (@id INT)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @return VARCHAR(8000)
DECLARE @delimiter CHAR(2)
SET @delimiter = ', '
SELECT @return = COALESCE(@return + @delimiter, '') + val FROM Test
WHERE id = @id
RETURN @return
END
GO
SELECT id,
dbo.uf_pivot_series(id) as val
FROM Test
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply