Optimizing a Query

I'm trying to optimize a query used for generating a report. Right now it takes 8 seconds for it to return 58,850 records.

I removed some fields from the select to see if it would take less time. At first that had no effect on the time then I removed everything except the fields that are put of the indices. That caused it to only take 1 second. Obviously, I need more then just the key fields to generate the report but adding any field causes the time to jump up to 8 seconds.

Does anyone know why the time would increase like that or how to adjust the database the reduce the time to return the results. Well, aside from adding an index on all the fields.

Thanks,

I don't know which database you are using. If you are using SQL 2005, you should look at the Execution Plan to find out what is taking longer time. Also try using SQL Profiler to see what is going on behind the scenes. This will give you a clear idea of what needs to be tuned in order to have an optimized query. Take a look at Database Engine Tuning Wizard too.

Honestly speaking 8 seconds is lot of time for 58K records.

It is impossible to give much specific advice without the specifics, but as Shuja says - you should take a look at the execution plan.
Generally anything called "scan" is bad, and means you don't use proper indexes, joins of various types can be bad as well and could possible be reworked to other types of joins/another sequence, sorts can also be bad because they can mean you use an index poorly and so on.
Also whether or not specific fields should be selected out or not is a case-by-case evaluation and can mean you'll either have to rework your select or rework indexes for more specialized indexes.

Optimization is a process which starts with database design, and very much consists of trial and error until you get the hang of it after which you can start to notice the patterns. But without specifics, it is impossible to be specific.

The fact that you can get it from 8 to 1 if you remove fields to me indicates you utilize the indexes poorly either by having poorly structured indexes or because you use them in the wrong order in a ON or WHERE clause, and should properly look at changing/optimizing those aspects.

The reason the time increases when you select the fields is because the fields either don't exists in the index, and thus you force the database engine to look them up (key lookup) in the table to pick them out, or because (as mentioned briefly) you use them in the wrong sequence in the ON or WHERE clasuses which means you can't seek through indexes.