Post reply

Progressive Search / SELECT

  • November 21, 2006 at 12:55 am

    #153305

    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?

  • November 21, 2006 at 1:51 am

    #673136

    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

  • November 21, 2006 at 7:35 am

    #673203

    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.

  • November 22, 2006 at 5:02 pm

    #673620

    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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 24, 2006 at 4:33 pm

    #673868

    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)
    Intro to Tally Tables and Functions

  • December 4, 2006 at 1:16 am

    #675398

    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 Device User Agent

    133 NETSCAPE4 Mozilla/4.78 [en] (Windows NT 5.0; U)

    87 BLACKBERRY7290_VER1_SUBMOZ48NT5 Mozilla/4.8 [en] (Windows NT 5.0; U) BlackBerry7290/3.8.0

    71 BLACKBERRY7290_VER1_SUBMSIE6NT5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7290/3.8.0

    71 BLACKBERRY7100_VER1_SUB380MOZ Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7100/3.8.0

    71 MSIE5_NT Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)

    71 MSIE6_NT Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

    68 M3GATE_VER1_SUB05NT M3GATE [Microsoft Windows NT 4.0]/0.5

    59 M3GATE_VER1_SUB502000 M3GATE [Microsoft Windows 2000 5.0]/0.5

    54 NETSCAPE7_1_NT_US Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)

    51 WAPAKA_VER1_SUB0317 Wapaka/03.17 (Windows XP; 5.1; x86) DAW/1.0 Symb1/1.00 UP/4.1.9

    Levenshtein Algorithm Results (Almost 1 minute)

    Dist Device User Agent

    25 NETSCAPE7_1_NT_US Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)

    45 NETFRONT_VER3_SUBSERIES60NF32GECKOEN Mozilla/5.0 (Symbian OS; U; Symbian OS-Series60; en; rv:1.0.1) Gecko/20020823 NetFront/3.2

    46 NETFRONT_VER3_SUBSERIES60NF32GECKOENNOSPACE Mozilla/5.0 (Symbian OS; U; Symbian OS-Series60; en;rv:1.0.1) Gecko/20020823 NetFront/3.2

    47 NETFRONT_VER3_SUBSERIES60NF33GECKODE Mozilla/5.0 (Symbian OS; U; Symbian OS-Series60; de; rv:1.0.1) Gecko/20020823 NetFront/3.3

    54 NETFRONT_VER3_SUBPDA101NF30 Mozilla/4.0 (PDA; Windows CE/1.0.1) NetFront/3.0

    54 BLACKBERRY7290_VER1_SUBMOZ48NT5 Mozilla/4.8 [en] (Windows NT 5.0; U) BlackBerry7290/3.8.0

    56 NETFRONT_VER3_SUBPDA100NF31 Mozilla/4.08 (PDA; Windows CE/1.0.0) NetFront/3.1

    59 BLACKBERRY7100_VER1_SUB380MOZ Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) BlackBerry7100/3.8.0

    59 NETFRONT_VER3_SUBFBED09NF30 Mozilla/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