improve performance of sp

Question

improve performance of sp

Viewing 15 posts - 16 through 30 (of 51 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

You aren't going to improve the performance of your stored procedure until you eliminate the cursor(s) and implement set-based logic to accomplish what you are doing in your cursor row by agonizing row.

riya_dave Hall of Fame Points: 3106 More actions · Answer 2

i remove cursor with while ,not improving still.

what other i need to use in place of cursor

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

riya_dave (5/9/2012)
i remove cursor with while ,not improving still.
what other i need to use in place of cursor

A WHILE loop is still row by agonizing row processing, just like a cursor. You need to develop a set-based solution to solve your problem.

riya_dave Hall of Fame Points: 3106 More actions · Answer 4

i got it,but anyhow i have to use while loop for condition

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 5

riya_dave, I've not been here for a couple of months, but you have at least three of the best experts giving you free help here. You haven't given them any information as to what you want to do, as far as I can see

So I have a tip on that basis. Replace the whole SP with SELECT GETDATE(). This is quick and might return the results you want based on the information supplied.

Best of luck.

Tim

.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

riya_dave (5/9/2012)
i got it,but anyhow i have to use while loop for condition

Why?

Take a break, let me ask you to solve the following hypothetical problem.

I own a company that makes widgets. What a widget is does not matter. All that matters is my company is very good at making widgets. I have been in business for 10 years and my employee table (Emps) has 2135 records, of which 176 are active employees. The others are no longer employed by my company.

This past year our recurring revenue has more than doubled and I want to share some of this with my employees by giving them all a raise. There are 4 classes of employees, and based on their class I want to give either a 10% (1), 8% (2), 6% (3) or 4% (4) raise. The relevent columns in the Emps table for this problem are EmpId, Salary, Class, IsActive. Class is 1,2,3, or 4. IsActive is 0 or 1 with 1 indicating an active employee.

Write the SQL needed to give each employee the appropriate pay raise.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

riya_dave (5/9/2012)
i got it,but anyhow i have to use while loop for condition

Okay, riya_dave, do you have a solution yet? I have 3 cursor-based solutions and a set-based solution already written. Let me know if you want to see them.

WayneS SSC Guru Points: 95405 More actions · Answer 8

Lynn Pettis (5/9/2012)
riya_dave (5/9/2012)
i got it,but anyhow i have to use while loop for condition
Okay, riya_dave, do you have a solution yet? I have 3 cursor-based solutions and a set-based solution already written. Let me know if you want to see them.

Writing cursors now Lynn? Was working with Oracle that horrible?

The force is getting weak with this one...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 9

WayneS (5/9/2012)
Lynn Pettis (5/9/2012)
riya_dave (5/9/2012)
i got it,but anyhow i have to use while loop for condition
Okay, riya_dave, do you have a solution yet? I have 3 cursor-based solutions and a set-based solution already written. Let me know if you want to see them.
Writing cursors now Lynn? Was working with Oracle that horrible?
The force is getting weak with this one...

Yes, I wrote cursors. Trying to show riya_dave the difference between cursor-based solutions and set-based solution. I'm just waiting to see what kind of solution he develops for the problem I gave him above.

He wants help improving his stored procedure, he needs to give us the information we need to that. How better than showing him the difference?

WayneS SSC Guru Points: 95405 More actions · Answer 10

Lynn Pettis (5/9/2012)
WayneS (5/9/2012)
Lynn Pettis (5/9/2012)
riya_dave (5/9/2012)
i got it,but anyhow i have to use while loop for condition
Okay, riya_dave, do you have a solution yet? I have 3 cursor-based solutions and a set-based solution already written. Let me know if you want to see them.
Writing cursors now Lynn? Was working with Oracle that horrible?
The force is getting weak with this one...
Yes, I wrote cursors. Trying to show riya_dave the difference between cursor-based solutions and set-based solution. I'm just waiting to see what kind of solution he develops for the problem I gave him above.
He wants help improving his stored procedure, he needs to give us the information we need to that. How better than showing him the difference?

Well, how about a little bit of guidance? Let's show him how to make sample data for your "problem" in a set based way:

DECLARE @Emps TABLE (

EmpId INT IDENTITY,

Salary numeric(8,2),

Class tinyint,

IsActive bit);

with cte as

(

select top (2135)

RN = row_number() over (order by (select null))

from sys.all_columns sac

)

insert into @Emps (Class, IsActive, Salary)

select Class = NTILE(4) over (order by RN),

IsActive = CASE WHEN RN % (2135/176) = 0 THEN 1 ELSE 0 END,

abs(checksum(newid()))/100000.0

from cte;

select *

from @Emps;

riya_dave: at least one set based solution is even easier than this code for making the sample data.

Also, please note that this now tells us:

1. Table structure involved. (We don't have to guess the data types of any columns.)

2. Sample data that shows the problem. (2135 records, 176 marked as active, all divided into 4 groups, all with a salary)

I know you've been pointed to the first link in my signature numerous times. In between Lynn's definition of the problem, and this sample data, this shows you the best way to post your problems so that you can get help in solving your problem.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95405 More actions · Answer 11

riya_dave (5/9/2012)
ok.
i remove inserting in identity,but still nt improving performance

The identity column was only to aid you in adding a unique index on your table variable with duplicate values.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95405 More actions · Answer 12

riya_dave (5/9/2012)
i remove cursor with while ,not improving still.
what other i need to use in place of cursor

See my earlier post where I explained why a while loop without a c.u.r.s.o.r is worse than a c.u.r.s.o.r.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95405 More actions · Answer 13

Tim Walker. (5/9/2012)
riya_dave, I've not been here for a couple of months, but you have at least three of the best experts giving you free help here. You haven't given them any information as to what you want to do, as far as I can see
So I have a tip on that basis. Replace the whole SP with SELECT GETDATE(). This is quick and might return the results you want based on the information supplied.
Best of luck.
Tim

Hi Tim.

SELECT 1 might be a wee bit faster.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 14

riya_dave, still waiting to see what kind of solution you came up with before I show you the four solutions I put together in 30 minutes.

Wayne showed you how to setup some test data, and it won't take me much to fixup my code to use his test suite in my code.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 15

riya_dave (5/9/2012)
i remove cursor with while ,not improving still.
what other i need to use in place of cursor

TSQL 101, SQL for Dummies, or any other TSQL book for beginners. Spend a few weeks running through the examples. It will take you far less time to learn the basics from a book than by posting random questions here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden