Possibilities of Query tuning in my case using SQL Server 2012

Question

I have 2 tables called Sales and SalesDetails; SalesDetails has 90 million rows.

When I want to retrieve all records for 1 year, it almost takes 15 minutes, and it's still not yet completed.

I tried to retrieve records for 1 month, it took 1 minute 20 seconds and returns around 2.5 million records. I know it's huge.

Is there any solution to reduce the execution time?

Note

I don't want to create any index, because it already has enough indexes by default

Answer 1

I don't know what you mean when say that you have indices "by default." As far as I know, creating the two tables you showed us above would not create any indices by default (other than maybe the clustered index).

That being said, your query is tough to optimize, because you are aggregating and taking sums. This behavior generally requires touching every record, so an index may not be usable. However, we may still be able to speed up the join using something like this:

CREATE INDEX idx ON sales (ID, Invoice) INCLUDE (Date, Register, Customer)

Assuming SQL Server chooses to use this index, it could scan salesDetails and then quickly lookup every record against this index (instead of the sales table itself) to complete the join. Note that the index covers all columns required by the select statement.