Question for SQL GURU

  • I think the following indexes need to exist. For solution by LENE.

    With an index on the Student table on the id column and a index on Grades table on the columns id and name the code should be fast enough for production.

  • Hey LenE,

    Sorry that no one responded to your final comment, and thanks for letting me know.

    As far as the indexes, you may be right, but really it depends on how many rows in each table. If you only have a few SQL will probably do a table scan regardless. I'm pretty much in favor of indexing foreign keys even if the volume of data doesn't require it initially. Jump in any time you have comments or questions.

    Andy

  • Though I hate using cursors, You could do os in this instance. If the I/O between the ASP and SQL server is too expensive, you could try a server side cursor.

    PSEUDO CODE:

    Set a bunch of variables for @classname1,@classname2,@classname3, etc.

    then @grade1,@grade2,@grade3, etc.

    Set cursor for student selection

    Fetch each student

    set cursor for each class

    Fetch each class

    Set the values for each classname and gradename

    Select the student and classnames and grades in one select statement

    If you have trouble with this post your efforts and I'm sure we can figure it out.

  • Andy Warren said

    quote:


    Along those lines, let's use ANSI SQL - no joins in the where clause!


    Specifying joins in the where clause is ANSI sql compliant.

Viewing 4 posts - 16 through 18 (of 18 total)

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