1
vote
1answer
41 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
0
votes
1answer
24 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
1
vote
2answers
42 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 ...
1
vote
1answer
70 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 ...
0
votes
0answers
23 views

Sql Server Standard VS Enterprise

I have been bench-marking SQL Server Enterprise and Standard, Enterprise is using Partitioning by month on the table. The table only has 3 fields, Date-time (DateTimeOffeset(3)), Value (integer in the ...
3
votes
2answers
112 views

Enhance performance of update query

I have a query that is running against a table with 50 million rows. The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values. We are seeing ...
2
votes
1answer
63 views

How to monitor load on database server

Could anybody please suggest me a way to monitor the load on my database server? What perfmon counters should I monitor on daily basis and confirm that load on the server is increasing day by day [or] ...
8
votes
3answers
211 views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
11
votes
1answer
509 views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
0
votes
0answers
31 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
134 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 ...
1
vote
0answers
113 views

Postgres database zero down time production deployment

I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
0
votes
0answers
25 views

Mysql View Performance on Aggregate Functions

I've a table that contains weekly statistical infos. ex. Society - Week - Year - Data AAA Inc - 1 - 2014 - 10250 AAA Inc - 2 - 2014 - 5235 BBB Inc - 2 - 2014 - 3210 ... Over this table i need to ...
6
votes
2answers
170 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
0
votes
1answer
136 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
0
votes
1answer
176 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, ...
0
votes
0answers
67 views

Order before Group

I have this query: SELECT HOTEL.hotelname AS NAME, HOTEL.`boardbasis` AS board, HOTEL.`engine`, HOTEL.`itemcode`, Group_concat(Round(((hotel.cheapestprice / 2) + ...
3
votes
1answer
44 views

How to show the related (suggested) keywords?

We do a program for cataloging images by assigning keywords (tags) to them. An image has the following keywords "John", "Jane", "Portrait". Another image has the following keywords "John", "Jane", ...
2
votes
3answers
124 views

MySQL query extremely slow after server restart

I am managing a very large database for a custom application I wrote to keep track of visitors. The DB server was rebooted for the first time in over a year - not even to install any updates, but ...
2
votes
1answer
49 views

How can I compare the server impact of transaction replication

We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster. One of our design goals is to limit the performance impact on that cluster as much as is practical. ...
2
votes
0answers
48 views

Identical query & instance but other execution plans & performance [duplicate]

I have 2 identical queries . One is executed with parameters, the other is executed with hardcoded values. Somehow sql server is creating 2 different execution plans for the 2 queries. The first one ...
0
votes
1answer
78 views

Does the recovery model affect query speed?

Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?
5
votes
1answer
93 views

Why would SQL cast have to be expensive if used on a join column?

I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join. From multiple articles on the internet I see SQL casting is said ...
1
vote
1answer
80 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
0
votes
0answers
98 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
14 views

how I can set tmp_table_size for 16GB long table? [duplicate]

I have a 16GB big table with 8 partition. And RAM 4 GB. I am running a query - SELECT c.eveDate,SUM(c.dataVolumeDownLink)+SUM(c.dataVolumeUpLink) FROM cdr c WHERE c.evedate>='2013-10-01' GROUP ...
0
votes
1answer
44 views

Suggest performance tradeoffs

I have a an inner query that needs outer query column values as input. It looks something like this CREATE TABLE TISSET( COL1 int ,COL2 int,COL3 int) INSERT INTO TISSET VALUES (1,1,1); INSERT ...
0
votes
1answer
51 views

SQL Server 2000 Replication, Performance, and Query Priorities

I'm a developer and wanting to confirm the following: Does anyone here have experience with SQL Server Replication (transactional) on 2000? We are trying to convince the DBAs to allow this in a ...
0
votes
0answers
30 views

Recommended plan for 0min RTO and RPO

I have a Windows Server 2008R2 Server, running SQL Server 2008R2 SP2. The server currently only does a full backup once daily. The server's operational hours are between 8am and 5pm. The server's new ...
0
votes
1answer
36 views

Does size of one particular column in a database affect query performance?

I have a table user_list CREATE TABLE IF NOT EXISTS `user_list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `email` ...
0
votes
2answers
101 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
0
votes
0answers
96 views

Oracle - How can I improve this query?

We have performance problems with some queries. We always use the rank() function to get the latest entries with a status. The question is, how to speed up things. CREATE TABLE PAYMENT ( CLIENT ...
2
votes
0answers
24 views

optimizing a CTE Hierarchy [duplicate]

I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, and on the ...
0
votes
2answers
173 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 ...
1
vote
4answers
190 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
0
votes
0answers
88 views

My queries are slow under load

I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast. The same SQL are becoming bottleneck when they are ...
3
votes
1answer
59 views

Restoring with replace going incredibly quickly [duplicate]

So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database ...
1
vote
1answer
114 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
0
votes
2answers
257 views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
2answers
244 views

Slow query responce on different mysql versions, percona, MariaDB

LEFT JOIN is supposed to be faster than a NOT IN query. This is what I've read online. But practically, this seems to be opposite. I have the following query here, INSERT IGNORE INTO callrequests ...
5
votes
2answers
499 views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
0
votes
1answer
76 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
2answers
770 views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
0
votes
4answers
423 views

Sql server full text search performance with additional conditions

We have a performance problem with SQL Server (2008 R2) Full text search. When we have additional where conditions to full-text search condition, it gets too slow. Here is my simplified query: ...
2
votes
0answers
381 views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
1
vote
1answer
79 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
1
vote
1answer
1k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
2
votes
1answer
254 views

Getting the most out of SQL Server with a million rows a day [closed]

I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like: id, datetime, key_id, value key_id is a foreign key to ...
1
vote
2answers
160 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
0
votes
2answers
69 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...