Tagged Questions
0
votes
2answers
39 views
Dropping and recreating indexes
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
0
votes
2answers
59 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
80 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 ...
2
votes
2answers
103 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 ...
3
votes
1answer
80 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
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 ...
2
votes
0answers
32 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 ...
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 ...
13
votes
4answers
452 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 ...
2
votes
0answers
138 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 ...
6
votes
3answers
525 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
-3
votes
2answers
270 views
Optimisation sub query SQL Server
I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 ...
2
votes
0answers
79 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 ...
1
vote
1answer
96 views
What would be a optimal index for this query in mysql
I'm trying to optimize this query.
SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, ...
3
votes
3answers
264 views
Why does the query cost change so much and how to prevent it
The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that:
The query has an structure like:
SELECT
... a lot of stuff ...
...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
3
votes
2answers
315 views
Help to understand explain plan in Oracle
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
2
votes
1answer
293 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
9
votes
4answers
789 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
1
vote
3answers
385 views
Improve performance of a Fact Table
I have a Fact table CardTransactionFact
Table Structure
TABLE [dbo].[CardTransactionFact]
[CardTransactionID] [int] IDENTITY(1,1) NOT NULL,
[TransactionTerminalID] [int] NOT NULL,
...
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 ...
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 ...
0
votes
1answer
212 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 ...
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 ...
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 ...
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, ...
1
vote
4answers
342 views
Performance degradation while updating tables having 10s of millions of records
I want to update tables ( my be 20-30 ) having 10s of millions of records each.
The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
2
votes
2answers
1k views
Will altering order of columns in GROUP BY affect performance?
I have a SQL query like this:
SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D)
FROM SomeTable
GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END)
On a huge dataset the actual execution plan ...
3
votes
2answers
137 views
How do I count rows with two properties in one index scan in SQL?
I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.
I need to output the following: for each day I need to ...
6
votes
3answers
413 views
Single query sending and retreiving duplicate data on MS SQL Server?
Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
1
vote
3answers
193 views
Checking if the query is made async or sync on SQL Server 2008 R2
I have tried to make async calls to my SQL Server instance as you may see on my SO post:
ASP.NET MVC 4 asyncronous database call: never returns the HTTP response
I did a benchmarking and the sync ...
3
votes
1answer
2k views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
2
votes
1answer
135 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
1
vote
2answers
117 views
Improving MySQL efficiency
I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
3
votes
1answer
246 views
Can you have a WHERE clause with a greater than operator on a composite key?
I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :
CREATE TABLE `bbcmapimp_pc_raw` (
`postcode` char(8) NOT NULL,
...
3
votes
2answers
2k views
MySql - How can I speed up this query
I have the following tables:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE ...
