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

learn more… | top users | synonyms

2
votes
2answers
89 views

Is 3 minutes to join a 50 million table too much?

I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not. Basically, I need to get a count of old and invalid entries per group, which ...
0
votes
1answer
26 views

Random performance of stored procedure across databases

I have a stored procedure that is installed on 30 databases across 4 servers. We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to ...
0
votes
1answer
41 views

Can I get an equivalent query for this Not in Query

SELECT ID FROM dbo.SomeTable WHERE ID not in ( SELECT MIN(ID) FROM dbo.SomeTable GROUP BY SystemNo ) As this query ...
1
vote
0answers
35 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
0
votes
2answers
60 views

Same query with less data takes more time [on hold]

I have a same query to find MIN(download_id) but different data ranges in where clause. The criteria which has more number of rows takes less time but query result which has relatively less number of ...
0
votes
1answer
21 views

Why does a query on 4 times more data take 300 times more processing?

To score the price preference of our individual customers in, we run the below query. Basically it is a weighted average of a product score. The score depends on the product group and the packaging ...
0
votes
0answers
18 views

query cost in postgresql using explain

I read the following link and try to understand the output. https://www.postgresql.org/docs/9.1/static/sql-explain.html EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; ...
0
votes
0answers
27 views

Execution time for CONTAINSTABLE/WHERE query

I am facing an issue for a seemingly trivial database query on SQL Server 2012 accessing a full-text index: SELECT MyTable.ID FROM MyTable INNER JOIN ( SELECT [KEY] FROM CONTAINSTABLE(MyTable,...
0
votes
1answer
34 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
3
votes
3answers
261 views

Long running delete query

I have a table with roughly 11 million rows, defined as: CREATE TABLE [sko].[stage_närvaro]( [datum_fakta] [datetime] NULL, [person_id] nvarchar NULL, [läsår_fakta] nvarchar NULL, ...
3
votes
1answer
44 views

Slow query on primary server runs fast on hot standby

I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
0
votes
0answers
17 views

Very slow query with percona XtraDB Cluster

I'm trying a percona xtradb cluster (3 nodes) on a rancher environment (automatically created from rancher catalog). Replication works great, but I've lots of problems with slow queries. First of ...
12
votes
2answers
243 views

How to track blocking that happen for less than a second - SQL Server

I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as ...
0
votes
1answer
29 views

Full scans/sec metric seems very high, dropped after killing 2 queries

Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value. I killed the 2 SPIDs that where ...
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
3
votes
2answers
153 views

SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
0
votes
0answers
32 views

Postgres: Understanding Hash Left Join?

Using Postgres 9.4, I'm trying to a query by conditions ==> no hash left join. EXPLAIN ANALYSE SELECT CEAR.RESULT_ENUM_TYPE_ID, CEAR.CE_CE_COMMUNICATION_EVENT_ID, MC.CAMPAIGN_NAME, MC....
1
vote
1answer
45 views

Performance of BINARY vs CAST AS BINARY in MySQL

Using MySQL 5.6.23, I recently changed a prepared statement from: SELECT * FROM table WHERE BINARY column = :boundValue to: SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue ...
1
vote
0answers
37 views

Under which conditions would selecting a small column off a primary key be slow?

Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out. What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow? ...
1
vote
1answer
47 views

three-table JOIN query on a MySQL Cluster has become unusably slow

I have a select query called by a script to our MySQL Cluster that has gone from running in a second or two to taking well over a minute per query (and it has to run this query 24000 times). Nothing ...
12
votes
2answers
939 views

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
0
votes
1answer
79 views

Is it beneficial to include ORDER BY fields in the list of SELECT fields? [closed]

I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in ...
-2
votes
2answers
63 views

Query taking very long time in 12C environment, but the same query is taking just a few seconds in 10G production environment

We are migrating a database from 10G to 12C and currently we are testing in a dummy 12C production environment. Particularly 3 SQL queries are taking almost 50 min. time to complete and show the ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
0
votes
1answer
50 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
1
vote
2answers
28 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
43 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 ...
1
vote
2answers
171 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
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 ...
-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 ...
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 - (...
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
315 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
57 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 ...
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 +-------------+--...
1
vote
2answers
84 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
60 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
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
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 ...
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
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 ...
3
votes
1answer
78 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
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 ...