July 5, 2013 at 12:21 am
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 = @Var1
Thanks in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
July 5, 2013 at 12:25 am
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 = @Var1
Thanks in advance
Try 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)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2013 at 1:27 am
ALTER FUNCTION [dbo].[IF_GetVar2]
(@Var1 VARCHAR(10))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
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.
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
July 5, 2013 at 1:42 am
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,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
July 5, 2013 at 1:53 am
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.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply