Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

create table valued function Expand / Collapse
Author
Message
Posted Sunday, July 28, 2013 1:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 1,072, Visits: 6,338
SQL_Surfer (7/28/2013)
Can you give me some pointers of rewriting this query? Can data be grabbed at 1 shot instead of doing UNION ALL.

I think so. Post the whole query and we'll see.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1478398
Posted Sunday, July 28, 2013 4:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 327, Visits: 853
Sorry, I cannot post the actual code for security reasons. I have figured out a way to write a function that returns the value that I am looking for which works perfect. Scalar function was taking really long about 20s; then I converted to MultiLine table valued function. But this is also taking about 8s to return result which is about 1400 rows. Is there anyway to convert this to Inline TVF? I am hopeing Inline TVF would give some perfromance gain. Or any other suggestion to gain performance?



CREATE FUNCTION [dbo].[getInfo]
(

@a varchar(100)

)
RETURNS
@Info TABLE
(

ReturnValue varchar(max)

)
AS
BEGIN


DECLARE @Var1 varchar(100), @Var2 varchar(100), @Var3 varchar(100), @Var4 varchar(100), @Var5 varchar(100)
DECLARE @ReturnVal1 varchar(100), @ReturnVal2 varchar(100), @ReturnVal3 varchar(100), @ReturnVal3 varchar(100), @ReturnVal5 varchar(100)
DECLARE @Result varchar(max)

SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a
SELECT @ReturnVal5 = RetVal, @Var4 = Val4 FROM Table5 WHERE Val5 = @Var5
SELECT @ReturnVal3 = RetVal, @Var3 = Val3 FROM Table4 WHERE Val4 = ISNULL(@Var4,@a)
SELECT @ReturnVal3 = RetVal, @Var2 = Val2 FROM Table3 WHERE Val3 = ISNULL(@Var3,@a)
SELECT @ReturnVal2 = RetVal, @Var1 = Val1 FROM Table2 WHERE Val2 = ISNULL(@Var2,@a)
SELECT @ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(@Var1,@a)

SET @Result = @ReturnVal1 + @ReturnVal2 + @ReturnVal3 + @ReturnVal3 + @ReturnVal5


INSERT INTO @Info VALUES (@Result)

RETURN
END
Post #1478413
Posted Monday, July 29, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 7,118, Visits: 13,484
SQL_Surfer (7/28/2013)
Sorry, I cannot post the actual code for security reasons....


Then obfuscate column and table names! Your pseudocode is so full of errors that making sense of it is more effort than writing the query.

Here's a best guess based upon the random ramblings you've posted so far:
SELECT Result = ReturnVal1 + ReturnVal2 + ReturnVal3 + ReturnVal4 + ReturnVal5
FROM Mytable m
OUTER APPLY (
SELECT ReturnVal5 = RetVal, Val4 FROM Table5 WHERE Val5 = m.mycol
) t5
OUTER APPLY (
SELECT ReturnVal4 = RetVal, Val3 FROM Table4 WHERE Val4 = ISNULL(t5.Val4,m.mycol)
) t4
OUTER APPLY (
SELECT ReturnVal3 = RetVal, Val2 FROM Table3 WHERE Val3 = ISNULL(t4.Val3,m.mycol)
) t3
OUTER APPLY (
SELECT ReturnVal2 = RetVal, Val1 FROM Table2 WHERE Val2 = ISNULL(t3.Val2,m.mycol)
) t2
OUTER APPLY (
SELECT ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(t2.Val1,m.mycol)
) t1
WHERE m.mycol = @a



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1478466
Posted Monday, July 29, 2013 3:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 327, Visits: 853
Where are you doing SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a ? I need to get max on that column from Mytable.
Post #1478760
Posted Tuesday, July 30, 2013 1:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 7,118, Visits: 13,484
SQL_Surfer (7/29/2013)
Where are you doing SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a ? I need to get max on that column from Mytable.


You can't get MAX(mycol) FROM Mytable when you have the filter mycol = @a.
MAX(mycol) can only be equal to @a, so @Var5 = @a and Val5 = m.mycol.
Unless of course your intention is to aggregate Mytable to one row. If it is, then adjust my query for this.


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1478846
Posted Tuesday, July 30, 2013 1:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 327, Visits: 853
U rock!! It worked awesome. I would never thought of rewritting it like that. Runtime is now 1 sec from 7-8 sec which is awesome. Thank you!!!
Post #1479152
Posted Wednesday, July 31, 2013 8:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:26 AM
Points: 341, Visits: 682
Why is this part of your code?

AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'')
+ '/' + ISNULL(Table4.MyCol,'')+ '/' + ISNULL(Table5.MyCol,'') IS NOT NULL

Using the ISNULL followed by a check that it IS NOT NULL will always evaluate to true. It just extra processing for nothing (unless there's something I'm missing?).
Post #1479519
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse