Progressive Search / SELECT
-
Hi good people,
I am building a database that contains a long list of names. I anticipate that the number of records will grow to a big number. I will also be using custom functions that slow down the search process.
I wish to do a SELECT statement but I am not willing to wait until the full resultset is ready. Is there a way of retrieving the first 10 records, show them to the user while the search is still going on, and then later come back for the other records later.
Is there a way of doing this?
-
I don't like to propose this, but serverside cursors
can provide that functionality. With the ado-propreties for the resultset/command you can specify a batchset. It will only load per batch-set. Watch out for locking and serverside objects to support the cursor (e.g. tempdb,.) Avoid the use of cursors whenever you can !Also keep in mind what's the use of having to pull 5000, 100000,... rows over the network into the client ! You may be better of to have a manadatory list of search-criteria or a relevant TOP nnnn statement, so you minimize the max number of rows !
With SQL2005 and using MARS you can mimic this behaviour without the actual use of a serverside cursor.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Look up OPTION (FAST n) under "OPTION clause" in BOL. I'm not sure how it works and whether it allows to display the first n rows immediately, because I never needed to use it, but the description seems to point that way :
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.
BTW, I would also prefer to pull only TOP n rows as alzdba suggested, and possibly request to adjust criteria if there are too many rows in the result.
-
Suggest you look at
Returning a Subset of a Recordset
Regular Columnist : Jon Winer
Posted: 08/29/2003
Use the search capability on the menu bar and retrieve a whole host of articles / discussions about what you seem to want to do. Just input "paging" - think amongst all the discussion / article you will find a technique to do just what you want to do.
-
Felix...
You may want to post some of those custom functions you're talking about... you'd be surprised at how some of them can be speeded up or maybe even eliminated with some very simple logic.
And to those of you who really aren't old enough to remember... a bit bucket was an actual bucket that had to be emptied on punch card machines long before it became a term for other things...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the advice!
I think I now have a better idea on what I need to do.
I have a long list of User Agents (from mobile phone browsers) and the device capabilities for each agent (8000, and 32,000) respectively. Every now and then I get a user agent that is just one version number from those in the database. Each type of browser puts its version number in different locations. When I use full text, the different versions of a browser do not necessarily have similar page rank, hence the need to use the levenshtein edit distance algorithm to find the most similar UA as illustrated in the dump below:
Search Phrase:
'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.8) Gecko/20061025 Firefox/1.5.0.8'
Full Text Results (Very Fast)
Rank DeviceUser Agent
133NETSCAPE4Mozilla/4.78 [en] (Windows NT 5.0; U)
87BLACKBERRY7290_VER1_SUBMOZ48NT5Mozilla/4.8 [en] (Windows NT 5.0; U) BlackBerry7290/3.8.0
71 BLACKBERRY7290_VER1_SUBMSIE6NT5Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7290/3.8.0
71 BLACKBERRY7100_VER1_SUB380MOZMozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7100/3.8.0
71 MSIE5_NTMozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
71 MSIE6_NTMozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
68 M3GATE_VER1_SUB05NTM3GATE [Microsoft Windows NT 4.0]/0.5
59 M3GATE_VER1_SUB502000M3GATE [Microsoft Windows 2000 5.0]/0.5
54 NETSCAPE7_1_NT_USMozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
51 WAPAKA_VER1_SUB0317Wapaka/03.17 (Windows XP; 5.1; x86) DAW/1.0 Symb1/1.00 UP/4.1.9
Levenshtein Algorithm Results (Almost 1 minute)
Dist DeviceUser Agent
25 NETSCAPE7_1_NT_USMozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
45 NETFRONT_VER3_SUBSERIES60NF32GECKOENMozilla/5.0 (Symbian OS; U; Symbian OS-Series60; en; rv:1.0.1) Gecko/20020823 NetFront/3.2
46 NETFRONT_VER3_SUBSERIES60NF32GECKOENNOSPACEMozilla/5.0 (Symbian OS; U; Symbian OS-Series60; en;rv:1.0.1) Gecko/20020823 NetFront/3.2
47 NETFRONT_VER3_SUBSERIES60NF33GECKODEMozilla/5.0 (Symbian OS; U; Symbian OS-Series60; de; rv:1.0.1) Gecko/20020823 NetFront/3.3
54 NETFRONT_VER3_SUBPDA101NF30Mozilla/4.0 (PDA; Windows CE/1.0.1) NetFront/3.0
54 BLACKBERRY7290_VER1_SUBMOZ48NT5Mozilla/4.8 [en] (Windows NT 5.0; U) BlackBerry7290/3.8.0
56 NETFRONT_VER3_SUBPDA100NF31Mozilla/4.08 (PDA; Windows CE/1.0.0) NetFront/3.1
59 BLACKBERRY7100_VER1_SUB380MOZMozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7100/3.8.0
59 NETFRONT_VER3_SUBFBED09NF30Mozilla/4.0 (Wireless; Frontbed/0.9) NetFront/3.0
In addition, I am building a database of users which I expect will grow to a large number. Every time a new person is added to the list, I need to look for similar names (again using the edit distance algorithm). I am always interested in the first 10 results first and then I make a decision on what to do next. When using skype and you are searching for someone's name, skype gives you the results while the search is still in progress. I wonder how they do that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply