Sql Server Most efficient way to join

Question

I have a table with approx 3000 records which I need to export to csv (through a .net Web application) however I also need to pull information from about 10 other tables (also about 3000 records each) so that the form more columns, e.g.

Individual Table
Organisation Table
IndividualOrganisation Table

Output: Individual.Name, Organisation.Name

Currently I am using a stored proceedure to create a temporary table with the extra columns (e.g. one for Organisation.Name etc...) then doing queries one after another from the other 10 tables to bring the information in. This causes timeout period elapsed errors on sql connection even if I extend the time to 200!

Would it be better to construct a massive single sql statement in my stored procedure and execute it. These 10 tables that are 'joined in' are conditional on user selection fo stored procedure currently has sequence of ifs then selects

Answer 1

With only 3000 records I would think almost any query would run fine unless your server is severely underpowered (or overworked).

Do you have proper indexes in place? That is what I would check first.

Answer 2

With many separated queries, the optimizer can only look at small parts of the workload. A single query allows SQL Server to optimize the whole. This usually ends up being much faster.

You can keep the new query readable by moving parts of the query into views. Views are like SQL macro's, and SQL Server optmizes a query with views as though the views were expanded into SQL.

Answer 3

• Check that you have indexes for all your foreign keys, primary keys and columns used to filter (after the WHERE clause).

• Only select the fields that you need from every table.

• You can use a INSERT...SELECT statement to do it all in one query.

Answer 4

With only 3000 records per table, indexes should be irrelevant - it ought to be quicker to do a full table scan, than to access via indexes.

I suggest you try removing the queries one by one, until you can isolate which query is causing the timeout problem.