June 14, 2013 at 7:01 am
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
June 14, 2013 at 7:12 am
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
June 14, 2013 at 10:31 am
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 on googles mail service
June 19, 2013 at 4:28 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 19, 2013 at 7:37 am
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 on googles mail service
June 19, 2013 at 8:18 am
We clearly operate in very different environments, as I stick by my comments too - glad that I gave you a laugh 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply