Really basic question..

  • Hey guys. In general, better to have 30 simple queries running to like 6 different tables, or 1 table which could return all results in one query?

    Purely for speed.  It doesn't matter if it's less work for me/us or that space is duplicated.  What does SQL Server run faster?

  • It isn't quite as simple as that.

    If your single table breaks normalisation rules then anything you gain on ease of retrieval will be lost on the difficulty of maintaining the data in a coherent state.

    It also depends on how selective your 30 simple queries are and how much data is in the six tables and the relationships between them.

    There are also different trade offs for different scenarios.

    In a small database joining six tables together can be very fast.

    In medium databases the best performance could be gained by running derived queries.

    In a large database you may have to resort to pre-selecting into temporary tables.

    Add into this mix the fact that the definitions of small, medium and large are largely subjective.

  • Well, I don't think there would be any normalisation problems.  The 30 simple queries each fetch one row from the 6 tables on one variable's value.  The size of the tables is approx between 4000-8000 records each.

     

    I am asking because with the current setup, there seems to be quite a lag, as, with the exception of the row ids and a year value, they are completed unrelated tables.

  • Maybe creating a view of the data you need might b a way to go.

Viewing 4 posts - 1 through 3 (of 3 total)

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