UPDATE with CURSOR

Hi,

I have a sp using cursor for importing datainto SQL.  It has been fine.  Recently, I need to do update in this SP.  But it is slow, I was wondering if there are any solutions.  thx
mcrmgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Melih SARICAOwnerCommented:
how can u do the Update Statement ?

Can u post the Code ? for an Optimization SQL Code ll be helpfull

Melih SARICa
0
mcrmgAuthor Commented:
hi,

the following is the structure of mu sp, of course, it is much longer than this.  I use the normal way to update table, if it does not exist, insert, else, update it.  please have a look. thx






DECLARE @cStaging CURSOR
SET @cStaging = CURSOR FOR
SELECT "Something here"
FROM "Something here"

OPEN @cStaging

FETCH NEXT FROM @cStaging INTO "Something here"

BEGIN TRAN T1
WHILE @@FETCH_STATUS = 0
BEGIN

--LoanInfo_Static
IF NOT EXISTS ("Something here")
      BEGIN
         INSERT INTO "Something here"
      END
ELSe
      BEGIN
         SELECT @S_myID = myID FROM someTable WHERE "Something here" ....................>>>>>>>>>>>>I need to grap @S_myID here because it will  be used later

         UPDATE MyTable.............................................>>>>>>>>>>>>>>.here
         SET "Something here"
                   WHERE myID = @S_mYID
      END


   FETCH NEXT FROM @cStaging INTO "Something here"

END






















0
SashPCommented:
It does not appear that you need to use a cursor from what you have shown, although I can appreciate that you have tried to simplify it.

Have you tried

INSERT INTO "Something Here"
WHERE "Criterea"

AND

UPDATE MyTable
SET "Something here"
FROM MyTable mt
JOIN SomeTable st on st.myID = mt.myId
WHERE st."something here"

Try to avoid cursor they are a performance overhead that should be avoided.

Cheers Sash
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mcrmgAuthor Commented:
hi,

Before I created that cursor, I have tried different ways to avoid it...  but for some reason(dont remember now)  lol,
only using cursor can get y data imported completely.  (I know I need to spend time on this sp by not using cursor.lol)

However, my boss want me to lunch this asap, so I am thinking when this is up and I have some quiet time, I will fix that.  In the meantime, if I use the current sp, is that a way to make update quicker?? thx


0
SashPCommented:
mcrmg,

Get it running with the cursor, accept the performance issue.   Then work on replacing the cursor.

Sash
0
Ken SelviaRetiredCommented:
You might be supprised at how quickly some of the SQL experts can turn a huge cursor into a small update.  Post the entire procecure. Someone will probably be able to show you how to do it without a cursor.

In general what you are doing might be optimized with;

SELECT a."Something here", b.b_key
into #tmp
FROM "Something here" a
outer join 'something here you would insert into' b on a.key = b.key

insert into bsomething (something)
select a.something from #tmp where b_key is null

update mytable
set someotherthing = #tmp.something
from #tmp
where b_key is not null

But I really confuse myself with all the 'somethings' so real code would help :)  But it can probably be done even more efficiently.
0
mcrmgAuthor Commented:
okay, I guess I will need help on the basic structure.

I import a txt file into a staging table in SQL, then I use cursor to loop through that staging and insert them into different table.

How can I acieve this without using cursor?  thx










0
SashPCommented:
mcrmg,

You will need to post the complete stored proc for us to be able to assist you with it.

Sash
0
mcrmgAuthor Commented:
I hope this will help..thx




ALTER                             PROCEDURE st_IMPORT(@AID int, @BID int, @CID int)

 AS
BEGIN

DECLARE @cStaging CURSOR
SET @cStaging = CURSOR FOR
SELECT @AID, @BID, @CID, [Field1],[Field2],[Field3]
FROM EXCEL_Source

OPEN @cStaging

FETCH NEXT FROM @cStaging INTO @AID, @BID, @CID, @S_Field1,  @S_Field2, @S_Field3

BEGIN TRAN T1
WHILE @@FETCH_STATUS = 0
BEGIN


IF NOT EXISTS (SELECT * FROM myTable1 Field1 = @S_Field1 AND AID = @AID AND BID = @BID)
      BEGIN
      
         INSERT INTO myTable1 Syntax here
        
set @MyErr = @@Error
IF @MyErr <> 0 set @AnyErr = @MyErr

      END
ELSe
      BEGIN
         SELECT @myTablePK = myTableID FROM myTable WHERE Field1 = @S_Field1 AND AID = @AID AND BID = @BID

         UPDATE myTable1 Syntax here
         WHERE myTableID = @myTablePK
      END


   FETCH NEXT FROM @cStaging INTO @AID, @BID, @CID, @S_Field1,  @S_Field2, @S_Field3

END

if @AnyErr = 0
      BEGIN
       print 'TRANSACTION SUCCESSFULLY!!'
         COMMIT TRAN T1
      END
else
      BEGIN
       print 'ERROR OCCURED: TRANSACTION ROLLBACKED!!!!!!!!'
         ROLLBACK TRAN T1
      END



CLOSE @cStaging

DEALLOCATE @cStaging




END


0
BillAn1Commented:
The detail will depend on the  "Syntax Here" bit, but in principle, there is no need to use a cursor to do what you want - you can do it with one INSERT statment, and one UPDATE statement along these lines - If you want more detailed help, please pst the full code.

