April 2, 2016 at 10:31 am
How to write a sql statement that can get the name from employee table by using id from user table ?
--User table
DECLARE @user TABLE (id INT, name nvarchar(32),firstname nvarchar(32))
insert into @user values (1,'jason lee','jason')
insert into @user values (2,'ken law','ken')
insert into @user values (3,'agnes cheah','agnes')
insert into @user values (4,'Tony Phua','Tony')
insert into @user values (5,'Mary Yam','Mary')
select * from @user
--employee table
DECLARE @employee TABLE (title nvarchar(20), userid INT, manager Int, hrid Int)
insert into @employee values ('Accountant',1,4,2)
insert into @employee values ('Auditor',4,5,2)
insert into @employee values ('Junior Accountant',3,1,2)
select * from @employee
--Expected result
DECLARE @result TABLE (title nvarchar(20), userid INT, name nvarchar(32), manager Int, managername nvarchar(32), hrid Int, hrname nvarchar(32) )
insert into @result values ('Accountant',1,'jason lee',4,'Tony Phua',2,'ken law')
insert into @result values ('Auditor',4,'Tony Phua',5,'Mary Yam',2,'ken law')
insert into @result values ('Junior Accountant',3,'agnes cheah',1,'jason lee',2,'ken law')
select * from @result
April 2, 2016 at 10:39 am
This sounds like a homework assignment so I will give you a few pointers but not a full solution. If you can show what you already have, I can provide more help.
You need to join the @employee table to the @user table to combined information from the two, and you need to join on the linking column. Which for the user is userid <--> id, and for the manager is managerid <--> id. So that means that you actually join to the user table twice. Be sure to use aliases so that in the query you can distinguish between the two copies of the table.
April 2, 2016 at 10:59 am
here the sql statement
select title,userid,users.name,manager,manager.name managername,hrid,hr.name hrname
from employee inner join users on
userid = users.id inner join users manager on
manager = manager.id inner join users hr on
hrid = hr.id
But beside this, is there a better way to get the result? the employee table is quite big, I just concern about the performance of using inner join
April 2, 2016 at 1:31 pm
If the employees table has lots of rows, you shouldn't be using table variables, as they t get the benefit of statistics.
If these were physical tables, I'd suggest primary keys. I'd also suggest foreign keys and nonclustered indexes to cover them. You can't do too much to the design of table variables.
April 3, 2016 at 2:42 am
sqlbaby2 (4/2/2016)
here the sql statementselect title,userid,users.name,manager,manager.name managername,hrid,hr.name hrname
from employee inner join users on
userid = users.id inner join users manager on
manager = manager.id inner join users hr on
hrid = hr.id
But beside this, is there a better way to get the result? the employee table is quite big, I just concern about the performance of using inner join
I would personally use aliases for all tables (because that makes it easier to prefix columns, and easier to read prefixed column names. I would also prefix all columns so that whoever reads the query doesn't need to think about what table each column is from.
As to performance - if you actually need this report for the whole table, then there's not much you can do. Sure, there are some rewrites, but most of the time they will not change the performance because the query optimizer does all those rewrites (and a lot more) for you.
If the tables are huge and you only a small part of the results, then add a WHERE clause to only generate that part of the result set, instead of generating the entire result set and filtering at the client.
If the tables are huge and you need all of the result set, then it will probably be slow. I didn't run your code but I expect to see an execution plan that scans the employee table once, scans the users table three times, and uses hash joins to combine the results. (For smaller tables and if the users table is indexed, I would expect a scan of employees and three nested loops joins into index seeks on users - but that will not scale so that plan will be terrible for large tables).
Ed's remark about table variables is very relevant. I assume that you used table variables for the post here but not for your actual problem. However, if you do then you will probably get reallly bad performance. The problem with table variables is that SQL Server has no statistics so it has no idea how many rows there are. It has to make an assumption. And that assumption is a really low number, so you might get the plan with the nested loops joins, that does not scale well at all - and then when your real data is millions of rows, prepare to drink lots of coffee while waiting for the results. So yes, do use permanent tables (of, if you have to, temporary tables) for your data in this case.
April 5, 2016 at 3:57 am
Ed Wagner (4/2/2016)
If the employees table has lots of rows, you shouldn't be using table variables, as they t get the benefit of statistics.If these were physical tables, I'd suggest primary keys. I'd also suggest foreign keys and nonclustered indexes to cover them. You can't do too much to the design of table variables.
Do this to get accurate Table variable statistics.
It might be less bad to do a recompile than to have way wrong statistics.
select title, userid, users.name, manager,
manager.name managername, hrid, hr.name hrname
from employee
inner join users on userid = users.id
inner join users manager on manager = manager.id
inner join users hr on hrid = hr.id
option(recompile);
April 5, 2016 at 4:04 am
MadAdmin (4/5/2016)
Ed Wagner (4/2/2016)
If the employees table has lots of rows, you shouldn't be using table variables, as they t get the benefit of statistics.If these were physical tables, I'd suggest primary keys. I'd also suggest foreign keys and nonclustered indexes to cover them. You can't do too much to the design of table variables.
Do this to get accurate Table variable statistics.
It might be less bad to do a recompile than to have way wrong statistics.
select title, userid, users.name, manager,manager.name managername, hrid, hr.name hrname
from employee
inner join users on userid = users.id
inner join users manager on manager = manager.id
inner join users hr on hrid = hr.id
option(recompile);
Recompiling the query will not force an udate of the statistics. If you suspect that statistics are out of date, then run an UPDATE STATISTICS statement. After that, the statement will recompile automatically, without any need for that hint.
Compiling queries takes a lot of resources, so you should be restrictive with the OPTION(RECOMPILE) hint. There are valid use cases for it, but I don't think this is one of them.
April 5, 2016 at 4:17 am
Hugo Kornelis (4/5/2016)
MadAdmin (4/5/2016)
Ed Wagner (4/2/2016)
If the employees table has lots of rows, you shouldn't be using table variables, as they t get the benefit of statistics.If these were physical tables, I'd suggest primary keys. I'd also suggest foreign keys and nonclustered indexes to cover them. You can't do too much to the design of table variables.
Do this to get accurate Table variable statistics.
It might be less bad to do a recompile than to have way wrong statistics.
Recompiling the query will not force an udate of the statistics. If you suspect that statistics are out of date, then run an UPDATE STATISTICS statement. After that, the statement will recompile automatically, without any need for that hint.
Compiling queries takes a lot of resources, so you should be restrictive with the OPTION(RECOMPILE) hint. There are valid use cases for it, but I don't think this is one of them.
Oops, you are right, I meant
select title, userid, users.name, manager,
manager.name managername, hrid, hr.name hrname
from @employee employee
inner join @user users on employee.userid = users.id
inner join @user manager on employee.manager= manager.id
inner join @user hr on employee.hrid = hr.id
option(recompile);
Thanks Hugo for sending me to the kitchen for some coffee!!!
April 6, 2016 at 12:49 pm
MadAdmin (4/5/2016)
Oops, you are right, I meant
select title, userid, users.name, manager,manager.name managername, hrid, hr.name hrname
from @employee employee
inner join @user users on employee.userid = users.id
inner join @user manager on employee.manager= manager.id
inner join @user hr on employee.hrid = hr.id
option(recompile);
Thanks Hugo for sending me to the kitchen for some coffee!!!
You are "sort of" right that a recompile hint can be useful when using table variables instead of temporary tables or permanent tables.
The effect of this hint is that the query will recompile at the start of the query. Actual rowcounts in the table variables will be known and used instead of the standard assumption of 1 otherwise used. That can produce a better plan - but at the cost of extra compilations.
If the number of rows in the table is always very low, the plan will still be the same or it might be different but provide only a small saving. In that case, the extra effort for the recompile is not earned back.
If the number of rows in the table is always very high, then the recompile is likely to produce a much cheaper plan, so it will be worth it. However, the same effect can often be achieved even cheaper by replacing the table variables with temporary tables or permanent tables.
If the number of rows in the table can vary wildly between executions, then the recompile hint may actually be justified. And in such a case, it might even also be justified if you use temporary or permanent tables.
Or to give the short version of the above: before adding a recompile hint to the code in production, test test test and test some more to see the actual performance impact.
All this being said, I assume that the table variables are only used as a quick way to show the problem and that the actual situation for the original poster involves permanent tables. If that assumption is correct, then none of this applies to his/her situation.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply