Creating a view with multiple inner joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a view with multiple inner joins

Author

Topic

munror
Starting Member

5 Posts

Posted - 2010-02-09 : 02:41:24

Hi There,

I'm trying to create a view with multiple inner joins, does anyone have any advise on how to make my query more efficient as it the are over 200000 sickleave entries and the view just doesn't get created, I think it takes up too many resources.

Any help would be appreciated.

CREATE VIEW temp_table_to_Query AS SELECT tb_COMPANY_Employee.employee_no, tb_COMPANY_Employee.Surname, tb_COMPANY_Employee.Gender, tb_COMPANY_Employee.DOB, tb_COMPANY_SickLeave.Start_Date, DATEPART(weekday, tb_COMPANY_SickLeave.Start_Date) AS WeekDay, SUM(tb_COMPANY_SickLeave.Leave_Duration) AS Tot_Sick, COUNT(tb_COMPANY_SickLeave.Employee_No) AS ToT_Ins, tb_COMPANY_Employee.Sallery, tb_COMPANY_Employee.Department , tb_COMPANY_Employee.SubDepartment, tb_COMPANY_Employee.SubDepartmentsLevel2, tb_COMPANY_Employee.SubDepartmentsLevel3, tb_COMPANY_Employee.SubDepartmentsLevel4, tb_COMPANY_Employee.Surname + N' ' + tb_COMPANY_SickLeave.Employee_No AS Sur_Employ FROM tb_COMPANY_Employee INNER JOIN tb_COMPANY_SickLeave ON tb_COMPANY_Employee.employee_no = tb_COMPANY_SickLeave.Employee_No INNER JOIN tb_COMPANY_Departments ON tb_COMPANY_Employee.Department = tb_COMPANY_Departments.DepartmentCode INNER JOIN tb_COMPANY_SubDepartments ON tb_COMPANY_Employee.SubDepartment = tb_COMPANY_SubDepartments.DepartmentCode INNER JOIN tb_COMPANY_SubDepartmentsLevel2 ON tb_COMPANY_Employee.SubDepartmentsLevel2 = tb_COMPANY_SubDepartmentsLevel2.DepartmentCode INNER JOIN tb_COMPANY_SubDepartmentsLevel3 ON tb_COMPANY_Employee.SubDepartmentsLevel3 = tb_COMPANY_SubDepartmentsLevel3.DepartmentCode INNER JOIN tb_COMPANY_SubDepartmentsLevel4 ON tb_COMPANY_Employee.SubDepartmentsLevel4 = tb_COMPANY_SubDepartmentsLevel4.DepartmentCode WHERE (tb_COMPANY_SickLeave.Start_Date >= CONVERT(DATETIME, '2008/11/01 00:00:00 AM', 102)) AND (tb_COMPANY_SickLeave.End_Date <= CONVERT(DATETIME, '2009/10/31 00:00:00 AM', 102)) AND (tb_COMPANY_SickLeave.LeaveCode = 28) GROUP BY tb_COMPANY_SickLeave.Start_Date, tb_COMPANY_Employee.Gender, tb_COMPANY_Employee.DOB, tb_COMPANY_Employee.employee_no, DATEPART(weekday, tb_COMPANY_SickLeave.Start_Date), tb_COMPANY_Employee.Surname + N' ' + tb_COMPANY_SickLeave.Employee_No, tb_COMPANY_Employee.Surname, tb_COMPANY_Employee.Sallery, tb_COMPANY_Employee.Department , tb_COMPANY_Employee.SubDepartment, tb_COMPANY_Employee.SubDepartmentsLevel2, tb_COMPANY_Employee.SubDepartmentsLevel3, tb_COMPANY_Employee.SubDepartmentsLevel4

I need a miracle!

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 04:30:19

would you please format your query and post it? Its really difficult to make out from current post. remember to use code tags while posting code.

Alternatively post some sample data from tables and sample output and we will help you with view creation query

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-09 : 05:09:53

Your statement with some formatting. This is tedious to do so it's best if, when you ask for help, you make it easy for us to help by formatting the code.


CREATE VIEW temp_table_to_Query AS
SELECT
	tb_COMPANY_Employee.employee_no
	, tb_COMPANY_Employee.Surname
	, tb_COMPANY_Employee.Gender
	, tb_COMPANY_Employee.DOB
	, tb_COMPANY_SickLeave.Start_Date
	, DATEPART(weekday, tb_COMPANY_SickLeave.Start_Date) AS WeekDay
	, SUM(tb_COMPANY_SickLeave.Leave_Duration) AS Tot_Sick
	, COUNT(tb_COMPANY_SickLeave.Employee_No) AS ToT_Ins
	, tb_COMPANY_Employee.Sallery
	, tb_COMPANY_Employee.Department
	, tb_COMPANY_Employee.SubDepartment
	, tb_COMPANY_Employee.SubDepartmentsLevel2
	, tb_COMPANY_Employee.SubDepartmentsLevel3
	, tb_COMPANY_Employee.SubDepartmentsLevel4
	, tb_COMPANY_Employee.Surname + N' ' + tb_COMPANY_SickLeave.Employee_No AS Sur_Employ
