Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: Showing only the first 30 results

    Hello!

    I have a quite complex SQL Statement. It is like This:

    SELECT PERS_DAT_KTO_ID, PERS_NR, NACHNAME, VORNAME, GEBDATUM, STRASSE, ORT, FROM pers_dat WHERE NACHNAME LIKE 'M%' ORDER BY NACHNAME, VORNAME ASC

    You see it's across several tables and it is generated by my Program.
    A user can fill out fields in a searchMask with search-criteria.
    When he types in % or M% or S% about 40000 data records come out of the Database. In my Application that takes a lot of time and the user can anyway only see the first 50 data-records within the first 20 seconds.

    What can I do in SQL that the database only returns the first 50 results?????????????

    I searched Tutorials, Manuals... everything I found. But I didn't find a command that allows to break up after the first 20 matches.

    Perhaps you can help me.



    WHAT I NEED IN SHORT:

    How do I have to change this statement

    SELECT * FROM myTable WHERE name LIKE '%'

    to get only the first 50 matches when there are 100000 rows in the Table myTable???

    Thank you very much for answering

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Showing only the first 30 results

    Originally posted by vincentvega55
    Hello!

    I have a quite complex SQL Statement. It is like This:

    SELECT PERS_DAT_KTO_ID, PERS_NR, NACHNAME, VORNAME, GEBDATUM, STRASSE, ORT, FROM pers_dat WHERE NACHNAME LIKE 'M%' ORDER BY NACHNAME, VORNAME ASC

    You see it's across several tables and it is generated by my Program.
    A user can fill out fields in a searchMask with search-criteria.
    When he types in % or M% or S% about 40000 data records come out of the Database. In my Application that takes a lot of time and the user can anyway only see the first 50 data-records within the first 20 seconds.

    What can I do in SQL that the database only returns the first 50 results?????????????

    I searched Tutorials, Manuals... everything I found. But I didn't find a command that allows to break up after the first 20 matches.

    Perhaps you can help me.



    WHAT I NEED IN SHORT:

    How do I have to change this statement

    SELECT * FROM myTable WHERE name LIKE '%'

    to get only the first 50 matches when there are 100000 rows in the Table myTable???

    Thank you very much for answering

    I dont see how this is across more than 1 table, but that it besides the point.

    It kind of depends on what you want here. Do you want the top 50 records (like first 50 ids) or just any 50 records?

    You dont mention which database you are on , so I'll give you the Oracle syntax. To do top 50 records, you have to put an order by, or the database will grab any 50 records.

    Do something like:

    Code:
    Select a. pers_dat_kto_id,
    a.pers_nr,
    a.nachname,
    a.vorname,
    a.gebdatum,
    a.strasse,
    a.ort
    from 
    (SELECT PERS_DAT_KTO_ID, 
    PERS_NR, 
    NACHNAME, 
    VORNAME, 
    GEBDATUM, 
    STRASSE, 
    ORT  
    FROM pers_dat 
    WHERE  NACHNAME LIKE 'M%'  
    ORDER BY NACHNAME, VORNAME ASC ) a
    where rownum <= 50
    This will give 50 rows specified by that order by. Hope that helps. Be aware that it will not give you records in chunks of 50 - it will ONLY pull 50 records and thats all.

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    The original Command is:
    SELECT PERS_DAT_KTO_ID, PERS_NR, NACHNAME, VORNAME, GEBDATUM, MELDE_KAMMER, MELDE_NUMMER, GEBNAME, GESCHL, adrdienst_vw.STRASSE, adrdienst_vw.PLZ, adrdienst_vw.ORT, adrpers_vw.STRASSE, adrpers_vw.PLZ, adrpers_vw.ORT FROM pers_dat INNER JOIN arzt_dat INNER JOIN adrdienst_vw INNER JOIN adrpers_vw ON(adrpers_vw.KTO_ID = adrdienst_vw.KTO_ID) ON(arzt_dat.ARZT_DAT_PERS_ID=adrdienst_vw.KTO_ID) ON (PERS_DAT_KTO_ID=ARZT_DAT_PERS_ID) WHERE NACHNAME LIKE 'M%' ORDER BY NACHNAME, VORNAME ASC

    I just shortened it because if I saw that command I wouldn't continue reading ;o))

    My Problem is, that this command needs 30Seconds to give back a ResultSet.

    So i want to show only the Top 50 results at first. While the user can see the first 50, I fetch the rest from the Database.

    Your command looks like it needs MORE time than the simple query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have an index on NACHNAME, or better yet both NACHNAME und VORNAME ?

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    No I don't have.

    It' an Oracle 9i database. Otherwise I could use LIMIT 0,50

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by vincentvega55
    No I don't have.
    I would very strongly suggest that you create an index on NACHNAME und VORNAME. I think that will help this query a lot.

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    8
    I don't exactly know what you mean. How to create an Index???

    I'm no SQL expert. I develop a Java Application and I'm not able and not allowed to change anything on that database.

    Do you understand my problem?? I have to speed up the query... so I want to fetch first 50 results and then the other ones.

    If it is impossible to fetch the TOP 50 data-records, I'm satisfied with any 50 records. I have no idea how to solve that problem

    Thanks for helping me till now.

    Vince

  8. #8
    Join Date
    Mar 2004
    Posts
    8
    Is there no command, to make the database stop searching after 50 records?????

    can you give me links to Forums to search, links of Tutorials... something like that. I googled and didn't find anything...

    I don't know where to search. BIG BIG Problem for me

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first advice is still to plead with the DBA, and ask them to create an index on NACHNAME. This should make your original query run in at most a few seconds, and return results as fast as the client can fetch them.

    If that fails, I think that you need to handle the same query asynchronously. The exact way to code an asnych retrieve varies, depending on the programming language and database library being used.

    -PatP

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Pat Phelan
    My first advice is still to plead with the DBA, and ask them to create an index on NACHNAME. This should make your original query run in at most a few seconds, and return results as fast as the client can fetch them.

    If that fails, I think that you need to handle the same query asynchronously. The exact way to code an asnych retrieve varies, depending on the programming language and database library being used.

    -PatP

    The syntax for TOP-N on Oracle is as follows (which by the way I gave you 6 posts ago)

    SELECT [COLUMN_LIST]
    FROM (SELECT[COLUMN_LIST] FROM TABLE
    ORDER BY TOP-N COLUMN)
    WHERE ROWNUM <= N

    or in english:

    Ex:

    Select pers_dat_kto_id,
    pers_nr,
    nachname,
    vorname,
    gebdatum,
    strasse,
    ort
    from
    (SELECT PERS_DAT_KTO_ID,
    PERS_NR,
    NACHNAME,
    VORNAME,
    GEBDATUM,
    STRASSE,
    ORT
    FROM pers_dat
    WHERE NACHNAME LIKE 'M%'
    ORDER BY NACHNAME, VORNAME ASC )
    where rownum <= 50


    Did you actually TRY it, or just look at the code and assume it would take a long time??? And by the way, the first code I gave you is the exact same as this, I just used an inline view.

    Rownum in this case is a pseudo-column (doesnt really exist) to tell the database to first select all of the rows (in the from part), and then select from that dataset (in the select part), but only row number less than or equal to 50 (Top 50).

    Top N in Oracle requires an order by clause to do top N (which you have). Otherwise if you leave off the order by clause, it will grab just the first 50 records. It is up to you whether you want to include it or not.
    Last edited by ss659; 03-31-04 at 08:05.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ss659
    The syntax for TOP-N on Oracle is as follows (which by the way I gave you 6 posts ago)
    You've confused the participants in this thread.

    My reasoning for the index is that an indexed search based on the first (or first-N) characters of the last name should be much faster than a table scan for them. If the base query takes 30 seconds to start to return rows, running the base query as a virtual table and then processing out only the top 50 should take longer to materialize than the original query did, although it will probably retrieve faster.

    I have no clue if the original poster tried your suggestion or not. I don't have access to their server or data, so I can't try it for them.

    -PatP

  12. #12
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Pat Phelan
    You've confused the participants in this thread.

    My reasoning for the index is that an indexed search based on the first (or first-N) characters of the last name should be much faster than a table scan for them. If the base query takes 30 seconds to start to return rows, running the base query as a virtual table and then processing out only the top 50 should take longer to materialize than the original query did, although it will probably retrieve faster.

    I have no clue if the original poster tried your suggestion or not. I don't have access to their server or data, so I can't try it for them.

    -PatP
    I understand what you are saying totally - I just added my comment b/c the original post asked for getting 50 records, which I responded to. My last post was not in response to you - It was directed toward the original poster ( I just clicked reply and didnt take out your quote).

    My 2nd post was in response to posters comment that "Your command looks like it needs MORE time than the simple query". The query I originally gave satisfied the original question (I am assuming it was never tested though b/c they would know it should perform quite swiftly).

    I didn't mean to steal your thunder about indexes!

Posting Permissions

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