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

learn more… | top users | synonyms

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
97 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
43 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
91 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
43 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
42 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
72 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
51 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
46 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
89 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
96 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
156 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
139 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
105 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
85 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
115 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
243 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
54 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
36 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
107 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
130 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 ...
6
votes
3answers
90 views

Query Processing & The Buffer Pool

When SQL Server Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer ...
1
vote
2answers
160 views

Faster query when joining data from multiple tables

I’m looking for a way to make a database query faster. The table parts has over 6 million rows. Each part row is associated with the manufacturers table (a couple hundred rows) by ID. When searching ...
1
vote
1answer
126 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
2
votes
1answer
262 views

Estimated row size and estimated data size is incorrect

I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns As a simple example, if I dump the table into a temp table, the ...
0
votes
0answers
48 views

How to tune a sql server for a specific process in particular?

I have a specific process that I am want to tune for in a sql server DEV server. The DEV server and the LIVE server are equal, just the DEV has less memory. Both LIVE and DEV read data from the same ...
0
votes
1answer
99 views

What's the biggest bottleneck (time) in writing data to a SQL database?

I'm running an ETL process that is writing about 2 million rows to a SQL Server database. I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story). I'm ...
1
vote
0answers
31 views

How to efficiently a query to return rows plus a summary

I need to write a query for a SQL Server 2012 database that will return rows to be presented to a search results page, this page has 12 filters that the user can use to narrow the results. I create ...
4
votes
5answers
584 views

How to optimize very slow SELECT with LEFT JOINs over big tables

I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case. My tables: persons (~10M rows) attributes (location, ...
1
vote
1answer
338 views

MySQL performance issue - intermittently slow queries

I manage an intranet application used by around 50 staff in a few locations. The application is hosted on a Windows Server 2008 VM with 8GB RAM and MySQL (5.5) is running on the same server as IIS. I ...
1
vote
1answer
58 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
2
votes
2answers
283 views

Very slow query in PostgreSQL on 10kk records table

I'm having very slow requests to my queries. I created an index on the filter columns and made partitioning but requests is still too slow. How can I make it faster? Here's query and explain: SELECT ...
5
votes
3answers
290 views

Should I split this large table into three smaller tables?

I'm designing a PostgreSQL database that will contain information about photos uploaded by my users. All users will have at least one main photo and optionally one or more public and/or private ...
1
vote
1answer
73 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
0
votes
0answers
31 views

How to Normalize dump table, Perform-query, Perform-database Mysql

I need to migrate information hosting provider to our own systems, in this context I pose the following: The vendor provided us with history of transactions totaling approximately 7.5 million ...
5
votes
2answers
86 views

Help finding join without predicate

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) ...
2
votes
1answer
98 views

Can I set order of query execution?

Below query is executing in approx 5 min: SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B, ...
0
votes
0answers
56 views

Slow query performance - aritmethic operators, not exists, subselects

In our application we have this query that takes A LOT of time to execute, considering that is frequently used (> 20 seconds). We use Hibernate as our ORM. All the tables have a clustered primary ...
8
votes
2answers
371 views

Query 100x slower in SQL 2014, Row Count Spool row estimate the culprit?

I have a query that runs in 800 milliseconds in SQL 2012 and takes about 170 seconds in SQL 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count Spool operator. ...
5
votes
2answers
79 views

looking for missing indexes in the cached execution plans

I have the following query that shows me what is cached in sql server: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS ...
1
vote
1answer
72 views

SQL Server - High Performance Record Comparison [closed]

Scenario : I have a collection of records to be inserted/updated into a table. Each record before getting inserted/updated in the table must be checked with the other rows in the table so as to find ...
-1
votes
3answers
80 views

Looking to improve query performance

This is my query: select S-Num.Title, jobs.JobNum, jobs.JobPart, Part.PartNum, jobs.RevisionNum, jobs.Seq, jobs.Parent, Part.PartDescription, ...
18
votes
1answer
649 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 ...