Post reply

Search engine basics

  • July 17, 2002 at 12:07 pm

    #101200

    Helloa all!

    I'm making a basic search engine or atleast I'm trying to.

    How do you fetch exactly 50 records? And how do you get the NEXT 50? Is this usually done with SQL or is it done with asp or another web programming language?

    Thanks for all help!

    /Tomi

  • July 17, 2002 at 12:56 pm

    #432914

    select top 50

    will get the first 50. For me, I do an order by and pass in the 50th result for the where clause to get the next 50

    select top 50

    from..

    where col > 50th result.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • July 17, 2002 at 1:37 pm

    #432915

    Usually for search sets I use ado cursors myself and set pages with 50 rows each. However Steve's suggestion will accomplish in round trips the same but memory load will be less at any given time. Also wanted to add instead of using SELECT TOP 50 it is better to do SET ROWSET 50 which is a bit faster and you can use user input to set inside a stored procedure.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 17, 2002 at 2:46 pm

    #432922

    Antares, can you tell me more about how/why set rowcount is faster? My preference is for Top N constructs unless I have a situation where N is variable, not often so far.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • July 17, 2002 at 4:04 pm

    #432933

    I will have to find the article but here is what I remember.

    With TOP N all rows in the query are processed first and then only the first N are output to the data stream. Thus you see the TOP N appear in the execution plan.

    With SET ROWCOUNT N the query runs to the point were the data is sent on the data stream and once the Nth record is reached the query is stopped.

    Now if you will look here you will find an issue with the Full-Text Search Engine but it is the only problem reported. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q296465

    Any long and short especially with a large dataset is SET ROWCOUNT N runs in less time that TOP N since all the output is not read first. Also not to mention SET ROWCOUNT N will accept variables as N which gives more flexibility.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 18, 2002 at 4:16 am

    #432972

    Thanks, guys!

    Once again this forum turned out to be the best place for SQL help! And it's all free

    Thanks again!

    /Tomi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply