looping through the records without using cursor???

[mrpro]

hi experts,
can any one give me a thought how can we write a stored procedure which loops through the records.
the basic purpose i need this query is to update each record with a new value by looping the records..

thanks for any help

 

[donutman]

Why do you want to avoid a cursor? If it's because they suck, then the same is true of a loop.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

Thanks for the reply donutman
i think loops are better than using a cursor
i am not sure whether this statement is right or wrong..

i just wanted to find a way of doing without using cursor,because i have heard most of the times you can avoid using cursors...

 

[Pattycake245]

What exactly are you trying to update? You may be able to update using a set-based solution and avoid using a loop all together.

Tim

 

[mrpro]

i need to update a table Timetable
which has three columns Id,setId and Sequence

i have a query

select Id from Timetable where setId=1 which returns all the Id which matches the setId 1

what i wanted to do is i wanted to change the sequence for each Id

thanks

 

[donutman]

Where is the information coming from regarding the SetID and the new Sequence value? Do all rows with SetID get the same Sequence value? Please answer both questions.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

sorry i haven't given you the whole stored procedure..
that is wrapped up in the stored procedure

what i wanted to do is i wanted to set a new sequence based on other parameters(for time being we can assume we need to increment sequence by one for each record)
for each setid we have different sequences

hope this will help you giving me the solution..

thanks

 

[donutman]

That problem has come up several times in this forum before. It can be done in basically two different ways. The first is very fast but it's not 100% guaranteed to order your sequence numbers correctly. The 2nd method is guaranteed, but the performance degrades very fast as the number of items in each sequence increases.
Give us some background information as to size and the details about the contents of your tables. Otherwise we can't possibly give you a solution.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TysonLPrice]

Just to give you an example like you asked here is a basic loop by indentity you can chop up for your own use. It does need cleaned up. I agree with the caveats you received about looping.

SET NOCOUNT ON
DECLARE     @iReturnCode       int,
            @iNextRowId        int,
            @iCurrentRowId     int,
            @iLoopControl      int,
	    @Msg varchar(100),
            @DoctorID          int,
 	    @PreviousDoctorID  int,
	    @Type              int		

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(DoctorID)
	FROM  DrContacts

SELECT  @iCurrentRowId   = Doctorid,
        @Type = Type
FROM    DrContacts
WHERE   DoctorID = @iNextRowId

WHILE @iLoopControl = 1
   BEGIN
	  -- processing starts here

	  -- processing end here	  
          print @icurrentrowid
          SELECT   @iNextRowId = NULL            
          SELECT   @iNextRowId = MIN(DoctorID)
          FROM     DrContacts
          WHERE    Doctorid > @iCurrentRowId
              IF ISNULL(@iNextRowId,0) = 0
                  BEGIN
                      BREAK
              END
          SELECT  @iCurrentRowId =   doctorID
          FROM    DrContacts
          WHERE   DoctorID = @iNextRowId
            
   END
RETURN

 

[donutman]

I would suggest using a cursor before using that code. And my position on cursors is pretty self-evident.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

I've had reason to do something similar. Here is my approach.

1. Create a temp table with all relevant ids.
2. Add a 'RowId' column Integer Identity(1,1)
3. Add a MinRowId Column Integer
4. insert into the temp table.
5. create another temp table with the MinRowId's
6. Update the temp table's MinRowId
7. Do a little math to get the new sequence number.

Please forgive the following example. It's from one of my tables, and therefore shows my table names and field names.
In my example, the SequenceNumber field got "out of whack", and needed to be corrected. For each RouteDescriptionId, SequenceNumbers should start at 1 and increment by 1 for each stop on the route.

There is probably a better way to do this, but at least it is set based, so I would expect the preformance to be much better than a row based solution.

Create
Table #Temp
(
RowId Integer Identity(1,1),
MinRowId Integer,
RouteDescriptionId Integer,
RouteId Integer,
SequenceNumber Integer,
NewSequenceNumber Integer
)

insert
Into #Temp(RouteDescriptionId, RouteId, SequenceNumber)
Select RouteDescriptionId, RouteId, SequenceNumber
From Route
Order By RouteDescriptionId, SequenceNumber

Select Min(RowId) As MinRowId,
RouteDescriptionId
Into #Mins
From #Temp
Group By RouteDescriptionId

Update #Temp
Set #Temp.MinRowId = #Mins.MinRowId
From #Temp
Inner Join #Mins On #Temp.RouteDescriptionId = #Mins.RouteDescriptionId

Update #Temp
Set NewSequenceNumber = RowId - MinRowId + 1

Select * from #Temp

 

[donutman]

Good outline gmmastros. That's an example of the fast solution that's not 100% guaranteed to sequence properly. It's very likely to work and the results can be verified so it's not as if you will be in the dark.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

thanks all for taking time to answer my query

i need to club the solutions given by gmmastros and TysonLPrice

because

i also need to update a date field in my table along with the sequence and also i need to check whether this date falls b/w weekday or a weekend

if it is a week end then should move to the next week day. so i hope by using the loop i can check each record and update my table based on the date


