SQL Server Performance Forum – Threads Archive

UDF and indexes

Hi All,
I have to create joins between couple of tables.All tables reside in my database. The total number of records fetched in this query is approximately 20,000 rows. I would be using this query often in all the SP.
what is the best way to go about –
1. Create temp table (which I can index)
2. Create indexed view
3. Create UDF which return table. I am tempted to use UDF. I would like to know
a. which method is most beneficial in my situation in terms of performance
b. Can i index on UDF which returns table Thanks

Hi ya, a UDF cannot be indexed, so only use that if you don’t require further joining or where clauses a temp table will need to be kept in sync a view with suitable indexes on the base tables or an indexed view, would be my preference… BUT if needed I’d test all three… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

So you need a set of data that you can use a few times in a single Stored Procedure? First of all, you must realize that UDFs and views are not the same as SQL 2005’s Common Table Expression (CTE) which I think is what you are thinking of here. If you include the view in more than one query statement, the underlying tables are queried once for each statement. If it’s an indexed view and the index is covering for the query, then the index will be queried once for each statement. If you call a UDF in three different statements, the complete UDF is executed three times. Now a temp table is more in line with a CTE – excellent option, especially with the indexes. But you’ll have to see if creating the temp table doesn’t cause problems with growth of the tempdb files, contention of tempdb from other apps, etc. You didn’t mention the TABLE type variable, which is available in SQL 2000 and 2005. You can treat it the same way as a temp table, but by way of indexes it only supports a primary key. SQL Server will try to maintain this in-memory, but if it expects there will be a lot of data inserted, then it will create a working table in tempdb – so you may well end up with the same possible problems as with a temp table. An indexed view might still be a good option, but only if you need indexes on columns that are not covered by existing indexes, or where the indexes on the table are not appropriate for your query. Compare performance with an non-indexed view to see if it’s worth the trouble. (After creating an indexed view, do double-check that you can still do data-entry on the underlying tables: there are some nasty gotcha’s if you don’t get your settings right, and don’t trust EM to get it right for you.) If you find that the indexed view performs better than a non-indexed view, then it will probably also perform better than querying the tables directly. With UDFs, as you say there’s no indexing at all – and I can’t see the point in creating a UDF for use in a single SP.

Oops, one of the things I should have noticed in the original post – "I have to create joins between couple of tables." So you tried a SELECT query with a JOIN, and found that performance is insufficient? Try feeding the query to the "Index Tuning Wizard" in Enterprise Manager first, to see if it can suggest some new indexes that might benefit you.

Thanks All,
I am running sql server 2000. Yes, I did say that I have to create joins between couple of tables. But at the same time, I would use the same query on multiple occasions in the SP. I was wondering if I could use temp, indexed view or UDF to improve performance. Thanks all for the tips and advises.

]]>