CODEWITHSUNDEEP
.netsqlsql-server
Mark Milford
Mark Milford

Reputation: 1226

Sql Server Most efficient way to join

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

Upvotes: 0

Views: 212

Answers (4)

user359040
user359040

Reputation:

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.

Upvotes: 0

David Espart
David Espart

Reputation: 11780

  • 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.

Upvotes: 0

Andomar
Andomar

Reputation: 238176

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.

Upvotes: 1

E.J. Brennan
E.J. Brennan

Reputation: 46879

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.

Upvotes: 7

Related Questions