Anti-Rich
Well-known member
hi all,
i have just developed a view and table valued function in sql server 2k5 to show
where an item in my company's two warehouses is located.
the database is structured like so:
i have a view which gets the base location (ie. LocationName) for each and every
itemcode in the db...
after this, i created an inline table function as follows:
note: i have only put in the return statement, the rest is irrelevant.
The reason i have done it this way, was so i could show a concatenated string of the locations (ie. warehouse 1,2 or both) of where the itemcode is... the problem is, the tblBLItems has about 80 thousand rows... so i think that this way is quite inefficient, as the function and view are being called about 80000 times per run (in this case, the run is when i start the relevant report). i will be linking the inline function i made to ANOTHER statement which will link it with a table reflecting pickup statistics (how many times the item is picked and how much is picked).
if anyone could help me optimize this entire thing, i would really appreciate it. i am really feeling my way in the dark at the moment... in addition to this (and this is embarrasing to admit), i dont understand the relevance of the FOR XML PATH keywords in the function. i understand how the function works, but not the relevance of the xml stuff...
i have spent close to a week on this, and am only now starting to make some headway, the one thing i am lacking is the optimization (and if someone could explain xml path properly that would be great).
thankyou
adam
i have just developed a view and table valued function in sql server 2k5 to show
where an item in my company's two warehouses is located.
the database is structured like so:
VB.NET:
tblLocation[INDENT]LocationID (PK)[/INDENT][INDENT]LocationName[/INDENT]
tblBLCodes[INDENT]LocationID (FK)[/INDENT][INDENT]BinLocation (PK)[/INDENT]
tblBLItems[INDENT]BinLocation (FK)[/INDENT][INDENT]ItemCode (FK)[/INDENT]
tblItems[INDENT]ItemCode (PK)[/INDENT][INDENT]ItemDescription[/INDENT]
i have a view which gets the base location (ie. LocationName) for each and every
itemcode in the db...
VB.NET:
------ My View: vReturnLocations --------
[SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2] l[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationName[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] bl[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]BinLocation[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] bl[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ItemCode[/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblBLItems [/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] bl [/SIZE][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblBLCodes [/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] bc [/SIZE][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][SIZE=2] bl[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]BinLocation [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] bc[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]BinLocation [/SIZE][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]tblLocation [/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] l [/SIZE][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][SIZE=2] bc[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] l[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationID[/SIZE]
after this, i created an inline table function as follows:
note: i have only put in the return statement, the rest is irrelevant.
VB.NET:
-------My Function: fReturnAllLocations ---------
[SIZE=2][COLOR=#0000ff]RETURN[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]DISTINCT[/COLOR][/SIZE][SIZE=2] s1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ItemCode[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff00ff]STUFF[/COLOR][/SIZE][SIZE=2][COLOR=#808080](([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]DISTINCT [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][SIZE=2] 100 [/SIZE][SIZE=2][COLOR=#0000ff]PERCENT [/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]','[/COLOR][/SIZE][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][SIZE=2] s2[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationName [/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]vReturnLocations [/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] s2 [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] s2[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ItemCode[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] s1[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ItemCode [/SIZE]
[SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]','[/COLOR][/SIZE][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][SIZE=2] s2[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]LocationName [/SIZE][SIZE=2][COLOR=#0000ff]FOR [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]XML [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]PATH[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]''[/COLOR][/SIZE][SIZE=2][COLOR=#808080])),[/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]''[/COLOR][/SIZE][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][SIZE=2] Locations[/SIZE]
[SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]vReturnLocations [/SIZE][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][SIZE=2] s1[/SIZE]
The reason i have done it this way, was so i could show a concatenated string of the locations (ie. warehouse 1,2 or both) of where the itemcode is... the problem is, the tblBLItems has about 80 thousand rows... so i think that this way is quite inefficient, as the function and view are being called about 80000 times per run (in this case, the run is when i start the relevant report). i will be linking the inline function i made to ANOTHER statement which will link it with a table reflecting pickup statistics (how many times the item is picked and how much is picked).
if anyone could help me optimize this entire thing, i would really appreciate it. i am really feeling my way in the dark at the moment... in addition to this (and this is embarrasing to admit), i dont understand the relevance of the FOR XML PATH keywords in the function. i understand how the function works, but not the relevance of the xml stuff...
i have spent close to a week on this, and am only now starting to make some headway, the one thing i am lacking is the optimization (and if someone could explain xml path properly that would be great).
thankyou
adam
Last edited: