A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.
2
votes
3answers
136 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, ...
2
votes
3answers
191 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 ...
2
votes
3answers
499 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 ...
2
votes
2answers
118 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
2answers
101 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 ...
0
votes
2answers
170 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 ...
4
votes
1answer
121 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:
...
2
votes
1answer
102 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 ...
1
vote
1answer
64 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
70 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 ...
0
votes
1answer
86 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
78 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
87 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 ...
-2
votes
1answer
85 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 ...
2
votes
0answers
34 views
How can one improve the performance of a query that selects on a low cardinality column in MySQL?
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
161 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
2
votes
0answers
160 views
Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load
A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening?
If I execute a
SELECT *
statement on any other table the results pop back ...
2
votes
0answers
80 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
0answers
238 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 ...
2
votes
0answers
72 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:
...
1
vote
0answers
97 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 ...
1
vote
0answers
75 views
How can I use mongoDB efficiently in this scenario?
I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned:
...
1
vote
0answers
213 views
Query tuning help needed (Hash Match and Table Scans)
I need help optimizing the following query (returning ~8k rows):
SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
FROM (
...
1
vote
0answers
86 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 ...
1
vote
0answers
540 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
0answers
30 views
Faster way to GROUP or ORDER dynamic fields?
We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results.
The table has around a million results and is growing.
The app has been ...
0
votes
0answers
9 views
Get turno.tipo, maquina.patente and operador.nombre and status, complex query
I've this DB design I'm trying to build a query where I should get turno.tipo, maquina.patente, operador.nombre and detalle_emisores.revisado` but it's a bit complex to my knowledge. I started this:
...
0
votes
0answers
40 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 ...
0
votes
0answers
93 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
0answers
76 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 ...
0
votes
0answers
22 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
...
0
votes
0answers
74 views
Performance Optimising MySQL query
I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5)
I was wondering if there is any way to enhance this query so ...
0
votes
0answers
110 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
0
votes
0answers
106 views
How to load XML data into an oracle db table ?
Is there a way to load the XML format data into an Oracle 11g DB table directly ?
I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ?
...
0
votes
0answers
103 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
votes
0answers
21 views
Why is simple timestamp comparison performing slow
I have a table with 12,582,912 rows and can't figure it out why is a simple comparison ad_hot_expire > 1371399048 so slow (SHOW PROFILES: http://i.imgur.com/6L5HQyW.png)? The subquery is faster, ...
