Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,161 questions
1
vote
2
answers
123
views
MSSQL Performance gain when clearing out (large size) filestore-table records?
On MSSQL (our DB size is 520g), there is one filestore table that holds tons of files. 65-70% of this 520g is sitting in there. So essentially, it's a 200g database with 320g worth of files in this ...
0
votes
0
answers
52
views
Kitchen Sink Pagination - How to avoid recompiles or bad estimates when materializing intermediate result sets (temp table vs table variable vs CTE)?
I have a high-traffic SQL Server stored procedure that runs thousands of times per hour. The procedure loads a filtered set of “projects” for a user, identifies the “active task” for each project, ...
- 821
4
votes
2
answers
596
views
Performance tanks after small change in search date predicate
Can anyone help me understand what's happening here....
I'm trying to get consistent performance from a query being called from a third-party application. Changing the query itself isn't an option. ...
- 491
0
votes
0
answers
45
views
Can I grant read-only access to MongoDB database without creating a user or enabling authentication?
My Questions:
Is it possible to share read-only access to my MongoDB database WITHOUT creating a user? Is it possible to grant read-only permissions WITHOUT authentication being enabled on the server? ...
- 1
1
vote
1
answer
119
views
Improve performance of full table scan in MySQL
I observed that full scan in MySQL is 10x times slower than in PostgreSQL.
More specifically - 6 sec vs 0.5 sec.
And there is no specific magic in PostgreSQL because in Oracle it is even faster - 0.2 ...
- 113
-1
votes
1
answer
132
views
Azure SQL DB - High Parallelism Waits
I'm performing a health check on Azure SQL Database after reports of intermittent timeouts from the application. This is my first time dealing with this particualar environment. From diving into query ...
- 491
0
votes
1
answer
86
views
postgres query optimization for batch fetches with 100M+ rows
Table Definition
CREATE TABLE public.table_1 (
id bigserial NOT NULL,
generation_date date NOT NULL,
"namespace" varchar(16) NOT NULL,
item_id int8 NOT NULL,
...
- 101
4
votes
3
answers
1k
views
Is it better to add conditional logic to a heavily used stored procedure or create a separate one for a new use case?
We have a core stored procedure that's a critical part of our system and gets called thousands of times every hour to handle important operations. Now, there's a new application being introduced that ...
- 821
1
vote
1
answer
114
views
(where exists) vs (group by + having count) performance
I would like to understand what is the best way to filter many-to-many relation for general cases where multiple filters may be used.
In addition to example filter others may be present (maybe ...
- 147
0
votes
1
answer
80
views
signs that optimization profile was concidered and matched, but overuled by the optimizer
I created an optimization profile and imported it into systools.opt_profile. I can see in the plan that OPTGUIDELINES is not applied.
I have verified the profile against DB2OptProfile.xsd so I'm ...
- 23.4k
0
votes
1
answer
55
views
optimization profile, STMTMATCH EXACT='FALSE'
I have a query that causes problem, and the only fix I found is to use an optimization profile that suggests an index scan with a certain index. So far, so good. Then I discovered that they generate ...
- 23.4k
2
votes
2
answers
133
views
Improve Performance when Sorting on an Aggregate
As an example; I have two simple tables as :
CREATE TABLE tierA (
id INTEGER NOT NULL,
code TEXT NOT NULL,
updated TIMESTAMP NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE tierB (
id ...
- 244
4
votes
1
answer
2k
views
SQL Server - Payroll query kills database every month
so admittedly I'm not the best at query tuning, and could really use some guidance on how to tune this query. It's a month end payroll query that pulls A LOT of data, but ultimately doesn't need all ...
- 57
0
votes
0
answers
91
views
Increased query latency in Postgres after reindex
I reindexed (concurrently) an index on the biggest table used in a query, but for some reason the latency of the query has increased from aroun 6 ms to 150-200 ms. The reindex was performed because ...
- 1
2
votes
2
answers
186
views
Evaluating File Data from FILESTREAM-Enabled vs. Non-FILESTREAM-Enabled Columns in a Single Table
Originally, I was working with .NET 9 and a Blazor web app for admin and a Flutter app for external end-user, but for this context, I constrained it to SQL Server 2017, to evaluate it first internally....
- 205