Sign In
Friday, February 23, 2007

Well, several days have passed but for a some reason I've started to feel uncomfortable about Numbers function. It's all because of poor recursive CTE implementation. I have decided to unroll the cycle. The new version hovewer isn't a beautiful but is providing much more superior performance comparing with previous implementation:

/*
Returns numbers table.
Table has a following structure: table(value int not null);
value is an integer number that contains numbers from 1 to a specified value.
*/

create function dbo.Numbers
(
/* Number of rows to return. */
@count int
)
returns table
as
return
with Number4(Value) as
(
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0 union all
select 0 union all select 0
),
Number8(Value) as
(
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4 union all
select 0 from Number4 union all select 0 from Number4
),
Number32(Value) as
(
select 0 from Number8 N1, Number8 N2, Number8 N3, Number8 N4
)
select top(@count) row_number() over(order by Value) Value from Number32;

The performance achieved is on pair with numbers table. Estimated number of rows is precise whenever we pass constant as parameter.

What is the moral? - There is a space for the enhancements in the recursive CTE.

Next day

Guess what? - Yes! :-) there is also the CLR, which allows to create one more implementation of the numbers and split functions. In the next entry I'll show it, and performance comparison of different approaches.

Friday, February 23, 2007 12:21:31 AM UTC      Comments [0] -
SQL Server puzzle
All comments require the approval of the site owner before being displayed.
 Name E-mail Home page Remember Me Comment (Some html is allowed: `a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u`) where the @ means "attribute." For example, you can use or
. Enter the code shown (prevents robots): Live Comment Preview
Navigation
Archive
 < February 2007 >
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
25262728123
45678910
Categories
 .NET Announce ASP.NET BizTalk Server C++ Incremental Parser Java javascript JSF and Facelets kendoui SCCBridge SQL Server puzzle Thinking aloud Tips and tricks Window Search xslt
Blogroll
Statistics
Total Posts: 274
This Year: 11
This Month: 3
This Week: 0
Comments: 198
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2013, Nesterovsky bros
All Content © 2013, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)