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

learn more… | top users | synonyms

1
vote
2answers
185 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 ...
1
vote
2answers
29 views

How can one identify if FK's needed to be indexed apart from the NC indexes already residing

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done. I have a similar env where there ...
1
vote
1answer
44 views

Single table having large no of rows [closed]

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 ...
2
votes
0answers
37 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
13 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 ...
19
votes
2answers
11k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
1
vote
1answer
276 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 = ...
1
vote
2answers
85 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 ...
4
votes
1answer
115 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 ...
1
vote
1answer
52 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 ...
3
votes
3answers
68 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
-1
votes
1answer
70 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 ...
2
votes
1answer
145 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 +-------------+--...
4
votes
2answers
143 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
317 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 ...
0
votes
1answer
76 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 - (...
3
votes
1answer
59 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
19 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 ...
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
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 ...
3
votes
2answers
210 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
37 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
62 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
1answer
79 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
117 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, ...
1
vote
1answer
81 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 ...
0
votes
0answers
31 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 ...
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 ...
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 ...
2
votes
1answer
38 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 ...
15
votes
2answers
446 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 ...
2
votes
0answers
53 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, ...
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 @...
1
vote
0answers
58 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 | ...
0
votes
1answer
46 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 ...
0
votes
3answers
79 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 ...
0
votes
1answer
45 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 ...
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 ...
20
votes
1answer
678 views

Seek and you shall Scan… on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a "...
0
votes
0answers
54 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 ...
2
votes
0answers
101 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 (...
1
vote
2answers
117 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
128 views

Slow response from SELECT query

I have 50 lac rows (i.e. 5 million rows) in student table. I fire select * from student where s_name like('%something word%') limit 0,5. I define indexing on s_name column. That query takes 2 ...
0
votes
1answer
89 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
185 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
0answers
67 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
2answers
84 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 ...
0
votes
0answers
91 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
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). ...