Stack Exchange Network

Stack Exchange network consists of 174 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

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

0
votes
0answers
23 views

Memory Pressure in SQL Server.Enable LPIM or not?

I have slow query execution in my SQL Server 2016. Server configurations: Total Physical Memory : 128 GB SQL server Max Memory : 102 GB Transaction replication is enabled. Database Size:1.6 TB I ...
0
votes
0answers
31 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
0
votes
3answers
59 views

How can I speed up this query?

Here is the definition of the relevant table: CREATE TABLE IF NOT EXISTS pricehistory ( id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, price INTEGER NOT NULL, region ...
2
votes
1answer
30 views

sparse column or DATA_COMPRESSION?

in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] ...
0
votes
0answers
51 views

Long running SQL Server Query, eats up a lot of resource

I have already posted on stack however as recommended by the guys on there I am now putting it on here. See my previous posts: https://stackoverflow.com/questions/52850961/sql-query-long-running-...
0
votes
1answer
55 views

How can I get SQL to do the sensible thing without resorting to a query hint?

I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000. Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2)...
0
votes
0answers
59 views

Very slow nested SQL query

I seem to have hit some bottlenecks around nested SQL queries. I have three tables in PostgreSQL with the following: Table-1: has 2443 unique rows Table-2: has 3414569 unique rows Table-3: has ...
0
votes
0answers
27 views

Remote Scan: What are some performance tuning techniques to improve a query when this operator is the bottleneck? [closed]

