In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

learn more… | top users | synonyms (1)

1
vote
1answer
46 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
0answers
91 views

Join on “poor” table

I have some data on same legacy tables that we've inherited. From those tables I need to make a query from 3 them, but the seems like the join for one of them is taking for ever! The query itself in ...
0
votes
1answer
47 views

get all the count of sub category from the product table in single query with mysql optimization [closed]

Table structure: CREATE TABLE some_table ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `Product_model` varchar(255) NOT NULL, `product_discount` ...
5
votes
3answers
651 views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
0
votes
1answer
69 views

Finding the right pattern for creating indexes,redesign query structure

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables. Because the logic of the initial query was to bring data based on ...
1
vote
2answers
480 views

MySQL InnoDB page_cleaner settings might not be optimal

Seeing this note in mysqld.log: [Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.) There seems to be ...
0
votes
1answer
43 views

Insert query optimization

Need your help optimising an insert query. I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2. Table 2 contains a couple of million records. ...
1
vote
1answer
100 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
0
votes
0answers
38 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
2
votes
1answer
72 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
38 views

Better ways than rownum limits on a union all for fallback-style queries?

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to ...
-1
votes
2answers
230 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
0
votes
1answer
25 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...
1
vote
1answer
52 views

Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

I am building a database in PostgreSQL for financial data, where the table looks like this: create table fin_data( brokerid text, stock int, holding bigint, stake float, value ...
0
votes
2answers
76 views

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID ...
1
vote
0answers
13 views

Variables for Merging MySQL InnoDB Tables

I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is ...
1
vote
2answers
85 views

simple SELECT EXISTS takes about two hours to execute

I have mysql server hosted. Now, If I issue simple SQL statement SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND ...
0
votes
0answers
34 views

Rewrite a Stored Procedure

i would like your help in the best way to tune and re-write a stored procedure. it is consuming a lot of time and don't know what should i do to make it faster CREATE PROCEDURE [dbo].[xxxxxxxxxxxx] ...
0
votes
0answers
26 views

Enormous MySQL 5.5 Handler_read_rnd_next

The value of Handler_read_rnd_next is actually 91,3 G on my Database server but I am pretty sure that all tables have proper indexes. I am 100% sure that there is no query with a WHERE clause that ...
0
votes
0answers
40 views

Postgresql configuration for tiny server and append-only data

Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware. It is: 512M memory 1 CPU My database works with these conditions: Data is never ...
0
votes
0answers
47 views

MySQL query hanging while copying to tmp table

I have a query that ran in 10+ seconds last week, but now is hanging even after 10 minutes. I'm running this in a test environment so the data hasn't changed much and I am baffled at what could be ...
0
votes
0answers
33 views

Why is one table taking up ~75% of time for DELETE

I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables. Two of the tables (Result, Amount) have ...
0
votes
1answer
81 views

MYSQL join on subquery optimization

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly ...
0
votes
1answer
45 views

Optimal design for 90mm row clickstream MySQL

Question Which optimizations should I employ to make this DB manageable? I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are ...
1
vote
1answer
24 views

How can I find out what stats objects were used during a SQL Server 2014 query?

I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012. They ...
0
votes
1answer
117 views

Optimizing queries vs. adding indexes

I have this very old and SLOW query that I am trying to optimize, but I am not sure I can do anything to it, but add more indexes on columns involved in WHERE, JOIN and ORDER BY. Query: SELECT TOP ...
0
votes
1answer
74 views

What's the biggest bottleneck (time) in writing data to a SQL database?

I'm running an ETL process that is writing about 2 million rows to a SQL Server database. I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story). I'm ...
2
votes
1answer
63 views

Why OUTER APPLY forces scanning the entire table?

I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following ...
0
votes
2answers
31 views

mySQL efficiency with inner join queries

I have two tables, both with about 1,000,000 rows. Here is the question I am trying to get mySQL to solve: return all record_ids from table_a where that id matches the cat_id in table_b, but only ...
4
votes
5answers
400 views

How to optimize very slow SELECT with LEFT JOINs over big tables

I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case. My tables: persons (~10M rows) attributes (location, ...
2
votes
2answers
59 views

Does my table need a primary key and clustered index change?

I have a table holding 1.7 Milion rows. Definition: CREATE TABLE T( [ID] [uniqueidentifier] NOT NULL, [AID] [uniqueidentifier] NOT NULL, [BID] [uniqueidentifier] NOT NULL, [iType] [int] NOT NULL, ...
1
vote
1answer
106 views

Create index clustered X NonClustered with include

I have a table like : create table x (a varchar(20), b varchar(30), c varchar(30), primary key clustered(a asc)) so I'm wondering if I create an index like: Create nonclustered index test on x(a) ...
0
votes
0answers
29 views

What is the fastest way to do a REGEXP find and Replace in MySQL database?

I have a table with 700,000 entries and I need to check each entry for 1,000,000 words and then replace the word if found from hello to #~hello~#. Words can occur multiple times in an entry and need ...
5
votes
3answers
168 views

Should I split this large table into three smaller tables?

I'm designing a PostgreSQL database that will contain information about photos uploaded by my users. All users will have at least one main photo and optionally one or more public and/or private ...
0
votes
0answers
27 views

How to Normalize dump table, Perform-query, Perform-database Mysql

I need to migrate information hosting provider to our own systems, in this context I pose the following: The vendor provided us with history of transactions totaling approximately 7.5 million ...
1
vote
3answers
66 views

How to optimize SQL with WHERE and ORDER BY

I have the SQL statement: SELECT erp_orders_id FROM erp_orders o WHERE o.orders_export_time >= '2015-09-20' ORDER BY o.erp_orders_id ASC LIMIT 1 where erp_orders_id is primary key of type int. ...
1
vote
0answers
67 views

Queries which return more than 100k rows

I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema ...
0
votes
0answers
48 views

Slow query performance - aritmethic operators, not exists, subselects

In our application we have this query that takes A LOT of time to execute, considering that is frequently used (> 20 seconds). We use Hibernate as our ORM. All the tables have a clustered primary ...
1
vote
2answers
40 views

Performing efficient searches over two columns

Disclaimer: I am extremely inexperienced with databases, so forgive me if this is an ignorant question. I have a table which contains several columns, but the ones that are important are the columns ...
5
votes
2answers
105 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
-1
votes
3answers
74 views

Looking to improve query performance

This is my query: select S-Num.Title, jobs.JobNum, jobs.JobPart, Part.PartNum, jobs.RevisionNum, jobs.Seq, jobs.Parent, Part.PartDescription, ...
2
votes
1answer
25 views

what monitor before applying changes on the server? - looking for a basic answer - starting point only

these settings were decision of our development team: there are 2 settings: 1 - max degree of parallelism 2 - optimize for ad hoc workloads On OUR_DEV_SERVER: 1. Run the following script: ...
1
vote
1answer
45 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
1
vote
1answer
31 views

How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure ...
8
votes
2answers
207 views

is there a work around for when you want to put an OR inside a filtered index?

is there a work around for when you want to put an OR inside a filtered index? create index FIDX_tblbOrders_sdtmOrdCreated_INCL on dbo.tblBOrder(sdtmOrdCreated) INCLUDE (sintMarketID, ...
2
votes
1answer
36 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
1
vote
1answer
387 views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
0
votes
0answers
30 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, ...
0
votes
1answer
45 views

Is there any better way in oracle to enable parallel other than this?

I want to do do following set of operation. execute immediate "alter session enable parallel query"; execute immediate "alter session enable parallel ddl"; execute immediate "alter session set ...
0
votes
0answers
61 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...