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 |
|
anti-rich
Starting Member
14 Posts |
 Posted - 2007-05-14 : 02:49:00
|
hi all,i have just developed a view and table valued function in sql server 2k5 which were adapted from a previous post in these forums http://www.sqlteam.com/item.asp?ItemID=979to the person who posted this; THANKYOU what you wrote saved me! i still dont fully understand it, but for now to the problem at hand... They show where an item in my company's two warehouses is located.the database is structured like so:tblLocationLocationID (PK)LocationNametblBLCodesLocationID (FK)BinLocation (PK)tblBLItemsBinLocation (FK)ItemCode (FK)tblItemsItemCode (PK)ItemDescription i have a view which gets the base location (ie. LocationName) for each and every itemcode in the db...------ My View: vReturnLocations --------SELECT l.LocationName, bl.BinLocation, bl.ItemCodeFROM dbo.tblBLItems AS bl INNERJOINdbo.tblBLCodes AS bc ON bl.BinLocation = bc.BinLocation INNERJOINdbo.tblLocation AS l ON bc.LocationID = l.LocationID 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 ---------RETURN(SELECT DISTINCT s1.ItemCode,STUFF((SELECT DISTINCT TOP 100 PERCENT ','+ s2.LocationName FROM dbo.vReturnLocations AS s2 WHERE s2.ItemCode= s1.ItemCode ORDER BY','+ s2.LocationName FOR XML PATH('')),1,1,'') AS LocationsFROM dbo.vReturnLocations as s1The 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-14 : 19:56:22
|
| hi peso,thanks for the reply, yes i am using sql2k5, and the post you were referring to i actually used in the function... the reason i used the view however, was because i couldnt adapt that post you are referring to to use multiple tables... it kept giving me one row for each location name... which was not what i wanted. so i used a view to first give me a condensed view of what i needed.i hope im making sense here... if not tell me and ill try and explain again...do you have any ideas of how to adapt your post to use multiple tables? im still only at a beginner(ish) level of sqlserver 2k5 and i have busted my brain to try and get to use multiple tables, but like i said it kept giving me separate location names on separate rows, not a concatenated version on one row per item code...thankyou for replying :)cheersadam |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-15 : 12:06:16
|
| It might help if you state specifically what you are trying to do. Maybe I missed the goal while reading over this. Are you trying to get a contactenated list of Locations for each Item? Or a list of Items for each Location? Or somethine else? I'm sure we can help you out, but I need a little more in the requirements department. :)-Ryan |
|
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-16 : 21:06:42
|
hi ryan,sorry, yes i am trying to get a contactenated list of Locations for each Item.like i said it uses a function that uses a view. i have a feeling this is horribly inefficient...i came across a command that i have never seen before: "SET STATISTICS IO ON"... and i ran it... *shudder*... im not sure what is considered 'fast' with the results, but i saw a lot of BIG numbers :( that kinda confirms my feeling that im doing this the wrong way... here are the resultsTable '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. *sigh* thanks for the response and offered help ryan, i have busted my brain trying to get this done efficiently, but i have a feeling that i just dont know sql server well enough... if you could offer some input i would be eternally grateful!thanks :)adamEDIT: posted the wrong results *sheepish grin*... edited to reflect the horrible results i was talking about |
|
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-17 : 23:09:16
|
| bump, is anyone able to offer any other input on this? i would greatly appreciate it, as this problem is frustrating the crap out of me :(cheersadam |
|
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-21 : 20:02:22
|
| is anyone able to offer any suggestions? once again, i would be extremely grateful to any input at allcheersadam |
|
|
|
anti-rich
Starting Member
14 Posts |
Posted - 2007-05-23 : 21:02:47
|
| is anyone able to give me an idea as to why my queries are taking so long?cheers adam |
|
|
|
|
|
|
|
|
help tuning a view and function