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
14 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
17 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
36 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
32 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
512 views

The performance of mysql fulltext depend on order of keyword

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result: FAST (1s) SELECT * FROM ...
1
vote
1answer
94 views

MySQL - Log data with Inner Join 12 Tables or Create View?

I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website. Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only ...
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
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
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
37 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
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....
0
votes
3answers
89 views

Query takes too much time to execute

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ I want to reduce the execution time of this query. So require to optimize ...
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
0answers
17 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
131 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE '...
0
votes
1answer
97 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
3
votes
2answers
116 views

Optmizing ORDER BY

I'm new to MySQL and have written the following query (see below). PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes ...
-2
votes
0answers
23 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 ...
1
vote
1answer
161 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
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....
1
vote
1answer
95 views

join_buffer_size causes high cpu load

I am playing and testing with my.cnf and at the moment I have these server specs: 12 CPU vCore 500 GB SSD 24 GB RAM 1 GB Swap and this is the input of my my.cnf: # GENERAL # # user ...
0
votes
1answer
19 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 ...
0
votes
1answer
161 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
1
vote
1answer
26 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` ( `...
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'); ...
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
2answers
28 views

Are there any Oracle query performance checker tools? [closed]

Is there a tool that checks oracle query performance? Something like a "grading" tool which basically tells that performance of the query based on let's say 1 being the lowest and 10 being ...
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). ...
9
votes
1answer
2k views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers ...
0
votes
1answer
255 views

Postgres 11 declarative partitioning by relative time

I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
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
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
31 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 ...
0
votes
1answer
231 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
-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 ...
0
votes
1answer
97 views

Query takes too long to query with OR clause but their parts are very quick

I am querying two tables, each with ~1M rows indexed by their IDs, using the following query: SELECT t.* FROM transactions t INNER JOIN integration it ON it.id_trans = t.id_trans WHERE t.id_trans = '...
0
votes
1answer
104 views

Postgresql Lateral Left Join Query Performance Issues - Takes a Long Time

I am trying to run the query below on a database with hundreds of thousands of lines but it is taking over 12 hours so far. It is the lateral left join that is taking so long. The reason I need the ...
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
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
2answers
58 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
1answer
25 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' ...
1
vote
1answer
473 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
0
votes
1answer
129 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
0
votes
1answer
745 views

PostgreSQL LWLock: lock_manager issue

In our application we need to insert a lot of historical data that will be consumed later by some mobile devices. These data is organized by a numeric incremental attribute that the device will use to ...
2
votes
1answer
89 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...

1
2 3 4 5
50