Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,481
questions
0
votes
0answers
11 views
Oracle Question
I have a 1 million row table in oracle 19c. I am running this query. Table has an index on ID column, stats are up to date.
select from (select from where ID > 42000 order by ID ASC) where ...
-2
votes
0answers
21 views
why it is really slow when select from table where id in a large list of ids?
I found this (pseudo) query is really slow:
SELECT * FROM table_users WHERE user_id IN a_list_of_100k_ids
So I tried to chunk up the a_list_of_100k_ids into small pieces and query each chunk in a for ...
0
votes
1answer
28 views
Does the number of columns been selected affect query speed?
I know some sql but don't quite understand the things behind the scene. And this question came to me the other day while I am taking shower...
If we compare these two queries below:
a. select * ...
1
vote
1answer
30 views
Query slowing down the database performance
I have a query to find the maximum spacing between charging stations on any given route, using PostGIS and pgRouting. An example query is below:
select (max(delr) * st_length(line::geography) * 0....
0
votes
0answers
34 views
How to adjust memory grants via hints [closed]
We are having issues on our READABLE secondaries with lots of waits around RESOURCE_SEMAPHORE
We have approx. 800 GB set as MAX server memory setting
Our ongoing task is to fix those SELCT queries but ...
0
votes
0answers
29 views
INNER JOIN taking so long for even simple query
My inner queries when executed independently works fast(<2 sec). I have then applied INNER JOIN on two sub query results and final output takes around 25 minutes.
SELECT DISTINCT
tableA.consumer,
...
1
vote
1answer
32 views
Different (much worse) query plan when using one view
I am running the following query:
SELECT m.entityid,
m.alias::text AS alias,
m.measurement_ts,
m.volume_m3::double precision AS volume_m3,
m.pressure_mca,
m.battery_volt,
(m....
0
votes
0answers
18 views
Fastest way of updating a spider web of data
I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
0
votes
1answer
23 views
What is the most performant way to group by and join high cardinality columns in column based table?
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
1answer
37 views
How to break down MySQL big table into multiple to solve operational issue
This is my current table schema in MySQL
we are not facing any performance issue in our application but there are operational issue .
The size of this table is 7 TB (6TB data and 1 TB index ) and has ...
0
votes
0answers
39 views
the query runs longer time than expected for unknown reasons
The query executed on 4 different tables containing just ~ 6k rows running longer time than expected.(~15 min). I'm unable to figure out what really went wrong here -
Query:
SELECT DISTINCT
tableA....
0
votes
1answer
15 views
MySql ORDER BY slow with join, fast as 2 queries
Why is the following query slow but fast when I provide the values inline?
select u.* from user u
join user_group g on u.group_id = g.id
where g.account_id = 1
order by u.id limit 10;
-- takes ~30ms
...
0
votes
1answer
35 views
Optimize BETWEEN Query in SQL Server Azure
I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:-
Timestamp | ComponenentID | Parameter1 | ...
0
votes
0answers
16 views
Optimisation of MySQL procedure to generate tree
I have MySQL table like this
|-----------------------------|
|IDENTIFIER | POINT1 | POINT2 |
|-----------------------------|
| NAME1 | AA1 | BB1 |
| NAME2 | AA1 | BB2 |
| NAME3 ...
0
votes
1answer
38 views
Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)
Any ideas how to improve and what the actual culprit is?
records in product are only 24000
takes 0.85s to run
Mysql 5.7
select `product`.* from `product`
inner join `manufacturer` on `product`.`...
0
votes
2answers
38 views
Moved site to new, similar server - same query is taking 10x longer?
Edit:
Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0
And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7
This morning I moved my WP website from one server to ...
0
votes
2answers
58 views
Query that returns a unique ID for each different clause matched?
I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information....
-2
votes
0answers
24 views
Why the jsonb performance is slower than left join(EAV) when retrieve data?
I have two SQL, one is using jsonb to retrieve data and another is using left join to retrieve data. The jsonb(time cost is about 40 second) is slower many times than left join(time cost is about 6 ...
0
votes
0answers
47 views
Update query performance improvement (MS-SQL)
UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1
ON M.MARKID = S.MARKID AND
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> '...
0
votes
1answer
36 views
MySQL - terrible performance for simple query on large table
I'm having huge troubles with the performance of a very simple indexed query on a quite large table.
Configuration:
48 cores, NVMe Raid, 376GB RAM (144GB used)
Mysql 8 (Percona server)
Server ...
0
votes
2answers
33 views
select insert query optimization on large volume MYSQL 8.x
Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2
INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1....
0
votes
0answers
18 views
Why does the size of fields in a collection in mongodb database affect query performance?
I had a collection that was growing and with it's growth had a crazy increase in query response times.
The collection records looked something like this:
{
orderStat: { type: String },
items:...
0
votes
1answer
20 views
Why turning off condition_fanout_filter speed up query
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &
Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
1
vote
1answer
27 views
PostgreSQL chooses to seq scan huge table instead of index lookup
I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:
...
1
vote
1answer
45 views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
1
vote
1answer
175 views
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?
I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I ...
0
votes
2answers
70 views
Cardinality , Parallel Hint alternative for MS-SQL
UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');
...
0
votes
2answers
38 views
Optimizing nested SQLite query
I have a SQLite table named hashbands that looks like this:
hashband | file_id | window_id
------------------------------
potato | 0 | 0
potato | 1 | 0
The table has 100M+ rows (lol). ...
0
votes
1answer
55 views
Why does pre-sorted index not help me in window function?
I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
%(max_providers_to_target_per_territory)s as max_providers,
case
...
0
votes
1answer
21 views
DB2 for i equivalent of MSSQL's Show Query Execution Plan
I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path.
If so, what's it called, how do I access it, and what permissions do I need to use it?
Thank ...
0
votes
1answer
29 views
Select students that has not been graded (experiment for performance difference)
MSSQL - Performance test question
Premises, One table (students) with id and name, another table (grades) with grades where student_id is one column. Student table has clustered index on id column, ...
0
votes
1answer
33 views
Disk queue length very high in SQL Server 2016
I have two Servers A and B with the same settings, but on Server A, the query runs in 6 minutes and on Server B the same query runs in 15 minutes.
what I noticed is that on Server B is that disk queue ...
-1
votes
1answer
19 views
Query optimization with joins and unions and fulltext boolean search
I have the following base tables with subtables for searchbait. (This is on aws aurora with mysql 5.7 compatibility)
tblA (id, userId, title, ...)
tblB (id, userId, title, ...)
tblA_searchBait (id-&...
1
vote
2answers
47 views
How to optimize a query with timestamp that using OR clause?
I have the follow table:
create table myTable
(
id serial not null
rowtype integer ...
1
vote
2answers
40 views
Unable to understand correlation between MaxWorkers created and CPU utilization
I am having a strange issue which i am unable to understand:
On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say ...
1
vote
3answers
59 views
Can I improve my duplicate-detection queries?
I have a DB table with US domestic flight data which I am in the process of sorta-kinda-de-duplicating. The schema appears here, but it is probably not very important. There are no perfectly-duplicate ...
0
votes
1answer
21 views
Using JOIN to find duplicates in another table taking too long
Have a MySQL 8 database with the following tables.
people
id | mobile | name
registrations
id | people_id | other_stuff
I am trying to find the mobile number and the number of times it's duplicated ...
1
vote
2answers
60 views
How to hint Remote Scan operator to estimate more than 10000 rows?
I need to read data from a linked server and insert into a local table. I need to remove duplicates in data and I need to do it on a local server, because remote server is overloaded. So, I added ...
0
votes
0answers
12 views
Optimising tracking page views Aggregation
select page.*, views.view_count
from page
join (select page_id, count(page_id) view_count
from (select page_id, user_id, floor(EXTRACT(epoch from time) / EXTRACT(epoch from ...
0
votes
1answer
26 views
Which query performs better in MongoDB ($or or $facet)?
I have a query similar to the following;
db.getCollection('list').aggregate([
{
$facet: {
"events":[{
$match: {
'type': 'Event'
...
0
votes
2answers
64 views
Comparing Query Performance Across DBMSs
I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.
I have been looking into command line tools for each system to get the ...
1
vote
1answer
24 views
Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:
There will be hundreds (maybe even thousands) of ...
2
votes
1answer
69 views
Forwarding records in #temp table
I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in ...
0
votes
2answers
58 views
Postgres index scan on large table taking a long time
I have a large table (~500M records, 85GB in size) on a PostgreSQL 10.10 database (AWS RDS instance).
The records are created by a bunch of game simulations which insert "events" whenever ...
0
votes
1answer
25 views
ola.hallengren - Alternative MySQL scripts!
Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment.
Is there a MySQL alternative to ola.hallengren? - Perhaps someone ...
0
votes
1answer
39 views
Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution [duplicate]
We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the ...
1
vote
1answer
17 views
How to optimise query fetching most recent report for each entry in another table?
I am attempting to optimise a query that displays the last reported movement for a set railroad locomotives. The query currently takes 8-12s to run for a common query to generate a report on a webpage,...
1
vote
2answers
64 views
Disable AUTO_UPDATE_STATISTICS
We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the ...
2
votes
2answers
206 views
Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?
I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[type] [int] NOT NULL,
[source_user_id] [bigint] NOT NULL,
[target_user_id]...
3
votes
2answers
254 views
Why doesn't SQL Server use my index in this SELECT … WHERE?
I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[...
