CODEWITHSUNDEEP
sqlsql-serverexecution-time
user1907557
user1907557

Reputation: 1

SQL query taking more time when columns reduced

I have a select query with 100+ columns from 15 tables and more then 100000 rows. And the execution time of this query is around 15 mnts. Now when I have reduced number of columns to 50 and comments out respective tables. As per my understanding new query should take less than 15 mnts but now the execution time increased to 4 hrs :(.

Please suggest.

Upvotes: 0

Views: 889

Answers (2)

csg
csg

Reputation: 2107

I suggest you a few optimizations:

  1. Make sure that your tables have indexes; the columns used in WHERE conditions or in JOIN conditions needs to be in those indexes
  2. Make sure you don't use '*' selector, unless is needed;

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300719

Without more detailed information, I suggest rebuilding the indexes on all tables involved in the query (and update any remaining column statistics):

DBCC DBREINDEX('MyTable')
go

UPDATE STATISTICS MyTable WITH FULLSCAN, COLUMNS
go

[The usual warning/caveats about running on a production server apply].

Upvotes: 1

Related Questions