Questions tagged [query-performance]

Use this tag for questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0
votes
1answer
30 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
9
votes
3answers
192 views
+100

Help performance tuning master/detail (email like inbox) SQL query

I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below. ...
3
votes
1answer
283 views

Optimize a slow nested loops join

I have this query running around 15 seconds on my system. The query plan is here. I believe the main problem is around here, where the optimizer chooses nested loops and is mistaken with cardinality: ...
1
vote
2answers
43 views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...
1
vote
2answers
17 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
1
vote
1answer
27 views

Alternative to slow [LEFT JOIN + OR] without UNION?

I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables. The query using only left joins ...
0
votes
2answers
54 views

non clustered index coverage with INCLUDED columns

If I have two indexes: IDX_1 = (COL1), (COL2) DESC IDX_2 = (COL1) INCLUDE (COL3) will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE ...
0
votes
1answer
44 views

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

Is there a scenario where it's faster for synchronizing the data from TableA to TableB by using a MERGE query as opposed to dropping TableB and doing a SELECT * INTO TableB FROM TableA?
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 ...
16
votes
1answer
12k views

Operator used tempdb to spill data during execution with spill level 2

I am struggling to minimise the cost of sort operation on a query plan with the warning Operator usedtempdbto spill data during execution with spill level 2 I have found several posts related to ...
1
vote
1answer
61 views

Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows

I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them. Trying to insert this query in a table on destination server takes over 2 ...
1
vote
1answer
22 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
3
votes
3answers
228 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 ...
1
vote
2answers
3k views

Creating Sort Index takes 96% of query time even without any ORDER BY

I have this query: SELECT itr.recipe_id, SUM(itr.weight), SUM(aprice_weight), SUM(itr.weight+aprice_weight) AS score FROM (SELECT itfin.id AS flyer_item_id, itfin.flyer_id, ...
1
vote
0answers
27 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
1
vote
2answers
69 views

Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU

My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is ...
0
votes
1answer
36 views

SQL logic tuning - approach [closed]

I've simulated my requirement in the below SQL and the o/p is as expected. My question is - if this is the optimal way of achieving or I'm missing something very obvious technique ? I need to find ...
1
vote
3answers
198 views

How to find resource intensive SQL operations in Postgresql? High CPU usage spike

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. ...
1
vote
1answer
100 views

Changing stored procedure parameter results slow stored procedure performance

I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to ...
-1
votes
1answer
56 views

Problem with indexing null values

There is a table 'test' with ~ 10M records with field 'finish'. select count(*) from test where finish is null; count --------- 2485009 (1 row) I'd like to create index to speed up queries like ...
1
vote
2answers
72 views

Overestimated query resulting in poor performance - TVF with xml processing

Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for ...
2
votes
3answers
50 views

MySQL query very slow with multiple concurrent users in a short time

I have a site with a MySQL 5.5 database where most of the traffic happens at a fixed time every single day when thousands of users are loading the site in a period of 2-3 hours. My slow query log (...
2
votes
1answer
38 views

Get first part of result faster in sorting query on hundreds of millions of rows?

I have a single table containing around 280 million rows in MariaDB 10.4.10. I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest ...
0
votes
1answer
16 views

Optimal query for comparing table column vs. summary column

What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table? For example, I have an account table and a task table. Below is the query I'...
0
votes
2answers
36 views

Select records that have at least one link to other tables

I want to select all users that have either a post, a photo or a video. I came up with the following query. select users.username from users where exists (select * from posts where users.id = ...
0
votes
1answer
57 views

Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows

I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why ...
1
vote
1answer
96 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
-1
votes
1answer
98 views

MySQL Huge DELETE

I'm trying to delete 10000 rows from my database on MySQL 5.7 but it's been stuck for 7 hours. Query: DELETE FROM article WHERE id in (52109548,52109558,52109560,52109564,....). This are my buffer ...
0
votes
1answer
92 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 ...
0
votes
2answers
37 views

How to write SQL queries to populate the tables while synchronizing transactions and account balance?

I have two tables having the following schema. CREATE TABLE users( `id` INT AUTO_INCREMENT, `balance` DECIMAL(10, 2), PRIMARY KEY(`id`) ); CREATE TABLE user_transactions( `id` INT ...
0
votes
2answers
95 views

MySQL - two tables with relation one to many - retrieve “n” fields from table1 WHERE SUM of a field on table2 is less/equal/greater than “value”

Tables structure: CREATE TABLE IF NOT EXISTS `A` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` int(6) NOT NULL, `docnr` int(6) NOT NULL, `docy` varchar(3) NOT NULL, `docd` date NOT NULL, `ent` int(3) ...
2
votes
0answers
37 views

Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?

I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1. #TempTable1 has a BIT field called ToInclude which is set in a ...
2
votes
2answers
68 views

Postgresql Query plan different on two servers

I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
39
votes
2answers
34k views

Configuring PostgreSQL for read performance

Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow. The primary key (constraint) structure is similar ...
2
votes
2answers
33 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
2
votes
2answers
249 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) ...
0
votes
1answer
77 views

Is filtering on int better than filtering on date [closed]

I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime. The question, if I move this datetime to another new table and ...
-1
votes
2answers
36 views

Query speed improvement (opening stock, running stock and closing stock)

I have a table (refer to below structure) where I track all stock ins and outs. Now I want to generate a report from this table to find the opening_stock, purchased, purchased_return, sales, ...
0
votes
1answer
103 views

Optimize schema and query for overlapping date ranges query

We have a database where we track the price of products from different sellers. If I strip everythin else it looks like this: I need to make a report where given a date range I have to return a row ...
-4
votes
2answers
467 views

Why over 1.3TB desired memory for this query, it should have no memory grant?

SQL Server version is 2014 Developer SP1. Trace Flags 272, 610, 1118, 1206, 1222, 8048, 9481 are globally turned on. We had to turn on flag 9481 since the new cardinality estimator badly effected ...
35
votes
2answers
10k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
0
votes
1answer
159 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 ...
10
votes
3answers
2k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
5
votes
2answers
98 views

Query Execution oddities between SQL Server 2008r2, 2017 and 2019

I have a very basic query that was running completely fine with index seeks across the board on SQL Server 2008r2. When we migrated to SQL Server 2017, it started performing worse, and now does a ...
0
votes
2answers
42 views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
0
votes
1answer
115 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 '...
1
vote
1answer
198 views

What causes temporary slow (fulltext) queries?

I logged this slow query on a 400k rows MySQL 5.6 InnoDB table: SELECT SQL_NO_CACHE topic_id FROM topics_search WHERE host_id = 1 AND forum_id IN (23, 50) AND MATCH(topic_text)...
1
vote
3answers
99 views

Execute a query but not return results to client

I am testing the performance a query and trying to fix a parameter sniffing issue. Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows. I want to test the impact ...
3
votes
2answers
155 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
0
votes
1answer
20 views

Stored Procedure taking longer than a normal query

I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query. Procedure DELIMITER $$ DROP PROCEDURE IF EXISTS `...