Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
5
votes
1answer
2k views

Do foreign keys affect performance in Oracle?

Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases? One specific area i was ...
5
votes
1answer
854 views

What is the most effective SELECTIVE XML index?

I am trying to determine the most effective selective XML index for an XML column within a table of 100 million rows. My question is similar to : Sql Server 2012 Extended Events for Selective Xml ...
5
votes
1answer
144 views

Will reloading a table cause recompiles downstream?

We have a vendor that is requesting we truncate a data table, and reload it with the same data daily. The table is 90k rows and approx 10mb. The reason they want this done is so the ETL program picks ...
5
votes
3answers
1k views

Could an update on all statistics separately cause bad performance?

We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases. (we are on sql server 2008 R2) 2 weeks ago though one of the DBA’s here has ...
5
votes
2answers
5k views

Speed up an aggregate query on an 11 million row table

I have a query that I want to speed up: SELECT sum(case when FlagDTD = 1 then Success else 0 end) as SuccessDTD , sum(case when FlagDTD = 1 then [Error] else 0 end) as ErrorDTD , round(sum(...
5
votes
2answers
203 views

Find missing folders paths in split hierarchies

I have a table which contains folders' paths. This table contains four columns: DirID - The folder's ID. BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the ...
5
votes
1answer
177 views

Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?

I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, ...
5
votes
2answers
9k views

Optimized date compare in WHERE clause, convert or datediff=0?

In my query I am comparing two dates in the WHERE clause once using CONVERT: CONVERT(day,InsertedOn) = CONVERT(day,GETDATE()) and another using DATEDIFF: DATEDIFF(day,InsertedOn,GETDATE()) = 0 ...
5
votes
3answers
1k 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 ...
5
votes
1answer
977 views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers | ...
5
votes
1answer
4k views

Finding distinct rows across two tables: Full Outer Join more efficient than Union?

When finding distinct rows across two tables where we can't necessarily ensure are pre-sorted, is it a good idea to use a FULL OUTER JOIN rather than a UNION? Are there any downsides to this approach?...
5
votes
4answers
13k views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
5
votes
2answers
29k views

Improve query performance when selecting almost all rows with many “group by” columns

I have a table with 20 columns and about 600,000 records. The maximum row size is only around 100 bytes. The table is repopulated every few days, but the number of records remain about the same. For ...
5
votes
4answers
2k views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
5
votes
2answers
300 views

Performance of different precedence pseudocolumn solutions with LIMIT

I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests. The OP wanted to get the first matching row given ...
5
votes
1answer
163 views

Queries running parallel with default maxdop

I need to understand below behavior of sql server queries System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance. Currently MAXDOP is 0 and ...
5
votes
2answers
519 views

Nested Loop has very low estimates due to skewed data

On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb. If I'm correct SQL Server 2014+ uses Coarse ...
5
votes
1answer
120 views

Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

Given this data: gid | val 1 | a 1 | a 1 | a 2 | b 3 | x 3 | y 3 | z the following queries return groups (gid) that contain exactly one distinct value (val): SELECT gid FROM t GROUP ...
5
votes
1answer
204 views

How to compute number of concurrent application sessions using SQL Server table storing session data?

I have a vendor application which stores session data in a SQL Server 2008 database table. Columns it includes are things like the sessionId, the user's IP address, the datetime stamp when the session ...
5
votes
1answer
1k views

Insert Into table Exec SP with bad performance

I am working on a datawarehouse. One of our staging tables that is refreshed every night has about 10 million rows. We are using a custom built ETL tool that I can't make too many changes to. The tool ...
5
votes
2answers
722 views

T-SQL Daylight Saving lookup table - poorly performing table-valued function

I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly. Any help ...
5
votes
3answers
224 views

What's the least risky way to Select on production environment?

My goal is to use selects on production without affecting anything else. When I was new to SQL, I just wrote simple selects, but later I learned this can cause locks. If I don't care about dirty ...
5
votes
2answers
6k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
5
votes
1answer
263 views

Pre-staging data causes execution plan cost to skyrocket

I have a troublesome query that we are trying to tune. One of our first thoughts was to take a portion of a larger execution plan and store those results to an intermediate temp table and then ...
5
votes
1answer
217 views

Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

I've got a schema roughly like this (this is a simplification of my actual schema): CREATE TABLE foo ( key1 NUMERIC(6) NOT NULL, key2 VARCHAR(32) NOT NULL, val VARCHAR(255) NULL, ...
5
votes
1answer
658 views

SUM same column twice performance

DBMS: SQL Server 2012 Express I have an SQL Statement which looks something like this: SELECT OverSpeedings = SUM(OverSpeeding), OverspeedingPoints = SUM(OverSpeeding) * 5, FROM ...
5
votes
1answer
2k views

Query plan showing a missing index even though the index is present in the table

In one of my production SQL server instance, I am facing an issue. A query is taking long time to run. While checking SQL query plan, I found that it was giving suggestion for creating a missing index....
5
votes
2answers
16k 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 ...
5
votes
2answers
6k views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
5
votes
2answers
3k views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
5
votes
2answers
6k views

MySQL slow select from a view

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot! I need to check the total amount for each the number ...
5
votes
1answer
795 views

Logical reads amount difference with almost identical indexes

I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server. The mentioned query is quite simple, ...
5
votes
1answer
94 views

Will these queries return the same results?

I have this simple query: SELECT friend_id FROM default_friend WHERE user_id = 1 And the results are below: +-----------+ | friend_id | +-----------+ | 2250 | | 4901 | | ...
5
votes
1answer
1k views

Query shows sort cost,even when required index is available

This question is from SO,iflagged it move to DBA.SE..but i may not see that happening,due to upvotes..so posting here below is test data : --Main Table CREATE TABLE [dbo].[LogTable] ( ...
5
votes
2answers
3k views

Aggregate sales of the past 12 months for the current row date

I need to compute the trailing 12 months sum of sales for a given client_id, based on each row given month. Here is the initial table in a month by month aggregated sales by client (filtered here on ...
5
votes
1answer
893 views

varchar and nvarchar in tuning a stored procedure - how to improve performance in this scenario?

I have the following procedure that is called over a million times a day, and I think it can be tuned for better resources usage. ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet] ( @itemNo ...
5
votes
1answer
2k views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
5
votes
1answer
724 views

Best database design for website ranking

I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 ...
5
votes
1answer
125 views

Mysql Query takes forever after adding a where condition

I have a mysql query which return results in 4 seconds. SELECT * FROM xercasehistory WHERE xerId = 192 ORDER BY id DESC limit 10; But when I add extra where parameters to ...
5
votes
1answer
8k 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 database(...
5
votes
2answers
769 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...
5
votes
1answer
2k views

Slow SQL Server query using a DB2 linked server in virtual environment

I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue: Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 & ...
5
votes
2answers
1k views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; +----+-------------+------------+-------+---------------+------+------...
5
votes
2answers
110 views

How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?

SQL Server 2012 optimizer does not get it right. Test case, summary: This is a simplified test scenario. DDL statements at the bottom. I have two tables for data logging, A and B. There is a 1:n ...
5
votes
1answer
555 views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

As a follow-up to this question, I have one of my own. The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
5
votes
1answer
132 views

Empty result takes longer with all columns included

I'm executing a query every 1 second and most of the time it returns no results (SQL generated by Entity Framework): SELECT TOP (5) [Extent1].[ID] AS [ID], ***30 more columns*** FROM ...
5
votes
1answer
425 views

Sort operator positioning in an execution plan

I am troubleshooting a performance issue and trying to get a stable execution plan for a reporting query. Links to the actual plans are below: Good vs Bad If you look at the Good plan, the sort is ...
5
votes
1answer
174 views

Variable Sniffing?

This might be dumb and feel like I am going back trying to understanding basics. So I create a test table like below and create a clustered index on it create table test( c1 int) DECLARE @Random ...
5
votes
3answers
2k 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 ...
5
votes
2answers
263 views

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `...