0
votes
0answers
3 views

Text Storage and Database Design Optimization in a SQL Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
2answers
54 views

DB Design Payments Table

I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to ...
0
votes
2answers
105 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
7
votes
3answers
173 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
3
votes
2answers
50 views

SQL Server Baseline and Performance Optimization

I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance. Could anyone help with this or point me to where I can possibly get a ...
0
votes
1answer
64 views

Need help optimizing query to prevent full table scans

I have the following query, which while it works, is also doing a full scan on each left joined table (the log* tables). I thought by using a subquery in the FROM that this would eliminate the ...
0
votes
1answer
33 views

Efficient matching rows across two tables with huge data set

I have two tables with about 300+ columns and 20k+ rows each. Each row is characterized by a primary key which both tables have.I have to find the identical rows, rows with changes,and rows without a ...
1
vote
2answers
129 views

Building A SQL table horizontally VS vertically

What is a better design principle when designing SQL Server tables. One can either add columns (ie grow horizontally) or grow it vertically (ie rather adding rows) I have a situation when there are ...
5
votes
1answer
326 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
4
votes
1answer
86 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
0
votes
1answer
46 views

How can I apply heuristics to optimise an expression tree?

I've converted the following SQL query: SELECT name FROM dog d, works_at w WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders"); into the following expression tree: My main ...
0
votes
0answers
56 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
1
vote
1answer
145 views

Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in ...
1
vote
0answers
53 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
1answer
197 views

Can I rely on this SQL shortcut to save a query?

Consider a table with a status field that holds one of two values: | status | set('active','default') | YES | | active | I need to know how many records are in the table, and additionally how ...
0
votes
0answers
40 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
1answer
202 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
2
votes
1answer
36 views

General purpose view for last value in a group

I am trying to create a view to show the last value in the group. Looking for a more general purpose query. CREATE TABLE a ( id INTEGER, seq INTEGER, val VARCHAR2(16), PRIMARY ...
4
votes
4answers
104 views

Optimize MySQL Query using LEFT JOIN

I have a query with execution plan as below and is taking more than 20 seconds to execute how can i optimize it EXPLAIN SELECT c.coupon_id as c_id, `c` . *, `st`.`name` AS `store` FROM `coupon` AS ...
0
votes
0answers
142 views

how I can set tmp_table_size for 16GB long table?

I am posting the same question as I posted in SO, but somebody advice me to post it here. I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT ...
0
votes
0answers
39 views

Tips for improving my particular batch processing

I'm working with mail data from the USPS, which contains 10 related tables to make up a single mailing, as well as a single table containing all of the scans that each mail piece may receive. Once per ...
0
votes
1answer
34 views

Oracle sql plan for a select with procedure in terms section

Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query ...
4
votes
1answer
202 views

SQL query filled Temdb complete Harddisk [closed]

I have a query which was earlier fine but now it is running me out of hard disk space with message that tempdb is full. My Harddisk has 90 gb of space as soon as i run query temdb runs out of space in ...
0
votes
2answers
189 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
2
votes
0answers
346 views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
1
vote
2answers
65 views

SQL Server 2000 optimzation job hasn't run for a while

Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database. We turned it ...
0
votes
1answer
394 views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE ...
5
votes
2answers
287 views

How to optimize NOT IN statement

Why does NOT IN performs INDEX SCAN instead of INDEX SEEK? Here's my current index on the table, CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc] ON [dbo].[courselist] ( [c_desc] ...
1
vote
2answers
48 views

Convert to JOIN [duplicate]

I have a correlated sub query, which is taking 16 Minutes to execute how can i convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE SSF.SM_StockCode = ...
2
votes
1answer
333 views

How to optimize a query that includes counts of rows in multiple tables?

My query looks like this: EXPLAIN SELECT ... FROM USER u LEFT OUTER JOIN (SELECT COUNT(*) notified_count, user_id FROM user_email_tracking ON u.id = email_tracking WHERE email_type_id = 4 ...
0
votes
1answer
753 views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
0answers
74 views

Vertica pagination

I have a query that gives the total number of rows to use in a pagination: select count(1) as total_rows from (subquery with some sum() and 2 inner joins) as as total_sums 1 - when I run only the ...
1
vote
1answer
256 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
1
vote
0answers
78 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
0
votes
1answer
52 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
2answers
258 views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
6
votes
2answers
2k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
2
votes
2answers
191 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
1answer
190 views

How to profile a particularly slow or inefficient query

I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries. ...
4
votes
1answer
375 views

ORDER BY optimization for a large table

I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest. Consider a table posts posts { id ...
2
votes
2answers
1k views

Optimizing mysql queries running on 50 million rows

Is there any technique/advice in order to optimize the performance of the queries below? According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
0
votes
0answers
596 views

Using something better than UNION for combining multiple queries

I have one big query which is based on 12 smaller queries that I have put together using UNION. A relative simple example of my query: (SELECT 'type1' AS 'type', fieldOne AS someField, ...
0
votes
1answer
28 views

Separate table by often changed / not often changed data?

I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely. ...
4
votes
1answer
544 views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
1
vote
2answers
292 views

Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Imagine you have inherited some project with extensive usage of SQL queries. Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point. A question is: could ...
0
votes
1answer
373 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
0
votes
1answer
99 views

What index to add in case of join by two optional fields

I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional. ...
0
votes
1answer
63 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...
4
votes
1answer
1k views

Indexing strategy when using the between operator SQL Server 2008

I have a large table ~25 million rows with the structure CREATE TABLE [dbo].[rx]( [pat_id] [int] NOT NULL, [fill_Date] [date] NOT NULL, [script_End_Date] AS ...
4
votes
1answer
223 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...