SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / Help me optimizing stored procedure / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 08:50:16 GMT20RE: Help me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspxWe clearly operate in very different environments, as I stick by my comments too - glad that I gave you a laugh :-)Wed, 19 Jun 2013 08:18:51 GMTPhil ParkinRE: Help me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspx[quote]ALWAYS is a bit strong. [/quote]We will have to agree to disagree. I'm sticking with my statement.[quote]SQL devs should know this stuff.[/quote]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).[quote]If you know that the people who are going to be maintaining the code are not that proficient, then maybe. [/quote]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??[quote]the problems you mention should be sieved out during testing. [/quote]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:Wed, 19 Jun 2013 07:37:37 GMTTheSQLGuruRE: Help me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspx[quote]Even if that is what you intended (it often isn't) ALWAYS EXPLICITLY USE BEGIN/END with IF/ELSE constructs:[/quote]ALWAYS is a bit strong. I'm not going to start replacing [code="sql"]if object_id('tempdb..#tmp', 'U') is not null drop table #tmp[/code]with this [code="sql"]if object_id('tempdb..#tmp', 'U') is not null begin drop table #tmp end[/code]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.Wed, 19 Jun 2013 04:28:09 GMTPhil ParkinRE: Help me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspxWe 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:[code="sql"]IF ISNULL(@AFonly, 0) = 0SELECT * FROM views(creted some view) SELECT od.* FROM tableA AS hINNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2AND h.couponend_date ts &gt; GETDATE() AND h.coupon_ startdate = 5[/code]That really executes like this:[code="sql"]IF ISNULL(@AFonly, 0) = 0BEGIN SELECT * FROM views(creted some view) ENDSELECT od.* FROM tableA AS hINNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2AND h.couponend_date ts &gt; GETDATE() AND h.coupon_ startdate = 5[/code]Even if that is what you intended (it often isn't) ALWAYS EXPLICITLY USE BEGIN/END with IF/ELSE constructs:[code="sql"]IF ISNULL(@AFonly, 0) = 0BEGIN SELECT * FROM views(creted some view) ENDSELECT od.* FROM tableA AS hINNER JOIN View AS od ON h.column1= od.column1 AND h.column2= od.column2AND h.couponend_date ts &gt; GETDATE() AND h.coupon_ startdate = 5[/code]Or you may have meant this, which is VERY different:[code="sql"]IF ISNULL(@AFonly, 0) = 0BEGIN 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 &gt; GETDATE() AND h.coupon_ startdate = 5END[/code]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! :-)Fri, 14 Jun 2013 10:31:53 GMTTheSQLGuruRE: Help me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspxPlease post table definitions, view definitions, index definitions and execution plan as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Fri, 14 Jun 2013 07:11:59 GMTGilaMonsterHelp me optimizing stored procedurehttp://www.sqlservercentral.com/Forums/Topic1463543-3077-1.aspxi 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 departmentCreate Proc USP_Offersdep @Af AS BIT )ASBEGIN 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 &gt; GETDATE() AND h.coupon_ startdate = 5ENDin that table indexes are already ter on Column1 and Column2i have optimize it please suggest meFri, 14 Jun 2013 07:01:55 GMTaslamshaik6