A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
1
vote
1answer
97 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
3answers
193 views
optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
1answer
63 views
Will these queries return the same results?
I have this simple query:
SELECT
friend_id
FROM
default_friend
WHERE
user_id = 1
And the results are below:
+-----------+
| friend_id |
+-----------+
| 2250 |
| 4901 |
| ...
2
votes
1answer
128 views
Need some help with this query get all status and their associated comments of me and all my friends
I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
1
vote
1answer
103 views
Several values from different tables
I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
3
votes
2answers
186 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
1
vote
0answers
85 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 ...
0
votes
1answer
138 views
Is possible to optimize this query or it's already optimized?
I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
...
6
votes
1answer
900 views
OPTION FORCE ORDER improves performance until rows are deleted
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.
...
3
votes
2answers
137 views
Sudden Query Performance Degradation On A Linked Server
I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
2
votes
2answers
367 views
How does compound index help speed up query?
Say we have a bunch of column in mysql table.
Latitude Longitude
Say we make an index (Latitude and Longitude)
Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
0
votes
0answers
101 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
1
vote
1answer
215 views
SQL Server 2012 Performance Test
I've been given a task to test where an SQL server 2012 can achieve better performances - we have two options:
Use the SQL Server as an On-Premise machine (Standalone server that we have)
Use the ...
1
vote
1answer
522 views
PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation:
We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
3
votes
1answer
284 views
Query taking long on table with low record count
I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.
...
1
vote
2answers
100 views
CPU or Page Reads
While comparing query performance should i go for CPU values or Page Reads. On live server (with better hardware) a query is showing different CPU when i test it on my local machine. But Page Reads ...
2
votes
1answer
162 views
Why are my queries getting really slow?
I own a WordPress installation, and Updated to 3.4.1.
After the upgrade, I experienced a very strange slow-down.
After all, I used the
define('SAVEQUERIES', true);
in my wp-config.php with ...
2
votes
1answer
99 views
Where to place non clustered index
Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
0
votes
1answer
81 views
Reviewing all new MySql queries on staging environment
The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter ...
2
votes
0answers
230 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 ...
0
votes
1answer
91 views
Similar queries taking different times to complete
I had the following query:
SELECT nvl(sum(adjust1),0)
FROM (
SELECT
ManyOperationsOnFieldX adjust1,
a, b, c, d, e
FROM (
SELECT
a, b, c, d, e,
SubStr(balance, ...
-3
votes
1answer
213 views
Single query to copy data from 3 tables into a single empty table
In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table.
I started saying "I will use a temp table or table variables", ...
0
votes
0answers
39 views
Resource to learn writing most efficient and highly optimized sql queries [duplicate]
Possible Duplicate:
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
Can some one point me to a great book/great books or some really solid reference/references ...
4
votes
2answers
271 views
Poor performance after changing SQL Server 2008 R2 database
Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size).
Last week I modified the database to add new a single new ...
3
votes
1answer
124 views
Get profiles values where message.sender_user_id = profile.user_id
I have this tables:
default_messages
CREATE TABLE IF NOT EXISTS `default_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`message` text ...
0
votes
1answer
213 views
Slow performance of MySQL Join Query
I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
6
votes
3answers
2k views
Improve performance of sys.dm_db_index_physical_stats
During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on ...
2
votes
0answers
69 views
In KB2658214, how does SQL Server know that “correlated AND predicates” are actually correlated?
While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article:
...
5
votes
2answers
3k views
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.
They ...
2
votes
2answers
95 views
which option is better for not slow down the performance of queries?
I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
9
votes
2answers
3k views
Slow Performance Inserting Few Rows Into Huge Table
We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
15
votes
2answers
504 views
T-SQL query using completely different plan depending on number of rows I'm updating
I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
3
votes
1answer
425 views
MySQL Handler_read_rnd_next == tablescans without indexes?
I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance.
In the MySQL Handlers Plot, i have been noticing alot of ...
2
votes
1answer
635 views
How to speed up mysql query?
I have query :
SELECT
`school`.`ARCO_name`,
`student-`.`ClassSize_7`,
`student-`.`ClassSize_8`,
`degree_o`.`degree_code`,
`accredit`.`full_faculty_3`,
...
1
vote
0answers
507 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
1answer
198 views
MySQL Partition based on month-year
Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ?
CREATE TABLE visitor_visits (
id int(10) UNSIGNED ...
3
votes
1answer
411 views
MYSQL Slow SELECT with multiple joins and ORDER BY
I have this query
SELECT
r0_.id AS id0,
r0_.deleted_at AS deleted_at1,
r0_.updated_at AS updated_at2,
r0_.created_at AS created_at3,
r0_.is_vehicle_offer AS is_vehicle_offer4,
...
1
vote
1answer
502 views
postgres composite index design
In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign ...
2
votes
1answer
360 views
MySQL 5.5.8 InnoDB Foreign Key, JOIN performance
We are using aws/rds, MySQL 5.5.8. all InnoDB.
For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
6
votes
2answers
466 views
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds).
After suspecting ...
4
votes
3answers
346 views
Some book suggestions for performance tuning
I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
1
vote
1answer
35 views
Get wall_post and profile data for several users
I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table ...
8
votes
3answers
278 views
What is retrieved from disk during a query?
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...
Do the number of columns in a particular table affect the performance of a query, ...
2
votes
1answer
94 views
Get the id_user_app if not exists in id_user_rq
I'm building a application and have this table for users relationships. I need to build some queries like for example:
Who invited me as a friend?
Who I invited to be my friend?
Who I accept as ...
2
votes
5answers
259 views
Query optimization [Oracle]
The Query:
select JOIN_RESULT.batch_id as BATCH_ID,
JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
JOIN_RESULT.brand as BRAND,
JOIN_RESULT.message_date_time as UPLOAD_DATE,
...
2
votes
1answer
296 views
How to calculate query that access system view cost in Oracle?
For a query that returns if there are any active transactions within current session ,
SELECT COUNT(*)
FROM
v$transaction t
INNER JOIN v$session s ON (t.ses_addr = s.saddr )
INNER ...
0
votes
1answer
154 views
MySQL query took 98 seconds to run but EXPLAIN looks okay
I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something?
Here's the SQL:
SELECT ...
0
votes
1answer
714 views
Is there an easy way to debug and find out why a query runs slow?
I'm running onto a weird issue.
We've got two db servers, one is development and the other one is production.
Both have same specs.
VMs (Hyper-V)
Windows Server 2008 R2 64 Bit
SQL Server 2008 R2 64 ...
2
votes
1answer
178 views
datetime index skipped for same query different params
I am running two queries with same query but different parameter and one of them is extemely slow while the other is fast. Both the results are alomost the same. (500 and 575)
Any idea why it skips ...
2
votes
1answer
123 views
Should I use an index or a hash function on a primary key if I have 100000 rows of data?
I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could ...
