InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:47:20 GMT20http://www.sqlservercentral.com/Forums/Topic1363745-392-1.aspxYou've got nested functions and you're accessing large amounts of data? That's a major design flaw. The only way to improve performance is back out of the use of multi-statement table valued functions and start addressing the T-SQL directly. The inline functions might be a good idea, but don't nest them. Otherwise, regular T-SQL queries are usually the best approach. If you really need to break down the code, which is a valid approach at times, then use temporary tables because they will provide statistics making queries that JOIN them or search against them more efficient.Also, one suggestion involved the NO_LOCK hint. I'm very wary of hints in general, but the NO_LOCK hint in particular is especially dangerous. Understand it thoroughly before you apply it.Tue, 25 Sep 2012 04:20:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1363745-392-1.aspxInline tvf's would get around the cardinality issues Gail mentions. Something like this:[code="sql"]DECLARE @strID int SELECT ID FROM Table1 WHERE ID = @strID AND REP = 1UNION ALLSELECT ID FROM Table2 WITH(NOLOCK)CROSS APPLY (SELECT REP FROM Table1 WHERE ID = @strID) x WHERE (x.REP <> 1 AND ParentID = @strID) OR (x.REP = 1 AND ParentID IN (SELECT * FROM dbo.fn_getOrgIDList(@strID)) )[/code]...where dbo.fn_getOrgIDList would have to be similarly tweaked.Tue, 25 Sep 2012 02:18:10 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1363745-392-1.aspxAssuming that the fn_GetOrgIdList returns a table then surely You're better doing something.INSERT INTO @table(ID)SELECT Table2.ID FROM Table2 WITH(NOLOCK) JOIN dbo.fn_GetOrgIdList(@StrId) org on org.<col>=Table2.ParentIdYou might get an optimisation from also doing IF (Select REP from Table1 Where Id=@strId)=1BEGINENDThose are just a couple of ideas, but without knowing what fn_getOrfIdList(@var) does its difficult to advise.Tue, 25 Sep 2012 00:57:29 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1363745-392-1.aspx55 sec to fetch, process and display 2.3 million rows isn't all that bad.It's not necessarily that function that's the problem, it's the usage of that function in complex queries (ie anything with a join and/or a where). Because of the table variable, SQL can't get a cardinality estimate for the function and is going to assume 1 row. Since it actually returns 2.3 million, that's a really bad estimate and it's very likely to cause seriously bad execution plans for other queries.I assume replacing that with a stored proc and temp table is out of the question?Is dbo.fn_getOrgIDList(@strID) also a multi-statement table valued function?p.s. [url]http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx[/url]Mon, 24 Sep 2012 16:30:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1363745-392-1.aspxHi Guys, Could you Please help me to improve the function Performance.This Function is used in allmost all procedures in my application.Execution statementSELECT * FROM dbo.fn_Child_09242012(1007538)--2320006 rows it took almost 55 sec Sql query Inside the Function CREATE FUNCTION dbo.fn_Child_09242012 ( @strID int ) RETURNS @table TABLE (ID int) AS BEGINDeclare @ind intSet @ind=(Select REP From Table1 where ID=@strID)If @ind=1BEGIN INSERT INTO @table(ID) SELECT @strID INSERT INTO @table(ID) SELECT ID FROM Table2 WITH(NOLOCK) WHERE ParentID in (Select * From dbo.fn_getOrgIDList(@strID)) ENDELSEBEGIN INSERT INTO @table(ID) SELECT ID FROM Table2 WITH(NOLOCK) WHERE ParentID = @strID END ReturnENDMon, 24 Sep 2012 16:21:44 GMTgordon.davis