CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-execution-plan
CharithJ
CharithJ

Reputation: 47510

Question on Query execution

In the below query if the Patients table has 1000 records how many times TableValueFunction executes? Only once or 1000 time?

This is a query in a Stored Procedure, do you have a better idea to improve this?

SELECT * FROM Patients
WHERE Patient.Id In (SELECT PatientId FROM TableValueFunction(parameters..))

Upvotes: 2

Views: 101

Answers (2)

Martin Smith
Martin Smith

Reputation: 452988

To some extent it depends on whether you are talking about an inline TVF or a multi statement one.

A multi statement TVF is totally opaque to the query optimiser. It always assumes that it will return 1 row and it will not get expanded out into the main query.

Because of the 1 row assumption then if your Patients table is indexed on PatientId you will probably get a nested loops join with the TVF as the driving table meaning that it is only executed once.

If it is not indexed and you get a hash or merge join both of these methods only process both inputs once.

An inline TVF gets merged into the query itself. So the function itself is never executed as such. However SQL Server can then refer to cardinality information and might order the plan such that the query contained in the TVF appears on the inner side of a nested loops join and has a number of executions greater than one.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

It depends on what you are using as parameters. If the parameters are constants the function will execute one time but if the parameters are fields from Patients the function will execute as many times as there are rows in table Patients.

Upvotes: 4

Related Questions