Performance problem in Function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance problem in Function

Table PROD : 20 Lacs records of product details.
Function Getprice1 (PROD.PROD_ID) :
-Complex logic Return price1 value
Function Getprice2 (PROD.PROD_ID) :
-Complex logic Return price2 value
Function Getprice3 (PROD.PROD_ID) :
-Complex logic Return price3 value How can I get those three prices joined into one Report-Query with the best performance? I have about 20 lacs records in the "PROD" table, and in most reports there are thousands of records used. So create one function which returns only one price, and would use subselects like "Select PROD.*, GetPrice1(PROD.PROD_id), GetPrice2(PROD.PROD_id), GetPrice3(PROD.PROD_id) from PROD", I would call the complicated Price-Function three times for each "PROD" record. This would surely result in a very poor performance. Second way of calculating all PROD-Records and then creating a join to this calculated resultset wouldn’t be better, because then always all 20 lacs PROD-Records would have to be calculated even if only a few records are used for the report. If anybody have any idea about this problem then please help me.
So, how should we suggest anything without seeing your code? —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?
Ich unterstuetze PASS Deutschland e.V.