Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
0
votes
0answers
26 views
Slow PostgreSQL query - how to improve it?
[Beginners question :-) hopefully on-topic here.]
In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
0
votes
0answers
17 views
is there Better way to do this kind database schema design, mysql
P.S. Hopefully I am not posting in the wrong place. If I am, please let me know where to post.
I am currently using laravel with query builder instead of eloquent for a small self project.
I am ...
0
votes
2answers
29 views
mysql simple update with 40 million and 128GB RAM taking too much time
We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows.
and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
33 views
what is more important on a query execution plan, cardinality or selectivity?
The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
0
votes
2answers
17 views
How can one calculate percentage from previous week in aggregate query?
I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain.
Consider the following simple view, used to validate a cron I wrote that queries a subset of 200 000 ...
0
votes
0answers
25 views
Aggregate per Group returns Duplicates
I have the following scenario a user belongs to several Groups. The Groups are organized in a hierachy:
I want to aggregate all the Person Information of the Groups the User Belongs to. Eg. Person 1 ...
1
vote
2answers
52 views
Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN
I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
0
votes
1answer
27 views
Optimize very simple Postgresql query (and understand Query Plan)
I have a DB with many tables created using the following structure :
CREATE TABLE table1 (id serial primary key, col1 real, col2 real, ..., coln real)
with about 50 columns and 400k rows. The tables ...
9
votes
3answers
408 views
Sort spills to tempdb due to varchar(max)
On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables:
CREATE TABLE [dbo].[Settings](
[id] [...
0
votes
1answer
23 views
Why are bulk multi-column key queries so slow in MySQL?
(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance)
Consider the following schema:
Table T:
col0: the usual autoincrement primary key
col1: varchar
...
3
votes
1answer
106 views
Execution Plan is different when Constant Variable is Null
I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes,...
0
votes
0answers
19 views
PostgreSQL decrease performance after migration
After postgresql database server v9.3 migration to v9.6 I noticed a decreace in the performance of the entire system. The config parameters are the same as in v9.3 taking in to account the next ...
0
votes
3answers
67 views
Slow performance matching columns to lists of values
I've made a stored procedure to get all results which match specific students and objectives.
CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY
AS
SELECT ...
-2
votes
0answers
61 views
3 question about database. i hope any one help me. i will be so gratefuel [closed]
hello i hope everyone is fine.
may i ask for help please?
i am beginner in the programming filed. i try to build my own app and i got confused in the database so i hope anyone helps me i will be so ...
0
votes
1answer
34 views
What is the best way to cross check the record in multiple collections
I am developing the application in which system will show the campaign content to users which he/s never seen. To keep the logs either the specific user seen the specific content or not, i am ...
0
votes
1answer
25 views
How can I disable parallel queries in PostgreSQL?
I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is.
...
2
votes
2answers
39 views
Trouble Figuring out why My Query is so slow
I have a query that takes 15 seconds to get 350 results in a MySQL 5.6 Server and I am unable to diagnose why, I am still very new to database optimizing. U
The EXPLAIN visual does show some non-...
-2
votes
1answer
53 views
What difference between stored procedure and query in application [closed]
What difference between stored procedure and query in application.
Performance , network load, server load and speed
Thank you very much
0
votes
2answers
36 views
2018 of MySQL (InnoDB) CHAR vs VARCHAR
In 2018, does it still hold true that MySQL (InnoDB) CHAR performs better VARCHAR speed-wise?
I've only found a performance comparison explanation that is 7 year old. I've checked the official MySQL ...
2
votes
1answer
36 views
How can I optimize this MYSQL Script For many Records?
I have This script by Helping @lukstorms ,
-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
id int unsigned NOT NULL PRIMARY KEY,
words text NOT NULL
);
-- DROP TABLE ...
1
vote
0answers
25 views
How can I store data with unpredictable data types in a table?
I'm currently working on a Headless CMS project and was wondering what approach would be the best for storing "fields" data in the database.
Basically, users would be able to create a content model ...
1
vote
0answers
64 views
Query performance of searching pattern
Posted the same question on SO, but did not get answer.
As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front ...
0
votes
0answers
33 views
Execution Plan did not change
After alter function execution paln did not change.
Alter FUNCTION dbo.F_New
(
@i integer
)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
-- Convert with a deterministic style
RETURN ...
0
votes
1answer
69 views
incorrect Estimated Number of Rows vs Actual number of rows
I'm seeing a huge difference in the estimated number of rows vs actual number of rows. I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned ...
2
votes
1answer
412 views
Query speed optimization
I have several tables that I'm trying to combine with the below query. I'm using the following tables:
LoanOrigination: This contains loan characteristics such as asset value, loan term, etc. There ...
0
votes
0answers
19 views
Increase select query execution time in mysql database with 15 million data
I have a table in my database which contains about 15 million data. I have to repeatedly perform join operations in my application and while applying joins into the table (containing 15 million data ...
0
votes
0answers
29 views
Confused mysql index priority
Situation
I have the following query that finds 3 candidates whose total point are larger than a random number.
SELECT * FROM ( ...
-1
votes
1answer
58 views
Choice of index on date range column having selectivity around 20 % and joined with other table [closed]
select c.customer_name , c.customer_address, i.invoice_num, i.invoice_date,i.invoice_amount
from Customer c, ---100 k rows
Invoice i ---- 10 M rows
where c.cust_id=i.cust_id
AND c....
-1
votes
2answers
50 views
Slow Performance of SQL Server Database [closed]
I have a SQL Server Database, In two weeks in each four months many insert operation is executed to the database and in this time the database experience very slow performance, it takes a lot of time ...
4
votes
2answers
98 views
Optimizing table valued function SQL Server
I am trying to optimize this table-valued function. If I could, I would change it to the procedure but I cant. The problem is with two update statements. I kept only those two in the function because ...
0
votes
2answers
59 views
Which column to index for this query?
select DISTINCT id_request,username_request
from darkhast_follower
where darkhast_follower.id_request != '9762952594'
AND darkhast_follower.id_request != 'null'
AND darkhast_follower.status !=1
...
1
vote
1answer
55 views
Equivalent Join Conditions Yield Vastly Different Results
The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
0
votes
1answer
44 views
SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?
In SQLite's query planners is it generally faster to do this kind of thing?
SELECT foo.bar,foo.baz
FROM foo
WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha)
... or ...
SELECT ...
0
votes
1answer
26 views
Slow query with multiple group by
I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
0
votes
0answers
7 views
NHibernate second level cache collection hydration takes too long
I have enabled query caching and entity caching in NHibernate to store large collections of objects, needed for repetitive operations. While I have minimized the number of hits to db, the performance ...
5
votes
1answer
112 views
Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)
Given this data:
gid | val
1 | a
1 | a
1 | a
2 | b
3 | x
3 | y
3 | z
the following queries return groups (gid) that contain exactly one distinct value (val):
SELECT gid FROM t GROUP ...
2
votes
1answer
102 views
Correct way to group by month and year?
I need to group by month (and year) and I was thinking about:
GROUP BY CAST(YEAR(tDate) AS NVARCHAR(4)) + '-' + CAST(MONTH(tDate) AS NVARCHAR(2))
But I found in the web something like:
GROUP BY ...
2
votes
2answers
60 views
Measuring latency of a long-distance SQL connection
We have written a simple "client" app for a customer that connects to their SQLServer database, runs about 360 queries against a "property", and returns that data as a .Net object we call a "cProp".
...
2
votes
1answer
29 views
MySQL when using WHERE BETWEEN, query hits all partitions
This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:
`ID` int(11) NOT NULL,
`target_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
...
1
vote
1answer
74 views
Best way to speed up requests using date columns
Our database is receiving the following requests:
select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)
This is a normal request that is made by our application. On ...
0
votes
1answer
37 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 ...
0
votes
1answer
30 views
MySQL under what scenario suitable to use WHERE EXISTS
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.
select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
from `table_2`
...
4
votes
1answer
55 views
Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?
I have the following table in Postgres 9.6:
Table "public.TagNotifications"
Column | Type | Modifiers ...
1
vote
1answer
74 views
Improve Performance on GROUP BY - large table PostgreSQL
I have a Postgres table containing the following three columns:
origin (INT, b tree index)
destination (INT, b tree index)
minutes (SMALLINT, b tree index)
The table has ~ 1.4 billion rows. Each row ...
-1
votes
0answers
26 views
Query time breakdown (parsing, “sub-fetching”, regular fetching)
Is there a way to distinguish the time a query spends on each of its execution stages (widely understood)?
My questions arises from the fact that I test two methods of data retrieval:
view vs. ...
0
votes
1answer
45 views
BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT
This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table:
| column name | type |
|----------------+-------------|
| id ...
0
votes
1answer
50 views
Loading 400 Million Records into MySQL Hangs on “Sending Data”
I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
3
votes
0answers
98 views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
1
vote
1answer
76 views
MySQL DB performance is degrading on connecting using multiple clients
I am facing weird behavior in our MySQL DB and need your expert guidance on this.
We have a query (simple select without join, usages index as well) which is executes in 5.8 seconds However, when we ...
1
vote
1answer
76 views
Mongo queries are slow and yielding/waiting for locks thousand times
We are using mongo setup with replica sets on AWS.
Setup Details:
1 Primary node: r3.8xlarge
4 secondary nodes: r5.xlarge
Mongo version: 3.0.8 (WiredTiger)
Database size: 358GB
We have configured ...
