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

learn more… | top users | synonyms

1
vote
1answer
74 views

How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

I have successfully added several calculated fields to different tables, for example: ALTER TABLE dbo.tblBGiftVoucherItem ADD isUsGift AS CAST( ( ISNULL( CASE WHEN ...
3
votes
2answers
88 views

Optmizing ORDER BY

I'm new to MySQL and have written the following query (see below). PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes ...
1
vote
1answer
59 views

Optimization of complicated sums and counts in related tables

I have a categories table, which have two columns, id and name. I have a types table, which have three columns, id, category_id and points. I have a products table, which have three columns, id, ...
1
vote
3answers
63 views

MySQL: Why is this UPDATE query so incredibly slow?

So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts ...
2
votes
1answer
27 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: ...
2
votes
1answer
126 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS ...
0
votes
0answers
38 views

Non-structured Query performs better than on clustered, hash-clustered and indexed?

I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join ...
1
vote
1answer
49 views

Can't eliminate index scan

I have a query, and even using SQL Sentry, I can't eliminate the index scan. This is the query: SELECT TOP 30 codCliente FROM ( SELECT t1.CodCliente, codcampo, valor, t1.chavealeat ...
2
votes
1answer
37 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
7
votes
1answer
525 views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
0
votes
0answers
32 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, ...
9
votes
1answer
263 views

Recursive sql query performance issue [closed]

This is the sixth time I'm trying to ask this question and it's the shortest one too. All the previous attempts were resulting with something more similar to a blog post rather than the question ...
2
votes
1answer
36 views

Better way to update table with value from another table

I'm trying to rewrite the following query to make it more efficient: update c set c.cartype = (select Id from carTypes where carType = 'Coupe') from cars c inner join doorcount dc on c.Id = ...
1
vote
1answer
70 views

Optimize slow query

I have this query that takes over half an hour to run on our Linux MySQL db... SELECT ls.emailaddress , lse.subscriberid , lse.eventtype , count(lse.eventtype) as total FROM ...
2
votes
1answer
94 views

Computational power required for this query

I am not sure if the query I am trying to run is unreasonably large, in terms of computational power. The query will identify islands (and their duration) of a certain condition in my db. Things work ...
2
votes
1answer
723 views

Help required with query (join) optimization

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause ...
1
vote
3answers
97 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ...
-1
votes
1answer
55 views

How can I return the different prices of my quantities? [duplicate]

I have two tables, one containing all the quantities of products by country and another table that contains the prices by country. When I add a condition for example country = "ch", I have the price ...
0
votes
0answers
67 views

Design database table for property listing

I want to design a database for a listing website, this is the first time I design the database, so I am not sure if I in the correct path. Here is the fields that needed in a listing, title - ...
2
votes
1answer
879 views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below, to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
2
votes
0answers
39 views

Composite index works for larger set of data, but not smaller

This is a follow on question to optimise my.ini for key query Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
148 views

Convert UNION query to JOIN. Is it actually needed for high performance?

I have the following table structure in MySql. To get the list of roles for a particular user, I use the following union Query, SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID FROM ...
2
votes
1answer
98 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
1
vote
1answer
298 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
2
votes
1answer
107 views

Stored procedure suddenly very slow, updating statistics not helping

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have ...
1
vote
1answer
43 views

optimise my.ini for key query

I have a database system with a fairly complex dashboard query. First of all the details. Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT ...
0
votes
0answers
37 views

What can I do to make this mysql script run faster (or run)?

I have a user table ldap_karen that has all employee data including sup_id (supervisor id) and the employee's job role. I also I have a supervisor table that has all the unique sup_ids. Currently I ...
40
votes
5answers
2k views

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
12
votes
4answers
2k views

UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to ...
2
votes
2answers
267 views

PostgreSQL query very slow when subquery added

I have a relatively simple query on a table with 1.5M rows: SELECT mtid FROM publication WHERE mtid IN (9762715) OR last_modifier=21321 LIMIT 5000; EXPLAIN ANALYZE output: Limit ...
1
vote
2answers
52 views

DB select query performance when queried by partial key of primary key [closed]

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, ...
4
votes
1answer
154 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
86 views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers | ...
7
votes
1answer
1k 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
79 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
71 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
99 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(), ...
0
votes
1answer
66 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
29 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
58 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
293 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
571 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
52 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
55 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
140 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
76 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
300 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 ...
6
votes
3answers
283 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
65 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
75 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', ...