1

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

Cœur
  • 37,241
  • 25
  • 195
  • 267
Liam neesan
  • 2,282
  • 6
  • 33
  • 72
  • do you have index on `ID`, `Invoice` ,`Date` ? – Squirrel Jul 13 '19 at 08:01
  • @Squirrel Please give me any other way except Index – Liam neesan Jul 13 '19 at 08:11
  • 4
    *"I don't want to create any Index, Because, it is already having enough index by default"* so what do indexes do you have if you already have loads? Post the table's DDL and the the DDL for **all** their indexes – Thom A Jul 13 '19 at 08:14
  • 1
    @Larnu If I read correctly the OP's comments under my now deleted answer, SQL Server is already using the clustered index for the join. Given that this is a sum aggregation query on very large tables, there may not much else which can be done. – Tim Biegeleisen Jul 13 '19 at 08:16
  • @Larnu Those indexes are created by Microsoft, here I am using one of Microsoft product. I can't do any changes to it., Is it possible, to reduce execution time by moving into temp table, but still retrieving records from table taking **2 min** sometimes for 1 month – Liam neesan Jul 13 '19 at 08:19
  • 1
    The clustered won't help for the `WHERE` though @TimBiegeleisen . If there's an index on `Date` which includes the other it'l be of great help as a seek can be performed on `sales`. – Thom A Jul 13 '19 at 08:19
  • 1
    @Liamneesan Microsoft doesn't create your indexes; you do... If that's the case, you have no indexes, and why your query is so slow. – Thom A Jul 13 '19 at 08:20
  • @Larnu when you instal software, it will automatically created Triggers, indexes by default not by myself. Again, Is there any other solution, Except index? – Liam neesan Jul 13 '19 at 08:23
  • 3
    What software @Liamneesan? You've only mentioned SQL Server, and SQL Server does not create indexes for you. – Thom A Jul 13 '19 at 08:24
  • @Larnu I don't want to create additional index, Each time my filter will be change, so each time should I need to create Index based on filter?. I am keep on repeating, Except Index any other solution or not? – Liam neesan Jul 13 '19 at 08:28
  • 3
    *"Except Index any other solution or not"* not one that is sensible, no. With that much data you need more indexes than just your clustered index. If you won't do that, you have to suffer that decision with slow queries. – Thom A Jul 13 '19 at 08:33
  • Possible duplicate of [Performance Tuning SQL - How?](https://stackoverflow.com/questions/463639/performance-tuning-sql-how) – Liam neesan Jul 13 '19 at 08:46
  • What do you mean by "enough" indexes? Specifically how many? – RSax Dec 07 '19 at 06:15

1 Answers1

2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • That index what you have mentioned, that has already on the `Sales` table. It has created already 4 indexes (Clustered and Non Clustered index) – Liam neesan Jul 13 '19 at 08:05
  • Is `ID` the primary key of the `sales` table? – Tim Biegeleisen Jul 13 '19 at 08:06
  • yes. it it any other possible, except index. Because, As I mentioned before, it has enough index already, If I Create more, it will affect more when insert records. Inserting records are also huge in one transaction, around 20k – Liam neesan Jul 13 '19 at 08:10
  • 1
    What does your execution plan say? Is the clustered index being used? No other way to speed this up other than an index or making the tables smaller. – Tim Biegeleisen Jul 13 '19 at 08:11
  • Yes, it is absolutely using clustered index from both tables – Liam neesan Jul 13 '19 at 08:14