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

learn more… | top users | synonyms

1
vote
1answer
36 views

Single table having large no of rows [on hold]

I am about to start a project and currently working on data design. While I am done in defining schema of db, I am just wondering to ask from experts. I have only one table in which I am going to ...
3
votes
0answers
31 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 ...
1
vote
0answers
11 views

NuoDB Statistics

I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB: SELECT * FROM LINEITEM SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 ...
1
vote
1answer
59 views

mysqld is terminated because the system is low on memory; what to do?

I did recently a site migration; during that I had to erase all plugins and the reinstalling only the minimum as the site was causing the server to crash (A Vps with 1gb of ram), the most problematic ...
4
votes
1answer
96 views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
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 ...
1
vote
1answer
51 views

Running select affects performance of stored procedure called from code

In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec ...
-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
votes
0answers
26 views

What is an efficient way of issuing a join query where the result from the 2nd table must be chosen from a set of rows for each row in the 1st table?

I'm modifying a question/answer forum component for a site and I want to create a page where each question is accompanied by it's top answer in a single panel. Here is an example (scroll down to the ...
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 - (...
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%. ...
20
votes
1answer
275 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
3
votes
1answer
55 views

PostgreSQL not using index scan when a second ORDER BY column is present

I need to query the latest entry from a table with >130mn entries using PostgreSQL 9.5. There are two columns of interest, row_id (integer, the primary key), and row_time (timestamp, not null, with ...
1
vote
0answers
17 views

postgresql 9.2 hash join issue

This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time. When should ...
2
votes
1answer
143 views

join to other table multiplied by loop foreign key in mysql

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases . table nodes +-------------+--...
1
vote
2answers
78 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
0
votes
0answers
18 views

Forked database doesn't use same query plan as original even after Vacuum Analyze

I forked a production database for some testing. I'm running a query select distinct store_id from <table>, where there is an index on "store_id" (the index was created via create index ...
1
vote
1answer
36 views

Order by varchar mysql

Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to ...
3
votes
1answer
54 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
3
votes
2answers
207 views

Adding 4th table to join jumps processing time from seconds to over 20 minutes

I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the ...
1
vote
1answer
77 views

How to change aggregate function without duplicating SQL (by using SQL)

In MS SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a ...
7
votes
3answers
51 views

Is it possible to have two copies of a table clustered in different arrangements seamlessly?

I have a table with around 100M rows. It only gets data inserted once/day but we need to do selects a lot. The selects are usually simple but need to return 100s of thousands of rows sometimes. It'...
0
votes
0answers
24 views

Calling stored procedure from ASP.NET MVC causes CREATE FUNCTION statements in Activity Monitor

Since updating to SQL Server 2016, some stored procedures have been behaving oddly. When run from SSMS they return results in one to two seconds. When run by an ASP.NET MVC web service they run ...
3
votes
1answer
71 views

Best practices, MySQL table with 150+ columns, many null

I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day). I'm certainly not an expert with databases and would appreciate some guidance ...
0
votes
1answer
36 views

Updating records from the past with MySQL query

I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago. E.g. let's say it's 1 May and report ...
15
votes
2answers
438 views

Why does Concatenation operator estimate fewer rows than its inputs?

In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs. However, an ...
0
votes
1answer
115 views

Speed up query that have joined tables and function calls

I have a Bootstrap Table that use server side pagination to display results from a query like this: SELECT Item.a, Item.b, Item.c, Info1.Name, Info1.Name, Info2.Name, ...
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
44 views

What happens when the buffer pool size is insufficient in mysql

Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
1
vote
1answer
85 views

Why is it faster to do four simple selects instead of one select with left joins?

I need to find a single value from a table A containing three foreign keys to two other tables B and C. For the sake of experiment, I tested two ways to query the value: Multiple queries: declare @...
0
votes
1answer
41 views

Does Oracle randomly switch execution plans to check if there is a better solution?

I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time. I don't talk about ...
1
vote
1answer
87 views

Troubleshooting extremely long running query (SQL Server 2012)

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've ...
3
votes
0answers
51 views

Do Large Hash Joins Contribute to Stolen Pages?

On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key. 100 GB is the max server memory setting on a 128 ...
1
vote
0answers
55 views

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
2
votes
1answer
37 views

Optimal query to show active records by date

I'm trying to determine what the optimal query might be for associating a date with a record that would have been active for that day. Here is a setup to demonstrate what I'm trying to do (demo at ...
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 ...
1
vote
2answers
116 views

Consideration for a little bit heavy database

Environment information OS : Windows Server 2012 R2 ( 64bit ) RAM : 16.00GB CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz SQL : Windows SQL Server 2012 Standard Brief Database Data Information ...
0
votes
1answer
88 views

What is difference between JOIN and simple query

I have two tables in database one is Orders and another one is customers Orders Table Contains : OrderID , CustomerID and OrderDate Customers Table Contains: CustomerID , CustomerName , ContactName ...
6
votes
2answers
184 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
0
votes
3answers
74 views

What is the performance impact of indexing a frequently updated column?

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer. I have a table with potentially a very large amount of inserts and updates. Any ...
4
votes
2answers
661 views

Is it possible to rebuild a dropped index?

I'm studying for the 70-462 exam and one of the training resources I'm using gives a scenario where an ETL process is taking a long time to complete an insert into a heap table due to non clustered ...
0
votes
0answers
64 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 (...
2
votes
2answers
64 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
0
votes
0answers
21 views

Example Database for tunning

I've been searching for so long and I've still have not found what I'm looking for ,is there any database or similar resource that people uses for tuning a database, or optimize queries?. I would be ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
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
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:...
5
votes
1answer
82 views

Indexes: integer vs string performance if the number of nodes in the index is the same

I hope this is the right place to ask! I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
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 ...