Professional Web Applications Themes

Efficient solution - Microsoft SQL / MS SQL Server

I have a table [CAR] Guid UNIQUEIDENTIFIER NEWID() Car VARCHAR(20) I have a table [Components] Id INT Identity(1, 1) NOT NULL Component I have to build another table that will look (i think) in this way: Id INT Identity(1, 1) NOT NULL Car_Guid UNIQUEIDENTIFIER -- Foreign key of CAR Car_Id INT -- Foreign key of Components that represents how many components one car has. This is a standard many-to-many solution. Is there something more effcient? Also considering that when I have to query this structure I have to group on a 16 byte column... Any help much appreciated regards -- ...

  1. #1

    Default Efficient solution

    I have a table [CAR]

    Guid UNIQUEIDENTIFIER NEWID()
    Car VARCHAR(20)

    I have a table [Components]

    Id INT Identity(1, 1) NOT NULL
    Component


    I have to build another table that will look (i think) in this way:

    Id INT Identity(1, 1) NOT NULL
    Car_Guid UNIQUEIDENTIFIER -- Foreign key of CAR
    Car_Id INT -- Foreign key of Components


    that represents how many components one car has.

    This is a standard many-to-many solution.

    Is there something more effcient?
    Also considering that when I have to query this structure I have to group on
    a 16 byte column...

    Any help much appreciated

    regards



    --
    ==
    fabriZio




    fabriZio Guest

  2. #2

    Default Re: Efficient solution

    You are right, but CARtable is the scenario that I found here.

    I can't change it!

    I think 16 bytes is slower than 4 :(

    What about many-to-many prob?

    Thx Aaron.

    --
    ==
    fabriZio


    "Aaron Bertrand - MVP" <com> ha scritto nel messaggio
    news:exJo$v#phx.gbl... 
    >
    > You're saying NEWID() is the default? If they're just surrogate keys, why
    > not use an IDENTITY in this table also? This will make your joins more
    > efficient, and your lookup table smaller.
    >
    >[/ref]


    fabriZio Guest

  3. #3

    Default Re: Efficient solution

    > I can't change it!

    That's unfortunate.
     

    Yes, I think you're right.
     

    Your lookup table is a pretty common approach; however, drop the IDENTITY
    column (it serves absolutely no purpose). Make sure indexing is set up such
    that queries are optimized -- whether you use a clustered and non-clustered,
    composite clustered, composite non-clustered, or individual non-clustered
    will really depend on other factors...


    Aaron Guest

  4. #4

    Default Re: Efficient solution

    >> Is there something more effcient? <<

    Yes, just keep it simple.

    CREATE TABLE Cars (
    Car_id INT NOT NULL PRIMARY KEY,
    Description VARCHAR(50) NOT NULL
    ....);

    CREATE TABLE Parts (
    Part_Nbr INT NOT NULL PRIMARY KEY,
    Detail VARCHAR(20) NOT NULL
    ....);

    CREATE TABLE CarParts (
    Car_id INT NOT NULL
    REFERENCES Cars(Car_id),
    Part_Nbr INT NOT NULL
    REFERENCES Parts(Part_Nbr)
    ....
    PRIMARY KEY (Car_id, Part_Nbr));

    Implement a similar schema and start yzing your queries. Since there are
    no standard rules on efficiency, you'll have to play with indexes, tune your
    queries & do all the other fancyworks and you'll most likely end up with an
    efficient solution.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. More efficient ActionScripting?
    By Nicholas in forum Macromedia Flash
    Replies: 6
    Last Post: January 4th, 11:46 PM
  2. Efficient Formatting
    By Mr. in forum PERL Beginners
    Replies: 3
    Last Post: July 22nd, 01:10 AM
  3. Working Efficient with ID CS
    By Harold_Lentink@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: June 17th, 07:30 AM
  4. Looking for the most EFFICIENT way to do the following...
    By Sugapablo in forum PHP Development
    Replies: 0
    Last Post: August 25th, 06:50 PM
  5. Efficient structure
    By Info@Best-It in forum PHP Development
    Replies: 1
    Last Post: August 17th, 10:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139