Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bhimrajg
Starting Member
5 Posts |
 Posted - 2009-08-03 : 05:04:46
|
We are using set of table valued user defined functions at base level and use it instead of direct table access in queries. Reason behind using the UDF's is to reuse the code.Suppose, there is table as 'Companies' in database. I have created few UDF's for different conditions which are required at many places e.g. fnGetSoftwareCompanies, fnGetContractedCompanies, gnGetCompaniesRegisteredInLastYear etc..There is also another table 'CompanyProducts'. And now consider that I need to retrieve products of software companies, I have written another UDF 'fnGetsoftwarecompanyProducts' which internally use 'fnGetSoftwareCompanies' and then join to CompanyProducts. Final data is pivoted get product names as columns.Now in one of my SP's, I need to join both UDF's 'fnGetSoftwareCompanies' and 'fnGetsoftwarecompanyProducts' in order to retieve some data. Second UDF itself uses first UDF internally. So same block of code is executed twice in that SP. I understand that I could have avoided that by not using UDFs and writting whole logic in one batch. But there are reasons I have to use UDFs compulsory.Now query looks something like Select --columnsFrom fnGetSoftwareCompanies(id)Join fnGetsoftwarecompanyProducts(id)WHERE --conditions Here 'fnGetSoftwareCompanies' code block will be executed twice. Once in this outer code and next in 'fnGetsoftwarecompanyProducts'I am currently working on performance optimization task and I want to get rid of this issue. I would have tried use of temp table but can not use it in functions.Other solution I tried was, to build XML from 'fnGetSoftwareCompanies' resultset and pass it to other function 'fnGetsoftwarecompanyProducts' which then use that XML data instead of call to 'fnGetSoftwareCompanies' function. But performance was slow in this approach.Could someone please suggest me solution to resolve this issue ? |
|
|
|
|
|
Optimizing UDF calls in SP