Questions tagged [query-performance]

The tag has no usage guidance.

390 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
1k views

MongoDB - slow remove query

I have a collection in MongoDB with 800M+ records. I wanted to do some cleanup, but the remove operation is really really (unusably) slow - even deleting 1000 records can take 10+ minutes. The ...
5
votes
1answer
493 views

MySQL Partitions with Partition limit

I have a partitioned table on the basis of the Primary key having 1.5 Billion rows in it. But when I check the .ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know ...
4
votes
0answers
823 views

How to measure the memory usage of a Mongodb aggregation?

I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
4
votes
2answers
688 views

Column Cardinality and Total Number of Rows Are Equal

I have a table with 128'914'635 number of rows. Some columns cardinality (i.e. address, lastname, firstname) is equal to the total number of rows in the table (128'914'635). The queries are really ...
4
votes
1answer
355 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
4
votes
2answers
431 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
3
votes
0answers
42 views

How to optimize this recursive postgresql query?

I've been searching a lot on this topic but I'm stuck at optimizing a query. I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
3
votes
1answer
828 views

What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
3
votes
3answers
1k views

Optimise Large Record Deletion Procedure

I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes. Ensuring we've enough data to operate on as an engineering team, I'm ...
3
votes
0answers
50 views

What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?

To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable: A sequential organization on a key attribute ? A ...
3
votes
0answers
108 views

Subquery in FROM slower than two separate queries

I'm working on a system where users can buy "punch cards" to join events. A client buys a card from an organiser. The card is valid for specific event types and for a specified number of events. The ...
3
votes
3answers
244 views

How to optimize an ordered date range query with several OR filters (for streaming)?

I have a table of data which is be outputted into a stream, so the upper bound of the cost doesn't matter as much as the lower bound. The table contains about 200M rows. The question is how to ...
3
votes
0answers
237 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
3
votes
0answers
74 views

Understanding Database Query Cost Optimzation

I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
3
votes
0answers
3k views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
3
votes
0answers
94 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
3
votes
0answers
79 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
3
votes
0answers
1k views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
3
votes
1answer
652 views

What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?

I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
3
votes
1answer
114 views

How can I improve my query performance?

I'm building a statistic application based on Django & PostgreSQL with focus on performance and maintainability. The problem is, that data is distributed among multiple relatively large tables. ...
3
votes
1answer
4k views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ,...
3
votes
1answer
261 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to be ...
3
votes
2answers
109 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
57 views

Why does MySQL take so long to execute this query on a large dataset?

Been banging my head against the wall on this one for a while now and hoping someone can help me out. The SQL query: SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `...
2
votes
1answer
52 views

Incredibly long execution time for a update query?

I am using MySQL 5.6. In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value. UPDATE invoices SET twoWeekAlert = DATE_ADD(now(), ...
2
votes
0answers
28 views

Improve split large table algorithm in MySQL

I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop ...
2
votes
0answers
102 views

How can i tune query without creating index

I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not ...
2
votes
0answers
57 views

Optimizing a paginated query with various filters and dynamic sorting

Introduction Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
2
votes
0answers
457 views

Postgresql slow remote execution when using alternative clients

I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server. Everything good when using psql: Connect via ssh and run things locally $ ssh user@example.org user@...
2
votes
0answers
90 views

Postgres / slow query / order by

I'm joining two tables, filter out results by second table and order results by first table's ID. The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But ...
2
votes
1answer
107 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources ...
2
votes
0answers
533 views

Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?

I run the following query against Postgres 9.5 which returns article information along with the currently valid price: WITH selected_article_rm(id) AS ( VALUES ('80512e52-a406-4a9b-a8df-...
2
votes
0answers
128 views

why is update from a join containing zero records taking a minute and 20 seconds?

SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
2
votes
1answer
99 views

SQL server slow query

I have the following query that is taking a long time to execute (nearly 20 seconds). SELECT Count(*) FROM "workorder" "wo" LEFT JOIN "workorder_fields" "wof" ON "wo"."...
2
votes
0answers
79 views

Problem query - execution plan

I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
2
votes
0answers
118 views

SORT(DISTINCT) :Is it because of Kitchen sink queries?

One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and ...
2
votes
0answers
2k views

Mysql: Stored procedures vs. inline SQL?

We are evaluating whether we should write stored procedure OR write inline queries. I read this link to get an opinion. I want to know pros and cons from mysql perspective as the above question is ...
2
votes
2answers
270 views

On Duplicate Key Update - million times per day optimization?

I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it: INSERT INTO campaign_stats (user_id, date, $country) ...
2
votes
2answers
303 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
2
votes
0answers
65 views

What can cause a static SQL query against a static data set take vastly different amount of time to complete?

I have this very strange issue on one of the production servers - a parameterless stored procedure is either taking 1-2 minutes or 45+ minutes to finish, and it seems completely random and not ...
2
votes
0answers
415 views

Why is Oracle SQL slower than cursors?

I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values. The amount of data is ...
2
votes
0answers
66 views

Redesign query/database because of high load

Need help with query or redesigning the db. Please bear with me the query will be changed to pdo ASAP. I'm not a DB admin, nor am I some MySQL wiz. I have the following tables: CREATE TABLE IF NOT ...
2
votes
0answers
61 views

What can I do to determine why my query occasionally hangs (currently hung for over 3 days)

I have a query in my data warehouse load that occasionally hangs. Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours. I'm unsure whether this is the query ...
2
votes
0answers
543 views

What to do when you find slow running query?

I have two databases. One with 10.000 rows and one with 10 million rows. I have a load test and I am running it towards a rest api endpoint. This endpoint does a query to database and average ...
2
votes
0answers
102 views

Performance on this query

We have the query below in our software to get a set of results which can be used to calculate the score of a location. As it stands it is too slow to retrieve the results in the timely fashion we ...
2
votes
0answers
516 views

PostgreSQL: Slow Update Query

I'm running this update script in PostgreSQL 9.6: do $$ begin create temporary table if not exists tmp_rutting1 ( road_id integer, direction_id integer, begin_km double ...
2
votes
0answers
32 views

How much does a query of deletion costs in terms of operations when the attribute is a key or an attribute?

How much does the following query costs in terms of operations when the attribute is a key or an attribute ? DELETE FROM R WHERE ATTRIBUTE = 50 We assume we have R(K,A,B,other) a relation with ...
2
votes
0answers
218 views

In memory table query performance

I am trying to optimize an sql query by using in memory tables. After I had ran the query some times I ran the Transaction Performance Analysis Overview report of SQL Server 2016 which suggested that ...

1 2 3 4 5 8