SQL Server Performance

Where to order the values displayed in a web page?

Discussion in 'Performance Tuning for DBAs' started by Cesar, Sep 10, 2004.

  1. Cesar New Member

    Hi,

    I have a table in my database with several car types, and the order I want for that table is:

    Car_typeA_1
    Car_typeA_2
    Car_typeA_3
    Car_typeA_4
    Other_Cars_typeA
    Car_typeB_1
    Car_typeB_2
    Car_typeB_3
    Other_Cars_typeB
    Car_typeC_1
    Car_typeC_2
    Car_typeC_3
    Car_typeC_4
    Car_typeC_5
    Other_Cars_typeC
    ...


    This table is more or less always the same, but from time to time I want to add a new car type, for instance; Car_typeA_5, but this new type must be located under the last register, in the example under ‘Other_Cars_typeC#%92. So, now the order is wrong, and when I want to display these car types to a web form object, the items will appear wrong ordered.

    My question is: To order the values(items) correctly, Where I have to do it? In the web page (ASP.NET) code behind, or somewhere in SQL Server (for example in the Stored Procedure that passes the value to the application)? Or maybe in the same database table..?

    Thank you,
    Cesar
  2. FrankKalis Moderator

    I assume you don't have a value like 'Other_Cars_Type_c' stored in the table, or?
    Other wise I would say you have a normalization problem.
    Anyway you should do the sorting on the server when retrieveing the information in a query. Sorting algorithms there are efficient. A stored procedure and a query with an ORDER BY would be a good place.

    --
    --Frank
    http://www.insidesql.de
  3. Cesar New Member

    I would like to put the 16 id between 4 and 5 id. There is a way using ‘ORDER BY#%92 into a Stored Procedure to order it in this way?

    What are Sorting algorithms? How can I use them? ‘ORDER BY#%92 is a sorting algorithm?

    Or perhaps the best choice would be recreate the table, set the first id to 1 and leave a gap of 10 between the id#%92s, thus later I could add a new car type well ordered.

    What do you think?

    Id Car_Types
    1 Car_typeA_1
    2 Car_typeA_2
    3 Car_typeA_3
    4 Car_typeA_4
    5 Other_Cars_typeA
    6 Car_typeB_1
    7 Car_typeB_2
    8 Car_typeB_3
    9 Other_Cars_typeB
    10 Car_typeC_1
    11 Car_typeC_2
    12 Car_typeC_3
    13 Car_typeC_4
    14 Car_typeC_5
    15 Other_Cars_typeC
    16 Car_typeA_5
    ...

    Thanks
  4. Adriaan New Member

    It looks as if you want to sort the results by the CarType field, alphabetically.

    So this is all you need to do:

    SELECT CarType FROM tblCarType
    ORDER BY CarType

    The order in which records are stored in the table is not relevant in a relational database. If you want the result to be ordered, you just query the table with an ORDER BY and there you have it.
  5. Cesar New Member

    Indeed my table exposed is an example, my real table must be sorted in a similar way, but it can#%92 t be sorted alphabetically because the ‘car_types#%92 name does not follow an alphabetical order.

    I have a little doubt, if the order in which records are stored in the table is not relevant in a relational database, What is the utility of leaving a gap between id#%92s, setting the first id value to i.e. 1 and an autoincrement of i.e. 10?
  6. Adriaan New Member

    If you have an auto-increment field, then it is meaningless in itself. You seem to need an alphanumeric identifier that you can 'translate' into "Car_TypeA_4", etc. You could put the Type apart into a foreign key table ("Type A", "Type B") and refer to that here, along with a serial number to distinguish between different cars that correspond to the type. The serial can start at 1 for each type you have in the reference table.
  7. Cesar New Member

    Sorry but I don' t understand what you mean. Would you mind giving me an example with my table example?

    Table Cars:
    Id Car_Types
    1 Car_typeA_1
    2 Car_typeA_2
    3 Car_typeA_3
    4 Car_typeA_4
    5 Other_Cars_typeA
    6 Car_typeB_1
    7 Car_typeB_2
    8 Car_typeB_3
    9 Other_Cars_typeB
    10 Car_typeC_1
    11 Car_typeC_2
    12 Car_typeC_3
    13 Car_typeC_4
    14 Car_typeC_5
    15 Other_Cars_typeC
    16 Car_typeA_5
    ...
  8. Cesar New Member

    And.. if I do this:

    Car_id /Car_Type / Car_Order
    1 /Car_typeA_1 /10
    2 /Car_typeA_2 /20
    3 /Car_typeA_3 /30
    4 /Car_typeA_4 /40
    5 /Other_Cars_typeA /50
    6 /Car_typeB_1 /60
    7 /Car_typeB_2 /70
    8 /Car_typeB_3 /80
    9 /Other_Cars_typeB /90
    10 /Car_typeC_1 /100
    11 /Car_typeC_2 /110
    12 /Car_typeC_3 /120
    13 /Car_typeC_4 /130
    14 /Car_typeC_5 /140
    15 /Other_Cars_typeC /150
    16 /Car_typeA_5 /45
    ...

    Then I write 'ORDER BY Car_Order' in my SP
  9. Cesar New Member

    Nobody knows how this can be done?

    I have seen this practice many times, it is a group of options in a web form object (for example in a ddl) in which the last option is ‘Other types#%92.

    For example, if you choose in a ddl the option ‘vegetables#%92, in the second ddl will appear a group of vegetables name, and if you choose the option ‘meat#%92, in the second ddl will appear a group of types of meat. For example the second ddl, if you have selected ‘vegetables#%92, would be:

    Onions
    Carrots
    Potatoes
    Spinach
    Peas
    Other Vegetables


    And if you have selected ‘meat#%92 would be:

    Chicken
    Beef
    Pork
    Duck
    Other Meats


    Both, vegetables and meat products aren#%92 t sorted alphabetically, and most probably the database table can be:

    Table: Supermarket_products

    Product_id / Product_name / Family_num
    1/Onions/1
    2/Carrots/1
    3/Potatoes/1
    4/Other Vegetables/1
    5/Chicken/2
    6/Beef/2
    7/Other Meats/2
    8/Spinach/1
    9/Peas/1
    10/Pork/2
    11/Duck/2


    So, How this kind of dropdownlist order can be done?
  10. Twan New Member

    I'd say most of the time an order_seq field would be added to give the administrator the opportuntiy to sort it any way they like...

    e.g.

    Product_id / Product_name / Family_num / order_seq
    1/Onions/1/1
    2/Carrots/1/2
    3/Potatoes/1 /3
    4/Other Vegetables/1/6
    5/Chicken/2/1
    6/Beef/2/2
    7/Other Meats/2/5
    8/Spinach/1/4
    9/Peas/1/5
    10/Pork/2/3
    11/Duck/2/4

    Then if another product gets added then either the admin or the user who added to product has the chance to resort the items which would cause the order_seq to be updated.

    Cheers
    Twan
  11. Cesar New Member

    Ok, I think that this is a good system.

    Thank you!

Share This Page