Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [query-performance]

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

1
vote
0answers
7 views

Postgres Row Level Security policy optimizes poorly compared to inline version

I have a query which looks like this: SELECT post.id, post.author_id, post.published_at, post.content FROM post WHERE post.group_id = 1 ORDER BY post.published_at DESC, post.id LIMIT 5; ...
0
votes
0answers
24 views

Designing a table for large amount of rows. No relationships

I am looking to design a database that'll allow me to store references to files. The table is pretty simple -> Id(GUID)(PK), Path(varchar), IsPublic(bit), FileType(int) The files can be accessed via ...
3
votes
2answers
118 views

Performance Improvement went wrong in Production, worked fine in Test

We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit ...
1
vote
1answer
58 views

Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence

Introduction I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a ...
3
votes
1answer
84 views

Queries running parallel with default maxdop

I need to understand below behavior of sql server queries System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance. Currently MAXDOP is 0 and ...
0
votes
0answers
10 views

NoSQL big DB retrieve performance

A question about retrieving performance for simple but bug key-value DBs on python3. I am working on a pipeline to compare several entries based on key-values (genetic variants found in patients with ...
0
votes
0answers
23 views

Increasing working memory in Postgres does not improve query performance

I am very new to database administration and could use any advice from more expert users of PostgreSQL My database is an AWS RDS db.m4.large whose specification are here My queries against one of ...
0
votes
1answer
19 views

Optimize query plan when your results are either a few rows or hundreds of rows?

The most frequent query typically uses a variable that has hundreds of rows, but also runs often with a variable that has only a few rows (it's always one or the other, nothing in between). SQL ...
0
votes
0answers
15 views

Joining multiple (10+) tables IN CASE attribute exists, min 1, max all

I've asked a version of the question here Allow Null Columns vs separate 1-to-many tables and here Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design? but ...
0
votes
0answers
29 views

Need Help in extended events SQL Server

I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now CREATE EVENT SESSION [...
1
vote
1answer
24 views

MSSQL Spatial performance (point in polygon)

Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused I have a query, below, that works - however its very slow, looping through roughly 24,000 points per ...
0
votes
0answers
25 views

The right choice to distribute reads and writes on different servers [on hold]

I have to get applications on my SQL Server 2017 Standard make reads faster and writes faster. Sometimes the reads and even the updates got blocked (300 deadlocks from the start of year). I have the ...
2
votes
1answer
50 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
0
votes
0answers
26 views

Optimise multiple inner JOIN

Consider the following query: mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n') -> FROM assignment a -> INNER JOIN statements s ON a.id = s.assignment_id -> INNER JOIN ...
3
votes
1answer
151 views

Adding an additional “order by” column gives me a much worse plan

in other words, how can I get rid of the sort operator on the picture below? the picture above shows the execution plan of the following 2 selects together: SELECT TOP 1 so.OrgType,...
0
votes
0answers
19 views

Efficient pseudo-random entry constrained selection

I am looking for a SQL query to pick up a (pseudo)random entry that matches a WHERE clause from a table of ~1,000,000 entries. This query must be as efficient as possible (must resolve in, at most, 0....
0
votes
2answers
31 views

does sequence of columns in where condition matters the sequence of composite key? in Oracle Database

here, In the code where exists clause contains all primary key columns in where, I want to know that the sequence of this columns in it, matters in query performance. update temp_fd_dtl_svj a set ...
3
votes
1answer
101 views

What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?

Question: Is there a better indexing strategy or query SELECT that I can use for looking up one large data set against another large data set? Or, should I look at placing the lookup dimension table ...
1
vote
1answer
49 views

Query performance depends on parameter value

In the system I'm working with there are many type hierarchies, where each type corresponds to a table. For example f_dp_rkkbase -> f_dp_rkk -> f_dp_inputrkk. To distinguish between different ...
1
vote
0answers
37 views

Query Store- force plan not working as expected

Have similar issue as was ask in other question. In my case none of cases from microsoft docs seems to apply Can something else be the reason? Schema changes not happening, but after index ...
3
votes
1answer
66 views

Non-clustered indexes for columns defined within a composite Primary Key

I have a multi-tenant database where I am utilizing an AccountId column within all tables as a part of the composite Primary Key for tenant isolation. Is it beneficial to create additional non-...
0
votes
0answers
34 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
5
votes
1answer
48 views

Why an incorrect JOIN using correlated sub-query is so much slower

I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
0
votes
1answer
70 views

Query is slow with JDBC parameters, fast with concatenated SQL

I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this ...
3
votes
0answers
51 views

Seeking Help Optimizing Self-Joins

This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the ...
2
votes
0answers
67 views

Why does the location of a join change performance? [duplicate]

I came across a performance issue and can’t quite understand what causes it. It has to do with the order in which the joins are done in my query. The predicates on the join is always the same, but ...
0
votes
2answers
48 views

select query with where clause having variable taking more time to execute

I have a query which selects a couple of rows from a view based on the variables declared. I ran the same query in lower environments and to my surprise, I got the result in less than 3 minutes ...
16
votes
1answer
1k views

Single-row INSERT…SELECT much slower than separate SELECT

Given the following heap table with 400 rows numbered from 1 to 400: DROP TABLE IF EXISTS dbo.N; GO SELECT SV.number INTO dbo.N FROM master.dbo.spt_values AS SV WHERE SV.[type] = N'P' ...
-1
votes
0answers
13 views

repalce query with left join and not in

i write a query with NOT EXIST like this > select id_request,username_request from darkhast_follower where > darkhast_follower.id_request != '9762952594' AND > darkhast_follower....
2
votes
0answers
32 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@...
1
vote
1answer
37 views

Advice on fine-tuning query and index for performance

Currently my query is as below: select id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.status =0 AND NOT exists (...
0
votes
1answer
22 views

Reducing latency in read operations

Table size 45 million rows Schema: item name, categories and subcategories, order quantity and price. What is the fastest way to put each item into an appropriate bucket? For example: price range ...
1
vote
2answers
26 views

How to most effectively get the first preceding/subsequent entry outside a given time interval?

We have a SQL Server DB that contains a large table (>100M rows) with archive values, basically in the form of [Id], [StationId], [DatapointId], [Timestamp], [Value]. As the stations do not get polled,...
0
votes
2answers
41 views

What kind of SSD to speed up querying a lot of data?

For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
1
vote
1answer
68 views

Simple SELECT taking too long to run

I am running this simple query: SELECT * FROM ABC_TABLE WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18' There are 64 columns and we need all of them. There are a total of 2,066,381 ...
2
votes
1answer
64 views

Does Batch Mode only apply to Agg Functions on ColumnStore Index

Azure SQL Database - Standard Edition (S3 Service Tier) Why does Batch Mode only come into effect when using Aggregate Functions? DROP TABLE IF EXISTS dbo.TransCS CREATE TABLE dbo.TransCS ( ...
0
votes
1answer
16 views

How to denormalize many-to-many relation without nested select?

I have the following schema: house has many students, student has one house gender has many students, student has one gender game has many students. student has many games For instance, consider ...
0
votes
1answer
55 views

Different manifestations of the same logic yield very different execution times [closed]

Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested. For every widget in the ...
0
votes
0answers
28 views

Improving/ Optimizing Performance Query in MySQL PHP

I have a invoices, invoices_items, order, order_items. Invoices and Orders tables contains around 1 Millions records. Invoices_items and Orders_items tables contains more than 2 Millions records. ...
-1
votes
2answers
162 views

Queries key look up [closed]

SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col25... FROM Tabl1 WHERE ( Col30 IS NOT NULL AND Col28 <> @P0 AND DATEDIFF( dd, Col31, ...
0
votes
1answer
25 views

How to delete all but the latest entry per customer in the fastest way?

I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as ...
1
vote
2answers
47 views

Why is a COUNT query faster than a result set query?

Suppose I have two similar queries, with complex joins; query 1 returns rows and columns: SELECT col1, col2, col3, ...etc FROM MyBigTable LEFT JOIN AnotherTable1 On ... etc LEFT JOIN ...
0
votes
0answers
25 views

Left join with same table making query too slow — MySQL

We have a table server_user_disk_usage which tracks the disk usage of users from different servers. Query is to find sum of last disk usage of each user from different servers SELECT m1.user_name, ...
0
votes
0answers
23 views

Adding LIMIT slows down query

SQL CREATE TABLE actions ( action_id bigint GENERATED ALWAYS AS IDENTITY, block_time timestamptz NOT NULL DEFAULT now(), block_num int NOT NULL, trx_id text NOT NULL, ...
0
votes
0answers
22 views

Sql Server - Execution Plan - Query Cost (relative to batch) calculation [duplicate]

We have a Table Value function which: Creates and Populates a table variable (@Temp) from a SELECT Runs two different UPDATE statement to change values on 2 different columns in @Temp Populates the ...
0
votes
1answer
32 views

SQL Server Files in Secondary file group updates much slower from linked server

My SQL server fact tables are incrementally being updated with reference to a linked server. The queries run routinely on a regular basis and have been executing successfully for a long time. The ...
-3
votes
1answer
26 views

Sql Server benchmarking - Clarifications [closed]

We are in process of enabling both TDE and EFS on our DBs. In process of performance benchmarking to understand how sql server is behaving on current hardware pre and post , we are using perfmon or ...
1
vote
3answers
132 views

Improve Query Performance of Select Statment inside IN Statment

My query is: (3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in ( (2)SELECT post_id FROM ( (1) ...
0
votes
0answers
15 views

any knowledge or experience with dynamic ax [duplicate]

I support a system called "dynamics ax" and sometimes my optimizer picks a good plan, drops it and then picks a bad plan (where the same code is been ran). users who usually wait for 10 seconds start ...
1
vote
1answer
31 views

Debug Connection Pool Error

I have been getting the below error. System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout ...