0
votes
1answer
124 views

Optimization for 100 GB Real-time info table

I have a table of device information feed which is filled by data from remote device units. There will be like 1000 units each sending an update per minute which will be stored as a row in that ...
1
vote
1answer
783 views

What are alternatives to Replication to distibute data (Microsoft SQL Server)

Is anyone knowledgeable of alternatives to SQL Server replication? I have a scenario where separate databases will share a lot of the same data which complex queries will match against. We want to ...
0
votes
1answer
478 views

change display of rows into single row in SQL server 2008 using dynamic PIVOTS

I have 2 tables - USE [Test1July] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Hotels]( [SeqID] [int] IDENTITY(1,1) NOT NULL, [HotelName] [nchar](25) NULL, ...
3
votes
1answer
595 views

ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT? What are the benefits between each other?
0
votes
2answers
3k views

ORA-00227: corrupt block detected in control file

I am getting the following error while starting the sqlplus (I am using Oracle XE 11.2.0) ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 0 Serial number: 0 So, I ...
-1
votes
3answers
984 views

Full table scan even after adding index

i have two tables in Oracle 11g server one is partitioned and another is having a 3GB of data. I've added a composite index on both the tables based on the where clause, in the order it appears. The ...
1
vote
1answer
889 views

Time series and gap filling [closed]

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
6
votes
3answers
1k views

Two tables with same name in MySQL

I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that: ...
-1
votes
1answer
145 views

how to link category, stock , purchase and sales tables in a erp

i have a database design like the following: i have a table for storing camera details and then different tables for stock, purchase_stock, purchase_order, delivery_challan and sales_invoice. The ...
1
vote
1answer
1k views

How to Reclaim Data Free in InnoDB Tables?

I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) STEP 02) Drop ...
1
vote
1answer
485 views

How to store multiple values in a column in database?

I'm trying to make three tables in phpMyAdmin. The main columns here are named Campaign, Program and Asset. Under Campaign, there are different kinds of Programs. And under each Program, there are ...
-1
votes
2answers
70 views

query multiple unrelated tables

Just learning mysql and db so guess this is a general schema design question. Say i have 10 different types of widgets and each widget transaction has a date, amount and 8 other unique columns to that ...
0
votes
1answer
10k views

Problems during installation with Database Engine Service

Overall summary: Final result: Failed: see details below Exit code (Decimal): -2061893606 Start time: 2014-07-04 13:40:51 End time: ...
2
votes
1answer
582 views

Fastest way to migrate database to AlwaysOn cluster

I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery. Some databases are ...
0
votes
0answers
161 views

Performance: Interleaving several statement handles in MySQL

Does interleaving several statement handles slow the system down? Example (Perl): $sth1->execute(...); $sth2->execute(...); $sth1->execute(...); $sth2->execute(...); (interleaved) vs ...
1
vote
1answer
132 views

How to configure read transactions to never cause other write serialization transactions to fail

I am protecting critical transactions with application mutexes and SERIALIZABLE. I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures ...
3
votes
1answer
195 views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
0
votes
2answers
169 views

All queries on the server have slowed down, cpu is normal, plenty of free memory but SQL is using very little

The other day I logged onto our server to see SQL was using 100% cpu and the server was slowed to a crawl. I decided to stop SQL and restart it. However, it refused to stop properly and stopped ...
0
votes
1answer
78 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
1
vote
1answer
299 views

Personal and Global messaging database design

I am trying to implement a database design system that will be sending messages to a person(thus 1 to 1) or many persons (1 to many). table --users |u_id | Name | | ...
1
vote
1answer
118 views

MYSQL testing string field contains another altered string field

I have this table : mysql> desc mytab; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
28 views

SHOW INNODB STATUS averages calculated

I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed ...
1
vote
3answers
287 views

Relational Database design best practice

I am a student and I am trying to design a simple database, so if the ideas seem bad please let me know how to improve. I have three tables in my mind, one for Professors and one for Students. Both ...
0
votes
1answer
712 views

Create and fill temp table

I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure. I define it as follows: create table ...
0
votes
1answer
73 views

Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6

I have the following demo data (taken from Adventureworks) SalesOrderNumber SalesOrderDetailID SO43659 12 SO43659 11 SO43659 8 SO43659 9 SO43660 ...
1
vote
1answer
639 views

Problem with pgpoolAdmin installation

I got a problem with permissions during installation of pgpoolAdmin(newest version). The problem is when I get to window with main config that should be all okay to move to next installtion step. The ...
0
votes
4answers
115 views

Use SQL or NoSQL? [closed]

I'm designing a system that checks a given website for any security vulnerabilities. The system includes a client (firefox plugin) and a server. The server does all the scanning while the client just ...
1
vote
1answer
696 views

