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

0
votes
2answers
238 views

postgresql being slow on count distinct for dates

I have a very simple, but very big, table. Its schema is like this (yadda int, yadda1 int, yaddate date, ... other stuff). Now, yaddate has an index by itself and it is also in other indexes ...
1
vote
2answers
80 views

What's the best way to translate a website [closed]

I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate. What's the best way to do it? Create a new database for every language? Create other table for ...
1
vote
0answers
38 views

Microsoft Access database performance

I wonder how MS Access manage to show long list of records when we want to browse theme in Datasheet view. 1 - Does it load limited amount of records on start-up and then load the remainder upon user ...
1
vote
0answers
89 views

Best database for fast read all in one table

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
0
votes
1answer
85 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
0
votes
2answers
356 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
3answers
199 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 ...
0
votes
0answers
53 views

Big Mysql table optimization

I have a table that I use to cache google translate API answers. Table structure is orig_string and group are indexes Now it has more that 1million rows with 35000 distinct values in group field. ...
3
votes
1answer
72 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
1
vote
1answer
384 views

How can I speed up a Postgres query containing lots of Joins with an ILIKE condition

I have an odd problem which I really don't understand. Simply put I have a join containing 4 table joins, which I believe all have appropriate indexes, but which takes a ridiculous amount of time to ...
1
vote
2answers
65 views

Oracle result cache behaviour

Oracle by default caching the query results and function results(Buffer cache). I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next ...
0
votes
2answers
75 views

Get only future records

I have the following query: SELECT master.master_id, master.showname, MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date, MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS ...
1
vote
1answer
60 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
0
votes
2answers
52 views

MySQL query optimization for legacy application

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. ...
0
votes
1answer
87 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
2
votes
2answers
940 views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
1
vote
0answers
54 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
0
votes
1answer
62 views

too many entries for one single session and serila# in V$Session_longops

I have a concern with the entries in v$session_longops. I used a query to identify query running from past 25 minutes for sid=62 which is given below: SELECT s.sid, s.serial#, ...
1
vote
1answer
271 views

Poor performance on inner join using dates and between

I am having trouble optimizing a query that does an inner join using a date range. The purposes of the query is take daily data and summarize by week. Select pcw.EndDate WeekEndDate, h.Store, ...
4
votes
2answers
201 views

Query tuning to meet timeout settings [closed]

We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual ...
0
votes
0answers
55 views

Oracle Merge performance

I've a question about the merge performance in oracle. I work in the business intelligence area of a big company and its quite common to have to move millions of records one side to other. The other ...
10
votes
3answers
562 views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
1
vote
0answers
68 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
0
votes
0answers
102 views

Remote scan in OpenXMl

I'm looking for some advice on OPENXML. Specifically, the performance. I am seeing very slow performance on a very small piece of XML. Something about this is causing a Remote Scan. Any ideas on how ...
1
vote
2answers
136 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
3
votes
2answers
2k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
0
votes
2answers
431 views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), ...
0
votes
1answer
160 views

Delete millions of rows, fast

I currently have a database with the following structure: id->varchar(9) time->datetime val01 - val20->int(11) val21 - val40->tinyint(1) id and time are the primary key. also I defined an index ...
0
votes
2answers
118 views

Instead of static list, using a table select result for IN key

I use a static list for some queries WHERE clause like WHERE STypeID IN ('12', '13', '14', '265') This list will be changed in the future; new values will be added. When list changed, I don't want ...
0
votes
1answer
67 views

Query causing Database lock problem (php/Mysql) [closed]

hi friends can anyone tell me what is the issue with my query. when i execute, it lock the Database for 5 to 10 minutes, please help me to optimize this query . SELECT u.username, ...
1
vote
1answer
92 views

Firebird query performance with text search, also some numbers

I have situation as follows: I'm having a table OFERTY of 10k+ records, and when I select * from it, I get PLAN (OFERTY NATURAL) Executing... Done. 656 fetches, 0 marks, 0 reads, 0 writes. 0 inserts, ...
0
votes
2answers
55 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
4
votes
2answers
75 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 ...
-1
votes
1answer
149 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
1
vote
1answer
66 views

How can I improve performance of update query in PostgreSQL

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth). If the heigth is between 0 and 6, I want to fill ...
-4
votes
2answers
78 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
0
votes
0answers
47 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
4
votes
1answer
108 views

Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

Background I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of ...
1
vote
1answer
55 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = ...
1
vote
1answer
156 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
1
vote
1answer
162 views

MySQL with slow queries

I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page. I ...
0
votes
1answer
1k views

How index work in oracle? [duplicate]

I want to understand : How index work in oracle. How they are stored in the disk. How they increase the efficiency of a query? By third point I means how Index contribute to the efficiency of ...
3
votes
1answer
220 views

Most efficient queries on flags in TSQL: bit field or table join?

Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
0
votes
2answers
148 views

What is the performance difference between IN (42) and id = 42 in MySQL?

Are there any performance differences between SELECT * FROM table WHERE id IN (42); and SELECT * FROM table WHERE id = 42; ? The question is about just supplying a single value. The ...
0
votes
1answer
58 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
2answers
65 views

How should I structure my time based index?

So I have this table in SQL Server 2008. Let's just say it's called TimeSheets. Now, that table has two columns I am concerned with. TimeSheetDate and UserCustomerKey. TimeSheetDate is a ...
0
votes
1answer
175 views

MySQL: Different thread states in Query execution

My MySQL 5.1 bases Intranet system is facing very bad performance during high load. When observe through MonYog, I found below thread states: We can simply understand the different thread state by ...
3
votes
2answers
748 views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
2
votes
2answers
383 views

Query hangs forever at first but works after one day

I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it First I rename the original tables in schema B Then, using SQL Developer I copy the table ...
1
vote
1answer
299 views

How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ...