performance tuning a view and table valued function

[Anti-Rich]

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:

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...

------ 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.

-------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

 

[cjard]

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.

Bit of trivia for you.. Numbers written like 2K5, 4M740, the letter replaces the decimal point and indicates the multiplier.. So 2K5 is SQLServer 2500 That's quite advanced software uh?


You dont say how you want your output to look but try this:

SELECT
  i.ItemCode,
  i.ItemDescription,
  CASE SUM(POWER(2,c.LocationID-1))
    WHEN 2 THEN '1+2'
    WHEN 1 THEN '2'
    WHEN 0 THEN '1'
  END as Located
FROM
  tblItems i
  INNER JOIN
  tblBLItems b
  ON
    i.ItemCode = b.ItemCode

  INNER JOIN
  tblBLCodes c
  ON
    b.BinLocation = c.BinLocation

GROUP BY
  i.ItemCode,
  i.ItemDescription

Its quite simple logic:
find all items, in their bin, in their warehouse. Items that are in both warehouses will produced 2 rows. Turn the IDs of the warehouses into powers of 2.. and sum them. An item in WH1 has a sum of 0, WH2 a sum of 1 and both, a sum of 3
This is extendible logic. If you had 3 warehouses their IDs of 1,2,3 would become 2^0, 2^1, 2^2 and an item in all 3 warehouses would have a sum of 7. 6 is for items in WH 2 and 3 etc...

But there are a few logical caveats:

LocationID must be a number

An item must be in only one bin in one warehouse otherwise things really get screwed up. If things are in multiple bins, just take the max id of one of the bins, we dont really care which of N bins an item is in if they are all in the one warehouse

 

[Anti-Rich]

hi cjard

cheers for the input mate, i must admit, i NEVER would have thought to do it like that, but it doesnt seem to work properly (probably because one item can be in more than one location at once... irritating huh?), but not only that, there will eventually be more than 2 warehouses... i need to account for other warehouses as well!! grrr!

remember how i said performance was horrible? well, i found out the other day exactly HOW horrible... i ran my function/sp with the command 'set statistics io on'... and this is what i got...

Table 'tblLocation'. Scan count 1, logical reads 242018, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBLCodes'. Scan count 1, logical reads 242118, physical reads 84, read-ahead reads 100, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 79544, logical reads 462341, physical reads 2068, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBLItem'. Scan count 2, logical reads 1036, physical reads 221, read-ahead reads 865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

i dont know what is considered 'good'.. but all i see are a LOT of BIG numbers!!

any suggestions?

 

[cjard]

hi cjard

cheers for the input mate, i must admit, i NEVER would have thought to do it like that, but it doesnt seem to work properly (probably because one item can be in more than one location at once... irritating huh?), but not only that, there will eventually be more than 2 warehouses... i need to account for other warehouses as well!! grrr!

erm.. i really thought i'd covered both those points in my post.. did you cath it?

remember how i said performance was horrible? well, i found out the other day exactly HOW horrible... i ran my function/sp with the command 'set statistics io on'... and this is what i got...

any suggestions?

I tend to ignore big numbers and only worry about queries that take a long time. I have just finished an SQL that is 33 kilobytes long, and according to oracle's plan, requires 9 terabytes of temporary space. I'm guessing that's over a period of time while results are assembled, because the machine doesnt have enough memory or storage space to service the request.. It takes 3 minute to run too, which is better than I expected considering it has to collate over 400 disparate statistics related to every operational aspect of the business it was prepared for; calls, credit card activity, accounts transactions, applications, complaints, revenue, authorisations.. That's what happens when the MD wants the entire databsae summarised on 3 sheets of A4

The downside to this query is, because it literally uses nearly every significant record in the database, it needs running at a quiet time. Long running jobs really do put this query on pause

 

[Anti-Rich]

This is extendible logic. If you had 3 warehouses their IDs of 1,2,3 would become 2^0, 2^1, 2^2 and an item in all 3 warehouses would have a sum of 7. 6 is for items in WH 2 and 3 etc...

haha... yeah i re-read your post.. i hate making an ass of myself... *goes to edit post so no one will know the stupidity i just said*... haha

i will try and work it so its extendable, i have an idea of how to go about it as well,

i shall return if things go tits up!!

thanks cjard, you also put my mind at rest about the big numbers... but i still think it can be optimized, i just need to have the time to think abou it.

have a good weekend