A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
2
votes
1answer
81 views
Oracle: How to gather stats in a logical standby database?
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
0
votes
0answers
17 views
Properly index magento mysql tables to improve specific query performance
I am running magento community edition and its db performance is horrible. I have the following query:
SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
-1
votes
1answer
36 views
what's the fastest method to export a table through sql query ? 11g - performance
I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
0
votes
1answer
74 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
75 views
Looking for performance improvements for this query
This query is taking a long time to execute. We are expecting results in 0 sec, if possible.
Please help me to do any other following:
Rewrite the query
Suggest any indexes
Any other optimization ...
4
votes
2answers
123 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
2
votes
2answers
91 views
Different queries result in big difference in performance
I have 2 similar functions, different way result in big diff in performance.
The PostgreSQL version: 9.2.1.
Function 1
create or replace function func_1() returns text as
$$
declare
v_i ...
0
votes
1answer
57 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
1
vote
1answer
58 views
Query optimization when no row is returned
I am using explain to figure out what is happening in my query which is:
explain select t from c where u1_id = 1 group by t;
I see "Using where" in the Extra column of the resultset. But this only ...
0
votes
0answers
77 views
how to find similar word with more similarities
how to Find words with length less than or equal...
declare @inp nvarchar(max),@data nvarchar(max)
set @inp='You can dance, you can jive, having .... jove... jve, ...'
set @data = 'jeve'
...
0
votes
2answers
153 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
80 views
How can I optimize this query and support multiple SKUs?
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
0
votes
0answers
15 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
2
votes
3answers
449 views
MySQL optimization - year column grouping - using temporary table, filesort
I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
0
votes
2answers
53 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
3
votes
3answers
76 views
How can I improve this query?
About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
0
votes
2answers
72 views
speeding up a query on MySql
I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.
create table t2
select field_1,
sum(ifnull(field_2,0)) ...
0
votes
2answers
49 views
How to use and optimize subquery on 100 million rows
What I'm trying to do is running a job on more than 100 million domains which haven't processed before.
I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
6
votes
2answers
21 views
Postgres performance difference on insert
I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions?
I went quite straight forward way (i know that it is not ...
2
votes
2answers
102 views
Is there a difference in performance between @date and getdate()?
Usually I use the getdate() function in my where clauses to go back in time.
Something like:
DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())
Will SQL Server 2008R2 perform faster ...
2
votes
3answers
97 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
6
votes
1answer
180 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
0
votes
1answer
34 views
Optimizing Query
Can anyone help me tuning this query.....
explain extended
SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a
LEFT OUTER JOIN
(SELECT msisdn
FROM as_treat_pre_usage_30days
GROUP BY ...
0
votes
1answer
91 views
What is the correct model for related tables with millions of rows?
I need to create a question and answer tables that will have millions (maybe billions) of rows.
The current model is:
Question Table
id_question (PK, auto increment)
id_user
question_content
...
2
votes
2answers
105 views
Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
I have a table that has a large number of rows in it.
The primary key (an auto-incrementing integer) is, by default, indexed.
While waiting for a row count to be returned I did an EXPLAIN in another ...
0
votes
1answer
69 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
0
votes
1answer
104 views
slow queries - set weight to token type in tsquery - postgresql
Postgresql version 9.2.3!
I'm working on a database for mapping of chemical names. My main table contains aprox 91 million records and it's indexed by gin. I want to query it with multiple names (I'm ...
0
votes
0answers
47 views
SQL nested select (query rows after variable), running slow
Have large table (Sybase) rows = 6276225. I'm pulling call status and my query is very slow.
In effect what I’m trying to do is pull data for any event after my variable. While querying for just one ...
1
vote
1answer
52 views
Avoiding performance hit from GROUP BY during FULLTEXT search?
Is there any clever way to avoid the performance hit from using group by during fulltext search?
SELECT p.topic_id, min(p.post_id)
FROM forum_posts AS p
WHERE MATCH (p.post_text) AGAINST ('baby ...
0
votes
1answer
34 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment i have decided to create separate database just for stored routines storage. Mainly i am using them for reporting. ...
1
vote
3answers
168 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
0
votes
1answer
20 views
Simple query not using indexes
My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?
The query:
select TIME_FORMAT(start_time,'%H:%i') start_time,
TIME_FORMAT(end_time,'%H:%i') ...
3
votes
1answer
104 views
MI Data Warehouse Advice
I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
...
0
votes
1answer
47 views
Optimizing ORDER BY for simple MySQL query
I'm trying to optimize this really simple query, and it gives me grief for a day now :(
Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
4
votes
2answers
110 views
Is high Extent Fragmentation a problem?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents ...
4
votes
3answers
214 views
Query runs slow in test site on first execution. Why?
I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
3
votes
1answer
97 views
How does order of index affect performance in SQL Server?
I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
2
votes
1answer
175 views
How to speed up queries on a large 220 million rows table (9 gig data)?
The issue:
We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in ...
3
votes
1answer
77 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
6
votes
4answers
296 views
Gaps and islands: client solution vs T-SQL query
Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?
To be specific, let us provide some test data:
CREATE TABLE dbo.Numbers
(
n INT NOT NULL
...
1
vote
1answer
131 views
Slow query performance
My database contains 20,000 rows and the following query is slow. Please help me to speed this up:
SELECT r.report_id,
r.user_id,
u.user_name,
u.user_mail,
...
0
votes
1answer
61 views
Speed efficient query for membership first joined, latest category from membership table (min, max)
I have the following table representing membership information:
CREATE TABLE IF NOT EXISTS `membership` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`organisation_id` int(11) NOT NULL,
...
3
votes
1answer
236 views
Need help with long running query
UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.
Thanks for taking the time to read this post - ...
2
votes
1answer
34 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
1
vote
1answer
86 views
SQL Server 2008 search joining query
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.
for example,
M_MessageMessageId=========
...
13
votes
4answers
448 views
Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
3
votes
4answers
565 views
Optimize Query with Derived Table
I have the following query
select
ps.id, ps.title
, IFNULL(s.likes,0) as num_likes
, IFNULL(s.comments,0) as num_comments
, IFNULL(s.ratings,0) as num_ratings
, IFNULL(s.views,0) as num_views
, ...
1
vote
2answers
76 views
Database and query optimizacion
I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
2
votes
2answers
70 views
MySQL index on column comparison
How do you go about setting an index to be used for a column comparison like WHERE col1 > col2?
col1 and col2 are of DATETIME type.
1
vote
0answers
78 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 ...
