Hi guys!
I have the following two test tables and have created the following function. My question follows these:
TestTable
ID Name
1 Test1
2 Test2
3 Test3
4 Test4
5 Test5
6 Test6
TestTable2
ID JobDescription Hours
1 Developer 10
2 Web Developer 8
3 Architect 12
CREATE FUNCTION [dbo].[TestTableFunction] (@name VARCHAR(255))
RETURNS INT
AS
BEGIN
DECLARE @IDNumber INT
SELECT @IDNumber = ID
FROM EcentricTestTable WHERE Name = @name
END
How can one optimise the following query taking into account that any text could be passed as a parameter
to the function, not just 'Test2'?
SELECT * FROM dbo.TestTable e
JOIN dbo.TestTable2 n
ON e.ID = n.ID
WHERE e.ID = dbo.TestTable('Test2')
Your function is a table, not a value. You're trying to compare a value, e.ID, to a table. You can't do that. You can join to your table instead, but you can't put your table into a WHERE clause like that.
Also, totally not understanding the need for the function here. Why not simply join to the EccentricTestTable and filter on the @name value?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2020 at 1:22 pm
Hi Grant
I have changed the Function as per your advice. Do you perhaps have advice as per my original question posed please?
Kind regards
July 1, 2020 at 3:39 pm
As I said, why not JOIN to the original table? Embedding it as a function doesn't make sense.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2020 at 6:08 pm
Here's an example of what Grant is telling you to do, replace your function and query with this:
SELECT e.ID, e.Name, n.JobDescription, n.Hours
FROM EcentricTestTable ett
INNER JOIN dbo.TestTable e
ON ett.ID = e.ID
INNER JOIN dbo.TestTable2 n
ON e.ID = n.ID
WHERE ett.Name = 'Test2';
Your use of a Scalar function will slow everything down, and seems to have no benefit considering how simple it's code is. Is there a reason you chose to use a function to implement this? If there are multiple records in the EcentricTestTable with the same name value, then maybe use a subquery to ensure only one of them is selected:
SELECT e.ID, e.Name, n.JobDescription, n.Hours
FROM dbo.TestTable e
INNER JOIN dbo.TestTable2 n
ON e.ID = n.ID
WHERE e.ID = (SELECT TOP 1 ID FROM EcentricTestTable WHERE Name = 'Test2' ORDER BY SomeOtherColumn);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply