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 |
|
gaupa
Starting Member
1 Post |
 Posted - 2006-03-30 : 01:46:39
|
| Table PROD : 20 Lacs records of product details.Function Getprice1 (PROD.PROD_ID) : -Complex logic Return price1 valueFunction Getprice2 (PROD.PROD_ID) : -Complex logic Return price2 valueFunction Getprice3 (PROD.PROD_ID) : -Complex logic Return price3 valueHow 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. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-30 : 02:08:12
|
Can you post the related tables DDL, some sample data and the expected result ? Also post the GetPrice1, 2, 3 codes. Somebody will be able to advice you how to improve the performance.
KHChoice is an illusion, created between those with power, and those without.Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant |
 |
|
|
|
|
|
|
|
Performance problem