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 --...
