tag:blogger.com,1999:blog-3000721125799986885.post7377554748836972482..comments2024-03-28T04:05:20.028-07:00Comments on SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server(): Know Your Data!Brad Schulzhttp://www.blogger.com/profile/01852762873611487368noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-3000721125799986885.post-6588822389163552972012-01-24T13:49:30.190-08:002012-01-24T13:49:30.190-08:00@Anonymous:
This is an example of getting the fir...@Anonymous:<br /><br />This is an example of getting the first two words of all the messages in a table:<br /><br />declare @t table (Message varchar(100))<br />insert @t select 'cross apply is really cool'<br />union all select 'and ntile is really cool too'<br />union all select ' and iced tea is really cool too '<br /><br />select Word1,Word2<br />from @t<br />cross apply (select TrimMessage=ltrim(rtrim(Message))) f1<br />cross apply (select WorkString=TrimMessage+' ') f2<br />cross apply (select p1=charindex(' ',WorkString)) f3<br />cross apply (select Word1=left(WorkString,p1-1)) f4<br />cross apply (select RestOfString=ltrim(substring(WorkString,p1,len(WorkString)))) f5<br />cross apply (select p2=charindex(' ',RestOfString)) f6<br />cross apply (select Word2=case when p2>0 then left(RestOfString,p2-1) end) f7<br /><br />--BradBrad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-85022381795666432652012-01-24T13:35:41.230-08:002012-01-24T13:35:41.230-08:00Is posssible to get only the first two word of the...Is posssible to get only the first two word of the sentece?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-7981601909159928502009-09-30T17:37:28.529-07:002009-09-30T17:37:28.529-07:00The query plan shows 99%:1% Numbers:Function. It ...The query plan shows 99%:1% Numbers:Function. It essentially sees the function call as a "black box" and so it can't realistically associate any cost with it.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-11001826440694613522009-09-30T08:35:38.449-07:002009-09-30T08:35:38.449-07:00That is huge difference in IO, in favor of the num...That is huge difference in IO, in favor of the numbers table. I am curious what the % batch cost is for each method.Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-37456051393824314722009-09-30T07:06:52.515-07:002009-09-30T07:06:52.515-07:00I was going to add something to the post regarding...I was going to add something to the post regarding the IO, but I mainly wanted to talk about time.<br /><br />Interestinly, the Function Approach had huge IO, while the Numbers Table Approach remained low. For example, for 100 words/row, the Function recorded 1081536 reads and Numbers recorded 30730. When we got up to 500 words/row, the Function recorded 5416181 reads and Numbers recorded 70008.<br /><br />The Numbers table did have a clustered index. I didn't specify a fill factor when I created the table... according to the documentation, I think if I don't specify anything, it defaults to 100%. I'll have to look further.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-15569642494846641642009-09-30T06:33:09.819-07:002009-09-30T06:33:09.819-07:00Interesting.. Did you do any performance metrics o...Interesting.. Did you do any performance metrics on IO? I would imagine the performance degradation scales with additional IO costs. Also, did you have a clustered index, with fillfactor 100 on your numbers table? Having a good index may help reduce IO.Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.com