Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
1
vote
1answer
51 views
What are some consequences of putting quotes in a WHERE clause for an integer column?
I have a table with a column some_column of data type integer.
I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same:
select * from my_table ...
0
votes
0answers
10 views
How to Configure MySQL for Percona Monitoring and Management [on hold]
I can not get any analytics from _PMM Query Analytics showing
https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html this is that link. I changed the configuration as ...
1
vote
1answer
76 views
Understanding MAXDOP behaviour for queries going parallel
For my current server as below
4 Sockets with 10 cores per socket and 20 logical processors per socket.
Total 80 per SQL licensing: 4 NUMA nodes with HT enabled
I've set CTOP 50 and MAXDOP 8 on ...
-3
votes
0answers
27 views
Open source database for testing algorithm [on hold]
I have developed algorithm which probably would optimize query executions and accuracy of results. I am seeking for some open source or free database systems where I could run this algorithm. Even ...
0
votes
1answer
28 views
Speed up slow postgres query with indices (~5 minutes)
I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so:
...
2
votes
2answers
151 views
Should I split timestamp parts into separate columns?
I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
0
votes
0answers
23 views
ORACLE : Query optimisation - How to optimize query by replacing “order by” and few row fetch after ordering
Below is the join query where in query takes time mostly for "order by" and fetching top few rows after ordering.
SELECT *
FROM (
SELECT q.*
,ROWNUM RN
FROM (
SELECT T1.X,T2....
0
votes
0answers
39 views
Designing a database for a weekly time schedule - speed vs space
I need to "store" a weekly time schedule as compactly as possible. The schedules are in 15-minute increments. In a 24 hour period that would give me 96 time slots. Midnight is 00, 0600 is 24, 1800 ...
1
vote
0answers
33 views
Different query plans for same In line Function [duplicate]
I am facing a potential parameter sniffing issue with an In Line function in sql server 2008.
Basically Given the below three queries (same function):
First Query:
DECLARE @SQL NVARCHAR(1000)
...
1
vote
2answers
41 views
Inline function Plan Properties in SQL Server 2008
Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
0
votes
0answers
42 views
How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres
We have a table X in postgres v11, where it has 5 columns like A B C D E.
We all columns are indexed and there are all integer values.
We have around, 100 milion records, but soon will turn into 800 ...
0
votes
0answers
13 views
MYSQL 5.7 Online DDL (Alter) Statement causing Lock_Wait_Timeout : AWS RDS
We are not able to do updated code publish from application on MySQL 5.7 as it contains ALTER statement on the table which has more than 50 million records and application team escalated that most of ...
1
vote
1answer
26 views
Mysql Query Optimisation For Joining a BETWEEN Date and Date query
I have some instagram API data sitting across two tables. One with post_data, and one with profile_data.
The profile_data table has a timestamp of when the account was checked through the API to get ...
0
votes
2answers
36 views
Why MySQL is ignoring my indexes in a query?
I have the following query in a production database server(master):
SELECT *
FROM Enlace_llamadas
where id_empresa IN (1,5,33,36,40,44,48,53,55,111,115,125,127,151,167,175,181,185,187,189,195,199,...
0
votes
0answers
28 views
Does keyset/cursor pagination scale query performance better than offset pagination?
I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow.
Naturally, I used offset pagination because the library I am using ...
7
votes
3answers
219 views
Long Running Query on Read-Only Replica that takes moments on the Primary
I've got a 4 node AG setup as follows:
VM Hardware Configuration of all nodes:
Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710)
16 vCPUs
356 GB RAM (long story to this one...)
max ...
0
votes
0answers
32 views
SQL Server query extremely slow. It fixes itself after rebooting [duplicate]
I have an SQL Server instance running on AWS RDS that is performing badly. A query that usually takes milliseconds to run, is taking minutes, causing my application to time out. This particular query ...
0
votes
2answers
52 views
PostgreSQL 10 Optimizing slow query performance
I have the following query:
select i.id as id,
i.ts_updated as updated,
i."name" as name,
cast(attr.data_norm as text) as data_norm,
cast(_59.json_agg as text) as _59, ...
1
vote
1answer
24 views
Is it possible to change between SELECT column based on an “IF” statement? and use INNER JOIN variables?
I have a table of 'mapped_products':
product_id | price
A table of 'product_id_to_name':
product_id | product_name
(please don't ask me why, this is an already built web-app i am working on)...
4
votes
1answer
259 views
Seeing 2 very different execution plans for an UPDATE between test & prod environments
I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the ...
2
votes
2answers
71 views
Why does a parameterised query lead to an index scan but hardcoded values leads to index seek? [duplicate]
I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.
It comes down to 2 child tasks in the ...
0
votes
1answer
52 views
Why a certain field causes a slow query?
I have 6 almost identical queries on a page. I am loading logs by keyname.
Lattitude and longitude take over 4s each, where as 4 other values take 0.09s How could just the one text value in where ...
3
votes
3answers
208 views
Query to categorize rows based on a “time” column without using a CASE expression
There is a ProductTT table as you can see below:
[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)
...which contains the following rows:
1 XX 0030
2 UY 0354
3 YY 0517
4 ZZ ...
1
vote
3answers
42 views
Detect one-sided matches
I have a SQL Server 2014 database table containing a code and two identifiers:
DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT)
INSERT INTO @Foo (code, id_a, id_b) VALUES
('A', 1, 2),
('B', 2, 1),
(...
5
votes
2answers
240 views
How to get SEEK accessing converted ID via view
Assume I have a table:
-- just for test purposes
CREATE TABLE SomeTable (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
,SomeColumn1 NVARCHAR(50) NULL
,...
7
votes
2answers
601 views
can anyone help me with this awful query plan?
The query:
SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11,...
1
vote
1answer
46 views
Query Slower after Rows Deleted
I have a Table that stored GPS data from vehicle trackers.
There are 8 vehicles tracked via an API. Each time the information is returned from the web server each of the vehicles has its information ...
1
vote
0answers
20 views
PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40
I've got following sample query from our Postgres DB:
SELECT * FROM advertised_car a0_
LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id
WHERE c1_.status = 'ACCEPTED'
ORDER BY a0_.id
LIMIT 4;
...
0
votes
4answers
83 views
How to delete large amount of data in sql server without data loss?
I've been dealing with million of data deletion in day to day process.
Basically I have 4 tables.
Table_A
Table_B
Table_C
Table_D
I'm attempting to delete data older than 10 days in all tables.
...
2
votes
2answers
117 views
Archiving process not running fast enough
I am archiving data from one database to another database on a different SQL server.We are archiving multiple tables in our database.Recently our inserts into the source database has increased and ...
0
votes
1answer
60 views
Job with a cursor is abnormally slow [closed]
I have a query that takes more than 9 hours to complete.
Looking at the execution plan, I find this high-cost operator (query plan link):
How can I improve this job?
0
votes
2answers
39 views
Index for VARCHAR column has low efficiency
I have two tables with relationship one to many.
create table locations
(
id bigint
auto_increment
primary key,
location_id varchar(128)
null,
constraint ...
2
votes
1answer
79 views
Improving performance of count where only result > 0 matters
I have following query:
select count(*) -- rp.tag
from submission
inner join post rp on submission.id = rp.submission_id
where user_name = ?1
and lower(rp.tag) like ('%verified%')
and not lower(rp....
1
vote
1answer
36 views
Optimize MySQL InnoDB query for SUM and Order By
I have an MySQL InnoDB table with 1.3M rows and 670MB size:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------...
10
votes
3answers
305 views
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
-2
votes
1answer
26 views
Azure SQL Server - big query choosing bad execution plan [closed]
I'm using Azure SQL Server v12 and I have big, complex query that runs ok but when I add more joins, it doesn't matter which table I'm joining to, it gets super slow. The important thing to point out ...
1
vote
1answer
50 views
Why are some count queries so slow?
I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour.
Explains are ...
2
votes
1answer
59 views
CTE Query to CONCAT text (over 10,000 rows)
I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.
Below is what I am working with:
CREATE TABLE #SampleTable
(
...
7
votes
2answers
910 views
On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?
we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to ...
6
votes
1answer
86 views
Understanding Plan Choice When Query Store Doesn't Respect Forced Plan
I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found ...
0
votes
0answers
15 views
Converting SalesForce IDs for Performance Reasons
I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so:
001n000000UELLJAA5
Where each digit is a base-62 value denoted by:
a-z ...
0
votes
2answers
42 views
How to index/optimize sum of two columns in PostgreSQL?
We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background ...
-3
votes
1answer
66 views
Setting Cardinality Estimator in SQL Server 2016 for performance & using old CE of SQL Server 2012
Post upgrade to SQL Server 2016, we saw a lot of I/O issues followed by slowness which affected the whole system.
Upon investigation, we found that top queries are delete statements.
Execution plans ...
3
votes
1answer
102 views
Direct values on predicate produce not-so-good plan
I'm using StackOverflow dump to run some tests.
In particular, I'm querying this table:
I have created this index:
I'm running the following query (just forcing an index to test alternatives)
I ...
1
vote
3answers
155 views
Estimate query speed for +1.5 billion rows mysql table
I'm working on audio fingerprinting problem where I need to
query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to ...
0
votes
1answer
28 views
Union of two queries taking 2.4 seconds I need it in ms
I need to fetch data from two different sources one is filtered by geography column and second is just simple table with few joins, this is a response to an api call which should be fast enough for ...
2
votes
1answer
86 views
SQL Server expensive nested loops join and lazy table spool
I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:
select distinct d.documentpath as path, d.documentname as name, d....
8
votes
3answers
191 views
How can I get running totals of recent rows faster?
I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
0
votes
1answer
33 views
Mysql Performance Issue with Group By/Order By
I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes ...
3
votes
1answer
220 views
SQL Server 2016 - excessive memory grant warning on poor performing query
I have a relatively large database of 550GB on a SQL Server 2016 EE instance which has a max memory limit of 112GB of the total 128GB RAM available to the OS. The database is at the latest ...
