I'm trying to integrate data virtualization technique in our wpf project. Based on this blog: http://bea.stollnitz.com/blog/?p=411 the integration for client works perfectly but for the server
(ms sql) i'm having problem integrating some kind of numbering functionality. So the data collection / preparation is slow.
Currently i have a stored procedure and is composed of 2 queries: inner and outer. The inner query is where the business logic lies and takes 2 - 4 seconds to complete collecting the data depending on supplied parameters. The
outer query is there to implement data virtualization which simply selects the inner query, adds a new field 'rownr' using row_number and makes use of top(). With top and row_number in place i can now get data result piece by piece. However my query now takes 7
seconds to finish when eg. getting 100 rows from the 36 000 result rows within 3/4 range.
Select top (x) from (select ROW_NUMBER() OVER (ORDER BY ID) as RowNr, * from ([inner query] where 1=1 and @params)) where RowNr > y
Some analysis on my sp:
- (top) 100 rows from 36 000 result rows where rownr > 1000 => 0 secs
- (top) 100 rows from 36 000 result rows where rownr > 5000 => 1 secs
- (top) 100 rows from 36 000 result rows where rownr > 15000 => 4 secs
- (top) 100 rows from 36 000 re
View Complete Post