FROM
	tb_COMPANY_Employee
	INNER JOIN tb_COMPANY_SickLeave ON tb_COMPANY_Employee.employee_no = tb_COMPANY_SickLeave.Employee_No
	INNER JOIN tb_COMPANY_Departments ON tb_COMPANY_Employee.Department = tb_COMPANY_Departments.DepartmentCode
	INNER JOIN tb_COMPANY_SubDepartments ON tb_COMPANY_Employee.SubDepartment = tb_COMPANY_SubDepartments.DepartmentCode
	INNER JOIN tb_COMPANY_SubDepartmentsLevel2 ON tb_COMPANY_Employee.SubDepartmentsLevel2 = tb_COMPANY_SubDepartmentsLevel2.DepartmentCode
	INNER JOIN tb_COMPANY_SubDepartmentsLevel3 ON tb_COMPANY_Employee.SubDepartmentsLevel3 = tb_COMPANY_SubDepartmentsLevel3.DepartmentCode
	INNER JOIN tb_COMPANY_SubDepartmentsLevel4 ON tb_COMPANY_Employee.SubDepartmentsLevel4 = tb_COMPANY_SubDepartmentsLevel4.DepartmentCode
WHERE
	tb_COMPANY_SickLeave.Start_Date >= CONVERT(DATETIME, '2008/11/01 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.End_Date <= CONVERT(DATETIME, '2009/10/31 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.LeaveCode = 28
GROUP BY
	tb_COMPANY_SickLeave.Start_Date
	, tb_COMPANY_Employee.Gender
	, tb_COMPANY_Employee.DOB
	, tb_COMPANY_Employee.employee_no
	, DATEPART(weekday, tb_COMPANY_SickLeave.Start_Date)
	, tb_COMPANY_Employee.Surname + N' ' + tb_COMPANY_SickLeave.Employee_No
	, tb_COMPANY_Employee.Surname
	, tb_COMPANY_Employee.Sallery
	, tb_COMPANY_Employee.Department
	, tb_COMPANY_Employee.SubDepartment
	, tb_COMPANY_Employee.SubDepartmentsLevel2
	, tb_COMPANY_Employee.SubDepartmentsLevel3
	, tb_COMPANY_Employee.SubDepartmentsLevel4

As long as all those objects in the FROM clause are base tables and not other views then this doesn't look too bad.

My guess is that you don't have good indices (or any indices?).

This is what you should do.


SET SHOWPLAN_ALL ON
GO

run the SELECT part (all of it) in the same management stuido window. This will give you the query plan.

You want to look for:

TABLE SCAN / INDEX SCAN. These are areas that will take up most of the time. With a good index you will be able to turn these into SEEKs which will be many many times faster.

If you want a graphical representation then you can put the SELECT statement into a management studio query window and select the "SHOW ACTUAL EXECUTION PLAN" from the querry tooltip. Then run the SELECT and you'll get a nice visual representation. Just look for the areas that take up the most time.

As a rule of thumb you want an index that covers each join condition if possible and a covering index that covers the where conditions

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 08:42:05

If you have indexes on Start_Date and End_Date this may help (assuming my logic is reasonable)


WHERE
	tb_COMPANY_SickLeave.Start_Date >= CONVERT(DATETIME, '2008/11/01 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.Start_Date <= CONVERT(DATETIME, '2009/10/31 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.End_Date >= CONVERT(DATETIME, '2008/11/01 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.End_Date <= CONVERT(DATETIME, '2009/10/31 00:00:00 AM', 102)
	AND tb_COMPANY_SickLeave.LeaveCode = 28

Might be worth trying that and adding a specific index to cover the query:

Start_Date, End_Date, LeaveCode

If LeaveCode = 28 is less common than End-Dates being within the year then perhaps change the index to be:

Start_Date, LeaveCode, End_Date

and as T.C. says you definitely need indexes on the JOINED tables' columns (but they are probably the PK of those tables already):

tb_COMPANY_SickLeave = Employee_No
tb_COMPANY_Departments = DepartmentCode
tb_COMPANY_SubDepartments = DepartmentCode
tb_COMPANY_SubDepartmentsLevel2 = DepartmentCode
tb_COMPANY_SubDepartmentsLevel3 = DepartmentCode
tb_COMPANY_SubDepartmentsLevel4 = DepartmentCode

and it would be worth trying (separate) indexes on tb_COMPANY_Employee for:

employee_no
Department
SubDepartment
SubDepartmentsLevel2
SubDepartmentsLevel3
SubDepartmentsLevel4

and seeing if any/all make any difference to the Query Plan.

If you change the SELECT statement to "SELECT COUNT(*)" (and delete the GROUP BY clause) how many rows are involved in this query?

munror
Starting Member

5 Posts

Posted - 2010-02-14 : 22:02:59

Thanks so much for your advise and assistance Charlie and Kristen, thanks for formatting the query Charlie, I am sure that took some time, I really appreciate it. Sorry that I didn't get back to you sooner but it's been crazy times! I didn't realize that indices made such a difference to the speed! Thanks also for your advise on the management studio query window, I've never used one before, I'll definitely look into it, I'm sure it'll make my life much easier! Thanks again for your time and invaluable advise!

Regards,
Richard

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-15 : 05:11:40

You're welcome munror.

quote:

I didn't realize that indices made such a difference to the speed!

Pretty amazing eh? best I've seen with my company is a change from over 14 hours brought down to under 2 minutes with some very minor WHERE clause adjustments and a proper covering index. Of course, then the users didn't believe that the process still *worked* as it didn't take "enough time" any more.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources