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

learn more… | top users | synonyms (3)

2
votes
1answer
59 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
0answers
11 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
12
votes
2answers
1k views

Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ...
0
votes
2answers
97 views

How to improve perfomance or Query Execution Time in MySQL?

This is my query: SELECT a.z_companyid_pk "z_companyid_pk" , a.company_name "Client" , a.display_name "Display_Name" , ...
1
vote
0answers
47 views

Oracle tuning strategy for columns with long tails

We have an Oracle 11g database, where the two biggest tables are Customer and Order. The order profile of our customers has a long tail: we have millions of customers, with most only having a small ...
3
votes
1answer
96 views

MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

This is my table definition, without any special index for now: CREATE TABLE `filter` ( `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `category_id` varchar(32) COLLATE ...
3
votes
2answers
226 views

SQL server tools to identify query optimisations and missing indexes [closed]

I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL ...
1
vote
1answer
82 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
2
votes
0answers
116 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
0answers
192 views

optimizing paging in a stored procedure

I have the following SQL command which is built inside a stored procedure according to input parameters: SELECT DISTINCT * FROM ( SELECT DISTINCT TOP @displayLength * FROM ( ...
-3
votes
1answer
50 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 - ...
1
vote
0answers
18 views

Optiming ACT_RU_JOB Query

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there ...
0
votes
1answer
65 views

NC Index not being used [closed]

I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it). I'm wanting to count how many records meet the following criteria: ...
0
votes
2answers
129 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...
7
votes
2answers
2k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
8
votes
1answer
169 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
2
votes
1answer
80 views

MySQL Filesort/Order By Slow

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
5
votes
1answer
4k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
5
votes
1answer
124 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
0
votes
0answers
31 views

Optimization for query n increase performance

I am executing the following query: select count(inspection_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and inspection_result= 1 and part_type = "39000048" and ...
1
vote
1answer
99 views

Dynamic Partition Elimination when using “OR ” or “IN”

Say I have a query like this: SET @ContainerId = 5 SELECT * FROM Container.Container_Partitioned WHERE containerId = @ContainerId When I run this, I get a query plan with Actual Partition ...
0
votes
2answers
409 views

How can i update my database from another database using temporary tables?

I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it? This my temporary table : use data_pro; ...
3
votes
0answers
101 views

Why the stored procedure is much slower than a temporary table? [closed]

I want to do the updating of my database every day and I will like to use stored procedures but the time of execution of the temporary table is faster than the stored procedure. How can I optimize my ...
0
votes
0answers
192 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
2
votes
2answers
211 views

SQL Server 2008 R2 Memory Pressure

My SQL Server has a weird problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used dbcc memorystatus to check the problem. ...
1
vote
1answer
277 views

Why is my stored procedure very much slower than the same MYSQL query? [duplicate]

My stored procedure is very slow compared to a normal query or a temporary table. How can I optimize my stored procedure? CREATE DEFINER=`datasll`@`...` PROCEDURE `product`() BEGIN EXPLAIN ...
12
votes
1answer
361 views

Why does referencing a variable in a join predicate force nested loops?

I came across this issue recently and couldn't find any discussion of it online. The query below DECLARE @S VARCHAR(1) = ''; WITH T AS (SELECT name + @S AS name2, * ...
9
votes
2answers
174 views

Why isn't the primary (clustered) key being used in this query?

I have a SQL Server 2008 R2 table whose schema structure looks as follows: CREATE TABLE [dbo].[CDSIM_BE] ( [ID] [bigint] NOT NULL, [EquipmentID] [varchar](50) NOT NULL, [SerialNumber] ...
3
votes
1answer
98 views

Improve performance on SQL Server Query

I'm trying to increase the performance of a query and I'm out of ideas: The query does this: gets a 'Process' with specific priorities on selection namely: Picks up a process that has been waiting ...
6
votes
2answers
396 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
0answers
79 views

Need your help in query tuning

I read ​one article which shows how to use different aggregations effectively. http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx I am ...
2
votes
1answer
106 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
1
vote
0answers
290 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
3
votes
1answer
969 views

Mongodb high lock percentage / slow queries

I'm working with an application using MongoDB that is getting a fairly large number of incoming requests and I've noticed that the responses are getting slower as the number of requests get higher. ...
1
vote
2answers
83 views

Is there's another function to optimize this query in MySQL?

I want to use a function that returns for me the same result in one line is that's possible?? MIN( CASE WHEN (data.pro LIKE '1 %' ) THEN 1 WHEN (data.pro LIKE '2 %' ) THEN 2 ...
2
votes
1answer
89 views

How to optimize this MySQL query?

Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query SELECT ...
1
vote
2answers
67 views

Is LIMIT processed before or after retrieving data?

MySQL. My heavy query crashed our MySQL server. If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries). Which way LIMIT are processed: before the rest of ...
3
votes
3answers
279 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
0
votes
0answers
35 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
1
vote
0answers
404 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
1
vote
2answers
87 views

Optimize table by removing large field

I have a MySQL table of articles. It's growing rapidly, and over 50% of the size on disk is accounted for by the article body field, as you might expect. There are no indexes on the body, but 8/16 ...
0
votes
2answers
42 views

Which query performs better when searching a row where a column has maximum?

Consider a simple table for example purposes: person(name varchar, age int) I have a task to retrieve the oldest person. Which query is better in general for such problems? Query 1: criteria with ...
0
votes
1answer
35 views

optimizing mysql query using explain

I'm trying to optimize a query for a client; I'll try to describe what I'm going for at a high level before diving into the structure. The client database has a table of transactions (currently ~352k ...
1
vote
4answers
1k views

MSSQL Select all parent rows for which exists a combination of children rows with desired value

I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone ...
1
vote
1answer
65 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 ...
2
votes
2answers
290 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
1
vote
1answer
97 views

Optimizing a query that performs worse on a smaller dataset

I am trying to run this query. The * is just for simplification, I'm selecting ~10 specific fields for some calculations. SELECT * FROM `orders` LEFT JOIN `orders_processed` ON ...
0
votes
0answers
28 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create ...
12
votes
2answers
3k views

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
2
votes
2answers
596 views

Slow Performance caused by Correlated Sub-queries & Clustered Index Seek

I'm running a query in which one of the tables (FIELDDATA) has nearly 400,000 records which is taking more than 2 minutes to execute. I ran the execution plan, and noticed that the clustered seek ...