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

Need to optimize/Rewrite this query.. Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 12:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:54 PM
Points: 390, Visits: 2,384
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
Post #1470581
Posted Friday, July 5, 2013 12:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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)




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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1470583
Posted Friday, July 5, 2013 1:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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.



“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 #1470606
Posted Friday, July 5, 2013 1:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:54 PM
Points: 390, Visits: 2,384
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
Post #1470615
Posted Friday, July 5, 2013 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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 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 #1470618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse