July 2, 2020 at 3:30 am
Hi clever people!
The following is part of a TEST QUESTIONNAIRE. Although in the following two part question it
seems silly to create a function and there are other, better ways to do this the test questionnaire
is about CREATING A FUNCTION. Please bear that in mind.
The following are the two tables followed by the two questions the test poses:
CREATE TABLE TestTable1
(
ID INT,
Name VARCHAR(255)
)
INSERT INTO TestTable1
VALUES(1, 'Test1'),
(2, 'Test2'),
(3, 'Test3'),
(4, 'Test4'),
(5, 'Test5'),
(6, 'Test6')
CREATE TABLE TestTable2
(
ID INT,
JobDescription VARCHAR(255),
[Hours] INT
)
INSERT INTO TestTable2
VALUES (1, 'Developer', 10),
(2, 'Web Developer', 8),
(3, 'Architect', 12)
Here are the questions:
a) Create A FUNCTION that takes in a name as parameter and returns the ID for the
corresponding name from TestTable1.
(For the sake of the test the function is called TestTableFunction but feel free to use your own if you want)
b) How would yo optimize the following query taking into account that any text could be passed
as a parameter to the function created above, not just 'Test2'?
SELECT *
FROM TestTable1 a
JOIN TestTable2 b
ON b.ID = a.ID
WHERE a.ID = TestTableFunction('Test2')
I'm looking for answers that are better than mine so I'm asking you clever people for help.
Kind regards
July 2, 2020 at 7:19 pm
You say you have tried things and are looking for answers that are "better than" yours. To avoid us posting identical things than what you already have, could you post the answers you came up with?
To me, the two questions sound fairly easy. For the first one, the function takes a single parameter and returns a single int. I would do that with just a simple select and a where from the table. Nothing complicated there.
As for number 2, an index could help, removing the function could help, indexes could help (if not already present), selecting only the columns you care about could help, MIGHT get a better execution plan if you change the function lookup in the WHERE clause into a variable lookup... I personally would start by running the query and checking the execution plan; then I'd look at ways to rewrite the query to improve the execution plan and test them out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 23, 2020 at 8:14 am
You say you have tried things and are looking for answers that are "better than" yours. To avoid us posting identical things than what you already have, could you post the answers you came up with?
To me, the two questions sound fairly easy. For the first one, the function takes a single parameter and returns a single int. I would do that with just a simple select and a where from the table. Nothing complicated there.
As for number 2, an index could help, removing the function could help, indexes could help (if not already present), selecting only the columns you care about could help, MIGHT get a better execution plan if you change the function lookup in the WHERE clause into a variable lookup... I personally would start by running the query and checking the execution plan; then I'd look at ways to rewrite the query to improve the execution plan and test them out.
This helped me save time
July 23, 2020 at 12:42 pm
You still never gave us what your answer was...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply