How can I structure a SQL query so it is lazy

Let's say I have the following query:

SELECT
t1.ConfigKey,
[robo].dbo.fd_getC(t1.ConfigKey) as b_b,
t1.mn,
t1.ot,
t1.op,
t1.ov
FROM
[n].[dbo].[Cg] as t1 WHERE
ot like 'tos'
and
machineName like 'v%_T'
and
[robo].dbo.fd_getC(t1.ConfigKey) IS NOT NULL

maybe this is already going on, but how can i make sure it won't even compute the function [robo].dbo.fd_getC(t1.ConfigKey) as b_b unless machineName like 'v%_T'
Matt_DuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tony_angelopoulosCommented:
you should already be filtering down on that but here's a case statement for future reference:

case when machineName like 'v%_T' then [robo].dbo.fd_getC(t1.ConfigKey) else null end  as b_b

change null to whatever you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
<< maybe this is already going on, but how can i make sure it won't even compute the function [robo].dbo.fd_getC(t1.ConfigKey) as b_b unless machineName like 'v%_T' >>

As we are giving out the AND condition in the query, it would be computed for your result sets to be produced.

One another way to avoid that function being executed on all records is by applying Where conditions to reduce the no of records it will be applied.
In your case you have two conditions ot like and machineName like and those records which passes these criterias will be computed for that function for NULL Check.
SELECT t1.ConfigKey,[robo].dbo.fd_getC(t1.ConfigKey) as b_b,
t1.mn,t1.ot,t1.op,t1.ov
FROM [n].[dbo].[Cg] as t1 
WHERE ot like 'tos'
and machineName like 'v%_T'
and [robo].dbo.fd_getC(t1.ConfigKey) IS NOT NULL

Open in new window

0
tony_angelopoulosCommented:
If I remember my syntax for temp tables correctly, you could do it in two steps if you really don't want to compute it even in the where clause:

1st step gets only the results that have the machinename you want and pumps it into temp table,
second step draws from the temp table and computes
SELECT
t1.ConfigKey,
t1.mn,
t1.ot,
t1.op,
t1.ov
into #temp_t
FROM
[n].[dbo].[Cg] as t1 WHERE
ot like 'tos'
and
machineName like 'v%_T'
 
 
 
SELECT
t1.ConfigKey,
[robo].dbo.fd_getC(t1.ConfigKey) as b_b,
t1.mn,
t1.ot,
t1.op,
t1.ov
FROM
#temp_t t1 WHERE
[robo].dbo.fd_getC(t1.ConfigKey) IS NOT NULL

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.