Take the 2-minute tour ×

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

share|improve this question

4 Answers 4

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.

share|improve this answer
There are 10 tables with 3000 rows. If you just join em together, you'd get 3000^10 rows. That's a number with 34 zeroes. :) –  Andomar Aug 6 '10 at 11:17
@Andomar: No, you get 3000 rows with 10 times more columns in average. –  David Espart Aug 6 '10 at 11:18
Only if you are doing a Cartesian join between all the tables, which I doubt is what you are really intending to do. –  E.J. Brennan Aug 6 '10 at 11:22
A cartesian join can explain a 30 second timeout. A missing index on 3000 rows can explain a 0.0001 second delay, at most –  Andomar Aug 6 '10 at 11:56

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.

share|improve this answer
  • 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.

share|improve this answer

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.

share|improve this answer

Your Answer


