Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help me optimizing stored procedure


Help me optimizing stored procedure

Author
Message
aslamshaik6
aslamshaik6
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
i have a Stored procedure which is consuming the most time on the server. i have find some solution for
can i do any of these

sql improvements, stagged data, web service caching

this SP shows all the activeoffers that have in a store based on department

Create Proc USP_Offersdep
@Af AS BIT
)
AS
BEGIN

SET NOCOUNT ON;

IF ISNULL(@AFonly, 0) = 0
SELECT * FROM views(creted some view)
SELECT od.* FROM
tableA AS h
INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2
AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5

END


in that table indexes are already ter on Column1 and Column2

i have optimize it please suggest me
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
Please post table definitions, view definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6012 Visits: 8314
We need more info like Gail said to be able to help with performance.

But I would like to point out a common TSQL programming flaw I see:

IF ISNULL(@AFonly, 0) = 0
SELECT * FROM views(creted some view)
SELECT od.* FROM
tableA AS h
INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2
AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5



That really executes like this:

IF ISNULL(@AFonly, 0) = 0
BEGIN
SELECT * FROM views(creted some view)
END

SELECT od.* FROM
tableA AS h
INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2
AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5



Even if that is what you intended (it often isn't) ALWAYS EXPLICITLY USE BEGIN/END with IF/ELSE constructs:

IF ISNULL(@AFonly, 0) = 0
BEGIN
SELECT * FROM views(creted some view)
END

SELECT od.* FROM
tableA AS h
INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2
AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5



Or you may have meant this, which is VERY different:

IF ISNULL(@AFonly, 0) = 0
BEGIN
SELECT * FROM views(creted some view)

SELECT od.* FROM
tableA AS h
INNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2
AND h.couponend_date ts > GETDATE() AND h.coupon_ startdate = 5
END



Doing this not only ensures YOU get the right code, but it keeps devs who may debug/refactor this in the future from screwing up too! :-)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19514
Even if that is what you intended (it often isn't) ALWAYS EXPLICITLY USE BEGIN/END with IF/ELSE constructs:


ALWAYS is a bit strong. I'm not going to start replacing

if object_id('tempdb..#tmp', 'U') is not null 
drop table #tmp



with this

if object_id('tempdb..#tmp', 'U') is not null 
begin
drop table #tmp
end



in my code. SQL devs should know this stuff.

If you know that the people who are going to be maintaining the code are not that proficient, then maybe. But nonetheless, the problems you mention should be sieved out during testing.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6012 Visits: 8314
ALWAYS is a bit strong.


We will have to agree to disagree. I'm sticking with my statement.

SQL devs should know this stuff.


But they don't. Or they are in a hurry, or their coffee spilled and distracted them, etc. Build a box around them (and yourself) with a VERY simple construct (that can be auto-created with SQL Prompt et al).

If you know that the people who are going to be maintaining the code are not that proficient, then maybe.


You may have 100% rock-star TSQL types at your company right now. What about starting next week, or 6 months or 3 years from now??

the problems you mention should be sieved out during testing.


Sorry, but that one is actually laughable!! If I had a nickel for every client I have ever had in about 20 years of database consulting that actually did GOOD (if any, usually) database testing I would have ... $0.00. Blink

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19514
We clearly operate in very different environments, as I stick by my comments too - glad that I gave you a laugh :-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search