complex correlated subqueries, inline functions with data access, functions in the where clause

hi,

I read from some web site that complex correlated subqueries, inline functions with data access, functions in the where clause is not good in T-SQL programming, is it real ? any URL reading for real world case ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Vikas GargAssociate Principal EngineerCommented:
Hi,

Using sub-queries cab be replaced with Joins
Since subquery is row-by-row operation while join is set based operation,

It is better to use set based operation in T sql rather than row by row.

The same way using function is also time consuming which should be replaced with query if possible
Since for function calculation each row has to go to call the function.

There are few articles are available on same.

http://javarevisited.blogspot.in/2012/07/subquery-example-in-sql-correlated-vs.html
marrowyungSenior Technical architecture (Data)Author Commented:
"Using sub-queries cab be replaced with Joins
Since subquery is row-by-row operation while join is set based operation,"

good !

how about function in where clause ?
marrowyungSenior Technical architecture (Data)Author Commented:
"The same way using function is also time consuming which should be replaced with query if possible
Since for function calculation each row has to go to call the function."

replaced with what kind of query ? please give example.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vikas GargAssociate Principal EngineerCommented:
Hi,

For simple example if you have a function which combines first name and last name

select *, dbo.funconcate(firstname,lastname) from table

can be replaced with select *, firstname + ' ' + lastname from table
Jim HornMicrosoft SQL Server Data DudeCommented:
marrowyung

Do us a favor and ask a single actionable question.  Asking multiple vague questions is really not going to encourage experts to respond, as it's not certain when we've answered the question, and experts will be wondering exactly how many follow-up questions there will be before points are awarded.

‚ÄčTop 10 Ways to Ask Better Questions, Number Nine..  Ask a question, don't read a fairy tale!, among others.
PortletPaulEE Topic AdvisorCommented:
please be careful....

a "correlated subquery" forms a row by row operation & only some subqueries are correlated subqueries

subqueries used in the select clause are usually "correlated subqueries", these are candidates to be replaced by joins

e.g. select ..., (select top (1) x from inner where inner.y = outer.z order by x) from outer
notice how that where clause crosses from inside the subquery to some table in the outer query, THAT is the indication of a "correlation", and that is why it forces a row by row operation.

In T-SQL you can also consider using the APPLY operator instead of placing correlated subqueries in the select clause. There are performance benefits in doing this see: SQLug.se - Itzik Ben-Gan - Creative Uses of the APPLY Operator, session 1session 2

My point is that there are many good reasons to use subqueries in a from clause.
e.g. there are circumstance where using a  subquery to produce a "derived table" is the best approach
So do not think all subqueries are bad please.

=============
Functions in the where clause should definitely be avoided.
e.g.
this is bad:  where cast([datetime_column] as date) = '20150822'
this is better:  where [datetime_column] >= '20150822' and [datetime_column]  < '20150823'

this is bad: where ISNULL([any_column],0) = 0
this is better: where [any_column] IS NULL
using functions on data in the where clause removes the ability to use indexes and forces table scans instead.

SARGABLE  Search ARGument ABLE
Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.

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
marrowyungSenior Technical architecture (Data)Author Commented:
Vikas Garg,

"select *, dbo.funconcate(firstname,lastname) from table

can be replaced with select *, firstname + ' ' + lastname from table"

so this example what it means is the function only place the firstanme and lastname together, this example is too simple and I don't think anyone will need to build a function like this, right? some bit more complex example and how to solve it?

"http://javarevisited.blogspot.in/2012/07/subquery-example-in-sql-correlated-vs.html"

this one seems only for MySQL but not MSSQL, right?

Paul Maxwell,

"a "correlated subquery" forms a row by row operation & only some subqueries are correlated subqueries"

you mean the subquery can contain some query which is not needed anymore ?

"My point is that there are many good reasons to use subqueries in a from clause.
e.g. there are circumstance where using a  subquery to produce a "derived table" is the best approach"

Tks,  an example please. how subquies can be use as derived table to opimize the code for example.

how about  inline functions with data access ? wyy it is no good ?

"using functions on data in the where clause removes the ability to use indexes and forces table scans instead. "

tks, this is a very good statement, so we expected to see table scan/index scan if the where clause like that?

"SARGABLE  Search ARGument ABLE"

so you mean Non-sargable argument always include function in the where clause ?

but this example from your link:

"Non-sargable: SELECT ... WHERE Email LIKE '%wikipedia.org'"

the email column still indexable, right?
PortletPaulEE Topic AdvisorCommented:
"a "correlated subquery" forms a row by row operation & only some subqueries are correlated subqueries"

you mean the subquery can contain some query which is not needed anymore ?

NO

I did provide an example as carefully as I could think of. A very common form of correlated subquery are those found in a select clause.  These subqueries have a where clause that relies on data somewhere else in the overall (outer) query. It is this FACT (a subquery being reliant on a value of the outer query) that DEFINES a "correlated subquery".

I repeat my earlier example, perhaps the extra words above will make it apparent why I used inner (inside the subquery) and outer (outside the subquery)

e.g. select ..., (select top (1) x from inner where inner.y = outer.z order by x) from outer
notice how that where clause crosses from inside the subquery to some table in the outer query, THAT is the indication of a "correlation", and that is why it forces a row by row operation.

Please review my comments on the question regarding derived tables and CTEs, there are examples of subqueries there.

so you mean Non-sargable argument always include function in the where clause ?

In most cases, yes, this is true.
I cannot say it is always true because someone will find some reason why it isn't

I did not write the examples at Wikipedia. I believe the authors would be attempting to illustrate a general principle that a LEADING wildcard can invalidate the use of an index, but a TRAILING wildcard does not. However different databases have different ways of dealing with this, I just do not want to start a long conversation on an example I didn't write.
marrowyungSenior Technical architecture (Data)Author Commented:
Paul Maxwell,

you are explaining good tks.
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
Microsoft SQL Server

From novice to tech pro — start learning today.