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)

4
votes
1answer
71 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
-1
votes
0answers
11 views

Further optimisation of sqlite

Currently I have been using C# and sqlite together. Of my knowledge I have done the index on those fields where I have the "where" query. In addition I have been reading of pragma settings. I am not ...
2
votes
0answers
35 views

Understanding Database Query Cost Optimzation

I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
0
votes
1answer
43 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...
1
vote
2answers
31 views

MySQL to use all cores 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64G ...
3
votes
0answers
83 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
0
votes
0answers
78 views

Query fast, but slow from procedure - or - why my new index is not being used? [closed]

In my wonderful job of tuning stored procedures, often times when I remove the query from inside the stored procedure it generates a very good plan, and when I put the same query wrapped inside a ...
0
votes
1answer
20 views

Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table

I have a query similar to this below. As you can see, there are multiple parts which are very similar. SELECT id, aaaa, bbbb, tags ,( SELECT COUNT(*) > 0 ...
-1
votes
1answer
66 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
vote
2answers
136 views

Efficient workflow/queries to save unique information?

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and ...
0
votes
1answer
75 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
0
votes
1answer
160 views

How to load entire database into memory?

I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory. When I do select count(*) from ...
4
votes
2answers
142 views

Execution plan percentages don't add up to 100% [closed]

I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. But the total of individual query costs adds up to more than 100%. ...
0
votes
2answers
62 views

MySQL Optimization Suggestions for EC2 T2 Small 2GB RAM

I'm trying to optimize our MySQL configuration for our server. The reason I believe that MySQL needs to be configured further is because my log has shown that our SELECT queries are taking a long time ...
4
votes
1answer
36 views

What is the best way to reload data in MongoDB

I am benchmarking MongoDB using YCSB framework using the latest drivers. During the benchmark, I change the cluster setup (adding/removing shards) extensively and delete/insert workloads in the order ...
-1
votes
1answer
16 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 ...
2
votes
0answers
52 views

Procedure runs slower over time; fixed by cache clear or reboot

We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
0
votes
1answer
47 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 ...
1
vote
1answer
49 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, ( SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p....
0
votes
1answer
22 views

MongoDB ixscan seems to be scanning more objects than expected

I'm using MongoDB 2.6.12 and a collection has the following index {a: 1, d: -1}. In case the types matter a is a Number and d is a Timestamp. From what I can see in the code we are limiting this ...
0
votes
0answers
24 views

MySQL: excessive memory usage

In our production system, MySQL service uses above 95 % of the available memory. System Info: OS: CentOS 6 RAM: 32 GB CPU: 4 core MySQL version: MySQL 5.5 Database Size: 158 GB my.cnf info: # ...
3
votes
0answers
156 views

Best procedure to measure query optimization effects?

I could not find valuable answers or explanations to the following question: How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. ...
3
votes
1answer
59 views

Increase in IO Wait after adding indexes with postgres

I've recently been put in charge of a system that has been fairly untended for a period of time. The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...
0
votes
0answers
46 views

Statistics shows CPU time = 0 ms, elapsed time = 0 on second time and query running blazing fast?

I tried to run this query on a database which is located on my network, but not on my own computer. set statistics time on SELECT id, status FROM MyTable EXEC MySproc @id='3000021' set statistics ...
3
votes
0answers
55 views

Slow Postgresql query with left join

I have a query on Postgres, and I also have added proper index. Is there anything missing here? SELECT orders.*, demo.name as d_name FROM orders LEFT JOIN users as demo ON demo.id = orders.dr_id ...
0
votes
0answers
65 views

how to find overlapping or duplicated indexes? when is it O.K. to keep them?

this is a script that does the following: create a database called "radha" for testing purposes, create the table ProductItemDetailsDenorm create the clustered primary key create ...
2
votes
0answers
100 views

Advice on how to improve this stored procedure

Edited: Added table column datatype I have 2 tables ReportItem id (int, primary key clustered index) report_item (nvarchar(100)) flow_name (nvarchar(50)) source (nvarchar(50)) Tech_Details id (...
0
votes
0answers
17 views

Optimal Number of Queries Per Batch for SQL Server

I am optimizing an application for a scenario where it will import an sql file and execute over 11 million create commands, which have have some expensive subqueries that are outside my control. This ...
3
votes
2answers
63 views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
3
votes
0answers
42 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
0
votes
0answers
81 views

SQL query too slow to fetch single row of data

we have this SP, running extremely slow to fetch a single row of data from tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete. We have tried to remove all ...
1
vote
0answers
24 views

optimize a window aggregate function

First, the query SELECT tc.numreg, com.numdep, tc.numcom, tc.numiris, sum(tc.conso_climat_reel)/sum(sum(tc.conso_climat_reel)) OVER (PARTITION BY tc.numreg) AS ratio, tc....
1
vote
2answers
53 views

How to improve performance of a query with an IN subquery

Get the count of other users having the x tag with order_id in order_id of users having the y tag table: MyCustomTable (200K rows) order_id user_id type ('x' and 'y') my custom tags and the query:...
1
vote
0answers
32 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
1
vote
1answer
39 views

are overlapping multi column statistics redundant?

I have run a database engine tuning advisor session, DTA, to optimize my database. The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example ...
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
0
votes
1answer
39 views

In what circumstances I am more likely to benefit from auto update stats async on?

I update statistics on a weekly basis using Ola Hallengren's solution. Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371. Databases With ...
0
votes
1answer
29 views

How to list the missing multicolumn statistics?

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases. It produced a list of recommendations, mostly multicolumn statistics. These ...
4
votes
2answers
216 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
2
votes
2answers
261 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
3
votes
2answers
209 views

IN Clause - performance difference between listing values and query

I changed this query SELECT ... FROM linkedServer.DB.Schema.Table1 t1 LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID WHERE t1.BRANCH_ID NOT IN ( '009991', '009992', '...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
6
votes
0answers
104 views

SQL Server Sharding strategy optimization

I have implemented a sharding solution for our SQL Server databases as per the MSDN documentation here: https://msdn.microsoft.com/en-gb/library/dn589797.aspx This has been running for around 6 ...
2
votes
0answers
30 views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
4
votes
1answer
36 views

Question about cost optimization formula for SELECT

I am a student in computer science and I have a question, which refers to cost optimization. I have to read the book (Fundamentals of Database Systems from Elmasry and Navathe) and try to present the ...
11
votes
2answers
171 views

Queries without Good Enough Plan Found

I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are: What are all ...
1
vote
2answers
39 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
1
vote
1answer
27 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
2
votes
1answer
65 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
1
vote
0answers
883 views

SQL server 2008 querying very large large tables

I've recently started a new job, the table row counts are huge compared to where I was before (11 billion rows in one of our main tables). I've been told there isn't time to update the statistics on ...