Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
0
votes
1answer
13 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
0
votes
1answer
486 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow ...
0
votes
1answer
248 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
2answers
15 views

Join on large table Myisam & Mysql 5.0

I searched on some forums, perconna and more but i didn't find answers. We have a legacy server with Mysql 5.0 and some tables on Myisam. I'm trying to optimize my request. We have 3 tables mysql&...
0
votes
0answers
14 views

Save multiple dynamic filter for aggregation in Postgresql

What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data? Lets assume I have a table called t_filter which ...
0
votes
1answer
23 views

Querying an enormous table (~150 million rows) with multiple joins - SQL Server

First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
1
vote
1answer
25 views

optimizing Postgresql query with left join and indexes

I am trying to run the following query but it is taking forever to execute: Select Distinct ON (S1.sql_id) S1.msgdata as replymg, S1.time as replytme, S2.receiver, S2.time,...
8
votes
2answers
41k views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
0
votes
0answers
15 views

getting all files into one database [closed]

so i have got around 50 acces files wich contain some data that data has exactly the same structure i mean tables are the same i just posted the images to show they are the same however they all ...
1
vote
0answers
20 views

MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`

In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join ...
0
votes
1answer
30 views

Method for Finding Top Record with WHERE conditions in Child Tables

Setup Recently I worked on an optimization for a SQL Query which returned the Prescription Product most recently used by a Patient. The original query handled this via 3 nested CTE's and had really ...
1
vote
2answers
91 views

How can I optimize and speed up a query [closed]

Below query never completes and runs for over 14 hours: SELECT U.Age, U.CreationDate, U.DisplayName, P.AcceptedAnswerId, P.ClosedDate, p .Title, P.ViewCount, C.CreationDate,C.Score ,C.UserId FROM ...
0
votes
3answers
65 views

Why does SQL Server not use the Index it recommends?

The query I had a specific query I wanted to optimize: SELECT * /*12 columns*/ FROM [dbo].[EnterpriseGroup] WHERE (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234) AND StatusCd &...
1
vote
2answers
28 views

A SQL query optimzation issue with ORDER BY

An SQL query optimzation issue with ORDER BY Current status I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
0
votes
0answers
15 views

Condition filtering estimate calculating double dipped extra value without performing range reduction

In a query like below select e.emp_no, concat(e.first_name, ' ', e.last_name), d.dept_name, t.title_name, es.salary, es.insurance, es.pf, ea.city, ea.state, ea.phone from ...
0
votes
2answers
74 views

T-SQL Query or DB Engine Optimization? [closed]

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
1
vote
1answer
14 views

Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database ...
0
votes
1answer
26 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
1
vote
0answers
27 views

How did we get condition filtering value of 5 percent in this plan instead of 10 percent? MySQL 8.0

Assume the below schema, this is just created to test some features, and not a valid design for production. When I run the below query and check it's optimizer trace, I find lot of plans. select e....
2
votes
1answer
37 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
0
votes
1answer
24 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
-1
votes
1answer
13 views

Find closest follower in a rank list

On my database I have the table named Rank_List_T: Rank Name Time 1 John 110 2 David 120 3 Bob 121 4 Hans 124 5 Claus 126 6 Gustav 140 I am interested in the person, who ...
0
votes
2answers
55 views

Why does table SELECT become twice slower after restoring a db from 5.0.24 to 8.0.20?

I've got a new server so i had to configure MySQL on it, the old server had installed MySQL 5.0.24 so i've done backup of my DBs and restored them on newly installed MySQL 8.0.20 on the new server.. ...
1
vote
1answer
29 views

Slow Database with concurrent queries, Table cache hitrate 0% [Newbie! Need help :)]

I'm a bit of a newbie when it comes to dba work so please take it easy! I have gone through countless threads trying to improve the performance of my database, looking for someone who could give me ...
1
vote
0answers
46 views

Properties of grouping in relational algebra

The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically ...
0
votes
2answers
57 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
0
votes
1answer
17 views

Index dive/sampling row count very inaccurate in MySQL

The table emp_address_phone is filled with many random values. CREATE TABLE `emp_address_phone` ( `address_no` int(11) NOT NULL, `emp_no` int(11) DEFAULT NULL, `street1` varchar(255), `...
0
votes
0answers
56 views

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where ...
0
votes
1answer
456 views

How to reduce redundant time-series data in MySQL into a function?

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this: How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but ...
0
votes
1answer
44 views

Schema Design - would you split this table?

Table A - auto-incrementing integer primary key - 3 dates - 17 booleans - 7 varchars (less than 40 bytes in these combined varchars in 99% of rows) - 5 foreign keys Table B - auto-incrementing ...
0
votes
1answer
100 views

MYSQL Select query stuck at “Sending data”

I have an select query with about 1M records, I'm working on Magento 1.9 database. SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4), mt....
0
votes
2answers
141 views

Efficient Value Update with Large Database

I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive ...
4
votes
1answer
3k views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
0
votes
1answer
33 views

problem with optimizing SQL query

I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query. Here are the relations: CREATE ...
3
votes
4answers
2k views

Way To Speed Up Query

My query returns roughly 590 rows and 8 columns. The issue that I have is that from start to finish the query takes 2 minutes 30 seconds to complete. The great group of people on here have taught me ...
15
votes
3answers
2k views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT o....
0
votes
1answer
64 views

Need sample script for Cluster index scan

Do we have any example which is behaving as cluster index scan when ever queried with range of values. I am trying to get the conclusion on optimizer behavior as when ever the select count increases ...
0
votes
0answers
50 views

How to count references to fields given the definitions of views?

I have a bootstrapping script which defines views to be exposed to the data users in public schema. It filters out sensitive fields from the tables in private schema, also makes various aggregations. ...
0
votes
2answers
439 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
1
vote
1answer
86 views

Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

I am building a database in PostgreSQL for financial data, where the table looks like this: create table fin_data( brokerid text, stock int, holding bigint, stake float, value ...
1
vote
1answer
37 views

Does “Table Valued Function” force the plan to get into cache?

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
30 views

UNION ALL vs CASE - SQL query optimization

I need to retrieve the count of both approved and unapproved data from my_table. And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion DDL Query ...
0
votes
0answers
14 views

do statistics need to be calculated each time or stored?

I have a "user" table in a video-game, which have these properties: Money Experience Number of friends Each of these properties can be either stored in the table, and updated when something happen (...
0
votes
0answers
55 views

Calculating historical data over date ranges for up to 1 million rows

I need to be able to calculate the average sales of employees over a period of time. At the moment, I am doing this by using MySQL to get the total sales per employee per day: SELECT ANY_VALUE(...
0
votes
1answer
39 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
6
votes
2answers
7k views

Can I optimize this MERGE statement?

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows. In my development environment, the query ...
1
vote
1answer
84 views

Slow query time after upgrade to MySQL 8.0.20

I apologize in advance if a similar question has been asked. I've been digging through Google and StackExchange trying to find an answer but now I'm stuck and stumped. I am still relatively new in ...
0
votes
1answer
99 views

Optimize Stored Procedure

I am not an extremely experienced SQL Dev. I am worried that some of the Stored Procedures I have created for my database aren't exactly optimized, and may not be all efficient when they are running. ...
6
votes
1answer
1k views

Stored procedure with recompile

I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params ...

1
2 3 4 5
37