Use this tag for questions about improving the performance and/or efficiency of database queries.
1
vote
2answers
30 views
DB select query performance when queried by partial key of primary key [on hold]
My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, ...
3
votes
1answer
44 views
Best database design for website ranking
I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 ...
3
votes
1answer
32 views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers | ...
6
votes
1answer
247 views
Query is slow 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 SQL ...
3
votes
1answer
22 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). ...
2
votes
1answer
58 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 | ...
3
votes
2answers
85 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
36 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 ...
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 ...
0
votes
1answer
53 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, ...
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 ...
4
votes
1answer
122 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 ...
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 ...
0
votes
0answers
48 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
...
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, ...
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, ...
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 ...
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 ...
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, ...
1
vote
1answer
46 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
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 ...
2
votes
1answer
44 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 ...
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
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
25 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 ...
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 ...
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
78 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
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` ...
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, ...
1
vote
1answer
39 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 ...
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
245 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
58 views
Any SQL where clause performance similar to where 1=0
(i'm using SQL Server 2008R2 or later)
For centralize manage and consistent, there is a view definition as follow:
CREATE VIEW view_all_situation
(
select null as src, tf.col1, sum(col2) as fee, ...
3
votes
0answers
43 views
Efficiently computing aggregate functions over subqueries with incremental data
I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
1
vote
0answers
47 views
Tuning MySQL MyISAM for large joined updates
I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
1
vote
1answer
48 views
Speed up COUNT(*) when using an index
I have a table similar to this (simplified):
CREATE TABLE books (
id INT AUTO_INCREMENT,
category INT NOT NULL,
PRIMARY KEY (id),
KEY (category)
);
This table has over 10,000,000 ...
3
votes
2answers
97 views
Use a view to join tables or a stored procedure for a better execution plan?
First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.
What I want to know is for a stored procedure which the ...
1
vote
1answer
52 views
MySQL Query with good execution plan is very slow due to Sending data operation
I have the following query:
SELECT
a.borrowerId AS borrower_id,
a.created,
NULL AS initiator_team_id,
NULL AS initiator_user_id,
NULL AS old_team_id,
NULL AS old_user_id,
...
3
votes
1answer
77 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 ...
2
votes
4answers
174 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
2answers
76 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" ,
...
4
votes
1answer
49 views
Fast alternative for “NOT IN”
I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key.
Now I want to get all rows from ...
2
votes
1answer
58 views
Row with latest timestamp
How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)?
My app records data ...
0
votes
1answer
74 views
how to minimize the data scanned on my database using index?
Is there anyone how to minimize the data scanned on my database??
please show me.. using index?
query
SELECT
IF (e.channel LIKE 'IAX2%', e.calldate,
IF(d.channel LIKE ...
1
vote
1answer
32 views
Calling a View from Procedure
I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters.
Ex:
CASE pSearchType
WHEN 1 THEN
SELECT Col1, ...
1
vote
1answer
38 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 ...