I've ran into the Remote Scan operator as the bottleneck for a multitude of queries I've written (usually when I need to update data on one linked server from another). (The issue where the whole ...
1
vote
0answers
77 views

Reduce query time for higher offset in sql server

Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows: CREATE Procedure [dbo].[spTestingBaseVoter] @SortColumn NVARCHAR(128) = N'...
1
vote
1answer
46 views

Why is my query taking so long if my field is correctly indexed?

I’m trying to figure out why my query is taking so long. I have indexed my property_id field but still get awful results when querying it. I have the following Explain and wonder if anyone can shed ...
0
votes
0answers
27 views

Enabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways

First of all just want to say , I am completely new to Transparent Data Encryption. Can I get some guidance on things to keep in mind while I am enabling TDE on Critical PRODUCTION Servers. I have ...
1
vote
0answers
27 views

Complex query help needed

Dear Stackexchange members, My problem I need help with the following in MYSQL: For a website that rents accommodations I need to calculate the availability of the rooms. A scenario: A user wants ...
1
vote
1answer
21 views

PostgreSQL: Slow JOIN between multiple tables.

Context: I am working on a data warehouse that needs to have responsive look ups as it is used as the source for an operator console. The target is to achieve sub 1 Second lookups (ideal would be ...
4
votes
0answers
67 views

SQL Server High Hash Match and Sort

I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete. ...
1
vote
2answers
32 views

Optimal way to save Social media posts from multiple platforms

I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields ...
2
votes
1answer
42 views

Efficient pagination for big tables

Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results. In an attempt to not use OFFSET, I pass the id from the last row in ...
0
votes
0answers
38 views

sqltrpt - sql performance tuning

I have an SQL SQL ID : 94357g2s5u538 SQL Text : SELECT PN.ENTITY_NAME_UPR, SA.OLD_ACCT_ID, STC2.CHAR_VAL, ACT.MAILING_PREM_ID FROM CISADM.CI_SA SA, ...
0
votes
1answer
21 views

mytop to monitor multiple mysql servers

I have multiple MySQL servers running on CentOS. I would like to use mytop to monitor real time database query activities, but do not want to install mytop locally on each of my MySQL instances. How ...
2
votes
1answer
63 views

How can I make this nested query more efficient?

I have 3 tables: Room, Conference, and Participant. Room has many Conferences, and Conference has many Participants. I need my query to display the fields from Room, as well as the number of ...
-1
votes
2answers
37 views

Group by in subquery

So my problem is the following. Consider this query: SELECT P.id, (SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample FROM publisher P WHERE P.name LIKE '...
1
vote
0answers
35 views

mongodb aggregation query performance

We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse. The record count is about ~4m for PurchaseOrder and each purchase ...
0
votes
1answer
60 views

Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server

I am trying to get data using offset limit concept as follows: CREATE PROCEDURE BaseUserIndex @Offset int, @Limit int, @SortCol int, @SortDir nvarchar(10), @Search nvarchar(255) = ...
0
votes
0answers
15 views

How to optimize (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
0
votes
2answers
31 views

Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL

I have one SP which generates a final query (like the one below) which is taking too much time to execute. Basically it has the following parts: IFNULL to check that if value is null then send ...
1
vote
1answer
49 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 ...
0
votes
0answers
42 views

Change to query with a number of CTEs confuses query planner

The below query runs normally in about 1 minute. I made some changes to it for reasons and suddenly performance was ~90x worse. When I run the query with every CTE instead inserting into a temporary ...
0
votes
0answers
28 views

Performance issue while using connect by for Hierarchical data [on hold]

I have a change point of year and quarter in a table as below ID Year QTR CITY 1 2018 3 X 2 2018 1 X 3 2017 3 Y 4 2017 1 Y I used connect by to fill the gaps of every city with ...
27
votes
1answer
2k views

Why is a scan faster than seek for this predicate?

I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals. On my machine, the following query does a clustered index ...
0
votes
2answers
92 views

Understanding why a query is slow

I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its ...
2
votes
2answers
77 views

Slow Bitmap Heap Scan, Order by killing the execution time

A query that is going to slow on table users, the table has 5 million records, and for increasing query performance I often index function outputs of aggregated data that are convenient for each case, ...
2
votes
1answer
71 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 ...
0
votes
0answers
19 views

find out postgresql query progress [duplicate]

I have ran an UPDATE query in postgresql. After 5 days it has not finished yet. Is it possible to check percentage of completeness of my query which I have ran previously. I want to know if it takes ...
0
votes
0answers
20 views

Reasons why same query, same data, different Server/ DB can be much slower [duplicate]

I migrated a database from one instance on a server to another. The structure is the same, the data is the same. BUT ... the queries are going at much different speeds. A variety of queries; not just ...
2
votes
1answer
58 views

Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
0
votes
1answer
64 views

How to improve execution time for queries by multiple fields of different data types on a single table?

Table design On SQL Server Microsoft SQL Server Enterprise Evaluation (64-bit), version 12.0.5207.0, I have this table, called InputResult: Table has 11.645.408 rows. The columns ValueAsString, ...
0
votes
0answers
11 views

Posgres full-text search slow

Advise how to speed up the search based on postgres. I have been trying to cope with this for a week already. There is a news table. I created a column tsvector ALTER TABLE articles ADD searchable ...
0
votes
1answer
51 views

Is it harmful to update columns which aren't changed?

Say I have a table with some columns: CREATE TABLE t ( key int PRIMARY KEY, c1 int, c2 text, c3 timestamptz ); I now fetch a row from the table: SELECT * FROM t WHERE id = 180; ...
-1
votes
1answer
49 views

SQL Server database design for fields of different data types - single table with multiple columns or multiple tables for each data type?

We have a production application which gathers data for certain type of incidents, based on a collection of fields (aprox. 1000) of different data types - string, int, double, date, time, GUID (id of ...
1
vote
0answers
40 views

Adding field in SELECT cause Using temporary; Using filesort [closed]

I have query with JOIN EXPLAIN SELECT o.id, ostring.services_string FROM offers o LEFT JOIN offers_string ostring ON o.`id` = ostring.`id` WHERE o.`date` >= '2018-09-22 00:00:00' o.`date` <= '...
-5
votes
1answer
40 views

please help me tune the below SQL Server query..its running forever [closed]

Declare @bst varchar(1000), @pst varchar(1000), @ost varchar(1000), @t_cnt int = 1; WHILE @t_cnt <= (SELECT count(*) FROM t_temp) BEGIN SELECT @bst = g_Search_Text FROM t_temp WHERE id = ...
0
votes
1answer
34 views

Postgres query planner issues

There are two cases from different DBs and my main questions are: why it is happening? what can I do to stop it from happening? OK, case #1 EXPLAIN (ANALYZE, BUFFERS) SELECT "notes"."id" AS t0_r0,...
1
vote
4answers
79 views

How to Improve Performance of Query in SQL Server

I have a query and currently it's taking 10 seconds for only 324 records. Is there any way to improve this performance? I have tried using SET NOCOUNT ON in the SP as I have read that it improves ...
0
votes
0answers
12 views

What has better performance, one select or two separate selects in MySQL?

In MySql, is better to do 1 select to all the table and then filter in the backend or do 2 selects that retrieve a half of the table each and not filter in the backend?
0
votes
2answers
38 views

SUM value in table with timestamp column where timestamp is between start and stop timestamps in different table

I have a situation where there are network hosts whose monthly bandwidth is summed based on a start date that varies for each host. The number of hosts is quite small (< 5000) but the history ...
0
votes
2answers
34 views

Optimize Postgresql query

I'm struggling to get the below query to perform well. It's doing a sequential scan on the children table even though there's indexes on the join columns. Any ideas? EXPLAIN ANALYZE SELECT "children"....
1
vote
1answer
48 views

what are memory fractions and how to get rid of them (or the sort operator) in the example below?

This execution plan has the following memory fractions: Memory fraction input:1, Memory Fraction Output:1. it is a very simple query: SELECT [a].[activityId], ...
0
votes
0answers
6 views

Handling Many Text Fields in a Mysql Table

Specification : Mysql (5.7.23) , Baracuda file format, DYNAMIC row format. I am working in bootstrapping a product. I have a scenario where the user may require 1 - 500 Text Fields for a particular ...
1
vote
1answer
33 views

Find missing timestamps grouped by key: finding gaps in my data

I have a table that has a timestamp, some data and a identifying key for the data source: create table test_data ( id serial primary key, key text, timestamp timestamp with time zone )...
0
votes
0answers
43 views

Is there an alternative to savepoints to handle postgres errors?

I am using SQL Alchemy, but to the extent of what I need, just assume I wrap all my code in this structure: I get the DB connection from a pool. I start a transaction. On success, I commit. On error, ...
0
votes
2answers
53 views

Need to understand the drastic difference in execution plan between two queries

I have 2 queries. select guid=NEWID() into #guid --update statistics #guid with all --create statistics s1 on #guid(guid) select * from Party a join #guid b on a.[Party_GUID] = b.guid --...