A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

2
votes
1answer
175 views

Parameter triggers different execution plan, but why?

I have a table CREATE TABLE [dbo].[Numbers] ( [Date] [date] NULL, [Time] [time](3) NULL, [Value] [char](10) NULL ) and the table has > 10 Billion rows, therefore it ...
1
vote
2answers
290 views

Efficient way to query set of rows from MySQL

I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database. I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is ...
0
votes
1answer
112 views

Query taking long time

My query is taking around 540 seconds to get result. Please help me to rewrite it. Query: SELECT pd.domain, fd.passkeyid FROM portfolio.domains AS pd, fabulousdomains.domains AS fd WHERE ...
4
votes
1answer
135 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 ...
2
votes
1answer
253 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()) ...
1
vote
1answer
83 views

Dealing with large log files

I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and ...
2
votes
0answers
201 views

Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load

A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening? If I execute a SELECT * statement on any other table the results pop back ...
1
vote
2answers
303 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
4
votes
1answer
569 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 ...
2
votes
1answer
250 views

Slow query performance

My database contains 20,000 rows and the following query is slow. Please help me to speed this up: SELECT r.report_id, r.user_id, u.user_name, u.user_mail, ...
2
votes
2answers
636 views

Slow MySQL query with Order and Group by

I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query: SELECT `user_articles`.* FROM `user_articles` ...
1
vote
1answer
162 views

How can I use mongoDB efficiently in this scenario?

I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned: ...
3
votes
3answers
900 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 ...
3
votes
1answer
379 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 - ...
2
votes
1answer
822 views

Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted). Furthermore I'd like to show ...
3
votes
1answer
319 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 . ...
2
votes
1answer
103 views

Slow Query Gets Even Slower After Indexing

I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number: select * from my_table where ...
4
votes
2answers
114 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 ...
6
votes
4answers
16k 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 ...
4
votes
2answers
210 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
1answer
160 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 ...
3
votes
1answer
139 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 ...
1
vote
1answer
131 views

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
9
votes
1answer
559 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 ...
2
votes
2answers
154 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
0
votes
1answer
83 views

Long running query

This is my long running query: select * from t_keyword where id in ( select rel.element_id from t_event event join t_event_element_rel rel on event.id = rel.event_id where ...
1
vote
2answers
136 views

How to optimize this query?

Query: Select * from `t_event` where `create_user_id`=7 and (`event_create_date`)=('2012-12-18 00:00:00') and `event_type_cd`=11 and `domain_id` =602 and `job_id` =1 limit 1 Table structure: ...
2
votes
2answers
253 views

MySQL index on column comparison

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2? col1 and col2 are of DATETIME type.
11
votes
3answers
1k 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 ...
0
votes
1answer
263 views

Design database to query a date range for scheduling

I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made​​ periodically I would like to design the table for them to be as ...
1
vote
1answer
602 views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...
1
vote
1answer
235 views

Simplify and optimize a complex query

Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
2
votes
1answer
955 views

how to improve query performance

## Only 1 query which returns 10 rows. ## It took about 66 secs mysql> explain SELECT al.log_id , al.user_id , al.page_name , al.section_name , al.submit_time , al.url , ...
2
votes
3answers
258 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' ...
1
vote
1answer
190 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
0
votes
2answers
175 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
4
votes
2answers
4k 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
2k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
1
vote
1answer
99 views

Topics and posts represented by one table or two?

I'm currently building a forum component for a larger application and I'm considering different approaches to certain parts of the database schema. In particular, I am considering representing topics ...
0
votes
2answers
528 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
10
votes
1answer
4k 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 ...
2
votes
2answers
258 views

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

I have a table that has a large number of rows in it. The primary key (an auto-incrementing integer) is, by default, indexed. While waiting for a row count to be returned I did an EXPLAIN in another ...
-3
votes
2answers
723 views

Optimisation sub query SQL Server

I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 ...
2
votes
0answers
113 views

How To Analyse Index Scans

I am using merge replication with SQL Server 2012. During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
1answer
593 views

Oracle 10g using collection with sql

I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables. For example, something like create or ...
2
votes
1answer
479 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
3
votes
2answers
73 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
2answers
483 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 ...
14
votes
2answers
11k 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 ...
5
votes
2answers
2k 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 ...