Thanks Lynn for Chris's modified function.

Both that function and the one in the DB backup I provided both execute in the same amount of time.

There is a mistake in the view, I posted the correct line in an earlier post.

This...

Chris,

Just a simple execute the two views, the original came back in 12 seconds, yours was over 2 minutes.

Lynn,

No you where right on the mappings

Now to throw...

There is a mistake in the view in the database, got a bit delete happy with obfuscating the view.

The line

join StandardsGroupsClassification ccmap

ON ccmap.PID = s.SID

should be

join StandardsGroupsClassification ccmap

ON...

Sorry all, went on holiday, just back on with this now.

I have created a database and backed it up which you can download from here

http://antg.dyndns-home.com:8080/myweb/other/joiner.bak.

It contains the tables with sample...

This is the view, currently obfuscating the tables and generating some sample data which will follow if needs be.

selects.SID,

s.Label,

s.State,

clAge.ClassID AS AgeGradeID,

clSubject.ClassID AS SubjectID,

s.Description,

dbo.functioncall(s.SID) AS ResourceList

from Stand s

join...

Thanks Lynn I will give that a go.

I just took Cadavre's solution and change it to a iTVF and craeated a test view and it took 13 seconds to run.

I...

Essentially yes just a comma seperated of CID's from table1 & 3 based on a SID.

The SID in both table2 and table3 will link together as they do map back...

We have a hierarchy structure which is what is worked out in tables 1 and 2, where a CID maybe me mapped to more than 1 PID, a PID is...

Hope this help Lynn, its only for 1 STD but should hopefully be a small enough sample of data to see how the tables relate.

Sample data for STD ID 4946

INSERT...

Sure, I am just obfuscating the view definition and will supply DDL and sample data for the tables in the view. So that you have the bigger picture to...

The tables are as follows

CREATE TABLE [dbo].table1(

[PID] [int] NOT NULL,

[CID] [int] NOT NULL,

CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED ([PID] ASC,[CID] ASC)

)

GO

CREATE TABLE [dbo].[table2](

[PID] [int] NOT NULL,

[SID] [bigint] NOT NULL,

CONSTRAINT [PK_tbl2] PRIMARY...

The CTE within the function unfiltered on a SID brings back over 4.2 million rows,

John's solution just run again took 8mins 8 seconds

Lynn's solution as used in a iTVF took 18 minutes 26 seconds.

Thanks all.

With John's solution the view complete its run in 7 minutes 7 seconds and didnt max out TempDB.

I have had to change Lynn's solution slightly

DECLARE @descr VARCHAR(MAX) = '',...