Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Angry Unanswered: How to query a great db with less time ?

    Hy fellows...

    I have a table with over 1 million rows, 20 columns and several dependent tables.

    Now the problem is that I have to return only rows which are between for example position 679820 to 679840.

    The table has an index.

    The query looks like this...

    select * from table where table.id IS IN (select TOP 679840 table.id from table) and table.id IS NOT IN (select TOP 679820 table.id from table)


    Now if the table has only about 40000 rows the results will come in about 1-2 seconds. No problem.

    But as the table grows to over 150000 rows, the query blocks the server for about several minutes.
    Not thinking of over 1 million lines...

    So...

    How should this query look like for greatest speed ???


    Thnx for advise
    Thomas alias Jobs-for-you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your query is executing three different select statements against your table, and hence make three passes through the data. That's expensive.

    This makes only one pass through the original dataset:

    select TOP 20 *
    from (select TOP 67840 * from table order by table.id asc) SubQuery
    order by Subquery.id desc

    ...but I don't recomment the use of TOP. It cannot accept parameters, so in order to make the query flexible you would need to execute it as a dynamic SQL statement. It also must order the data, and that is often an expensive task.

    This is more flexible, and I think it will run faster than what you are using now:

    select yourtable.*
    from yourtable
    inner join
    (select yourtable.id, count(*) as ordinal
    from yourtable
    inner join yourtable yourtable2 on yourtable.id >= yourtable2.id
    having count(*) between 67820 and 67840) idsequence
    on yourtable.id = idsequence.id

    blindman

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: How to query a great db with less time ?

    Try one of these, maybe it will be faster :


    select top 679840 id into ##temp from Table;
    select * from Table where id in (select top 20 id from ##temp order by id desc);

    drop table ##temp

    OR =======================================

    select top 679840 * into ##temp from Table;
    select top 20 * from ##temp order by id desc;

    drop table ##temp


    OR =======================================

    add a column with an autoincremental numeric that gives the row number

    select * from Table where AutoColumn between 679820 and 679840

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    Declare CURSOR and FETCH ABSOLUTE 679820 is probably a better choice than sectioning very large selects.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and then loop through the cursor to fetch the next 20 or so values? Where are you going to store them? Are you going to make 20 individual inserts into a #temp table and then select again from the #temp table? I don't think this is going to be more efficient.

    blindman

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    just for info...
    gna-gna-gna in Sybase we have the function Number(*)
    that retruns the row number...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd be green with envy, except that I've used Sybase before and don't have any regrests about not using it any more!

    Just curious, how does Number(*) work? Do you need to supply a sort order? I wonder if it isn't doing the same operations in the background that are specifed through SQL statements in SQL server. If so, it is convenient that it is saving typeing and making the code simpler to read, but it probably isn't any more efficient.

    blindman

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What kind of meaning does that result set hold for you?

    How do you know what's contained in it?

    It seems so arbitrary..

    Whats the sql statement you're using?

    What about the DDL of the table inclusing the index...

    can you post them?

    Did you do a show plan on your query?

    If it's got a proper index, it should be an index seek, and should fly...

    also are you doing a select *? DONT

    How many columns do you really need?

    Are you using covered indexes?

    (geez what a rant)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    when i run on Sybase (on my not-at-all-powerful-french-computer)

    select number(*) as nb, id into #temp from Table;

    I get

    1 ID1
    2 ID2
    3 ID3
    ...


    then i could :

    select * from #temp where nb>50 and nb<1000

    to get the good ones

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    It would be very simple if he had an
    autoincremental column to identify the row number

    and it would'nt change any insert queries

  11. #11
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by blindman
    ...and then loop through the cursor to fetch the next 20 or so values? Where are you going to store them? Are you going to make 20 individual inserts into a #temp table and then select again from the #temp table? I don't think this is going to be more efficient.

    blindman
    As a FETCH ABSOLUTE through a static keyset resolves to a single (usually clustered) index scan, I think fetching next 20 rows into an in-memory @table is going to be seriously faster than: a clustered index scan, twice, an implicit transfer to tempdb of approximately 680,000 rows, twice, and an exclusion select (which given the scale of data will probably done using a lazy spool); OR a clustered index scan followed by a forward sort of 680,000 rows, followed by a reverse sort of 680,000 rows followed by a filter to TOP; OR any activity involving a ##temp with the scale of rows concerned.

    It should be easy enough for Thomas to try.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmmm....interesting argument.
    Worth testing.
    Anybody else have experience comparing these two methods?

    blindman

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    - Again - I ask
    why does'nt he just add an autoincremental clumns on his table ?

    so he would only have to do :
    Select * From Table Where AutoColumn>54560 and AutoColumn<54680

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, misread...thought the id was an IDENTITY..

    Still what meaning does it have...

    Give me the middle 20 id's out of the database...

    ?????


    The order of data in a database has nothing to do with the relational model....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I recall that Mister Jobs-for-You
    knows witch rows he wants in his select
    -------

    select * from table where table.id IS IN (select TOP 679840 table.id from table) and table.id IS NOT IN (select TOP 679820 table.id from table)


    with an autoincremental column, this select would simply be :


    select * from table where table.newautocolumn< 679840 and table.newautocolumn> 679820

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •