Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
6
votes
3answers
228 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
6
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?...
6
votes
3answers
1k views

Index scan of table with one record with 2.2 billion executions

There's something I'm not sure how to tackle with in the query I have. First, definitions: Courier Services table. With one record. CREATE TABLE [dbo].[CS]( [ServiceID] [int] IDENTITY(1,1) NOT ...
6
votes
2answers
4k views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
6
votes
3answers
461 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
2k 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
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....
6
votes
1answer
4k 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
22k 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 ...
6
votes
1answer
1k 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
283 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
7k views

Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)?

Let's say i have a table with 3 columns a,b and c. Could i possible speed up a query that looks like this by using index(es) ?? SELECT a,b,SUM(c) # or AVG(c) FROM table GROUP BY a,b ORDER BY a,b ; ...
6
votes
2answers
7k 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
408 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
100 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
15k 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
238 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
1answer
19k views

Improve SQL Performance with LIKE '%ABC%' QUERY

I know that using a LIKE '%ABC%' query will not use indexes and there is little that can be changed to the query to improve this, however what can do to make execution quicker? At this stage we ...
6
votes
1answer
2k views

Identical query, tables, but different EXPLAIN and performance

I have two tables levels and users_favorites +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------...
6
votes
1answer
417 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
1k views

Unbelievably slow and unusable Query Store

I'm begining with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It's just keep saying "Waiting" for hours and hours. Query on the background (...
6
votes
1answer
534 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
204 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
4k 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
1answer
198 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
2answers
580 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 ...
6
votes
1answer
198 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
992 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
916 views

How to measure the memory usage of a Mongodb aggregation?

I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
5
votes
2answers
429 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
850 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
27k 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
2answers
5k 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
2answers
389 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 ...
5
votes
2answers
3k views

Performance refactoring: Trying to avoid the table scan

I have a proc containing a query joining a few tables and I am having some performance issues. The main table (which is a huge table) has a PK and a few NC indexes. CREATE TABLE [dbo].[TableA] ( ...
5
votes
4answers
300 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
330 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
2answers
574 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
2answers
7k 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
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
709 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
3answers
12k views

Duration Field in Profiler

I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query. My ...
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 ...

1
3 4
5
6 7
44