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

learn more… | top users | synonyms

1
vote
1answer
69 views

Decrease Query Execution Time

I have been having issues with 2 queries with query execution times up to 250 seconds for each query. The tables joined have each less than 10,000 rows, with exception to 2 tables that have around 60k ...
2
votes
1answer
37 views

Key/Value table with limiters

I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search. I have a table storing key/value pairs. There are also 2 limiting ...
0
votes
2answers
60 views

Perform join on multiple tables

I have three tables right now in a postgres DB: Artists ID Name Shows ID Date Venue Price Is_sold Ages Time Multi_day Pit Artists_shows ID Show_id ...
3
votes
1answer
53 views

Indices for a join query with datetime range

Given the following query: SELECT DISTINCT COUNT(DISTINCT "patients"."id") FROM "patients" INNER JOIN "transactions" ON "transactions"."patient_data_id" = "patients"."patient_data_id" WHERE ...
4
votes
1answer
62 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
1
vote
1answer
79 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
7
votes
1answer
88 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
3
votes
1answer
62 views

Update multiple Ids when SET = (Sql Query)

I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use: UPDATE T1 SET A1 = (SELECT ...
1
vote
0answers
70 views

High load on server because of one query

I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem ...
4
votes
2answers
72 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
1
vote
1answer
26 views

Struggling to design addresses table for fast querying

I have two tables: addresses and locations. Locations zipcode city county state Addresses line1 line2 city county state zipcode Posts Id Address_id Body All my users are required to provide at ...
1
vote
1answer
25 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
7
votes
1answer
143 views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
18
votes
2answers
349 views

Huge data and performance in SQL Server

I've written an application with a SQL Server backend that collects and stores and extremely large amount of records. I've calculated that, at the peak, the average amount of records is somewhere in ...
0
votes
1answer
25 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
0
votes
1answer
58 views

9 Minute Long Running Query [closed]

We recently stood up Documentum. We have a query that runs 9 minutes. We have applied indexes, this is a cotts product, the query runs against a view that the product creates. Below is the syntax: ...
2
votes
2answers
66 views

Optimizing a MySQL “IN” query

I'm trying to optimize a MySQL table for a query of the form: SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...) I've added an index to the table: ALTER MyTable ADD ...
0
votes
0answers
20 views

Is there something like Query Analyzer for Postgres?

We are running EF6 and I'm trying to find out exactly what query is running against our Postgres database. With Sql Server, I would just fire up Query Analyzer and look at the query there. Is ...
0
votes
1answer
29 views

Can I use the cost of a query to prove its efficiency when there is only a small number of rows?

Can I run EXPLAIN (Oracle) for a query when there is only a few dozen rows in the table and use the cost to prove whether the query is efficient or not ? Can you measure the efficiency of a query ...
1
vote
0answers
33 views

Query slow on subscriber but fast on publisher (Merge Replication)

As the title suggests, I am having issues with performance with my subscriber. I run the exact same query (it's a recursive query with a few joins but nothing too serious). I can run this query on my ...
2
votes
2answers
47 views

Search multiple values in multiple columns

I have a table results with this fields: id results_id number_one (int) number_two (int) number_three (int) number_four (int) number_five (int) Each number_* column can contain a number from 01 to ...
0
votes
0answers
38 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
0
votes
2answers
58 views

How to improve performance of frequently updated table in mysql?

my table structure:: CREATE TABLE `gps_device_data`( `imei_no` bigint(15) NOT NULL DEFAULT '0', `ioelementpriority` int(1) DEFAULT NULL, `longitude` int(15) DEFAULT NULL, `latitude` int(15) ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
0
votes
1answer
36 views

How simplify/optimize a search of keywords in two tables at the same time?

I'm trying to model a function which will be used by a search engine (of cars for the example). The last version of PostgreSQL will be installed on a Windows server (I don't know server's ...
0
votes
1answer
41 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
1
vote
0answers
42 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
2
votes
1answer
36 views

Questions about DBCC FREEPROCCACHE

I used spAskBrent for troubleshooting a SQL Server 2005 performance issue. I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and ...
1
vote
0answers
24 views

PostgreSQL: labels vs columns

I have a data structure similar to this: date foo bar baz boo ---------------------------------------- 2015-11-18 1 null 2.5 81.0 2015-11-19 1 78.4 12.8 ...
-1
votes
1answer
57 views

Rebuild an index in SQL Server 2012

When I execute this query, my .MDF file grows by 120 GB: ALTER INDEX ALL ON TABLE_NAME REBUILD WITH (FILLFACTOR = 90) or ALTER INDEX ALL ON TABLE_NAME REBUILD Can you tell me how to avoid ...
1
vote
0answers
46 views

Optimizing a group by/where in/min() query in MySQL

Assume I have a table that looks like such (The actual column names of my real table would only make things harder to explain, so I have created a mimic that behaves similarly). id | cond1 | cond2 ...
0
votes
1answer
69 views

Finding the right pattern for creating indexes,redesign query structure

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables. Because the logic of the initial query was to bring data based on ...
2
votes
1answer
72 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
1
vote
1answer
39 views

How to optimize query with max on Postgres?

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It takes over an hour ...
0
votes
1answer
25 views

Postgresql delete query never ends

I am having an issue which I can't resolve. delete from bb_gamelist_league this operation is taking too long. There are 276224 rows in that column. Delete is performed without any WHERE ...
0
votes
1answer
58 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
0
votes
1answer
42 views

Very slow query, not sure if MySQL index is being used

I have a query which includes a join and I have an index on the tables: SELECT e.entry, e.user_id from entries e left join users u on e.user_id=u.user_id where e.tenant_id=? AND u.active=1 AND ...
1
vote
2answers
80 views

charindex wrong result

Charindex returns no record if only one letter is provided. If parameter matches the whole word it shows the record. Why??? USE [database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER ...
4
votes
2answers
91 views

Can an index on one table be used in the query for another table?

I am working in SQL Server (2014). I have a table that contains an XML column. There are a well known set of queries against that column that I would like to materialize as persisted computed columns ...
5
votes
1answer
102 views

When does an execution plan not show in sp_WhoIsActive?

I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries. Why does the execution plan not show all the time in the results? ...
0
votes
1answer
54 views

Efficient way to get last 10 rows of each group in SQLite, can CTE help?

Consider there is a table of job runs history with the following schema: job_runs ( run_id integer not null, -- identifier of the run job_id integer not null, -- identifier of the job ...
0
votes
0answers
35 views

Rewrite a Stored Procedure

i would like your help in the best way to tune and re-write a stored procedure. it is consuming a lot of time and don't know what should i do to make it faster CREATE PROCEDURE [dbo].[xxxxxxxxxxxx] ...
4
votes
1answer
67 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...
0
votes
1answer
47 views

Slow query log on production?

What are the best practices regarding the slow query log? Should it be active in production environment? If so, what would be a reasonable long query time? I know its different for every application, ...
2
votes
2answers
101 views

SQL Job is running slow after a few days

I have a simple SQL job that runs a stored procedure that doesn't take any parameters. The job runs once a day and it normally takes less than 3 minutes to complete. What's odd is that this job runs ...
3
votes
1answer
155 views

How to insert/update millions of rows inside a sql server table(s) in an effective way?

Often in my job I have to create a procedure inside SQL Server that will process millions of data rows, save them into a temp table (staging table) and finally save them into a table (s) in a ...
0
votes
1answer
50 views

Why does STRAIGHT_JOIN consume more CPU?

Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea? When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, ...
0
votes
0answers
34 views

Why is one table taking up ~75% of time for DELETE

I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables. Two of the tables (Result, Amount) have ...
3
votes
1answer
94 views

Select performance problems with conditional index vs non-conditional on large volume table

We are trying to change some indexes on a large-volume table to be conditional indexes (function-based indexes) to reduce how much space is being used for indexes against this table. When testing the ...
1
vote
0answers
77 views

Mongodb Huge Number of Open Connections and Write Locks

We are seeing around 800 open connections to our Mongodb. To clarify our issue: We have 5 different clients who write the data into Mongodb. Each of these clients - write four different type of ...