SQL Server Performance

Performance problem in Function

Discussion in 'T-SQL Performance Tuning for Developers' started by gaupa, Mar 29, 2006.

  1. gaupa New Member

    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.
  2. FrankKalis Moderator

Share This Page