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
-1
votes
2answers
164 views

How to implement GROUP BY aggregation with holistic aggregation if partitions do not fit into main memory?

Let's say I want to compute the median of some data given some grouping attributes G, e.g. the median price of all product sold daily (assuming a star-schema) SELECT T.day_id, MEDIAN(price) FROM ...
-1
votes
2answers
240 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
-1
votes
2answers
240 views

MySQL 8 - optimize all tables + indexes

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
-1
votes
1answer
92 views

How do you convert a cursor to CTE? T-SQL

In an effort to clarify I have added more information and removed the "simplified" code. I have table with datetime punch information. CREATE TABLE [dbo].[AT_Punches]( [PunchID] [int] ...
-1
votes
2answers
86 views

How to optimize for a 50million+ row table read with SELECT statement?

I have a large table (32 GB) with about 52 million rows. The table structure involves a few numerical and a few string columns, out which I select only one varchar column. I don't need the rest. ...
-1
votes
1answer
113 views

Help to optimize specific SQL query

I have WORKLOGTABLE which has index on RECORDKEY column. WORKLOG table has currently 1,200,000 records When I do the query select * from worklog where (recordkey= 'SR11034') It returns result very,...
-1
votes
1answer
492 views

Meaning of terms in SQL Server execution plan [closed]

I am finding difficulty in understanding the output of execution plan. Execution plan consist of terms like "Estimated sub tree cost", "Estimated I/O cost" etc. I can find the definition of those on ...
-1
votes
1answer
59 views

Long Running Query Optimization

I need to optimize this query as this query is taking minutes of time in execution. Below is the query. i'm giving date parameters and shift id is optional. as query is returning same in and out from ...
-1
votes
1answer
15 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 ...
-1
votes
1answer
265 views

How to optimize a slow select query with tuples in WHERE clause

I am currently trying to optimize a select query which takes 5-10 seconds on tables with 50k rows (and will grow 100x this soon). The purpose of this query is to find which tuples from a given set of (...
-1
votes
1answer
545 views

MySQL - Optimize a table which has mostly text data type

I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
-1
votes
3answers
191 views

Normalization Problem for drugstores information [closed]

I want to capture all data about 10000 drug stores in my country. I design as follows: Drugstore main info (uniqueid, hi_number, drugstorename, drugstoretype, drugstorerelateuniversity, servicetype, ...
-1
votes
1answer
87 views

Data nomalization and table column counts

I have a rather challenging requirement that I think I have designed correctly but am unsure as it violates a data normalization edict. I am acquiring data from sensors at with a frequency of about 2 ...
-1
votes
1answer
40 views

Optimising tables for ordering from a join

Hopefully this is simpler for someone here than me, I am struggling how to optimise database where a query orders from the result of a join on MySQL. Below: SELECT `n`.*, `c`.`author`, `c`.`...
-1
votes
1answer
560 views

Optimizing a query to avoid CXPacket wait type

I am selecting some columns from a table, then matching these columns' fields with another table to retrieve some data. The code(with the indexes on respective tables) is as follows: CREATE CLUSTERED ...
-1
votes
1answer
578 views

message table schema - body TEXT or varchar [closed]

I want to make a small social network. For messaging body which one is more suitable? VARCHAR or TEXT? if VARCHAR how long is efficient?
-1
votes
2answers
85 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
-1
votes
1answer
1k views

Aggregate function on subquery

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order: Offer -> Order -> Delivery -> Invoice. For each new row their ...
-1
votes
1answer
317 views

Simple “SELECT” query taking too long

I'm trying to run a SELECT query on our PostgreSQL database using pgAdmin 3. It looks like this: SELECT tstamp AS "time" dvalue AS "Weight" FROM tablename WHERE tstamp BETWEEN 'time1' ...
-1
votes
1answer
55 views

What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of ...
-1
votes
1answer
350 views

MariaDB query optimizer not using correct index

I got the following query and it was supposed to use index to speed up but it is using the wrong index image_likes and takes around 60 seconds to execute. If I add use index(image_user_id) then it ...
-1
votes
1answer
1k views

How can Mysql server auto re-index tables

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table. Ex : +-----------------------+--------+---------+------------...
-1
votes
1answer
170 views

SQL Server Parallelism Disabled

I came up with a strange issue where the performance of select queries was degraded after creating missing indexes on tables. Initially, it looks like all the queries are affected but when looked into ...
-1
votes
3answers
580 views

MySQL process takes up to 180% of the CPU (optimisation need)

I have a dedicated server with 128 GB of RAM and an 8 core CPU and 2TB hard drive. I have cPanel and MySQL installed in the same server. In this server I have 12 websites hosted including 5 with a ...
-1
votes
1answer
262 views

Optimizing sql query to search in more than 10 million record

I have a SELECT query to search a large table which contains more than 10 million records. I wish to optimize this query for reduce CPU load and get faster response: $query= 'SELECT DISTINCT ...
-1
votes
1answer
613 views

Union function in one function in asynchronous way with postgres

I have several function queries in postgresql. To avoid multiple resquest from the frontend to the server I has grouped all functions in a single funtion like: select * "function_name"() union ...
-1
votes
1answer
615 views

How to reduce the query time on large data table with self inner join in DB2?

I have a table called 'ASSETINFO' in DB2 .This table has 3 MILLION records. I want to fetch the records based on 'Asset Price Range' from the table, so I have applied Inner Join on the same table on ...
-1
votes
1answer
44 views

A test fixture database to experiment on performance tuning?

I would like to learn the basics of database performance tuning, and for myself I learn things faster if I can do hands-on to practise the learning materials. For the performance tuning, it seems ...
-1
votes
2answers
237 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT COUNT(*)...
-1
votes
1answer
48 views

60M+ InnoDB table simple query performance HELP

I know it is a repeated question but really need help here. What can I do to improve the performance of this query? Considering that I have no freedom to change system variables/configs :( Thanks in ...
-1
votes
1answer
101 views

Optimization of a select statement

I'm using MySQL and have a table user_data like this: user_id int(10) unsigned reg_date int(10) unsigned carrier char(1) The reg_data is the unix timestamp of the registration ...
-1
votes
2answers
165 views

Is it possible to optimize this query? Or any recommendations to speed it up?

After looking at my mysql slow log file I see that almost every entry there is related to this query: SELECT n.nid AS nid, AVG(v.value) AS rating FROM node n LEFT OUTER JOIN votingapi_vote ...
-1
votes
3answers
99 views

Looking to improve query performance

This is my query: select S-Num.Title, jobs.JobNum, jobs.JobPart, Part.PartNum, jobs.RevisionNum, jobs.Seq, jobs.Parent, Part.PartDescription, ...
-2
votes
1answer
64 views

Optimize query with nested inner query and case statements [closed]

I have below query written by someone targeting Microsoft SQL server 2017. I do not have access to database to create indexes etc., So only way I can do is optimize below query as efficient as ...
-2
votes
1answer
58 views

Do databases optimize random write and read operations? [closed]

Random write/read is slower than sequential for HDDs and some SSDs. Some database operations (UPDATE .. WHERE ..) may cause many random writes/reads. So I want to know if modern databases implement ...
-2
votes
1answer
30 views

Should I use the LIKE predicate or fetch all rows and process them on the client?

I am optimizing my application, while this task, I find one query which is fetching data from database using like . and database rows are around 10000. So is this a good way to use like in query or I ...
-2
votes
1answer
537 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
-2
votes
1answer
39 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
-3
votes
1answer
2k views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
-3
votes
1answer
12k views

OPTION(RECOMPILE) and OPTION(FAST n) [duplicate]

I was checking the performance of OPTION(RECOMPILE) and OPTION(FAST n) and their behaviors are surprisingly different. OPTION(FAST n) is much faster then OPTION(RECOMPILE). Does OPTION(FAST n)? create ...
-3
votes
1answer
124 views

SQL Server procedure optimisation

I need to optimize the procedure below (SQL Server 2008) and badly need help: I am thinking to reduce search space o Splitting the joins and create intermediate (smaller) results o Force the most ...
-3
votes
1answer
135 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
-3
votes
1answer
79 views

What actions or practices contribute to high CPU and RAM usage in SQL databases? [closed]

I'm curious about which common practices or actions contribute to spikes or high CPU usage and RAM usage for SQL databases, I am trying to estimate the resources I would allocate to a new MySQL ...
-3
votes
2answers
202 views

Are SQL Server Reader-Reader Deadlocks Possible?

We have heard of writer-writer and writer-reader deadlocks. Is it possible to create a reader-reader deadlock?
-3
votes
1answer
135 views

PostgreSQL - what do tms and mt mean in a SELECT query? [closed]

I'm trying to see if I can optimize the following query which was written by my predecessor: SELECT tms.*, 0 as kyori , (power( (139.745069 - to_number(tms.lng,'...
-3
votes
0answers
47 views

Doubt :: my sql performance tuning [duplicate]

Possible Duplicate: MySQL performance tuning I would like to optimize my MySQL DB and I identified some important points as follows: enabling query cache, using memory storage (I know pros and ...
-4
votes
2answers
2k views

How to improve TempDb on RAMDisk showing performance on par with physical hard drive [closed]

Given 2 SQL Server Instances where the second instance is configured with a RAMDisk for tempdb and the following test case. -- Create source data select top(1000000) * /* ~10 cols */ into #t1 from ...

1
33 34 35 36
37