A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
4
votes
2answers
120 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
4
votes
1answer
101 views
In SQL Server, what are the typical symptoms of parameter sniffing?
There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of ...
4
votes
1answer
118 views
Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)
I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server. The estimated query execution plans have one obvious ...
4
votes
1answer
126 views
help required with indexing and query optimzation
After browsing most of the posts and after running out for options I am posting here.
I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country.
Here is my table ...
4
votes
1answer
2k views
Why does a query run slower in a Stored Procedure than in the Query window?
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?
Here's what my query looks ...
3
votes
2answers
340 views
Do I risk losing the benefits of indexing if I have an index on every column?
I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
3
votes
2answers
185 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
3
votes
2answers
1k views
How does paging work with ROW_NUMBER in SQL Server?
I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
3
votes
3answers
193 views
optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
3
votes
3answers
76 views
How can I improve this query?
About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
3
votes
2answers
61 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 ...
3
votes
1answer
94 views
Why does SqlSentry Plan Explorer not give the duration of my queries?
I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan ...
3
votes
2answers
137 views
How do I count rows with two properties in one index scan in SQL?
I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.
I need to output the following: for each day I need to ...
3
votes
1answer
283 views
Query taking long on table with low record count
I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.
...
3
votes
4answers
560 views
Optimize Query with Derived Table
I have the following query
select
ps.id, ps.title
, IFNULL(s.likes,0) as num_likes
, IFNULL(s.comments,0) as num_comments
, IFNULL(s.ratings,0) as num_ratings
, IFNULL(s.views,0) as num_views
, ...
3
votes
2answers
2k views
MySql - How can I speed up this query
I have the following tables:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE ...
3
votes
1answer
246 views
Can you have a WHERE clause with a greater than operator on a composite key?
I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :
CREATE TABLE `bbcmapimp_pc_raw` (
`postcode` char(8) NOT NULL,
...
3
votes
2answers
322 views
PostgreSQL Sequential Scan instead of Index Scan Why?
Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
3
votes
1answer
197 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 ...
3
votes
1answer
236 views
Query Performance Issue
I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this.
Version : Sql Server 2008 R2 Enterprise
No fragmentation . ...
3
votes
2answers
156 views
Do MySQL sub-queries essentially use as much overhead as separate queries?
If I have a query like this:
SELECT name FROM
(
SELECT * FROM `table`
WHERE id IN (1,40,300)
ORDER BY AnotherColumn
)
From a bandwidth and overhead standpoint, is that the same thing as doing ...
3
votes
1answer
124 views
Get profiles values where message.sender_user_id = profile.user_id
I have this tables:
default_messages
CREATE TABLE IF NOT EXISTS `default_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`message` text ...
3
votes
2answers
246 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;
...
3
votes
2answers
1k views
Copy huge amount from 1 table to other
I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.
So I ...
3
votes
1answer
97 views
How does order of index affect performance in SQL Server?
I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
3
votes
1answer
2k views
MySQL: Optimize UNION with “ORDER BY” in inner queries
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them ...
3
votes
2answers
137 views
Sudden Query Performance Degradation On A Linked Server
I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
1answer
403 views
MYSQL Slow SELECT with multiple joins and ORDER BY
I have this query
SELECT
r0_.id AS id0,
r0_.deleted_at AS deleted_at1,
r0_.updated_at AS updated_at2,
r0_.created_at AS created_at3,
r0_.is_vehicle_offer AS is_vehicle_offer4,
...
3
votes
1answer
2k 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 ...
3
votes
4answers
329 views
Help with Query. Finding records that have the same relationships (MySQL)
I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this:
listings
----------------
id (int)
name (varchar)
listings_taxons
...
3
votes
1answer
76 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
3
votes
1answer
236 views
Need help with long running query
UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.
Thanks for taking the time to read this post - ...
3
votes
2answers
62 views
Looking for a possible multi-column index
I have a table highscore containing the columns:
game (text)
date (timestamp)
score (integer)
more irrelevant ones...
The query most often run on it is:
SELECT *
FROM highscore
WHERE game = ...
3
votes
3answers
264 views
Why does the query cost change so much and how to prevent it
The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that:
The query has an structure like:
SELECT
... a lot of stuff ...
...
3
votes
2answers
313 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 ...
3
votes
1answer
424 views
MySQL Handler_read_rnd_next == tablescans without indexes?
I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance.
In the MySQL Handlers Plot, i have been noticing alot of ...
3
votes
1answer
212 views
Measuring the relative performance of queries given the assertion that “cost” isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does ...
3
votes
1answer
132 views
How to search for an ID and its descendants in this query?
I have the following tables:
Project Unit
------------- ---------------
ID | IDUnit ID | IdParent
I have to select all projects that have the IDUnit ...
3
votes
2answers
350 views
Separate Address Table, Latitude, Longitude
We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
3
votes
1answer
103 views
MI Data Warehouse Advice
I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
...
3
votes
3answers
201 views
PostgreSQL 8.3: Slow GROUP BY on large table
I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow...
select run_id, count(*) from result group by run_id;
I have an index ...
2
votes
4answers
504 views
Remove sub-string from WHERE clause
I have a stored procedure that is using the SUBSTRING function in the WHERE clause.
SELECT DISTINCT
ColumnBB,
ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;
...
2
votes
3answers
181 views
selecting multiple instances of a record based on record lifespan over years
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
2
votes
3answers
173 views
Is it better to seperate a big query into multiple smaller queries?
There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using ...
2
votes
2answers
95 views
which option is better for not slow down the performance of queries?
I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
1answer
185 views
Why is the following query so bad performance-wise
After asking this question, I got to thinking about why a certain query was doing all the problems.
In short, there was a query which took 500ms, I ran some execution plans and applied the ...
2
votes
2answers
450 views
Why won't SQL Server optimize the UNIONs?
Consider these queries (SQL Fiddle):
Query 1:
SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;
Query 2:
SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
...
2
votes
2answers
98 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 ...
2
votes
1answer
142 views
Optimize simple query in SQL Server
This should be a quite easy query, but I honestly think its execution time can be improved.
select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
2
votes
3answers
91 views
mariadb ignoring force index
I'm trying to tune a relatively simple query:
select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
...
