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

3
votes
0answers
307 views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
2
votes
0answers
60 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
37 views

Single table row “sharding/versioning” to customize values

Introduction Hello everyone, I have done quite a bit of searching and I cannot find a solution for my specific issue. There are a few factors that complicate my situation, which I have outlined ...
2
votes
0answers
57 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
384 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
0answers
188 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 ...
2
votes
0answers
108 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
0answers
304 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
1
vote
0answers
25 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
51 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 ...
1
vote
0answers
49 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 ...
1
vote
0answers
27 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` ...
1
vote
0answers
44 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
1
vote
0answers
40 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
0answers
57 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
1
vote
0answers
138 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
1
vote
0answers
573 views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
0answers
83 views

Read vs. Write speed

In the data import procedures I have, I get external raw data and import it into tables. I don't just overwrite the data, even if it is only from one source, but before I allow an update, I check if ...
1
vote
0answers
59 views

Mysql performance issue when selecting text column

I have a performance problem when selecting rows containing text fields: SELECT id FROM message WHERE NOT(message.sender_id = 368352 AND message.is_deleted_sender = 1) AND ...
1
vote
0answers
109 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
1
vote
0answers
264 views

SOLVED: Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table

SOLVED. I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000; Now the queries are running very fast, down to ...
1
vote
0answers
144 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
0answers
799 views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
0
votes
0answers
9 views

Can't run subqueries on my server (MySQL)

I have a weird problem, where I can run sub-queries just fine on my local machine, however they seem to be hanging forever when I attempt to run them on my server. Note that my server has plenty of ...
0
votes
0answers
18 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
0
votes
0answers
49 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
0
votes
0answers
24 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. ...
0
votes
0answers
20 views

Dynamic chart data from MySQL

I'm building a chart with JS, where i'm doing a query with PHP and pass it to chart in JSON format. The problem is the whole table exported in json file gets up to 10mb, and it takes too much time ...
0
votes
0answers
32 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 ...
0
votes
0answers
52 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 ...
0
votes
0answers
32 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 ...
0
votes
0answers
24 views

Request for Help Optimizing a Query in MySQL

I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to ...
0
votes
0answers
102 views

Yet another slow query Postgresql 9.3

I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, ...
0
votes
0answers
26 views

What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

I have the following table: CREATE TABLE `wp_prize_draws_log_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `entry_date` date NOT NULL, `entry_time` datetime ...
0
votes
0answers
32 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
0
votes
0answers
82 views

Execution time vary for the same query

I have a query which looks like this: select * from ORDERDOC, ORDERPOSITION where ORDERDOC.id = ORDERPOSITION.orderId --some other conditions; Execution plan looks like this: 0 SELECT ...
0
votes
0answers
113 views

MySQL: how to determine key_len and its role in performance improvement of a query

I have a query, below is the structure of the table: CREATE TABLE `jos_tag_rules_fields` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', `label` varchar(255) ...
0
votes
0answers
47 views

MySQL, query optimization with join on huge table

I'm trying to execute a query on MySQL (Database created from a GTFS schema) I have two tables like these: trips trip_id | route_id --------------------- 1 | 409 2 | 409 3 ...
0
votes
0answers
99 views

MySQl: slow query is not using index in joining

Below is the configuration of my DB server: Apache version : Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8 PHP version : 5.3.13 MySQL version : mysqlnd 5.1.11 Windows Server 2008 Standard ...
0
votes
0answers
69 views

Query with large number of or'ed predicates on the same column?

At one place we need to query a table to determine whether any records exists that are in a subset of the same table. The columns that are queried are varchar columns containing file-paths, and we ...
0
votes
0answers
81 views

Postgres chooses a bad query plan

The following query finishes in 1.5s (which is ok, the table contains about 500M rows): explain (analyze, buffers) select sales.* from sales join product on (product.id = sales.productid) join date ...
0
votes
0answers
95 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
507 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
0
votes
0answers
57 views

My transaction's max time is going beyond a minute

I have a transaction called "updatePolicyStatus" whose response time is usually under a 3 seconds for many instances during a peak load. This transaction processes bunch of business rules and fires ...
0
votes
0answers
74 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
0answers
48 views

Compare elapsed time for 2 oracle Sql files. (2300 pairs of sql files)

Version ORA 11G I have some timing comparisons that I want to do on about 2300 Pairs of Oracle SQL statements. I'm trying to convince management that tuning of these 2300 would give them a ...
0
votes
0answers
36 views

Two-part question on how Postgres queries multiple columns and its performance

I have a Django "nominations" app that stores some personal information of nominees, e.g. name, email, shipping address. The email column has a max length of 255, where the shipping address would ...
0
votes
0answers
99 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
0
votes
0answers
86 views

Slow query with ORDER BY with index created (more than 4mins on 0.6million records

I ran into a very weird query performance situation. If I run this query without order by, it finish in millsecs, with order by, it ends up with more than 4mins. EXPLAIN SELECT `book_book`.`id`, ...
0
votes
0answers
148 views

Mysql high delay in critical peak | Professional Help request

I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33. My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 ...