ALTER PROCEDURE st_IMPORT(@AID int, @BID int, @CID int)
AS
BEGIN
        INSERT INTO myTable1 Syntax here
      WHERE NOT EXISTS (SELECT 1 FROM MyTable1 JOIN Excel_Source
      ON MyTable1.Field1 = Excel_Source.Field1
      AND MyTable1.Field2 = Excel_Source.Field2
      AND MyTable1.Field3 = Excel_Source.Field3
       )

      UPDATE myTable1 Syntax here
      FROM myTable1 JOIN Excel_Source
      ON MyTable1.Field1 = Excel_Source.Field1
      AND MyTable1.Field2 = Excel_Source.Field2
      AND MyTable1.Field3 = Excel_Source.Field3

END
0
Melih SARICAOwnerCommented:
Try this..

Its not an Cursor but ll work better I guess
Create Procedure st_IMPORT(@AID int, @BID int, @CID int)
as
declare @AnyError int
set @AnyError = null

Begin Trans T1
insert into MyTable
          select Es.Field1 , Es.AID ,Es.BID , <Other Columns Here>  from EXCEL_Source Es
                left Join MyTable Mt on Mt .Field1 = Es.Field1 AND Mt AID = Es.AID AND Mt BID = Es.BID AND Mt CID = Es.CID
                where Mt.AID is null
                            and Es.AID = @AID and Es.BID =@BID and Es.CID = @CID
if @@Error <> 0
begin
  set @AnyError = @@Error
end
else
begin  
   Update MyTable
        set  Mt .Field1 = Es.Field1 , Mt AID = Es.AID , Mt BID = Es.BID <Fields ll Updated must be here instead of these Fields>
        from MyTable mt inner join Excel_Source Es
        on Mt .Field1 = Es.Field1 AND Mt AID = Es.AID AND Mt BID = Es.BID
  if @@Error <> 0
      begin
      set @AnyError = @@Error
      end
end

if  @AnyError <> null
begin
   Rollback Trans T1
   print ' Error Occured TranSaction RollBacked'
else
  Commit Trans t1
   print ' Transaction Completed'
End


Melih SARICA
0
mcrmgAuthor Commented:
@BillAn1,

So, UPDATE will get fired everytime even if the new record get inserted??  thx
0
mcrmgAuthor Commented:
If I am noy using CURSOR, is there a way that I can loop through that staging table?  thx
0
BillAn1Commented:
"So, UPDATE will get fired everytime even if the new record get inserted??"
If you want it to fire automatically, you need a trigger.

"is there a way that I can loop through that staging table?"
you dont want/need to loop through the staging table. A set-based piece of SQL can do the whole thing in one go, you do not need to actually do it one row at a time.
0
mcrmgAuthor Commented:
>A set-based piece of SQL can do the whole thing in one go, you do not need to actually do it one row at a time.

Can you show me how??  thx

One of the reasons that I used cursor was because I do not know how to loop through the staging table properly......  thx again
0
BillAn1Commented:
SQL is 'set based'. That means that it will automatically run for multiple rows etc, it does not need anything specific to tell it to loop through a table. Hence, the examples given will effectvely 'loop through'

If you give more detail on what you want to actually happen, the 'Syntax Here' component, we can help structure your query for you.

As a more complete example, see below for waht I mean.

suppose you have a simple table called Table1
Col1 Col2
 1       2
 2       4
 3       5
 4       3

and a 2nd table called Table2
Col1 Col4
  1    6
  3    7
  9    9


You want Table2 to contain a row for each value of Col1 from Table1,
and the value of Col4
should be incremented by 1 if there is already a record,
otherwise insert a new one, and set the value to 1
plus, leave any records that already exist, but do not have a corresponding record in Table1.

i.e. the final result you want in Table2 is
   1     7
   2     1
   3     8
   4     1
   9     9

so, first off - do the update

UPDATE Table2
SET Col4 = Col4 + 1
WHERE EXISTS (SELECT 1 FROM Table1 WHERE Table1.Col1 = Table2.Col1)

Secondly, do the inserts

INSERT INTO Table2
SELECT Col1, 1
FROM Table1
WHERE NOT EXISTS (
      SELECT 1 FROM Table2 WHERE Table1.Col1 = Table2.Col1
)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken SelviaRetiredCommented:
I'm still watching this thread and feel compelled to say for what seems like the 4th time, if you will post the entire, actual, real, complete procedure, someone will be able to show you exactly how to eliminate the cursor.
0
mcrmgAuthor Commented:
@kselvia,

I know that in order to help EEs to help me, it is better to have more details.  If I posted all the code, my boss will kill me right away.......   lol
So, I will do my best............  thx for the understanding....


@BillAn1,

So, by your example, SQL will go through Table1 by itself, I do not need to do any coding for the "looping through part"?  thx
0
BillAn1Commented:
yes, try not to think of SQL as procedural. An SQL statement is normally set-based, it effects multiple rows (depending on what you specify in your WHERE clause) in one go, try not to think of it as looping through, it does all rows "simultaneously" (at least conceptually........)

and, as kslevia says, the more detail you can give, the more help we can provide....
0
mcrmgAuthor Commented:
okay, I will try that and post back....thanks for EEs' help.......
0
mcrmgAuthor Commented:
Since I have several tables that I need to insert or update(EE has help me on the update part)

For the rest of the tables, I want to force sql to do the insert, for ex, for table3, I do not want to check if the record exists in db or not, I want to insert into table3 from table1.  thx
0
BillAn1Commented:
then the basic syntax is just as follows :
insert into Table3 (col1, col2, col3)
select col1, col2, col3
from table1
0
mcrmgAuthor Commented:
Thank you.
0
Melih SARICAOwnerCommented:
mcrmg.. check the codes that i ve posted above


Melih SARICA
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.