SQL Server Performance

view + table function

Discussion in 'T-SQL Performance Tuning for Developers' started by Phthisis, Sep 11, 2006.

  1. Phthisis New Member

    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
  2. Chappy New Member

    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














  3. Phthisis New Member

    Thanks for your reply Chappy, yeah i forgot to enter the error <img src='/community/emoticons/emotion-4.gif' alt=':p' /><br /><br />The issue is that i get a syntax error:<br />Server: Msg 170, Level 15, State 1, Line 3<br />Line 3: Incorrect syntax near '.'.<br />The syntax error points at the "from newinstruments.dbo.clientinfo area<br /><br />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.<br /><br /><br />here is my function below:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />ALTER FUNCTION dbo.FSDQScoreGetTable (@ClientID as uniqueidentifier, @SurveyNumber as int) <br />RETURNS @ScoreTable TABLE<br /> (<br /> ClientID uniqueidentifier,<br /> Date datetime,<br /> TotalDifficulties int, <br /> EmotionalScale int, <br /> ConductScale int, <br /> HyperScale int, <br /> PeerScale int, <br /> ProsocialScale int, <br /> ImpScore int<br /> )<br />AS <br />BEGIN <br />INSERT @ScoreTable<br /><br />select S3.ClientID, S3.Date, S3.TotalDifficulties, S3.EmotionalSymptomsScale, S3.ConductProblemsScale, S3.HyperActivityScale, <br /> S3.PeerProblemsScale, S3.ProsocialScale, S3.ImpScore from (<br />select count(*) as Rank, A.*<br />FROM (SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties, <br /> NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale, <br /> NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore<br /> FROM NewInstruments.dbo.vsdqstats<br /> WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID<br /> order by NewInstruments.dbo.VSDQStats.date) as A ,<br />(SELECT top 20 NewInstruments.dbo.VSDQStats.ClientID, NewInstruments.dbo.VSDQStats.Date, NewInstruments.dbo.VSDQStats.TotalDifficulties, <br /> NewInstruments.dbo.VSDQStats.EmotionalSymptomsScale, NewInstruments.dbo.VSDQStats.ConductProblemsScale, NewInstruments.dbo.VSDQStats.HyperActivityScale, <br /> NewInstruments.dbo.VSDQStats.PeerProblemsScale, NewInstruments.dbo.VSDQStats.ProsocialScale, NewInstruments.dbo.VSDQStats.ImpScore<br /> FROM NewInstruments.dbo.vsdqstats<br /> WHERE NewInstruments.dbo.VSDQStats.ClientID = @ClientID<br /> order by NewInstruments.dbo.VSDQStats.date) as B <br />where A.date &gt;= B.Date<br />group by A.ClientID, A.Date, A.TotalDifficulties, A.EmotionalSymptomsScale, A.ConductProblemsScale, A.HyperActivityScale, A.PeerProblemsScale, A.ProsocialScale, A.ImpScore<br />) as S3<br />where S3.Rank = @SurveyNumber<br /><br />RETURN<br />END<br /></font id="code"></pre id="code"><br /><br />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.<br /><br />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).<br /><br />Thank you for your time
  4. Adriaan New Member

    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
  5. Phthisis New Member

    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.

Share This Page