May 9, 2012 at 9:47 am
hi
i have cursor in my sp ,so i tried to remove it with while loop in order to enhance performance,but it takes more time than cursor.
I am calling 5-6 sp inside my main sp and using table variables.
what causes it to run longer, what is the alternative of any of this??
thankis
May 9, 2012 at 9:52 am
...
what is the alternative of any of this??
...
Alternative is here: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
, but you've been referred to this magic place so many times but failed to follow simple tips...
I have a question for you, for a change:
I have written the sql script but it's not doing what I want it to do. Also, it does run slower than I want. Do you have any idea of how it can be fixed or changed to do what I need and do it faster?
May 9, 2012 at 9:53 am
May 9, 2012 at 10:35 am
riya_dave (5/9/2012)
hii have cursor in my sp ,so i tried to remove it with while loop in order to enhance performance,but it takes more time than cursor.
I am calling 5-6 sp inside my main sp and using table variables.
what causes it to run longer, what is the alternative of any of this??
thankis
CURSORS perform poorly, because they are R-BAR (row-by-agonizing-row). Changing this to a loop does nothing to change this feature and also requires you to set up structures to manage the housekeeping that is automatically and more efficiently done with a CURSOR.
The way to improve CURSOR performance is to replace them with a set-based solution. Since we don't know what your CURSOR is trying to do, we can't recommend an appropriate set-based solution.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 9, 2012 at 11:00 am
riya_dave (5/9/2012)
hii have cursor in my sp ,so i tried to remove it with while loop in order to enhance performance,but it takes more time than cursor.
I am calling 5-6 sp inside my main sp and using table variables.
what causes it to run longer, what is the alternative of any of this??
thankis
I hate to say it, but I knew this post was coming. You do realize that had you given us what we asked for many posts ago on several threads you posted we would have shown you a better way to write your stored procedure without using a cursor or while loop.
So the question now is, are you finally going to provide us with everything we need to help you?
May 9, 2012 at 12:57 pm
riya_dave (5/9/2012)
hii have cursor in my sp ,so i tried to remove it with while loop in order to enhance performance,but it takes more time than cursor.
I am calling 5-6 sp inside my main sp and using table variables.
what causes it to run longer, what is the alternative of any of this??
thankis
What causes it to run longer?
More disk activity. Let's walk through it:
A c.u.r.s.o.r. is one select statement that reads a record from a table, and gets some of the columns, and puts them into variables. Your loop then does "stuff" with those variables. Repeat for the next record in the c.u.r.s.o.r. - but it only has to get the next record, not re-run the select statement.
Your while loop runs a select statement to get the next record from a table, and puts them into variables, and then to do "stuff" with them. The loop increments a counter, or possibly even updates or deletes a record in the table (that prevents that record from being selected again), and then repeats the entire process.
C.u.r.s.o.r. - one select statement
While loop - one select statement per row. Plus possible updates / deletes.
The While loop has more (potentially a LOT more) activity reading data from the table. Since reading data from the disk is the second slowest thing that can happen (the slowest is writing data to disk), it takes longer. Sometimes, a lot longer. In all honesty, I'd rather see a c.u.r.s.o.r. than someone attempting to avoid the c.u.r.s.o.r. with a while loop that causes a lot more disk activity and slower performance.
what is the alternative of any of this?
Learning how to work in sets. SQL Server is designed to work best in sets. SQL Server can work procedurally, but it is not efficient to do so. When you start processing rows in a RBAR (Row-By-Agonizing-Row) manner, performance goes away.
Pretend that the SQL command "WHILE" is no longer supported, and learn how to work with sets. There are a lot of good articles on this site that will help you.
Think about these:
1. Quit thinking about what you want to do to that row... instead think about what you want to do to that column.
2. Indexes are built on columns, not rows.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 9, 2012 at 1:33 pm
thanks ,
it was really helpful.
i am thinking which join is better ,i am using inner and left join.
this is the only things i can work on
May 9, 2012 at 1:39 pm
riya_dave (5/9/2012)
i am thinking which join is better ,i am using inner and left join.
Neither. They do different things, so you use the one you need for the data that you want returning.
this is the only things i can work on
If you want to improve performance, focus on getting rid of the looping construct entirely. Anything else will give you minimal gains relative to that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 2:02 pm
i tried join also.nothing seems to work.
my question is i have table variables,i can use
UNIQUE CLUSTERED ( id ,account)
in table definition. and its improving performance,but i need to insert duplicate rows.
is there any way i can use any other things on my table variable,which needs to insert duplicate values.
May 9, 2012 at 2:07 pm
You aren't going to get the help you need as long as you refuse to provide the information we need. Again, plesae read and follow the instructions in this article, http://qa.sqlservercentral.com/articles/Best+Practices/61537/. Once you have provided that which we need, we can help you.
May 9, 2012 at 2:08 pm
riya_dave (5/9/2012)
is there any way i can use any other things on my table variable,which needs to insert duplicate values.
Temp table, proper indexing, get rid of the cursor, stop guessing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 2:35 pm
riya_dave (5/9/2012)
i tried join also.nothing seems to work.
my question is i have table variables,i can use
UNIQUE CLUSTERED ( id ,account)
in table definition. and its improving performance,but i need to insert duplicate rows.
is there any way i can use any other things on my table variable,which needs to insert duplicate values.
Add an identity column, and include that column as a key in the unique index.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 9, 2012 at 3:05 pm
ok.i insert identity column.
but getting error
Msg 1077, Level 16, State 1, Procedure , Line 837
INSERT into an identity column not allowed on table variables.
Msg 8101, Level 16, State 1, Procedure, Line 858
An explicit value for the identity column in table '@temp21' can only be specified when a column list is used and IDENTITY_INSERT is ON.
May 9, 2012 at 3:12 pm
Why are you trying to insert into an identity column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 3:20 pm
ok.
i remove inserting in identity,but still nt improving performance
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply