SQL Server Performance

Sql Server Performance Issue

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by yatish, Dec 31, 2008.

  1. yatish New Member

    Hello,
    weare facing some issues with performance with SQL server, we have morethan 100000 sales deal records in one table and almost 2500 actualsales persons in another table.
    Wehave created one Table valued UDF which takes three paramters andreturns the sales deals information.
    Thisudf returns approx. 89,000 rows for top level sales persons. But ittakes almost 48-49 secs to return 89,000 rows with more than 20columns for each row.
    Webelieve that indexes are set properly. This was working much fasterwith less data. But with growing data, performance has degraded. Could you please help us to resolve this performance issue ?
    Hereis sample SP code :
    SELECT
    field1,
    field2,
    field3,
    field4,
    s.*,
    field5,
    field6,
    field7,
    field8,
    field9,
    field10,
    field11,
    field12,
    field13,
    field14,
    field15,
    field16,
    field17,
    field18,
    field19,
    field20,
    field21,
    field22,
    FROM Level, fnGetWithMonthWINs(@loginid, @teamId,@IsNodeMultiteam) main
    JOIN fnGetAlldataByProduct(@loginid,@teamId,@IsNodeMultiteam) S ON S.id = main.id
    LEFT OUTER JOIN Locate ON s.LocationId=Locations.LocationId
    WHERE Opps. Levelno = Level. Levelno AND Level.Letter <> 'L'
    ORDER BY
    field1,
    field4,
    field6,
    field7,
    field8,
    field10,
    field15,
    field16,
    field19
  2. Adriaan New Member

    UDFs are usually not the best performing items, as SQL Server will often start ignoring indexes. You might try adding an index hint for the Locate table.
    Why not rewrite the function calls as derived tables?
  3. satya Moderator

    Welcome to the forums.
    ... or rather you could rewrite Functions as Views in order to get better optimization during the execution.
  4. FrankKalis Moderator

    As has been said, multi-statement table valued function can be performance killers. I also would start there and see if I really need the code encapsulated in its own function or not. chances are that it is faster to do the whole processing in just one procedure upfront to the final SELECT statement.
    Furthermore I can't spot where the Opps table alias here
    WHERE Opps. Levelno = Level. Levelno AND Level.Letter <> 'L'
    comes from. Is there anything important missing in your sample code?

Share This Page