A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
15
votes
2answers
504 views
T-SQL query using completely different plan depending on number of rows I'm updating
I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
13
votes
4answers
441 views
Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
12
votes
2answers
2k views
Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding ...
11
votes
3answers
454 views
Improve performance of query using IN()
I have the following SQL query:
SELECT
Event.ID,
Event.IATA,
Device.Name,
EventType.Description,
Event.Data1,
Event.Data2
Event.PLCTimeStamp,
Event.EventTypeID
FROM
Event
INNER JOIN ...
10
votes
3answers
1k views
Are Views optimized when I add a WHERE clause to them?
Does it make a difference if you filter a View inside or outside the View?
For example, is there any difference between these two queries?
SELECT Id
FROM MyTable
WHERE SomeColumn = 1
Or
SELECT Id
...
9
votes
3answers
331 views
Are these two queries logically equivalent?
Are these two queries logically equivalent?
DECLARE @DateTime DATETIME = GETDATE()
Query 1
SELECT *
FROM MyTable
WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7
Query 2
SELECT *
FROM ...
9
votes
4answers
777 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
9
votes
1answer
601 views
SQL query for combinations without repetition
I need a query which can be used in (or as) a function and retrieves all combinations of n values. And I need all combinations of length k where k = 1..n.
Extended sample input and result so input ...
9
votes
2answers
895 views
How (and why) does TOP impact an execution plan?
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
9
votes
3answers
2k views
Why is an aggregate query significantly faster with a GROUP BY clause than without one?
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate)
FROM MyTable
WHERE ...
9
votes
1answer
375 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
9
votes
2answers
3k views
Slow Performance Inserting Few Rows Into Huge Table
We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
8
votes
3answers
277 views
What is retrieved from disk during a query?
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...
Do the number of columns in a particular table affect the performance of a query, ...
8
votes
1answer
311 views
How to get accurate query performance?
I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...
8
votes
1answer
662 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
7
votes
3answers
473 views
What should I use? A string or 15 integer fields?
I am developing a student tracking program where I need to store 15 exam marks.
I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
7
votes
3answers
457 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
3answers
992 views
TSQL performance - JOIN on value BETWEEN min and max
I have two tables in which I store:
an IP range - country lookup table
a list of requests coming from different IPs
The IPs were stored as bigints to improve lookup performance.
This is the table ...
6
votes
3answers
522 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
6
votes
3answers
2k views
Improve performance of sys.dm_db_index_physical_stats
During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on ...
6
votes
4answers
1k views
What can speed up a SQL count query?
When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so ...
6
votes
3answers
244 views
How can I identify when to create a new table to hold data that can be obtained from a query?
We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when ...
6
votes
2answers
459 views
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds).
After suspecting ...
6
votes
3answers
774 views
Is it better to populate variables using SET or SELECT?
What is the better way (with regards to performance) to set a value to variable?
By SET command:
DECLARE @VarString nvarchar(max);
SET @VarString = 'john doe';
SELECT @VarString;
By SELECT ...
6
votes
4answers
286 views
Gaps and islands: client solution vs T-SQL query
Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?
To be specific, let us provide some test data:
CREATE TABLE dbo.Numbers
(
n INT NOT NULL
...
6
votes
1answer
146 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
886 views
OPTION FORCE ORDER improves performance until rows are deleted
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.
...
6
votes
1answer
180 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
2answers
20 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
1answer
180 views
Performance of query with a range condition and order by
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
...
6
votes
3answers
413 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 ...
5
votes
4answers
972 views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
5
votes
2answers
3k views
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.
They ...
5
votes
1answer
110 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
2answers
456 views
How can I improve the speed of a query on a 20 million+ row table?
I have a query that is used for getting internet traffic statistics of certain IP addresses.
There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
5
votes
2answers
247 views
What can I do to speed up this SQL Query?
I've created this SQL query with the help of @Dems :-)
Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
5
votes
1answer
484 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 ...
4
votes
3answers
345 views
Some book suggestions for performance tuning
I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
4
votes
3answers
211 views
Query runs slow in test site on first execution. Why?
I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
4
votes
3answers
236 views
Which is the most efficient way to run a particular select query?
If I run the following code:
select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, Risk
I get the following ...
4
votes
2answers
102 views
Is high Extent Fragmentation a problem?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents ...
4
votes
1answer
64 views
Covering Index Question
I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED,
PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND,
CASE Left(PT.INVENTREFID, 3)
WHEN ...
4
votes
1answer
178 views
How to Optimise Query
I have a database structure similar to this,
CREATE TABLE [dbo].[Dispatch](
[DispatchId] [int] NOT NULL,
[ContractId] [int] NOT NULL,
[DispatchDescription] [nvarchar](50) NOT NULL,
...
4
votes
1answer
215 views
Does the order of columns has a significant effect on INSERT?
Do we experience a noticeable difference in performance for these two queries?
INSERT INTO table (col1, col2, col3, col4, col5) ...
and
INSERT INTO table (col5, col3, col1, col2, col4) ...
Do we ...
4
votes
1answer
63 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 |
| ...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
4
votes
2answers
557 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
4
votes
2answers
176 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
4
votes
2answers
271 views
Poor performance after changing SQL Server 2008 R2 database
Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size).
Last week I modified the database to add new a single new ...
4
votes
2answers
187 views
Same queries, different servers, different exec plans, stats are up to date
I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on ...
