Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Need to optimize/Rewrite this query.. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, July 05, 2013 12:21 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, December 03, 2013 3:02 PM Points: 374, Visits: 2,227
 Implement a function with below logic in an efficient way ...Pass @var1 and return @var2 ...How can it be done as it does table scan now ??DECLARE @Var1 VARCHAR(10)DECLARE @Var2 VARCHAR(100)SELECT @var2= var2 FROM #test WHERE var1 = @Var1Thanks in advance Thanks,ChinnaIts the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1470581
 Posted Friday, July 05, 2013 12:25 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 11:25 AM Points: 4,632, Visits: 10,568
 Sri8143 (7/5/2013)Implement a function with below logic in an efficient way ...Pass @var1 and return @var2 ...How can it be done as it does table scan now ??DECLARE @Var1 VARCHAR(10)DECLARE @Var2 VARCHAR(100)SELECT @var2= var2 FROM #test WHERE var1 = @Var1Thanks in advanceTry creating an index on your temp table before running that query - something like this:`create nonclustered index ix_test_var1 on #test(var1) include(var2)` ____________________________________________________________________________________________Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1470583
 Posted Friday, July 05, 2013 1:27 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 7:32 AM Points: 6,274, Visits: 12,089
 `ALTER FUNCTION [dbo].[IF_GetVar2] (@Var1 VARCHAR(10))RETURNS TABLE WITH SCHEMABINDING ASRETURN SELECT var2 FROM test WHERE var1 = @Var1;GO-- Testing:SELECT var2 FROM IF_GetVar2('Somevalue')-- Examine the execution plan and create a new index -- on table 'test' to support seeks if necessary - as in Phil's post.` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1470606
 Posted Friday, July 05, 2013 1:42 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, December 03, 2013 3:02 PM Points: 374, Visits: 2,227
 Thanks Guys for the response.. But just want to know if there is a workaround for this issue using crossapply ?AS it is looping through all the records now .. Thanks,ChinnaIts the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1470615
 Posted Friday, July 05, 2013 1:53 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 7:32 AM Points: 6,274, Visits: 12,089
 What do you mean by 'looping'? A table or clustered index scan? You can incorporate an iTVF into a query using APPLY but that won't help at all if there isn't a supporting index for the query. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1470618

 Permissions