here is what i think should solve my problem


create temp table with an identity column rowid

insert all the elements required to loop in to this temp table

get the count of elements in this temp table

use while loop to update my table based on rowid



what i found now is I can also avoid creating temp table if i use the solution given by TysonLprice


thanks

 

[TysonLPrice]

mrpro,

Here's a loop example with a cursor:

DECLARE Update_Cursor CURSOR
dynamic scroll_locks
FOR select adduser from reportstable
        	
OPEN    Update_Cursor

declare @AddUser as int
declare @UpdateValue as int
set @updateValue = 0

FETCH   NEXT FROM Update_Cursor INTO @AddUser

WHILE @@FETCH_STATUS = 0
BEGIN
 	
    set @updateValue = @updateValue + 1 
    update ReportsTable set adduser  = @UpdateValue where current of Update_cursor
    FETCH   NEXT FROM Update_Cursor INTO  @Adduser
END

Close  Update_Cursor
Deallocate Update_Cursor

 

[mrpro]

Thanks TysonLPrice for the SP

Do you think temp tables are worse than cursors

if that is the case i can use your SP without any hesitation..

thanks

 

[TysonLPrice]

I'm really not qualified to answer. I once made a post about looping and got the standard admonishments about them and cursors. I trust the folks here that say use set based processing whenever you can versus loops and cursors.

I'm still a newbie. Maybe the gurus can chime in on your question on temp tables.

 

[dbomrrsm]

While temporary tables (in my opinion) are far better than cursors, they do cause a performance hit when being used.

Temporary tables slow performance dramatically. The problem with temporary tables is the amount of overhead that goes along with using them. In order to get the fastest queries possible, our goal must be to make them do as little work as possible. For example, with a SELECT statement, SQL Server reads data from the disk and returns the data. However, temporary tables require the system to do much more.For example, a piece of Transact-SQL code using temporary tables usually will:
1) CREATE the temporary table
2) INSERT data into the newly created table
3) SELECT data from the temporary table (usually by JOINing to other physical tables) while holding a lock on the entire tempdb database until the transaction has completed. 4) DROP the temporary table

This represents a lot of disk activity, along with the potential for contention problems. And all of this adds up to poor performance.

As queries become more complex, temporary tables are used more and more. While temporary table may sometimes be unavoidable, they can often be sidestepped by using derived tables instead. In brief, a derived table is the result of using another SELECT statement in the FROM clause of a SELECT statement. By using derived tables instead of temporary tables, we can boost our application's performance.

The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.Here are the steps when you use a temporary table:
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
6) Release the locks

Compare the above to the number of steps it takes for a derived table:
1) CREATE locks, unless isolation level of "read uncommitted" is used
2) SELECT data (read activity)
3) Release the locks

As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance.

Derived tables are essentially SELECT statements within SELECT statements.
Let's look at a very simple example:
Take a look at this simple query where we SELECT data from a table:
USE northwind
GO
SELECT * FROM categories

Now, instead of selecting data from the categories table, let’s select our data from a derived table. For example:

USE northwind
GO
SELECT * FROM (SELECT * FROM categories) dt_categories

This is all there is to derived tables. Remember, a derived table is just the result of using another SELECT statement in the FROM clause of another SELECT statement. Simply put the query in parenthesis and add a table name after the query in the parenthesis.

If You Do Use Temporary Tables, Optimize Their Use

If the situation mandates the use of a temporary table, then there are several things you can do to maximize their performance.

First, just because it is a temporary table do not be tempted to put all the columns and all the rows from your permanent table into the temporary table if you do not need them. Filter the data going into your temporary table to include the minimum number of columns and rows actually needed.

Second, do not use the SELECT INTO statement to create your temp table. The SELECT INTO should be avoided at all costs in your coding due to the locking it places on system objects while it determines how to build the table. Take the time to script the temporary table out and use a separate INSERT INTO to populate the table.

I will qualify this with that you can use a SELECT INTO if it includes WHERE 1=0 to create a table in the quickest way possible, but don't do this just to save a few keystrokes.
Third, watch how you use temporary tables to avoid recompiles on the stored procedure.

Fourth, test the need for a clustered-index on your temporary table. If the data set is large, a cluster-index will speed the operations against the temporary table, but you have to weigh the performance needs of creating that index and inserting data into a table with a clustered-index. This is one of those methods that needs to be tested both ways, with the largest data set you think will be placed into the temporary table, before deciding on the index.

And last, I know that when the stored procedure completes and the connection ends, the temporary table will be dropped, but why keep it around if you are done with it. If you code creates and uses a temporary table, and then goes on to do other things that do not involve that table drop the table when you are done. This frees up tempdb resources for other objects. I will even drop the table at the end of a stored procedure even though the connection is about to finish just to avoid any issues that may arise with unknown bugs.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[donutman]

Very professionally written DBomrrsm (JG).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

It was - but unfortunately not by me !!

and not just JG either ;-)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mrpro]

thanks dbomrrsm

good reasoning whether to go for temp table or not never thought this much about it.

I will point to this thread if any one in future raises question about temp table..

thanks