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

learn more… | top users | synonyms

1
vote
1answer
84 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
156 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
38 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
80 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
30 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
404 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
269 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
52 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
184 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
171 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
60 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
28 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
83 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
94 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
48 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
250 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
62 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
66 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
74 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
643 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 ...
1
vote
1answer
68 views

How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

I have successfully added several calculated fields to different tables, for example: ALTER TABLE dbo.tblBGiftVoucherItem ADD isUsGift AS CAST( ( ISNULL( CASE WHEN ...
3
votes
2answers
87 views

Optmizing ORDER BY

I'm new to MySQL and have written the following query (see below). PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes ...
0
votes
1answer
54 views

Optimization of complicated sums and counts in related tables

I have a categories table, which have two columns, id and name. I have a types table, which have three columns, id, category_id and points. I have a products table, which have three columns, id, ...
1
vote
3answers
62 views

MySQL: Why is this UPDATE query so incredibly slow?

So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts ...
2
votes
1answer
25 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: ...
3
votes
1answer
126 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS ...
0
votes
0answers
36 views

Non-structured Query performs better than on clustered, hash-clustered and indexed?

I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join ...
0
votes
0answers
45 views

Is it a good idea to disable lock escalation while adding new calculated columns to a table?

Having in mind that I can add computed columns to the subscription tables according to this link: SQL Server : Transactional Replication Computed Column Is it a good idea to disable lock escalation ...
1
vote
1answer
47 views

Can't eliminate index scan

I have a query, and even using SQL Sentry, I can't eliminate the index scan. This is the query: SELECT TOP 30 codCliente FROM ( SELECT t1.CodCliente, codcampo, valor, t1.chavealeat ...
2
votes
1answer
36 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
7
votes
1answer
503 views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
0
votes
0answers
30 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, ...
0
votes
0answers
64 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...
9
votes
1answer
254 views

Recursive sql query performance issue [closed]

This is the sixth time I'm trying to ask this question and it's the shortest one too. All the previous attempts were resulting with something more similar to a blog post rather than the question ...
2
votes
1answer
34 views

Better way to update table with value from another table

I'm trying to rewrite the following query to make it more efficient: update c set c.cartype = (select Id from carTypes where carType = 'Coupe') from cars c inner join doorcount dc on c.Id = ...
1
vote
1answer
69 views

Optimize slow query

I have this query that takes over half an hour to run on our Linux MySQL db... SELECT ls.emailaddress , lse.subscriberid , lse.eventtype , count(lse.eventtype) as total FROM ...
2
votes
1answer
92 views

Computational power required for this query

I am not sure if the query I am trying to run is unreasonably large, in terms of computational power. The query will identify islands (and their duration) of a certain condition in my db. Things work ...
0
votes
2answers
121 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
2
votes
1answer
303 views

Help required with query (join) optimization

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause ...
1
vote
3answers
67 views

Why does the query plan still sort a table despite having an index sorted on the columns?

I'm using Postgres 9.1 I have two tables I am joining: wikidb=> \d page Table "public.page" Column | Type | Modifiers ...
-1
votes
1answer
51 views

How can I return the different prices of my quantities? [duplicate]

I have two tables, one containing all the quantities of products by country and another table that contains the prices by country. When I add a condition for example country = "ch", I have the price ...
0
votes
0answers
53 views

Design database table for property listing

I want to design a database for a listing website, this is the first time I design the database, so I am not sure if I in the correct path. Here is the fields that needed in a listing, title - ...
1
vote
1answer
266 views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below, to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
2
votes
0answers
37 views

Composite index works for larger set of data, but not smaller

This is a follow on question to optimise my.ini for key query Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
127 views

Convert UNION query to JOIN. Is it actually needed for high performance?

I have the following table structure in MySql. To get the list of roles for a particular user, I use the following union Query, SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID FROM ...
2
votes
1answer
75 views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
1
vote
1answer
57 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
1
vote
1answer
297 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
2
votes
1answer
60 views

Stored procedure suddenly very slow, updating statistics not helping

Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have ...
1
vote
1answer
37 views

optimise my.ini for key query

I have a database system with a fairly complex dashboard query. First of all the details. Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT ...