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
6
votes
3answers
325 views

Slow query in PostgreSQL selecting a single row from between a range defined in two columns

I have imported a copy of the ip2location_db11 lite database, which contains 3,319,097 rows, and I am looking to optimize a numeric range query, where the low and high values are in separate columns ...
6
votes
1answer
1k views

Why does `SELECT count(*)` return 1?

Some RDBMS's seem to allow a query without a FROM clause, eg: postgres=# SELECT 'foo' bar; ┌─────┐ │ bar │ ├─────┤ │ foo │ └─────┘ But for those that allow it, why would SELECT count(*) not return 0?...
6
votes
1answer
571 views

Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

I use PostgreSQL 9.6. I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't ...
6
votes
1answer
943 views

Stored procedure with recompile

I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params ...
6
votes
2answers
3k views

Filter table before join

I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called ...
6
votes
1answer
6k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
6
votes
3answers
3k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
6
votes
1answer
275 views

Is it recommended to clear the query plan cache

Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation. After making the changes to the code, ...
6
votes
1answer
3k views

Performance issue with sp_executesql and VARCHAR parameter

Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)). Execution time of the first query is 34 seconds SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT FROM Customers c LEFT JOIN ...
6
votes
2answers
6k views

Which is faster? Multiple update queries in a cursor loop, or a single query?

Which is faster? Multiple update queries in a cursor loop, or a single query? For example, in this case, which is faster? This query is in a trigger. Multiple Query in loop DECLARE done INT DEFAULT ...
6
votes
2answers
6k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
6
votes
3answers
392 views

Why does this query take so long to execute?

I'm currently using Microsoft SQL Azure (RTM) - 12.0.2000.8 as the database. The database currently has 10 DTUs. The idea here is that I want to make the query based on the view. This view contains ...
6
votes
2answers
99 views

Retrieving data of vehicle drivers that have not been assigned to a job

Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display: Jobs that have not been ...
6
votes
1answer
7k views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
6
votes
2answers
14k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
6
votes
1answer
142 views

Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found ...
6
votes
1answer
149 views

Query optimization with multi-column variant matching

TL;DR - I'm looking for advice on how to better write the query below. Below is a pared down version of my table structure with some sample data. I don't have control over the data structure at all ...
6
votes
2answers
471 views

How do I effectively handle very skewed data? Statistics up to date but don't seem to help

I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed. 125,000 rows are 'user1', and the remaining 5000 rows are split ...
6
votes
1answer
410 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
1answer
7k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
6
votes
1answer
506 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
6
votes
1answer
202 views

6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2

I have a proc, looping through databases( the insert is in a fix database...the select uses the loop ), making inserts (Will hide names for security): insert into Main_Database..Table(16 Fields) ...
6
votes
2answers
3k views

Optimisation of complex query - 100% cost index seek?

I'm a c# developer, however I'm quite experienced with SQL. I have had a bit of experience with optimising queries but I've come up across one that has me stumped. The query takes about 3 minutes to ...
6
votes
2answers
2k views

Postgres slow query with order by, index and limit

I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows. Column | Type | Modifiers -------...
6
votes
1answer
186 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
6
votes
1answer
190 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
6
votes
2answers
1k views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
6
votes
3answers
968 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes (roughly)...
6
votes
0answers
94 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
6
votes
2answers
531 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 ...
5
votes
2answers
422 views

What is the optimum method of finding the per-item earliest date from a clustered index

I have a SQL Server 2012 table which contains columns like this: ID int NOT NULL, EventDate datetime NOT NULL, ... 32 other columns... where the table has about half a billion rows across a range of ...
5
votes
1answer
813 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations &...
5
votes
2answers
26k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
5
votes
3answers
186 views

Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
5
votes
2answers
1k views

Optimizing a Query for splitting strings with a known size

I have a Column that has a . as a delimiter, it looks like so.... abc.efg.hij I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this ...
5
votes
1answer
1k views

Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? If I have a table where some of the rows will be ...
5
votes
2answers
247 views

How to get SEEK accessing converted ID via view

Assume I have a table: -- just for test purposes CREATE TABLE SomeTable ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED ,SomeColumn1 NVARCHAR(50) NULL ,...
5
votes
2answers
333 views

Bad performance using “NOT IN”

In my application, I have a query which performs a search in "files" table. The table files is partitioned by f.created (see the table definition) and has ~100 million rows for the client 19 (f.cid = ...
5
votes
2answers
4k views

How to clear all plans from a single database?

I want to clear out the procedure cache for just one database, not the entire SQL Server. Is there a way?
5
votes
4answers
299 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
5
votes
2answers
328 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
5
votes
3answers
32k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
5
votes
2answers
530 views

Store categories in database or use hard coded integers?

An address can belong to either the mailing or billing categories. When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in ...
5
votes
1answer
2k views

Why does adding ROWNUM to a query improve performance?

I have two queries: 1) This query has a ROWNUM column (takes 20 seconds to execute): SELECT ROWNUM ,ROAD_ID ,VERTEX_INDEX ,SDE.ST_X(ST_POINT) AS X ,SDE.ST_Y(ST_POINT) AS Y ...
5
votes
3answers
704 views

Speed of MySQL query for the most recent 800 entries in chronological order

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are: ip_address varchar(16), status varchar(7), timestamp datetime Been running it for ...
5
votes
2answers
1k views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
5
votes
1answer
5k views

Can SQL Server use multiple indexes for the same query?

This question might have been asked before, but I tried searching with this term "can SQL Server use two indexes for same query" and it yielded no results. Assume you have the below query: select ...
5
votes
1answer
2k views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table "bss....
5
votes
2answers
7k views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
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 ...