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 |
|
blinton25
Yak Posting Veteran
90 Posts |
 Posted - 2004-04-01 : 17:05:26
|
Hello,How are you today?I have a function RegionofResidenceCrosstabFunc which runs a query against my database. SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@CurrYear1)UNION ALLSELECT regionorder, Regions3, 2 As Quarter, April, May, June FROM dbo.RegionofResidenceCrosstabFunc(@PrevYear1,@CurrYear1) Is it correct to say that each time the Select in the above Union is called the query is run? If so, this seems expensive for the four quarters that have to be run.If so, is there any way that I can run my function once but still obtatain the required results? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-01 : 17:10:46
|
| The function will be run twice in your query. Once for each SELECT. It is possible that you can only run it once, but we'd need to see more information such as the code of the UDF in order to help.Tara |
 |
|
|
blinton25
Yak Posting Veteran
90 Posts |
Posted - 2004-04-01 : 17:21:15
|
Hello,Sure:CREATE FUNCTION RegionofResidenceCrosstabFunc(@PrevYear varchar(4),@CurrYear varchar(4))RETURNS TABLEASRETURN (SELECT TOP 100 PERCENT WITH TIES [Final_Clean_Tourists_QueryView].REGIONS3, [Final_Clean_Tourists_QueryView].regionorder,'~' + Cast(sum(CASE (DatePart(yyyy,[F_ARRV_DAT]))WHEN @PrevYear THEN (1 *(1-ABS(SIGN(DatePart(mm,F_ARRV_DAT)-1))))else 0END)as varchar(5)) + '~' +Cast(sum(CASE (DatePart(yyyy,[F_ARRV_DAT]))WHEN @CurrYear THEN (1 *(1-ABS(SIGN(DatePart(mm,F_ARRV_DAT)-1))))else 0END)as varchar(5)) AS JanuaryFROM [Final_Clean_Tourists_QueryView]WHERE [Final_Clean_Tourists_QueryView].REGIONS3<>''GROUP BY [Final_Clean_Tourists_QueryView].regionorder,Final_Clean_Tourists_QueryView].REGIONS3Order By [Final_Clean_Tourists_QueryView].regionorder) I just pasted in the calculations for January, but basically this is repeated for the 12 months in a year. |
|
|
|
blinton25
Yak Posting Veteran
90 Posts |
Posted - 2004-05-04 : 12:11:29
|
| Hello,Has anyone had a chance to review this and see if there is a more efficent way of accessing the function without calling the funtion several times? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-04 : 12:45:03
|
| I think you scared everyone....Why is this a UDF? Why not a View?Also What does the ouput suppose to look like?And it looks the function is already referencing a view?Does your view reference a view as well?Brett8-) |
|
|
|
blinton25
Yak Posting Veteran
90 Posts |
Posted - 2004-05-04 : 13:09:50
|
Hi,My apologies, to scare was certainly not the intention since I need guidance on this.1. Are they any advantages to making this a view rather than a function? I beleive that I had a problem when trying to Union the view and it was suggested that I use a function.2. Yes, the function references a view. However, that view does not in turn reference another view.3. Output looks like this:regionorder Regions3 Quarter A B C2 CANADA 1 ~12~13 ~10~20 ~50~604 GERMANY 1 ~5~6 ~20~10 ~10~302 CANADA 2 ~12~13 ~10~20 ~50~604 GERMANY 2 ~5~6 ~20~10 ~10~30 This approach works but the performance is really bad, even in Query Analyser I have to wait over a minute to return my recordset, which I am sure is because I have to call the function 4 times. |
|
|
|
blinton25
Yak Posting Veteran
90 Posts |
Posted - 2004-05-06 : 04:43:59
|
| Hi,Some additional clarifications, I am invoking the code in my function from my Union query, which is why I use the function. Ideally I am thinking a stored procedure would be the way to go since the performance would improve over time but I don't think I can invoke a SP directly from the Union query.And because of the need for parameters, the use of a view is out as well. |
|
|
|
|
|
|
|
|
Optimise Union