SQL Server Performance Forum – Threads Archive

view + table function

Hello all,
I have a view which is calling a function table. I would like the table function to recieve dynamic parameters but my current code doesnt work.
I have 2 tables: 1 is a client table which lists all the clients, and the other is all the tests done by the clients. Clients can do multiple tests. I built a function table to pull back specific "tests". The function orders the tests by date and numbers them. All i have to pass the function is the client ID of the client whoes test i want, and pass it the test number i want. So if i want the first test the client done i pass the function the number 1, if i want the 5th test i pass it a 5 etc… The way the view is layed out is to show the client id and have the first 4 tests the client did in the same row. Here is the code for my view:
SELECT newinstruments.dbo.ClientInfo.ID, A.*, B.*, C.*, D.*
from newinstruments.dbo.ClientInfo LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.ClientInfo.ID, 1) as A on newinstruments.dbo.ClientInfo.ID = A.ID left outer join
dbo.FSDQScoreGetTable(newinstruments.dbo.ClientInfo.ID, 2) as B on newinstruments.dbo.ClientInfo.ID = B.ID LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.ClientInfo.ID, 3) as C on newinstruments.dbo.ClientInfo.ID = C.ID LEFT OUTER JOIN
dbo.FSDQScoreGetTable(newinstruments.dbo.ClientInfo.ID, 4) as D on newinstruments.dbo.ClientInfo.ID = D.ID
as you can see im trying to use the client id from the clientinfo table to get the return from my function table. Is this possible? Is there a better way? Thank you in advance

Hard to say without looking at FSDQScoreGetTable, but what Im suspecting is that the func does row-by-row operations, and so your view will perform badly (im just guessing remember!). Alternatives would be to consider restructing the view, to return individual tests as rows, rather than 4 sets of columns.
If thats no good, it may be an idea to abandon the function altogether, and put it all inline with subqueries and joins.
This might be ugly and less convenient, but could perform better You didnt say what youre actual problem was, Ive assumed it was a performance problem.
By all means paste the code for your function and there might be another way to write your view

Thanks for your reply Chappy, yeah i forgot to enter the error <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> The issue is that i get a syntax error: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near ‘.’. The syntax error points at the "from newinstruments.dbo.clientinfo area if i remove the references to newinstruments.dbo.clientinfo and just have the functions in there and manually add one client id to the function it works flawlessly and i get back all the tests done by that client. But i want it to do that for all clients. here is my function below: <pre id="code"> ALTER FUNCTION dbo.FSDQScoreGetTable (@ClientID as uniqueidentifier, @SurveyNumber as int) RETURNS @ScoreTable TABLE ( ClientID uniqueidentifier, Date datetime, TotalDifficulties int, EmotionalScale int, ConductScale int, HyperScale int, PeerScale int, ProsocialScale int, ImpScore int ) AS BEGIN INSERT @ScoreTable select S3.ClientID, S3.Date, S3.TotalDifficulties, S3.EmotionalSymptomsScale, S3.ConductProblemsScale, S3.HyperActivityScale, S3.PeerProblemsScale, S3.ProsocialScale, S3.ImpScore from ( select count(*) as Rank, A.* FROM (SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties, NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale, NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore FROM NewInstruments.dbo.vsdqstats WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID order by NewInstruments.dbo.VSDQStats.date) as A , (SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties, NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale, NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore FROM NewInstruments.dbo.vsdqstats WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID order by NewInstruments.dbo.VSDQStats.date) as B where A.date >= B.Date group by A.ClientID, A.Date, A.TotalDifficulties, A.EmotionalSymptomsScale, A.ConductProblemsScale, A.HyperActivityScale, A.PeerProblemsScale, A.ProsocialScale, A.ImpScore ) as S3 where S3.Rank = @SurveyNumber RETURN END </pre id="code"> I was thinking of just changing the function to return one of the values to take it out of the "from" area and put it back in the "select" area which has worked for me in the past when doing these types of queries. basically the function organizes the tests done by a specific client by date and appends a number to the row which indicates 1st test done, 2nd test done etc… If the client only did 2 tests and i call for the score of test 4 i get a null (which is what i want). Thank you for your time

Views are static structures, and as a rule should not be used to do overly complex processing. Your main query is also presenting data for four separate tests on successive columns, which is a de-normalized format. Normalization is the foundation for the whole concept of (relational) databases. Assuming a table Client, and a table Test with a FK on ClientId, here’s how clumsy it is to retrieve data in a de-normalized format: SELECT C.ClientName, FirstDetails.*, SecondDetails.*, ThirdDetails.*, FourthDetails.*
FROM dbo.Client C
LEFT JOIN (SELECT t.ClientId, MIN(t.TestId) FirstId FROM dbo.Test t GROUP BY t.ClientId) FirstTest
ON C.ClientId = FirstTest.ClientId
LEFT JOIN
(SELECT t.ClientId, MIN(t.TestId) SecondId FROM dbo.Test t GROUP BY t.ClientId
WHERE t.TestId NOT IN (SELECT TOP 1 t1.TestId FROM dbo.Test t1 WHERE t1.ClientId = t.ClientId ORDER BY t1.TestId)) SecondTest
ON C.ClientId = SecondTest.ClientId
LEFT JOIN
(SELECT t.ClientId, MIN(t.TestId) ThirdId FROM dbo.Test t GROUP BY t.ClientId
WHERE t.TestId NOT IN (SELECT TOP 2 t1.TestId FROM dbo.Test t1 WHERE t1.ClientId = t.ClientId ORDER BY t1.TestId)) ThirdTest
ON C.ClientId = ThirdTest.ClientId
LEFT JOIN
(SELECT t.ClientId, MIN(t.TestId) FourthId FROM dbo.Test t GROUP BY t.ClientId
WHERE t.TestId NOT IN (SELECT TOP 3 t1.TestId FROM dbo.Test t1 WHERE t1.ClientId = t.ClientId ORDER BY t1.TestId)) FourthTest
ON C.ClientId = FourthTest.ClientId
LEFT JOIN dbo.Test FirstDetails ON FirstTest.FirstId = FirstDetails.TestId
LEFT JOIN dbo.Test SecondDetails ON SecondTest.SecondId = SecondDetails.TestId
LEFT JOIN dbo.Test ThirdDetails ON ThirdTest.ThirdId = ThirdDetails.TestId
LEFT JOIN dbo.Test FourthDetails ON FourthTest.FourthId = FourthDetails.TestId

yeah it is a bit odd, but i was "asked" to create a view like this so it could be exported to an analysis program. I may just have to do the function only returning single fields since it seems this is an awkward query.

]]>