Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

2
votes
0answers
236 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
2
votes
0answers
78 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
2
votes
0answers
70 views

Network and memory waits after massive ALTER

We had to do a massive ALTER to our DB, changing one central table's PK to BIGINT due to approaching ID's generated in the 2 billion range. Due to this, several hundred referencing keys and indexes ...
2
votes
0answers
858 views

Best practice - SQL Schema to store Timerecords for a Time and Attendance module

I'm starting a time and attendance module in C# and SQL. There will be many time rules depending on every shift. For example: Shift 7:00 - 14:00 for Administratives: 15 min grace time, but if ...
2
votes
0answers
121 views

SQL Server 2012 High availability Groups for OLTP and Reporting environments

I would like to seek your advice in designing a high availability architecture at my current work place. We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases ...
2
votes
0answers
343 views

Empty Space in buffer pool

I was doing some reading and came across this article from Paul Randal: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/ Using the queries from that article, I ...
2
votes
0answers
67 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2
votes
0answers
159 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
2
votes
0answers
1k views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
2
votes
0answers
94 views

SQL Server 2012 Replication

Please help me out with a problem I am facing. I have a table T. I altered the table to alter the column C from varchar (10) to varchar (100). Now, there is a view V that is schema bound and selects ...
2
votes
0answers
2k views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
2
votes
0answers
1k views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
1k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
2
votes
0answers
231 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
2
votes
0answers
193 views

QBE Query By Example. How does it handle OR conditions?

I've been looking at implementing QBE into an application but need more information on how more complex queries work. How does QBE handle "OR" conditions? For example when searching a telephone book ...
2
votes
0answers
661 views

SQLite Performance Tuning

I have the following query, which fetches data from only one table. It is a query which is supposed to return data for an autocomplete function. Autocomplete data can be in text1 or text2. The ...
2
votes
0answers
216 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 ...
2
votes
0answers
1k 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 ...
2
votes
0answers
379 views

What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
2
votes
0answers
139 views

sql data to a csv-file via cobol

I 'm using the following code : string 'insert into ' delimited by size 'tgamGameTeams ' delimited by size '(Id,Name,RemainingBudget,TeamGamePoints,GameId) ' ...
1
vote
0answers
20 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
1
vote
0answers
19 views

Database Design for Sports Stats

I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to design a table when both data for basketball and ...
1
vote
0answers
27 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
1
vote
0answers
22 views

How to design for scalable set operations?

This is the requirement for an educational application to teach words to language learners: Learner can declare that he/she knows a given word Learner can memorize the given word (to be managed by ...
1
vote
0answers
74 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped ...
1
vote
0answers
29 views

Is this database in 1NF, 2NF and 3NF?

I'm doing a software development course and I have to normalise my tables as part of it. Can anyone double check that my data is normalised correctly? The database is for a gym. It has these tables ...
1
vote
0answers
34 views

SQL Server dynamic inserts based on another table's rows

I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert ...
1
vote
0answers
36 views

use IFNULL in laravel

I have a query in laravel which is working fine. $subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id') ...
1
vote
0answers
33 views

Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks: Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
1
vote
0answers
19 views

Clustered index on last_update_at timestamp

I have a Votes table , which can be considered a wrapper. I have a sub_items table, which holds one votable item. One Vote has many sub_items. On sub_item is owned by only one Vote. I have a ...
1
vote
0answers
46 views

Multiple conditional counts in the select clause - what's the best way?

(Bit of a long one, sorry) To set the scene: N.b. This is on SQL SERVER 2012 We have a table [CardLog] in an access control system database. Every time an ID card is printed, a row is added to this ...
1
vote
0answers
40 views

Convert specific column into row as output in sql server

There are two tables Eligibility Claim In eligibility table if employee has categoryId like MED_COPAY, MED_COINS, RX_DED, RX_COPAY then Dependent should also eligible for these categories. So ...
1
vote
0answers
64 views

Configure Oracle database (first time user)

I am trying to learn how to use Oracle Database. I downloaded the Standard Edition version 12c from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html. I ...
1
vote
0answers
31 views

Joining and combining MySQL tables from Informix

I previously asked this in StackOverflow, But I couldn't find a solution, so I thought I'd share my problem here too. I have an old system that uses Informix for data storage, and I'm working on ...
1
vote
0answers
67 views

High CPU usage by only 4 users

New as DBA and I feel really confused with this... 4 users working, doing stuff with macros, commited_snapshot ON, 90% cpu ran sp_blitz and found that 1 query is using more than 50 plan caches... ...
1
vote
0answers
8 views

Is it possible to index serial8 type column for BTS in Informix

Trying to Index an Informix Database for Basic Text Search. One of the columns is of type Serial8. Is there a way or is it possible to index serial8 column for BTS, without changing the type of the ...
1
vote
0answers
16 views

ROW_NUMBER() window_agg rows estimation

PostgreSQL 9.4 Consider the following pretty simlpe query: EXPLAIN ANALYZE SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY date DESC NULLS LAST) rn ...
1
vote
0answers
19 views

Question on comparing two fields in Access

I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes. Here is my enrolment table, I have added a field called pass/fail as you can see which is ...
1
vote
0answers
43 views

Change login name for all objects on server

Example, I have a create a windows login DOMAIN\GROUP01, and mapped that login to a database DATABASE01 with lets say DB_OWNER permissions I want to change the login name, I went into Active ...
1
vote
0answers
100 views

Unexpected SYSDATE behavior

I have a SQL trigger which adds the old value ,new value and time of update for column based on update, insert, update or delete. To update the time we used SYSDATEand inserted record from trigger. ...
1
vote
0answers
48 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
1
vote
0answers
43 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
1
vote
0answers
36 views

Merge databases with sql only: firefox places and visits

I have two tables in each of two dbs: places: id, url (unique), visits: id, place_id, timestamp, type. I need to merge those databases: There are one main database and one "to-merge". The unique ...
1
vote
0answers
85 views

High load on server because of one query

I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem ...
1
vote
0answers
30 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
1
vote
0answers
71 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
1
vote
0answers
64 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
1
vote
0answers
98 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
50 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for ...
1
vote
0answers
27 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...