Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

3
votes
1answer
49 views

Slow query in SQL Server 2014 fast in SQL Server 2012

During the migration of one of our databases from SQL Server 2012(SP1, CU2) to SQL Server 2014 (SP1) we have experienced some strange issues: One of the queries that completes within seconds on ...
2
votes
1answer
16 views

Storing prices with price history and different specificity (global, user group, single user) in MySQL database

I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
-1
votes
0answers
113 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. Without the create table statement the query is giving results. For Create table also if ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
2
votes
1answer
48 views

GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

Tables: content tag_map extrainfo |id|title| |id|tagid| |id|lat|lng| |--|-----| |--|-----| |--|---|---| |1 |Lorem| |1 |8 | |3 |0,4|0,1| |2 |Ipsum| |2 |9 | ...
2
votes
1answer
117 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not ...
3
votes
2answers
84 views

Can I improve the performance of this MySQL query by adding an index?

I ran SET GLOBAL slow_query_log = 'ON'; in MySQL and this appeared in the log: SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), ...
-1
votes
0answers
35 views

How can I take table as input dynamically from user?

I am new to SQL server and want to take table name and column names as input from user dynamically. Also, need to perform cursor implementation of comparing records on it. I have completed the ...
1
vote
1answer
60 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
1answer
20 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
0
votes
1answer
26 views

Record scan speed, column orientated databases and SSDs

I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated ...
16
votes
2answers
20k views

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
1
vote
2answers
73 views

Slow SQL query with LEFT JOIN

What my query is doing: I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current ...
0
votes
1answer
52 views

How to improve this query so it'll run quicker

I've got this query: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m, network_devices n, ...
4
votes
1answer
34 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
0
votes
1answer
47 views

Artificially increasing query time

Very weird question: Is there any way to artificially inflate the amount of time a query will take? An infinite loop would be great. SELECT CLAIM.UNIT_PRICE FROM CLAIM WHERE ...
4
votes
1answer
120 views

Finding distinct rows across two tables: Full Outer Join more efficient than Union?

When finding distinct rows across two tables where we can't necessarily ensure are pre-sorted, is it a good idea to use a FULL OUTER JOIN rather than a UNION? Are there any downsides to this ...
5
votes
3answers
71 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
0
votes
0answers
36 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
0
votes
0answers
47 views

Mysql query performance is really bad

I have a very simple query in MySQL which is taking hell lot of time. It ran for 18 hours and didn't complete Update OrderDetail OD Join TEMP_PARTMASTER_LATEST_UPDATES PM Using ...
2
votes
1answer
137 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
2
votes
1answer
43 views

how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ...
1
vote
0answers
27 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
8
votes
1answer
419 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
2
votes
2answers
42 views

Finding differences between two tables takes too long

I'm trying to find the diff of two tables using a left join. My query is as follows: CREATE TABLE my_diff (INDEX my_index (name, type)) SELECT AA.name, AA.type FROM AA LEFT JOIN BB USING (name, ...
3
votes
1answer
112 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
0
votes
1answer
27 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
1
vote
2answers
47 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
1
vote
1answer
45 views

Slow MySQL query when the IN list is long

I have 3 tables, namely: product, product_has_category, category: CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `des` text COMMENT 'description', ...
1
vote
0answers
201 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
5
votes
2answers
420 views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
1
vote
1answer
41 views

Is it more efficient to union from sub-query or combine two outer queries?

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked. I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
1
vote
1answer
24 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
1
vote
1answer
152 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
0
votes
0answers
41 views

Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but ...
2
votes
1answer
116 views

why this session in particular is suspended? Please see the details

My understanding regarding the suspended status of a session: A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource ...
2
votes
2answers
65 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
1answer
56 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
3
votes
0answers
76 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
2
votes
2answers
206 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
4
votes
1answer
86 views

Performance problems with TOP and user defined functions in views

I have problem with performance of queries on simple query on view. To keep things simple, assume my view is defined as follows: CREATE VIEW [mon].[ViewDevicesWithGroups] AS SELECT Id, Name, ...
3
votes
1answer
75 views

Problem with clustered index? Or bad indexes? (SQL Server 2008 R2)

I have this query (it was running in 2 minutes). With new indexes, it takes 40 sec: This is the execution plan: [![100% use][1]][1] [![it's not heavy][2]][2] [![not at all][3]][3] I can't ...
1
vote
1answer
38 views

Monitoring SQL Server 2012 slow queries/queries which are causing blocking

I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the ...
1
vote
1answer
134 views

Complicated data

I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, ...
2
votes
4answers
170 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
0
votes
0answers
33 views

How should massive effect update queries and scripts be performed?

I have some MongoDB databases in production (at a Mongolab AWS deployment) for which I've sometimes required to apply queries or scripts effecting on whole collections. As collections got over 20k ...
-1
votes
2answers
55 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 ...
2
votes
2answers
244 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
150 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...