January 2, 2024 at 8:38 am
I have a table that has 5.5 million records. The query used by a report filters records based on a date column in the table, and currently it returns all records in the table. The query gives result in 100 seconds. I need to make it run faster.
Query
-----
Select column1, column2... column18
From Table1
where Final_date > 'YYYY-MM-DD' AND Final_date < 'YYYY-MM-DD'
I tried using BETWEEN, forcing parallelism, creating column store indexes etc.. I have not been able to get any improvement in performance. SQL Server Version is 2019 Enterprise edition.
January 2, 2024 at 11:36 am
This will be easier to answer if you provide the DDL for the underlying table, including PK and Index definitions. If the query really is as simple as you suggest, it sounds like you might need a new index.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 2, 2024 at 11:51 am
The query plan will help also, you can use https://www.brentozar.com/pastetheplan/ for that.
Additionally as Phil mentioned the DDL etc.
https://www.sqlservercentral.com/articles/how-to-post-performance-problems-1
January 2, 2024 at 12:06 pm
How many rows does the query return?
An obvious solution is to create an index on Final_date and include columns (column1, column2... column18)
You might be able to get away with just creating an index only on Final_date and forcing a seek in the query if there are relatively few rows returned.
January 2, 2024 at 3:09 pm
If you typically/most often query the table by Final_date, you need to cluster the table first on Final_date. If you have a column that would naturally make the index unique, such as an identity value, then you absolutely should add that to the clus index to make it unique (rather than forcing SQL itself to make each key unique). That will give you the least data scanning for that type of query.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 2, 2024 at 4:17 pm
...currently it returns all records in the table...
We can ignore everything else for the moment.
If a query is returning everything from a table, and there is no way to filter the data, or you don't wish to filter the data, query tuning really isn't an issue. Instead, you need to be looking at hardware. Buy more memory. Get faster disks. Go to the next highest service tier on your cloud provider. Sure, maybe a different index that better covers the query will be a little smaller, so you'll see marginal gains. However, you're returning all data. No matter what, that's more hardware dependent than anything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 2, 2024 at 4:30 pm
and it takes 100 seconds to return 5.5 million rows TO WHAT CLIENT?? - if SSMS then the issue is likely not the query, neither the server but the simple fact that SSMS is not made to display such high volume of data - only way to "validate" that the query is behaving well, based on the required parameters, is to write it to a file for example - or potentially to a temp table on the server (assuming your server is up to a good standard).
And if using filters that do reduce the number of rows, look at the explain plan and see if it is doing table scan, clustered index scan or something else - and go from there if and as required.
January 2, 2024 at 4:32 pm
Is it really returning all the rows in the table? If so then why has the OP got a where clause?
January 2, 2024 at 4:50 pm
I'm going off what they typed. If that's true, there's the problem and solution. If not, we need to know that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 2, 2024 at 5:08 pm
At the moment, it retrieves all entries from the table despite having a filter condition. As you correctly pointed out, indexes don't seem to be significantly impactful in this situation. I share the belief that there may be limited options for query tuning. Nevertheless, I am open to any suggestions. My current focus for improvement is on acquiring a faster disk and increasing the available memory.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply