0

My teammates and I often have to write queries that revolve around a database that is not partitioned (a single heap file) and has no index. I was told by the DBA that this won't change. Are there techniques we can use that would help us in query performance or is it a it is what it is? For example there is a table that has just north of 54 million rows in it and it takes almost 40 seconds to get the answer to select count(*) from dbo.table

This is in SSMS.

Queries against these tables coming back with maybe 2,000 records with simple filters of columnA = 'this' and date is >= 'this date' take a few minutes.

Improve this question
7
  • I tried to talk to the DBA to see if they can be defined and if they can be partitioned, they said no and basically don't ask again. I'm just the user. Queries against these tables coming back with maybe 2000 records with simple filters of columnA = 'this' and date is >= 'this date' take a few minutes.
    – MCP_infiltrator
    May 26 at 16:53
  • What version and edition of SQL Server are you using? SELECT @@VERSION; should answer.
    – Erik Darling
    May 26 at 17:49
  • Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
    – MCP_infiltrator
    May 26 at 19:27
  • 2
    You should find out from your DBA why they don't want to index the table. That's pretty much the solution to your problem. Without an index, the whole table (all 54 million rows) will always be scanned first before it can be filtered down to the few rows your query cares about. Without a valid reason from the DBA, it sounds like they just don't want to do their job or are knowledgeable enough.
    – J.D.
    May 26 at 19:52
  • 1
    This sounds more like a workplace.se question than an dba.se question TBH. On the assumption that the queries you are doing are worthwhile then you should probably escalate it as what is currently provided by the DBA doesn't sound fit for the purpose you are using it for. So you need to understand what the arguments against indexing the table are and whether indexes can feasibly be added or maybe the data provided in some alternative way
    – Martin Smith
    May 27 at 10:30

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.