Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
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].[...

1
2 3 4 5
50