CREATE TABLE tbl_Clauses
	(
	ClauseID smallint identity (1,1),
	Standard varchar(100),
	Clause varchar(100),
	Description varchar(100),
	IsAS9100 bit not null,
	IsActive bit not null,
	Primary key clustered (ClauseID),
	Unique (Standard, Clause)
	)

	CREATE TABLE jntbl_RefDocToGroup
	(
	Group_Name varchar(100)not null,
	RefDoc_Title varchar (100)not null,
	Primary Key Clustered (Group_Name, RefDoc_Title)
	)

	CREATE TABLE tbl_GroupClauses
	(
	ID smallint primary key identity (1,1),
	Group_Name varchar (100)not null,
	Standard varchar (100) not null,
	Clause varchar (100)not null,
	IsPrimary bit not null,
	Unique (Group_Name, Clause,Standard)
	)

	CREATE TABLE tbl_Groups
	(
	Group_ID smallint identity,
	Dept_Name varchar(100),
	Group_Name varchar(100), 
	ManagerEmpID smallint,
	Key_Processes varchar(500),tb
	ManagerEmail varchar (100),
	Primary Key clustered (Group_ID),
	Unique(Group_Name)
	)

CREATE VIEW v_GroupInfo
AS
SELECT Dept_Name,v_ConcatGroupClauses.Group_Name,tbl_Groups.Group_ID,Supervisor as Supervisors,ManagerName, PrimClauses as PrimaryClauses, SecClause as SecondaryClauses, RefDoc as RefDocs,Key_Processes,  v_ManagerInfo.ManagerEmail, Email as Supervisor_Email
   from dbo.v_ConcatGroupClauses
   inner join dbo.v_ConcatGroupRefDocs 
      on v_ConcatGroupClauses.Group_Name = v_ConcatGroupRefDocs.Group_Name
   inner join dbo.v_ConcatGroupSecClauses
      on v_ConcatGroupClauses.Group_Name = v_ConcatGroupSecClauses.Group_Name
   inner join dbo.v_ConcatGroupSupervisors
      on v_ConcatGroupClauses.Group_Name = v_ConcatGroupSupervisors.Group_Name
   inner join dbo.v_ManagerInfo
      on v_ConcatGroupClauses.Group_Name = v_ManagerInfo.Group_Name
   inner join tbl_Groups
      on v_ConcatGroupClauses.Group_Name = tbl_Groups.Group_Name
GO

CREATE VIEW v_ConcatGroupRefDocs
AS
SELECT DISTINCT Group_Name, (SELECT SUBSTRING((SELECT ', ' + RefDoc_Title
FROM jntbl_RefDocToGroup
WHERE Group_Name  = t.Group_Name
FOR XML PATH('')),2,200000)) AS RefDoc
FROM jntbl_RefDocToGroup t
GO

CREATE VIEW v_ConcatGroupClauses
AS
SELECT DISTINCT Group_Name, (SELECT SUBSTRING((SELECT ', ' + Clause
FROM tbl_GroupClauses
WHERE Group_Name  = t.Group_Name
AND IsPrimary = '1'
FOR XML PATH('')),2,200000)) AS PrimClauses
FROM tbl_GroupClauses t
GO

CREATE VIEW v_ConcatGroupSecClauses
AS
SELECT DISTINCT Group_Name, (SELECT SUBSTRING((SELECT ', ' + Clause
FROM tbl_GroupClauses
WHERE Group_Name  = t.Group_Name
AND IsPrimary = '0'
FOR XML PATH('')),2,200000)) AS SecClause
FROM tbl_GroupClauses t
GO


CREATE VIEW v_GroupSupervisor
as
select group_name, Full_name as 'Supervisor',Email
from tbl_groups as g inner join (jntbl_GroupSupervisor inner join tbl_employee on jntbl_GroupSupervisor.emp_id = tbl_employee.emp_id)
on g.Group_ID = jntbl_GroupSupervisor.Group_ID
GO

CREATE VIEW v_ConcatGroupSupervisors
AS
SELECT DISTINCT Group_Name, (SELECT SUBSTRING((SELECT ', ' + Supervisor
FROM v_GroupSupervisor
WHERE Group_Name  = t.Group_Name
FOR XML PATH('')),2,200000)) AS Supervisor,(SELECT SUBSTRING((SELECT '; ' + Email
FROM v_GroupSupervisor
WHERE Group_Name  = t.Group_Name
FOR XML PATH('')),2,200000)) AS Email
FROM v_GroupSupervisor t
GO

quote:

---

Originally posted by theboyholty

Without going through this with a fine toothcomb (Its 10 to 5 on a Friday), I immediately noticed a couple of things with your code.

'The slowest query ever' has a number of joins, all of which join on Group_Name which being a VARCHAR(100) isn't going to be very quick. Its alwasy best to use an ID field if possible.
Also you've got some inline queries on your other views which is going to be a bit slow. There may be no way round these in your case but I would generally only use inline queries as a last resort.

So no mention of indexes there but often tweaking the queries can make a much bigger difference.


---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

---

quote:

---

Originally posted by nigelrivett

Can you materialise the views and index them. If you do that then you can try substituting the tables for the views and find out where the issue is.
Otherwise try omitting the views in turn to see which is causing issues.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by nigelrivett

Create tables from them.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

dbo.v_ConcatGroupClauses is not schema bound

Cannot schema bind view 'v_ConcatGroupClauses' because name 'tbl_GroupClauses' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

quote:

---

Originally posted by namman

How many rows returned if your query runs successfully?

How many rows are in each table?

Better if you can provide us some sample data (make it up) of those tables

---

       Group_Name       Standard  Clause  IsPrimary
Aircraft Repair Station	ISO9001	   4.1	      0
Aircraft Repair Station	ISO9001	   4.2.1      0
Aircraft Repair Station	ISO9001	   4.2.2      0
Aircraft Repair Station	ISO9001	   4.2.3      0
Aircraft Repair Station	ISO9001	   4.2.4      0 
Aircraft Repair Station	ISO9001	   5.2	      0

8	Engineering	Aircraft Repair Station	104	Aircraft Certification/Partnership for Safety
35	Salem Call Center	Call Center 	NULL	Customer Support and Service; Handling Customer Complaints.  Orders, returns, complaints, for consumer product.