Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Where do I start with Optimization?

    Hi,

    I will begin a new project soon using SQL Server 2005. I have limited experience w/ 2005 compared to 2000 but I am comfortable with SQL. My challenge here is the client has about 50 million customer records. I have not seen the data yet. It is on an Oracle server with only query rights. I believe I will have to migrate this data into a SQL Server 2005 environment which I have confirmed they have ownership of. I plan to do so in order to have full admin rights, ie to be a real programmer, being able to build objects and massage the data as needed to report upon. I have never worked with this large of a dataset and my questions revolve around optimization. I have been reading up and I understand that I will index everything that is a foreign key, that is straightforward enough but what are the other major and obvious steps that are most effective to make querying this data more efficient? I am considering using indexed views as well. This data will be a lead list for multiple sales people. Any thoughts/questions would be greatly appreciated. Thanks.

    DD

  2. #2
    Join Date
    Oct 2009
    Posts
    19
    Performance tuning is a big subject. Indexing the FK is a good start, but how to index them? Do you include all FKs (per table) in one index or one for each? Then there are SELECT queries you have to consider. And don't forget each index has to be maintained every time you do an insert/update/delete. It's always good to remember that an index is just another type of table.

    Big tip use "Database engine tuning advisor"
    And I would recommend buying a book on the subject.
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Indexing all foreign keys indiscriminately is incorrect. Where did you get that information?
    Quote Originally Posted by OxfordSmarty
    Do you include all FKs (per table) in one index or one for each?
    That is, I'm afraid, nonsense.

    This is an enormous subject. You are essentially building something from the ground up. Are you retaining the original Oracle schema or designing from scratch? Is this a transactional or reporting database? How much does the data change? Are there hot spots (for example, do most of those rows represent cold leads)?

    There are no magic bullets at this stage.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2009
    Posts
    19
    I Hope site helps:
    Indexes


    FOREIGN KEY Constraints
    Indexing FOREIGN KEY Constraints
    Creating an index on a foreign key is often useful for the following reasons:

    Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
    Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table. However, creating this index is not required. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria. For more information about using FOREIGN KEY constraints with joins, see Join Fundamentals and Query Types and Indexes.
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by OxfordSmarty
    I Hope site helps:
    Indexes


    FOREIGN KEY Constraints
    I typically prefer procedural RI vs. Declarative RI between tables, much less of a headache when migrating data.

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    Pootle Flump,

    Yes, I realize I am just touching the tip of the iceberg. All the leads are good according to the clients. Once I get the data migrated into sql server 2005 I can do whatever I want w/ it. I will be the only one pulling data. It seems like there are two main areas where I as a Developer would work with, the indexing and the query set up. I have read somewhere that placing the table with the smallest amount of records last in the FROM clause would help running speed for example.

    ddave

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by PMASchmed
    I typically prefer procedural RI vs. Declarative RI between tables, much less of a headache when migrating data.
    w wh wha what?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dolfandave
    I have read somewhere that placing the table with the smallest amount of records last in the FROM clause would help running speed for example.

    ddave
    No, that is nonsense too. May I ask what your source is because so far you have been fed duff information.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Posts
    193
    I am not sure the exact source but here is one site that mentions this concept:

    Forcing the optimizer to execute the query in a specific order Systems Engineering and RDBMS

    The third paragraph discusses the "driver table".

    PS. That's why I ask these things here first.

    ddave

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignore that article, and the concept. Query and index hints are a matter of last resort when you have exhausted every other option and are highly skilled and very familiar with the database and data.
    The technique described there is pretty niche and is more sophisticated than "stick the table with the least rows last in the FROM clause".

    You need to focus on fundamentals initially:
    1) Normalised, well designed database
    2) Appropriate indexes (these are based on DML operations only (selects, inserts, updates etc)). I am always amazed how people will describe the indexes a particular table requires without ever asking to see the SQL acting on it first
    3) Good, clean, efficient SQL (no cursors, no functions in where clauses acting on indexed columns etc.).

    That will get you 95% of the way there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Posts
    193
    OK,

    I appreciate it. That is what I wanted to focus on, getting off to a good start.

    ddave

Posting Permissions

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