Tagged Questions
0
votes
1answer
20 views
Separate table by often changed / not often changed data?
I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.
...
0
votes
2answers
19 views
Partitioning MySQL for “expired” transactions to improve performance
I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly ...
0
votes
1answer
48 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
1
vote
1answer
74 views
How to enhance the SQL query performance for thousands and millions of huge data
Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
5
votes
3answers
154 views
How to optimize a query that's running slow on Nested Loops (Inner Join)
Consider the following simple query (only 3 tables involved)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
...
0
votes
2answers
71 views
Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?
Imagine you have inherited some project with extensive usage of SQL queries.
Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.
A question is: could ...
3
votes
1answer
253 views
Oracle insert through dblink performance issue
I'm trying to do an insert as select to a table via dblink. The query looks like this:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select
{...}
from edhper a, edhper b
where ...
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
0answers
45 views
Database too slow after CHARSET Conversion
I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset.
The conversion is made without any ...
1
vote
0answers
164 views
Efficiently retrieve big amount of data based on date range
Table EVENTS
TimeStamp |O.Name |Location |Source |
2008-11-12 |Pretorian|Európe |England |
2009-12-24 |Lex Rosa |Európe |Italy |
2010-01-01 |Nasdaq |USA |Whasington |
...
3
votes
1answer
424 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
1
vote
1answer
97 views
How and what is the most efficient way to join two tables, retaining a particular field from both?
I have two tables, with the following columns:
Table 1
ID
Component
Data1
Data2
Table 2
ID
Component
Data3
Data4
If I have rows in these tables, with items Component1, Component2, Component3, ...
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
1answer
73 views
Is there a standard way to replace codes with values from a lookup table for reporting or analytics?
Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
1answer
96 views
cloud computer to speed up sql big data queries?
i have been doing a lot of data mining at my new job.
we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis.
we're currently ...
3
votes
2answers
113 views
Inner Join or direct data save in SQL Server
I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
1
vote
1answer
547 views
Oracle 11g: “With Clause” very slow
I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
3
votes
1answer
206 views
Will one nvarchar column affect query performance?
I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. ...
1
vote
2answers
138 views
Derby/SQL - How to increase performance when left joining and retrieving varchar columns
Through trial and error I found that if I do a left join such as:
SELECT
firsttable.id,
secondtable.id,
secondtable.varcharColumn
FROM
firsttable
LEFT JOIN
secondtable
ON
...
0
votes
2answers
72 views
MySQL query optimisation advice for search query
We are currently in the process of trying to re-write a very large and nasty dynamic search query. The search term for this query was 'red christmas napkins' and these 3 keywords have been added to ...
0
votes
0answers
39 views
mysql select query optimization [duplicate]
Possible Duplicate:
mysql select query optimization
I have two tables, testa and testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
...
4
votes
1answer
128 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
5
votes
3answers
287 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
1
vote
2answers
261 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
1
vote
0answers
62 views
need a sql query to find out the Database wait time ratio for MySQL
Oracle supports sql queries to SYS.V_$SYSMETRIC to find out Database Wait Time Ratio (% of wait or bottlenecks experienced in time spent on user-level calls) ...
3
votes
1answer
70 views
query discrepancies on very large postgres table
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.
these two queries although similar are taking massively ...
2
votes
0answers
141 views
MySQL database structure: more columns or more rows
I collect how people tag topics with categories in table like:
ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12
This table holds 1 row per topic and dynamically ...
1
vote
0answers
133 views
Multi-Table Join to Single Destination
I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.
To ...
2
votes
1answer
35 views
In real terms, how much effect does a bad fillfactor have on performance?
There's a storm brewing, or maybe I should say firestorm. A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit.
A list of fires to be put out has been ...
2
votes
2answers
223 views
Closed loops in database model, does it affect the performance?
I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the ...
2
votes
1answer
157 views
Oracle sequence generation performance during persists of large data sets
In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does ...
2
votes
1answer
529 views
Why Is oracle ignoring my index?
This query is not too complex:
select
trunc(created_date, 'MONTH') as created_date,
al.op_name,
al.region,
al.alarm_type,
COUNT(1) as total_new,
SUM(
(SELECT
...
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
790 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 ...
8
votes
2answers
161 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
1
vote
2answers
446 views
Querying a database efficiently for a huge chunk of data
Here is my query
SELECT *
FROM
(
SELECT a.*, rownum rnum
FROM
(
SELECT id, data
FROM t
ORDER BY id
) a
...
1
vote
1answer
58 views
SQL - Need some advices for building a large db
currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:
Size: HDD has > 4tb to store filegroup if using partition tables.
...
4
votes
2answers
471 views
What are the differences between SQL I/O Sim and SQL IO tools?
I want to test my different SQL Server alternatives with a benchmark tool.
I found two tools by Microsoft:
SQLIO Disk Subsystem Benchmark Tool
SQL IO Sim
What are the differences between them? is ...
3
votes
2answers
174 views
Are these MySQL queries efficient?
It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:
1) Are these doing what I expect
2) Are there ...
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 ...
2
votes
2answers
167 views
MySQL : UNION script help
I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small.
We run the followign ...
152
votes
14answers
35k views
Can MySQL reasonably perform queries on billions of rows?
I am planning on storing scans from a mass spectrometer in a MySQL database and
would like to know whether storing and analyzing this amount of data is remotely
feasible. I know performance varies ...
5
votes
2answers
200 views
Create function in central database or repeat in each database?
One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
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 ...
3
votes
2answers
300 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
6
votes
3answers
872 views
SQL Server 2008 R2 High CPU
We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.
Our database works very hard (we run around 600,000 queries in 1 minute) and ...
-1
votes
1answer
104 views
Tutorial in performance tuning
Where can I find about tutorial of performance tuning in SQL server 2012 or 2008 R2?
The purpose is to improve my knowledge in performance tuning.
2
votes
1answer
50 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 ...
8
votes
3answers
2k views
How to boost performance of virgin queries in MS SQL Server?
I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
4
votes
1answer
330 views
In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?
I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc.
For example:
CASE WHEN ...
