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

learn more… | top users | synonyms

3
votes
2answers
56 views

What's the best way to diagram this “relation” looking for performance and|or disk space savings

I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to ...
3
votes
1answer
164 views

Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process. DELETE FROM `table1` WHERE ( djID, localID ) IN (("128","26"),("110","32")) The table I'm querying on has about 20 million ...
4
votes
2answers
80 views

Update fields that contains certain prefix by lookup table

I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly ...
1
vote
1answer
77 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
38 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
63 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
54 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 ...
3
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
104 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
154 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
116 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
85 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
74 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 ...
0
votes
1answer
31 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
27 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
164 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
707 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
32 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
64 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
71 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
33 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
65 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
85 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
57 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
61 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
98 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
45 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
114 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
45 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
44 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
27 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
77 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
52 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
70 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
76 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
40 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
31 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
59 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
48 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
3answers
90 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
98 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
174 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
197 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
36 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
126 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
97 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
1answer
117 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
317 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
56 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, ...