How does Sybase store datatime information?

We have a Sybase database that currently stores datetime values in some of the tables. If we move that server with the Sybase database to another country which is in another time zone, will the ...
0
votes
1answer
69 views

Oracle: Replace corrupted first datafile

A datafile associated with a tablespace has been corrupted (got truncated to size 0). I don't mind losing the data, because I have a full DB dump which I can import. Now I want to replace this ...
1
vote
3answers
477 views

Matching 3 columns in row - if one column is null then exclude it from comparison

A B C Equal 1 1 1 Y 2 2 NULL N Hi, I have 3 columns which I need to compare : Columns A,B,C. If these 3 columns are the same then column 'Equal' = 'Y' But, if a column is null, ...
2
votes
1answer
1k views

MS SQL - export tables to format compatible with PostgreSQL

We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing. MS SQL Server has an export function but the only one that looks like it would work would be to export ...
1
vote
1answer
45 views

Understanding how relationship sides works

So, I have this relations on my tables (see image) and I need to understand some things. I'll enumerate what I understand and what I need from yours is to say if I'm right or not and in case I'm not ...
0
votes
1answer
6k views

Using mysql's load infile to only populate certain columns

I have a csv file with a lot of data in it and I want to upload it into a mysql table. However, I would like to keep one column (primary id) empty so I can edit it myself. In mysql's documentation it ...
10
votes
1answer
546 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
2
votes
1answer
511 views

Set maximum cpu usage to a single job in SQL server

Is it possible to restrict cpu usage for a job in SQL Server? I found Resource Governor does work for an instance of mssql for a specific users group. However, I only need to set the maximum cpu ...
0
votes
1answer
66 views

Indexes and their uses [closed]

Index improve performance in select statement, but what is the difference between this create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2) and this ...
0
votes
0answers
453 views

tsql to find out sql server alias

Using TSQL, is there any way to find out SQL server alias configured at configuration manager? Such as follows? Thanks!
1
vote
2answers
72 views

mysqld is not producing binary log

I'm trying to understand why mysqld is not writing binary log anymore... [root@ip-10-179-155-214 mysql]# grep ^log-bin /etc/my.cnf log-bin=mysql-bin [root@ip-10-179-155-214 mysql]# ll mysql-bin.* ...
2
votes
1answer
190 views

TSQL- Column DataType Length

Can anyone please explain me the below one. I used the below query in getting the table columns info, but the max length seems to be x*2 of the actual col length. I mean if I have a col with datatype ...
0
votes
1answer
647 views

Optimizing large table in mysql

I'm running mysql 5.6 and I am trying to optimize a large table without mysql recreating it. As a dry run I ran the same command (optimize table) on a smaller table in the same DB and mysql insists on ...
0
votes
0answers
31 views

Rebuilding an index not working? [duplicate]

I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see ...
2
votes
2answers
69 views

Is there a good way to select a row where the key represents the lower bound of a range?

I am designing some tables and a number of the tables have as the key a lower and upper bound which never overlap. I want to query by a value that can fall withing any of those ranges. If I keep both ...
2
votes
1answer
2k views

SQL Server many-to-many relationship, row reuse?

Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in ...
1
vote
2answers
5k views

How to remove a table partition

I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure ...
1
vote
1answer
317 views

How can I split Date column values in Access into 2 columns?

I have a date column that stores values for begining to end date as: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114, any suggestions? This ...
0
votes
0answers
50 views

Postgres sync database

I have two postgres databases. Master = master_database Slave = database All initial changes are made to master. Upon admin approval, the version is updated, and I want all changes to be copied ...
1
vote
2answers
2k views

How to activate load balancer in sharded MongoDB

I have a very simple MongoDB database structure. Also I have 5 shards in it (3 of them are replicasets). But we testing DB loading and it seems that load balancing doesn't currently enabled. On mongos ...
1
vote
2answers
227 views

Any way to force “garbage collection” in TokuDB?

Playing around with TokuDB I'm finding even after "optimize table" for things such as changing row compression or other DDLs it takes a non deterministic amount of time for freed space to be reflected ...
0
votes
0answers
87 views

How to improve performance joining tables with no primary keys

Given two large tables of ~8,000,000 rows each. They are being joined on substrings of name address zipcode Naturally, the query is taking a long time. What are the best methods to improve it
1
vote
1answer
59 views

Adding a group of rows in postgresql based on column id

I have a table with a sets of rows such as displayed below: Qty Equip_ID 21487.92;"Load Center-6" 99272.59;"Load Center-10" 68088.61;"Load Center-11" 21821.5;;"Load Center-6" 102165.2;"Load ...

15 30 50 per page