InnoDB Index Doesn't Fit in Memory I am using InnoDB tables in my database and total index_size is 250 GB and my data size is 500 GB. Server has 128 GB RAM, and innodb_buffer_pool_size is set to 64 GB. Data size is increasing every day and select queries performance is degrading because of swaps. What can be done to improve select query performance?

show variables like 'innodb%' output :

innodb_adaptive_flushing        ON
innodb_adaptive_hash_index      ON
innodb_additional_mem_pool_size 4194304
innodb_autoextend_increment     8
innodb_autoinc_lock_mode        0
innodb_buffer_pool_instances    1
innodb_buffer_pool_size 68719476736
innodb_change_buffering all
innodb_checksums        ON
innodb_commit_concurrency       16
innodb_concurrency_tickets      500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite      ON
innodb_fast_shutdown    1
innodb_file_format      Antelope
innodb_file_format_check        ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_trx_commit  1
innodb_flush_method     O_DSYNC
innodb_force_load_corrupted     OFF
innodb_force_recovery   0
innodb_io_capacity      200
innodb_large_prefix     OFF
innodb_lock_wait_timeout        60
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  67108864
innodb_log_file_size    805306368
innodb_log_files_in_group       4
innodb_log_group_home_dir       ./
innodb_max_dirty_pages_pct      75
innodb_max_purge_lag    0
innodb_mirrored_log_groups      1
innodb_old_blocks_pct   37
innodb_old_blocks_time  0
innodb_open_files       300
innodb_print_all_deadlocks      OFF
innodb_purge_batch_size 20
innodb_purge_threads    0

innodb_random_read_ahead        OFF
innodb_read_ahead_threshold     56
innodb_read_io_threads  4
innodb_replication_delay        0
innodb_rollback_on_timeout      OFF
innodb_rollback_segments        128
innodb_spin_wait_delay  6
innodb_stats_method     nulls_equal
innodb_stats_on_metadata        ON
innodb_stats_sample_pages       8
innodb_strict_mode      OFF
innodb_support_xa       ON
innodb_sync_spin_loops  30
innodb_table_locks      OFF
innodb_thread_concurrency       128
innodb_thread_sleep_delay       10000
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.5.36

innodb_write_io_threads 4

Edit:

This server is a backup server and is mostly read only. Read frequency is also very low. But the queries are such that it wants to access a months data from a table.

mysql> show table status like 'TABLE_NAME'\G

Name: TABLE_NAME
Rows: 456194631
Avg_row_length: 60
Data_length: 27472723968
Index_length: 14432813056
Data_free: 14381219840

This table has data of 6 months and query is like :

select col1 from TABLE_NAME where col2 = 4 and Time_1 BETWEEN "2014-12-01" and "2014-12-30".

BTREE index is created on both col2 and Time_1.

explain query Output :

possible_keys = Time_1,Col2
key used = Col2
key_len = 3
ref = const
rows = 98126
Extra = Using where

But this query takes more than 20 minutes to execute. What can be done to improve the performance?

SUGGESTION #1
Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings:
innodb_buffer_pool_instances=2 # If your Server is DualCore
innodb_buffer_pool_instances=4 # If your Server is QuadCore
innodb_buffer_pool_instances=6 # If your Server is HexaCore
innodb_buffer_pool_size=90G

I addressed InnoDB and Swapping in one of my older posts (How do you tune MySQL for a heavy InnoDB workload?). I referred to Jeremy Cole's blog The MySQL “swap insanity” problem and the effects of the NUMA architecture. You might need to increase innodb_buffer_pool_size to 60%-75% of RAM. In your case, I would go with 90G (70.3125%).
SUGGESTION #2
For more CPU engagement I recommend these
innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_thread_concurrency=0

I discuss why in my old posts

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

OTHER SUGGESTIONS

You might more RAM on the DB Server (Setting innodb_buffer_pool_size to 60-75% of RAM)
You might need to tune your queries if you read lots of unnecessary data as indicated by the Innodb_buffer_pool_read_ahead_evicted status variable.


Is this the correct/best way to create a custom column in mysql? I needed to select certain records from a selection with a custom column that have another column's modified value as its own value. The following query just works for my needs. But I wanted to know if it's the best/right way of doing this.

/* this is just OK */
SELECT column1,column2,SUBSTRING(column2,4) AS custom_column
FROM MY_TABLE
WHERE SUBSTRING(column2,4) = 'xyz'

Also, I saw I cannot simply say

WHERE custom_column = 'xyz' /* error: custom_column doesn not exixts */

It would throw an error. That's because I used the syntax above instead. But it feels strange to me.

It is unclear whether the method in your example is right or best for you, but if you need to query or return the same expression in many different queries, you can add a custom column to the table itself. In MySQL, this is called a generated column:

ALTER TABLE
  MY_TABLE
ADD COLUMN
  custom_column VARCHAR(50) AS
  (
    SUBSTRING(column2, 4)
  )
;

This way the column will be available for reference in any part of any query on MY_TABLE:

SELECT
  column1,
  column2,
  custom_column
FROM
  MY_TABLE
WHERE
  custom_column = 'xyz'
;

A live demo of this approach is available at db<>fiddle.uk.

On the other hand, if this is a one-off occasion where you simply need to be able to reference a calculated column in the WHERE clause of one specific query, then since MySQL 8 you can define such a column using a LATERAL join to a FROM-less SELECT. Once you have defined the column in this way, you can reference it by name in any part of the query, including the WHERE clause:

SELECT
  t.column1,
  t.column2,
  c.custom_column
FROM
  MY_TABLE AS t
  CROSS JOIN LATERAL
  (
    SELECT
      SUBSTRING(column2, 4) AS custom_column
  ) AS c
WHERE
  c.custom_column = 'xyz'
;

You can play with this method in another live demo.


Insert Records That Do Not Exist We are upgrading from one database to another.  Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew.  I need a way of

  for each table on dbNew check the table on dbOld - insert all rows that exist in dbOld into dbNew

How would I do this through code?

EDIT
This is code on what I'm looking at.  Obviously there are issues, but would this be a good approach to take?

Declare @tablename varchar(max)

Declare cursor_tables Cursor

For SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_CATALOG='salesData'

OPEN cursor_tables

FETCH NEXT FROM cursor_tables INTO @tablename;

 WHILE @@FETCH_STATUS = 0
 BEGIN
   insert dbNew.dbo. + @tablename
   select *
      from dbOld.dbo. + @tablename
      except (
      select *
        from dbNew.dbo. + @tablename)

FETCH NEXT FROM cursor_tables INTO @tablename;
END;

You can not give object names from variables in TSQL, so you have to be a bit hacky and make the statement as a string and run as ad-hoc SQL. Something like:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + '
        EXCEPT
        SELECT * FROM dbNew.dbo.' + @tablename + '
        '
EXEC (@SQL)

You are likely to find the following more efficient:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + ' oTbl
        EXCEPT
        LEFT OUTER JOIN dbNew.dbo.' + @tablename + ' nTbl
        ON nTbl.primaryKey = oTlb.primaryKey
        '
EXEC (@SQL)

but that does mean you need to work out what the primary key columns are by querying more INFORMATION_SCHEMA/sys.* structures in your cursor.

Another complication that you will hit in a well designed database is that you will need to order the table population to ensure that foreign key constraints are always satisfied during the process (or remove such constraints first and readd them after the transfer).


database design storing historical information I have this database table Templates and I need to keep historical information. So in code what I am doing is inserting a new row with the updated data and then setting the  IsCurrentVersion flag to false. This is working fine but I am not sure how this will grow. Will that have performance problems? Is there a better way to do this? would a archive table be better?
Table Templates {
  id int [pk]
  Title varchar
  Added datetime
  Moddified datatime
  Deleted datetime
  IsCurrentVersion bit
}

Instead of a bit for IsCurrentVersion, I'd suggest setting up a version table and then use queries that let you pick the max version. I have several examples of the necessary code in this article. This method performs extremely well and has scaled well over time. However, there are alternatives that also work well. Between these two methods, you should be able to do exactly what you're looking for.


Deleting "child" records on delete (reverse foreign key constraint?) To illustrate my problem, I have 3 tables.

Table A AND Table B both individually have FK C_ID that reference Table C's PK.

I want Table C's rows to be deleted if their related rows in Table A or Table B is deleted.

FK constraints seem to only work one way. How do I deal with this?

To clarify, A and B has a 1:1 relationship with C. To be more precise it's a (A,B)parent-child(C) relationship where the child's existence depends on the parent's.

I would use a delete trigger to remove the records from table C when a delete occurs on table a or b.

There is a question from SO where the answer gives you a nice example:

https://stackoverflow.com/questions/11818191/mysql-trigger-delete-from-table-after-delete

But to give you the rough idea from that answer:

CREATE TRIGGER table_c_delete AFTER DELETE on table_a
FOR EACH ROW
BEGIN
DELETE FROM table_c
    WHERE table_c.parent_id = old.id;
END


Inheritance of unknown type at creation time Say I have a table called People into which people are inserted from the moment they are born. Now lets say I want some subclasses of people based on their job. So there are also tables for Plumber, Carpenter and Electrician. Now, when a person is born, I don't know if they're going to grow up to be plumbers, or carpenters, or electricians - So I can't have reference the person in every table via a foreign key.

So when little Johnny grows up, how can I bind him to being a Plumber. Is there some sort of pattern for this, or is this a stupid question with an obvious answer? Either way I'd appreciate some help, thanks!

Try searching  "single table inheritance" or "class table inheritance".  Especially the web pages from Martin Fowler.

Also, if you use a technique called "shared primary key", you can reference a person by that person's personid regardless of what occupation the person eventually chooses.  In the Plumber table, the primary key is not plumberid, but rather personid, which is a copy of the personid from persons.  You declare plumber.personid to be a primary key, and also to be a foreign key that references person.personid.

This has some rather nice results.  It makes joining plumbers and persons simple, easy, and fast.  It maintains the one-to-one or zero-to-one relationship between plumbers and persons.  And it allows you to reference a child without knowing  whether that child will grow up to be a plumber or not.


SQL SERVER Storage of TinyInt In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask.

    USE tempdb ;
    GO

    CREATE TABLE tbl
    (
        i TINYINT NOT NULL
    ) ;
    GO

    INSERT INTO tbl (i)
        VALUES (1) ;
    GO

    DBCC IND ('tempdb','tbl',-1) ;
    GO

    DBCC TRACEON (3604) ; -- Page dump will go the console
    GO

    DBCC PAGE ('tempdb',1,168,3) ;
    GO

Results (I reversed the bytes due to DBCC PAGE's showing the least significant byte first):

Record Size = 9B
10000500 01010000 00
TagA = 0x10 = 1B
TagB = 0x00 = 1B
Null Bitmap Offset = 0x0005 = 2B
Our integer column = 0x01 = 1B
Column Count = 0x0001 = 2B
NULL Bitmap = 0x0000 = 2B (what!?)

If you compute the record using the simple size addition you indeed get 8: 4+1+2+1 (header+fixed size+null bitmap count+ null bitmap itself). But a heap record cannot be smaller than the forwarding stub size, which is 9 bytes, since the record must guarantee that it can be replaced with a forwarding stub. Hence, the record will by actually 9 bytes. A smallint will be 9 bytes both by means of compute and min size. Anything bigger is already bigger than the forwarding stub, so your compute size matches the record size.


MySQL how to release memory used up by SELECT I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C.

INSERT IGNORE INTO sid(sid)
SELECT sid FROM data WHERE sid != "";

The data contains 64 million rows.

Data type of sid is VARCHAR(13). There were 28.2M records that matched the SELECT (I ran it afterwards). That results in around 350MB of data that needs to be SELECTed and INSERTed.

Is it possible to release memory allocated by MySQL? The query was terminated and there is no longer a need for that memory. Why is it still lingering?

Here are some config details:

max_allowed_packet=1024M

# Memory Table

max_heap_table_size=8192M

Everything else is default.

MySQL version 5.5.25, using MyISAM

Inserts into ndbcluster 7.2.7

NDB Config

SharedGlobalMemory=256M
DataMemory=2048M
IndexMemory=512M

My box runs 1/3 SQL nodes, management node. 2 other servers run data nodes.

You could run RESET QUERY CACHE to eliminate collected result sets in the query cache. You could also be more aggressive and drop the query cache and recreate it a different size. For example, to set the query_cache_size to 512M, do the following

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(30);
SET GLOBAL query_cache_size = 1024 * 1024 * 512;

Unfortunately, you can't release the MyISAM Key Buffer and InnoDB Buffer Pool in the same manner. You would have to resize them in /etc/my.cnf (or my.ini for Windows) and restart mysql.

Reading your comments, I can confidently say that MyISAM does not cache data pages. The query cache is the only thing I can think of that grabs and holds result sets. If you are loading in bulk, there should be a lot of query cache invalidations going on.

I am not Cluster Certified, but I will take shot at saying this: Since you are using MySQL Cluster, you may want to try shutting down any mysqld processes or having the cluster disconnect data nodes. Also, be aware of the transmission speed that data hops around the Cluster.


How to get users how do not have a group (through join table)? I want to get users how are not present in a group. The group name is "everybody".

I have a join table called groups_users with user_id and group_id.

I did this

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id
    WHERE groups.name = 'everybody'
);

But it doesn't return any result. I have a user without any group and it doesn't appear. Did I miss something?

Right now your (uncorrelated) subquery returns all user IDs, which is obviously a non-empty set. You need to correlate the subquery to the particular user from the outer query:

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id
    WHERE groups.name = 'everybody'
    AND groups_users.user_id = users.id
);


Cannot figure out how to remove this Using Temporary I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good resources for learning more about query optimization!

Query:

SELECT SQL_CALC_FOUND_ROWS DISTINCT m.id
FROM messages m
INNER JOIN message_entries t2
    ON m.id = t2.message_id
WHERE m.user_id = 1234
    AND m.type = 1
ORDER BY m.sent_on DESC;

The messages table has a covering index for (user_id, type, sent_on, created) and it uses this index for the query, and the message_entries table has an index on message_id (through foreign key).

I've tried adding id to the covering index on the messages table (at the beginning or end of the index, i've tried both), but it still chooses to use the original index for user_id, type, sent_on, created.

What can I do to improve performance when adding joins to the base query?

There is no chance to remove Using temporary; Using filesort on that query.

If m.id is unique (or even primary key), DISTINCT is excessive. If not, the ORDER BY is random.- Akina


importing postgres tar file throws parallel not supported in postgres 12 I'm trying to import a db that someone sent me: testdb.tar (43GB size)
First I've imported using:
pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-password  --verbose testdb.tar

It took 23 hours to complete.
I supose it used just a single core.
Now, trying to do it faster, using the available 16 cores, I tried:
pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-password -j 16 --verbose testdb.tar

However, it throws the following error:
pg_restore: error: parallel restore is not supported with this archive file format

Not really sure why it complains.

Because the "tar" format cannot be restored in parallel.
Use the "custom" or "directory" format of pg_dump so that it can be restored with parallel processes.
But Jeff had a good idea (see the comment): If you untar the backup into a directory, you can then do a parallel restore by pointing pg_restore to that directory.


Missing rows in Visual DBA, exist when queried specifically I have an interesting issue on which Google has failed me.
Background
I am working with medical records managed with Ingres SQL. The other day I wrote a simple query in Excel to pull all patient rows that have died in a set date range and output them as a report. This query works. I happened to notice (because of my date range) that I was missing a recent row. Upon further inspection I realized I am missing more rows, not just in this report.
Details of issue
When I query specifically for these missing rows (by selecting their specific patient ID from its column) they exist. When looking at Visual DBA at the table in which they reside and were just queried from successfully, they do not appear.
Similarly, these missing rows do not appear in the raw data pulled by my Excel query, whereas other rows meeting the same criteria do appear. I have looked for inconsistencies between rows that are showing up in Visual DBA and rows that are not. They are all consistent (null values, format, etc) and have differences only in value, not type. ie, last name is 'Jones' instead of 'Smith'.
My question
Why are rows missing from Ingres Visual DBA (table 'x' let's say) but when queried specifically in Ingres Visual SQL from table 'x' they appear?
This is a serious issue for us as rows are missing from automatically generated medical reports and vital information could be missed.
Additional Details
When in the Object Manager in Visual DBA I navigate to my table in question (say d1_17, which is sorted by ascending values of d1_17.xpid) xpid = '27245' is not there. When I query specifically in Visual SQL for d1_17.xpid = '27245' it outputs the row correctly.
They are connected to same database. Excel and Visual DBA are also in sync (both missing the same rows). The discrepancy arises when I query specifically d1_17.xpid = '27245' in Visual SQL and the row prints, and yet, when browsing in object manager, in d1_17 in Visual DBA the row with xpid = '27245' isn't there.
Every value in d1_17.xpid is non-null. Other columns may have null values, but every row is a patient and every patient has an xpid (patient ID).
While we are admins on the network for our database we unfortunately did not create it, our software team for our EHR GUI did that part as well.

(Please forgive the SQL Server test case - the problem is common to all SQL implementations because that of common semantics mandated by the SQL Standard.)

Even though you have used a LEFT OUTER JOIN, the semantics of SQL can convert this to an implied INNER JOIN if you improperly put constant-test conditions in a WHERE clause instead of the JOIN clause. The example below ilustrates this.

Preliminaries to create test data:

if object_id('Address') is not null drop table Address;
if object_id('Person') is not null drop table Person;
create table Person (
    ID          int identity    not null primary key clustered,
    Name        varchar(10)     not null unique nonclustered
);

create table Address (
    ID          int identity    not null primary key clustered,
    PersonID    int             not null references Person(ID),
    SequenceNo  int             not null,
    Address     varchar(40)     not null

    ,constraint NK unique nonclustered (PersonID,SequenceNo)
);

insert Person(Name) values
     ('Fred')
    ,('George')
    ,('Ron')
    ,('Ginny')
;

with data as(
    select * from(values
            ('Ron',   1,'Attic bedroom, The Burrows')
           ,('Ron',   2,'Junior Boys Dorm, Gryffindor House')
           ,('Fred',  1,'Blue bedroom, The Burrows')
           ,('Fred',  2,'Senior Boys Dorm, Gryffindor House')
           ,('George',1,'Green bedroom, The Burrows')
           ,('George',2,'Senior Boys Dorm, Gryffindor House')
           ,('Ginny', 1,'Pink bedroom, The Burrows')
    )data(PersonName,SequenceNo,Address)
)
insert Address(PersonID,SequenceNo,Address)
select
    Person.ID,
    SequenceNo,
    Address
from data join Person on data.PersonName = Person.Name
;

Now these two queries

select *
from Person
left join Address on Address.PersonID = Person.ID
               where SequenceNo       = 2
;
select *
from Person
left join Address on Address.PersonID = Person.ID
                 and SequenceNo       = 2
;

respectively return

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House

ID          Name       ID          PersonID    SequenceNo  Address
----------- ---------- ----------- ----------- ----------- ----------------------------------------
1           Fred       4           1           2           Senior Boys Dorm, Gryffindor House
2           George     6           2           2           Senior Boys Dorm, Gryffindor House
3           Ron        2           3           2           Junior Boys Dorm, Gryffindor House
4           Ginny      NULL        NULL        NULL        NULL

Note how, despite the LEFT OUTER JOIN which one expects to ensure that all Person rows are returned, the placement of the condition SequenceNo = 2 in the WHERE clause instead of the JOIN clause coerces the join into an INNER JOIN; so that Ginny is dropped from the first result set.

This is a specific example of how, more generally, the occurrence of a NULL value in a field being tested violates intuition. One loses the Excluded Middle, so that when a predicate A may be NULL it is no longer tautologically true that A or NOT A will give you all rows; all rows with a NULL value for A will be silently dropped.


DELETE and INSERT using OPENQUERY to Oracle require long delay between them I'm doing a DELETE followed by an INSERT on an OPENQUERY to Oracle. I have tried a straight DELETE with a WHERE as well as filtering with a JOIN. INSERT is just an INSERT OPENQUERY/SELECT. I have also tried a filtered UPDATE followed by INSERT on new rows. Since I can't do a MERGE on OPENQUERY, I must do two statements back-to-back.
DELETE, UPDATE and the INSERT work as expected, but only if I run them 10 minutes apart. Individually, they complete in less than one second. When run back-to-back, using either sp_executesql or SSMS, whether running them individually or in one script, they take 10 minutes exactly.
Talked to the Oracle DBA, doesn't ring any bells for him.
The queries themselves, as much as I would like to share them to help you help me, I am not free to do so. Please take my word for the fact that they both work as expected in less than one second when run 10 minutes apart and take 10 minutes when run together.

This turned out to be an issue with OPENQUERY and the related "inconsistent metadata" issue requiring OPTION (RECOMPILE). Switching to EXEC AT for write queries (UPDATE, INSERT and DELETE) resolved the throttling effect.


Is it possible to show DML that is locking some table? I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the query from the linked question, I was able to discover 'someTable'. I want to retrieve the full query. Is is possible?

To see each session's currently running SQL (which supposedly holds the lock) you can join v$session with v$sqltext. I don't have anywhere to test this but something along these lines (using the answer you linked to as a starting point):

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine,
   s.sql_text
from
   v$locked_object a ,
   v$session b,
   dba_objects c,
   v$sqltext s
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and s.address = b.sql_address and s.hash_value = b.sql_hash_value
and s.piece = 1
;

This will retrieve the first "piece" of the SQL statement. If you need the complete statement, see for example this link on "Ask Tom".


Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift I'm quite new to using POSIX regex in Redshift. I honestly think that this pattern must be compatible.
[^A-Za-z0-9\\.\\,-]+

However, as I've tried a query like this
SELECT * FROM my_table WHERE string_column ~ '[^A-Za-z0-9\\.\\,-]+';

it gives me a result that doesn't match with what I like, and it seems to be considering the caret as a character literal. I've also tried using [^[:alnum:]] to no avail. What could be the reason why this happens? Did I input something wrong, or is this a limitation within Redshift or the JDBC drivers?
I'm currently running queries through JetBrains DataGrip (latest version) and running it with the latest Amazon Redshift JDBC driver.

The problem with how I used this pattern is how I wrongly tried to add characters after the - character. Basically, Redshift does support the use of carets for pattern list matching negation. So this
[^A-Za-z0-9\\.\\,-]+

should not cause any problems, but
[^A-Za-z0-9\\.\\,-\\s]+

might cause problems because it might assume a range lookup instead for looking for both - and whitespace characters.


MySQL InnoDB data file directly on a disk slice (fixed-size)? It seems that ibdata1 is like a disk slice. In Solaris (UNIX) UFS filesystems (the common one before ZFS), one would separate their disk c0t0d0 into slices, say a 10GB drive would be cut into three sections, 4GB, 1GB and 5GB. These would be fixed-size filesystems. The 4GB for, say, OS. 1GB for swap, and 5GB for software and data.

swap can be stored on a file on a filesystem, but for performance it usually stored on its own section of the disk.

Can ibdata1 be linked to its own slice for performance boost? Of course one would have to think carefully before deciding what fixed size is ideal and they would also have to know how to check usage levels.

A symbolic link can be placed from .../mysql/data/ibdata1 to /dev/rdsk/c0t0d0s3. MySQL would then see it as a file, but might perform better because it does not have to go through the file-system layer, it would write directly to a designated section of the disk.

Has anyone tried this?
Does anyone think this is a bad idea? (Keep in mind, this idea is for InnoDB-only database-only servers. Not multi-use servers.)
Would it work (Does innodb need to check the file length? If not, it should work.)
Can the usage be checked?

First of all, what are the entry types that reside in ibdata1 ? Four(4) entry types:

Table MetaData
Table Data Pages
Index Data Pages
MVCC Data

Whenever an InnoDB table experiences DDL, DML, or being used in a Transaction, all four of these types of entries are either read or written. Meanwhile, if innodb_file_per_table is disabled, all these entry types live in ibdata1. If it is enabled, only the Table MetaData and the MVCC Data would reside in ibdata1 while the Table Data Pages and Index Data Pages would reside in the database subfolder as a .ibd file.

That being considered, what would happen if ibdata1 were placed in another volume and symlinked ?

For starters, how does MySQL represent a table regardless of the storage engine ? As a .frm file. Where do .frm files live ? In the datadir. What's wrong with that ?

Here is an example:

Using the default datadir of /var/lib/mysql, let's use an InnoDB table called mydb.mytable.

With innodb_file_per_table disabled, everything would sit in ibdata1 (which you are proposing to symlink and send off to another data volume). For the table mydb.mytable, this is what you would have:

/var/lib/mysql/mydb/mytable.frm
Everything else about the table lives in ibdata1

Picture this now: You access the table, MySQL would first hit /var/lib/mysql/mydb/mytable.frm and then hit the data and index pages in ibdata1 for mydb.mytable. This would constantly be happening with every access of mydb.mytable. This cascading back-and-forth would somehow make things a little slower and you may not get the performance you were expecting by moving ibdata1 to some other data volume. In fact, the cascading effect would now be a factor of the number of InnoDB tables multiplied by two(2).

Imagine having innodb_file_per_table enabled. Now you would have a slightly different setup:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
MVCC data and Table MetaData would reside in ibdata1

This cascading would be a little worse because the cascading for table access would now occur among three files instead of two.

Here is one more scenario some have thought of: Instead of moving the ibdata1 to a different volume, how about enabling innodb_file_per_table and moving the .ibd files to one or more different data volumes ? The cascading effect would now be a factor of the number of InnoDB tables multiplied by three(3). Percona has expressed very good reasons for not doing this that you will find helpful.


Select unique values for one column There is a table with different sets a(1,2), b(2,3,4) and etc.

-- table1 --
set_name | value
a        | 1
a        | 2
b        | 2
b        | 3
b        | 4
c        | 3
c        | 5
...      | ...

How to get a table containing name of set with values which is unique for all table? Result should be like this:

-- table2 --
set_name | value
a        | 1
b        | 4
c        | 5
...      | ...

Since value 2 is in set a and set b, it's excluded; since value 3 is in sets b and c, it's excluded....

In other words, I want to see the rows where the value is listed for one and only one set_name.

create table test(set_name text, value int);
insert into test values
('a', 1),('a', 2),('b', 2),('b', 3),
('b', 4),('c', 3),('c', 5);

You can use a Window function and a CTE (you can move the CTE part to a subquery), to get the desired result.

;with x as
(
    select set_name, value,
           count(value) over (partition by value) cnt
    from   test
)
select set_name, value
from   x
where  cnt = 1;

set_name | value
:------- | ----:
a        |     1
b        |     4
c        |     5

dbfiddle here


Recovering deleted records from ibdata1 Is it possible to recover deleted rows from ibdata1? I have only ibdata1 file and no other files or tables. I want to recover all table structures and datas with deleted records. Is it possible ?

Is it possible to recover deleted rows from ibdata1?

Probably not.

  My purpose is to learn how to recover tables and data from ibdata1 file.

You should not be trying to do so.

That file is in a proprietary format might could, potentially, change between DBMS versions.

In order to Restore anything about a database, you should be working with the Backup tools relevant to your DBMS.  Trying to glean anything from the shattered remains of a broken database file is a Fools Errand.


SQL SERVER dynamic Query with Like Statement for null values I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "Command(s) completed successfully" without any values.

Here is my query:

DECLARE  @articleNo VARCHAR(100), @query varchar(8000)

 SET @articleNo = NULL

SET @query =
 'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo, AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal
    FROM [dbo].[Articles] AS AT
    INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
    LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
    LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
    WHERE
    ('''+ CAST((@articleNo) AS varchar) +''' = '''' OR AT.ArticleNo LIKE ''%' + CAST((@articleNo) AS varchar) + '%'')'

    EXEC (@query)

Try This

DECLARE @articleNo VARCHAR(100)
,@query VARCHAR(8000)

SET @articleNo = NULL
SET @articleNo = COALESCE(@articleNo, '') -- SET to a blank value if NULL, you can't concatenate a NULL value into the string like you were doing.
SET @query =
'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo,
AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal
FROM [dbo].[Articles] AS AT
INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].
[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
WHERE
       (@articleNo = ''''   OR AT.ArticleNo LIKE ''%@articleNo%'') '
--DON't use EXEC (@query), you want to make sure dynamic sql is parameterized

EXEC sp_executesql @query
,@params = N'@articleNo VARCHAR(100)'
,@articleNo = @articleNo


mysql (5.5.) cmd unresponsive when selecting a huge db I had initiated on Friday night a huge (~800G) dump from my machine to a remote host;

Today, (although the command does not seem to have returned yet), I am able to log in to the machine and mysql cmd but when selecting this db, the shell becomes unresponsive:

mysql> use my-huge-db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -

_

How to debug this?

Is there a way to check if the import is still taking place?

The client message should read (emphasis mine)

  Reading table information for completion of table and column names You
  can turn off this feature to get a quicker startup with -A

where "-A" is the shortcut for --no-auto-rehash.

By Default, the MySQL client will, on connection, run queries on the INFORMATION_SCHEMA to discover

all the tables in the current database
all the columns for all the tables found

This is to support "auto completion", so that a human can type

SELECT * FROM EMPLO<TAB>

and the tool will then expand the query text to

SELECT * FROM EMPLOYEE

This feature is very costly when the database is big, and it should be disabled to improve connection time.

See the reference manual:
https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_auto-rehash


Converting Database from GUID to Integer Keys I have a medium sized database (~100 tables) that uses GUIDs as primary keys in all tables.

In one of our most active tables the GUIDs are causing an unnecessary increase in storage requirements.  The table is essentially a primary key (GUID), 4 foreign keys (GUIDs) and two datetime fields.

I would like to change the primary key and all foreign keys to integers, thus dropping the table to about a third of it's current size. There isn't a huge amount of data in the system yet, so converting existing data shouldn't be a big issue.

My main question here is, what would be the best approach?

Convert all referenced tables (and most of the tables in my database) to using integer keys.
Add an additional autoincrement integer identity to each of the reference tables to use as a foreign key, and maintain the GUIDs in those tables for outside reference (use through APIs, etc).

Your plan sounds basically OK to me. I would proceed cautiously though - add a new int column to the tables, populate it with unique values (the ROW_NUMBER() function is handy for this kind of thing) and then change it to be the primary key and check all is ok before considering dropping the GUID cols.

You perhaps should create a set of archive tables containing the GUID and PK for each of the tables that you can refer back to if you needed to.


SQL Server 2014 data integration between servers Given two MS SQL servers, each with a different database, how does one create an SSIS package such that one can run a complex query that fills a temporary table and then transfer that data as a bulk insert into a permanent table on the other server?

Today this is done by running an external program that generates insert statements for each row, but this is slow and results in a lot of transaction logging.

I have been looking for resources on how to do this online, but as per usual, MS docs are less than helpful.

I have visual studio 2013 and 2015, but am unable to find any SSIS package creation tool, even after install "DACFramework" and "SSDT".

If You already install SQL BI Pack for VS 2013 - http://www.microsoft.com/en-nz/download/details.aspx?id=42313

for create SSIS package You just need:

  "Create New Project" -> "Business Intelligence" - > "Integration Services
  Project"

Main idea of SSIS (and similar tools) - not only load data from server to server, but transformation and  check on the fly, and some of this transformations hard (or impossible) made by SQL only tools.

Bulk insert in SSIS mean load from text file, when You not need any transformation

Linked Server is good solution if Your servers have good network connections, it allow You not worry about 3rd party tools (SSIS as well 3rd party relate to SQL Server engine).

Other way - unload result of complicated SELECT into text file and use bulk load resulted flat file into destination server.


choose Username or ID as primary key? my sites dont need use ID at all, then I am trying to manage user by username.

Assume my sites is abc.com, then access to user profile by URL: abc.com/user1

So if you want to view all info of that user, you can get by Model User ( Laravel Framework):

$user = User::find(user1);
$email = $user->email;
$sex = user1->sex;

Second method, if use ID as primary key, but my project no need to use id at all.

$id=User::where(username=user1)->id;
$user = $User::find($id);
 $email = $user->email;
 $sex = user1->sex;

As you can see, First method is faster than second, because "Username" field has indexed.
Should i use username for primary key in this situation? I use MySQL.

If you don't like using primary keys that are auto generated, there is another way.

Instead of indexing the userID (which is large and slow to index, because it is a string) you can instead index the hash value of userID. You can then do:

CREATE TABLE Users (
  ID BIGINT
  , Username VARCHAR(255)
)

And you can find the user with:

$user = User::find(hash(user1));

You just need to pick a good hash function. A great candidate is MurmurHash (google it).


How to create a tablespace on Windows 7 for Postgresql 9.2? I try to run this simple command:

CREATE TABLESPACE tb_rec
  OWNER postgres
  LOCATION 'd:/tablespaces';

However, the tablespace is not created and I got this message:

ERREUR:  n'a pas pu configurer les droits du répertoire « d:/tablespaces » : Permission denied

For non french speakers:

ERROR: was not able to configure the directory rights « d:/tablespaces » : Permission denied

I didn't find any postgres user (net user) and I'm stuck on this...

How to give access to the directory to postgres?

Windows 7
Postgres 9.2

For PostgreSQL 9.2 and above there is no postgres system user (by default). It runs as NETWORK SERVICE.

Create d:\tablespaces and give the NETWORK SERVICE (SERVICE RÉSEAU in french) "Full control" over it. That should do the trick.


Can I calculate ROW_NUMBER() for only consecutive records? I need to calculate sequence numbers for consecutive values.  That sounds like a job for ROW_NUMBER()!

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

I expected the PARTITION clause to make the count reset at each change in Subset, but instead SQL Server collects all values for a given Subset value and numbers them.  Here's what I expected, and what I got:

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

When SQL reaches line #6, it resumes numbering subset "A", whereas I see it as the first line of a new subset that just happens to also be named "A".

Is there a way to make ROW_NUMBER() partition strictly, rather than the default behavior?

There are a number of questions here and elsewhere about counting consecutive values with SQL.  However, I have not yet seen one that addresses repeated values in the PARTITION BY field(s).  Most deal only with increasing values, often dates.

I was able to address this using LAG():

SELECT
    Sequence, Subset,
    CASE WHEN Sequence = 1 OR Subset <> LAG(Subset, 1) OVER (ORDER BY Sequence)
        THEN 'New subset'
        ELSE 'Continuation'
        END
FROM
    @Data

This returns "New subset" for records #1, #4, and #6.  Apparently LAG() partitions slightly differently from ROW_NUMBER().

Obviously this doesn't provide row numbers, but it helped me reach the goal of identifying consecutive sequences of numbers, when the subset identifier can repeat.


First you have to filter the userid that appears more than one time:

select
userid
from table
group by userid
having count(1) > 1

This will return:

userid
------
     5
     8

Now using this as a subquery you can select the complete result you want:

select
*
from table
where userid in (
  select
  userid
  from table
  group by userid
  having count(1) > 1
)


Database structure for a large amount of data with different types Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system.

I have to store device id (int), timestamp and value itself. Value could be bit, int, float or even string. It's SCADA application so I have to store parameters like speed, temperature, status, product name, etc...

The end users select data portions without doing math on server side - so it's simply select by id and timestamp.

My questions are: how to design the relevant tables and what datatypes should I use to make it work as efficiently as possible?

Current options

My idea is to keep it in one table with values as VARBINARY. I use Entity Framework Core 2, since I am familiar with it, but maybe you can suggest me a better approach.

You didn't mention whether your parameters are dynamic and variable.  If not then the correct logical design is:

(id,ts,speed,temp,pressure,status)

And the correct physical design is a clustered columnstore with a non-clustered primary key on (id,ts).  Possibly partitioned by id or ts into ranges.

If your parameters are variable, you should consider using JSON over using a Key-value-pair across multiple rows.

(id,ts,telemetry_json)

or

(id,ts,telemetry_json_gzip)

With the telemetry data stored in JSON key-value pairs, and/or JSON arrays.  Not only is this model efficient and handy, you can use a Value Conversion in EF Core to have a complex .NET type serialized to/from JSON for storage in the database.

You can use

(id,ts,attribute,value)

but this pattern is generally not the most efficient, or convenient, and is widely frowned-upon.  There many discussions on this site and others regarding the Entity-Attribute-Value model.


Need to export table into 2 columns, one repeating, one with rest stacked Have a table that has one column I want to duplicate, corresponding to several columns I want to stack on top of each other. I have a table like this (foo):

|col1   |col2   |col3   |
|   1   |   A   |   F   |
|   2   |   B   |   G   |
|   3   |   C   |   H   |
|   4   |   D   |   I   |
|   5   |   E   |   J   |

And I want to create a table like this (foo2):

|col_a  |col_b  |
|   1   |   A   |
|   2   |   B   |
|   3   |   C   |
|   4   |   D   |
|   5   |   E   |
|   1   |   F   |
|   2   |   G   |
|   3   |   H   |
|   4   |   I   |
|   5   |   J   |

This is as far as I got, this only stacks into one column:

DROP TABLE IF EXISTS foo2 CASCADE;
select col_a INTO foo2 from (
select col1 as col_a from foo
union all
select col2 as col_a from foo
union all
select col3 as col_a from foo
) as myview;

select col_a, col_b INTO foo2 from (
       select col1 as col_a, col2 as col_b from chandlerV2
       union all
       select col1 as col_a, col3 as col_B from chandlerV2
     ) as myview;

Each part of subquery myview select main column (col1) and one of two secondary column (col2 or col3 respectively). Both part united by UNION ALL clause.

As variant you can CREATE TABLE and after that run INSERT INTO ... SELECT ...


Which of the following data duplication options across shards is recommended? High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where the post is stored.

So this raises the question how to reliably duplicate this data. Which of the following data duplication options across shards is recommended?
Option 1: Make 2 separate inserts from the PHP script.
Pros: a) Logic is in application layer.
Cons: a) User is held for 2 inserts. b) This logic will need to be duplicated in every client trying to insert similar data.
Conclusion: Seems reasonable.

Option 2: Form federated tables and use some trigger to handle the insert of duplicate.
Pros: a) App layer doesn't need to worry about multiple inserts
Cons: a) Every shard need to have federated connection to every other shard; b) Federation will work on machines in LAN, but what about at 2 different sites. c) what if connection to federated server fails.
Conclusion: Doesn't seem like a sound idea.

Option 3: Messaging such as RabbitMQ
Pros: a) Different clients can insert data at one place, and all subscribers can consume the insert.
Cons: a) Complex; b) may impose overhead in order to host messaging server, and clients; c) not sure how will it work with a look-up service to locate appropriate shards
Conclusion: Not sure

Option 4: your suggestion?

I will greatly appreciate your help.

As you point out, having triggers between the various shards is silly; the whole reason for sharding is independent database
  operations. So you can throw it out right away.

  Updating both tables at the same time is the approach with the fewest
  moving parts. Over the long term, it will be the most maintainable.
  And it will be the easiest to debug if something goes wrong.

  But if response time is important, then you might think of some sort
  of messaging approach: update the comments-by-entry table, and queue a
  message to update the comments-by-user table. If it takes an hour for
  that message to be processed -- or if it gets lost in a system crash
  -- no big deal, you can always recover. By no means should you use a messaging approach to update both tables.

Answer by: @kdgregory Link: https://softwareengineering.stackexchange.com/a/134607/41398


Sql Server Server Group query Scenario

I have 4 front-end sql server.

All of them have the same db instances. Data are replicated from back-end databases.

Issue

In SQL Management Studio I have registered the servers and I have created a group (called front_end). The group can be inquired using the sql editor opening a connection to the servers group.

Is it possible to inquire the group programmatically setting the USE clause?

E.g.:

    use [front-end]..myDatabaseInstance
    select * from myTable

If you right click the registered servers group and select "New Query" you get a query window that connects to all the servers, whatever you execute will run on all and return combined results.

http://msdn.microsoft.com/en-us/library/bb964743.aspx

If you want to programatically do it, you can use a central management server and query that for a list of servers, and use linked servers to run the query at each location using openquery

http://sqltidbits.com/scripts/query-central-management-server-list


Json append results in error of - "No operator matches the given name and argument types" clusters_v2=# UPDATE cluster SET "clusterCALogs" = "clusterCALogs" || '["test"]'::jsonb WHERE "clusterId" = 9;
ERROR:  operator does not exist: json || jsonb
LINE 1: ...ATE cluster SET "clusterCALogs" = "clusterCALogs" || '["test...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

According to https://stackoverflow.com/a/42233548/1663462 I should be able to append to the JSON array? Why am I getting this error?

My column type for "clusterCALogs" was json... It had to be jsonb.


MongoDB in ReplicaSet databases showing different sizes and objects I have a MongoDB in a ReplicaSet with 3 members, and last week the primary node got corrupted somehow and stopped. I had to resync the data from the other nodes.

It has been a week but I was checking this morning and the databases looks different somehow between the primary (that crashed) and the two secondary that were used for resync the data.

  PRIMARY (after crash)

  Secondary

I've seen other questions similar to this one but at the end the databases had the same amount of objects, however, my scenario it isn't the same.

Any idea? Or does it look ok?

The rsync process will also apply a de-fragmentation for the MongoDB data files on the crashed primary replica. That's why you see the data file reduced in size on the primary after crashed.

This effect is the same if you run the compact command on your secondary to reclaim the fragmented storage caused by data CRUD operations over time.


SQL Server Table Index To big I have a table that has approximately 450k records
here is the structure of it

CREATE TABLE [dbo].[Reviews](
[ReviewID] [int] IDENTITY(1,1) NOT NULL,
[AuthorName] [nvarchar](50) NULL,
[AuthorURL] [nvarchar](500) NULL,
[Rating] [int] NULL,
[Comment] [nvarchar](max) NULL,
[UTCCreatedDate] [date] NULL,
[IsImported] [bit] NULL,
[ListingID] [int] NULL,
CONSTRAINT [PK_Reviews] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Reviews]  WITH NOCHECK ADD  CONSTRAINT [FK_Reviews_Listings] FOREIGN KEY([ListingID])
REFERENCES [dbo].[Listings] ([ListingID])
GO

ALTER TABLE [dbo].[Reviews] CHECK CONSTRAINT [FK_Reviews_Listings]
GO

Now when I look at the index sizes of all my tables (via posted tsql) here http://basitaalishan.com/2012/07/06/find-the-size-of-index-in-sql-server/

The review table has a massive 446264K (approximately 450MB) index on just the primary key.

What are my options on reducing/eliminating this as I am not sure how useful this is as this is just a related table (child)

Your problem is not the primary key.  It is a misunderstanding of what a clustered index is.  If you look at your structure you will see that the primary key is clustered.  This is the default and fairly normal but is not always the case so it pays to check.

The clustered index is basically the table itself.  The data pages of the clustered index are the data pages of the table.  So your 450mb for the primary key is mostly the data of the table itself.  At 446264K that makes it about 1k per row which is not unreasonable depending on the amount of data in the AuthorURL and Comment columns.


Can text fields be not loaded for join memory purposes in MySQL? I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow
I understand that text field are not stored in memory so they forbid their rows from being cached, so I would like to switch off text field loading from disc making the rows of this table remain in fast memory and allowing load text fiels "manual loading" when necessary
Is this possible?

Maybe.  That is, there is no setting to achieve such; however, rewriting the query can achieve such.
Please show us the query that is causing such.  Meanwhile, here are some possible examples:

SELECT * ... loads all the text columns.  If you don't need all the columns, spell out what you need instead of saying *.

SELECT big_text FROM t WHERE ... ORDER BY ... LIMIT 10 -- In some cases, the optimizer can focus on the 10 rows desired, and load big_text for only those.  For other cases, it loads big_text for 'all' the rows before discovering which 10 you needed.  This can usually be worked around.  But I need your SQL to talk you through it.

JOIN is another case where the previous item may or may not show its ugly head.


SQL Import/Export defaults to byte stream on export So, I've done some research and I can't seem to figure this out.  I'm not sure if it's some setting that I'm missing or what.

For the basic info, running SQL2012, 64 bit, all that good stuff.  I noticed that, for some reason, when I export from a table into a flat file using a query, the data type is defaulting to bytestream.  In the past, it always defaulted to DT_STR and went through without any issues.

Here's the query:

SELECT pin from IncrementalDM_Emails
where email in (select email from [7755_forsql])
order by pin

Here's the error I'm getting:

Here's what the export is showing when I select "Edit Mappings..."

Now, this can be easily fixed by simply selecting "DT_STR" under the Mappings dialog but I frequently export using this method so I'd like to find out why it's doing it and fix it so I don't always have to go into the Edit Mappings dialog.

Is it something to do with the query and the use of the IN function?  Any help would be greatly appreciated!

EDIT: The data in both tables is stored as varchar(50)

When you retrieve or load data, SSIS tries to automatically convert it to the correct types. If SSIS can’t implicitly convert the data—and transforming the data within the package doesn't work—you might need to modify the XML mapping files (C:\Program Files\Microsoft SQL Server\110\DTS\MappingFiles -- folder), stage the data so it’s compatible with both SSIS and the data source, create a custom component that can retrieve or load the data, or implement a solution outside of SSIS to prepare the data.

SSMS on the background creates an SSIS package. At the end it will even allow you to save it as an SSIS package.


TNS-12535: TNS:operation timeout I have Windows Server 2008 R2 with Oracle Client 11.1.0.7.0 client installed. TNSPing to 100% available databases (accessible from other machines, i.e. I can do TNS ping to these databases from other machines) returns me following error: TNS-12535: TNS:operation timeout. At the same time I can telnet to database host/port and see that proper tnsnames file is being used. Environment variables are configured as appropriate (ORACLE_HOME, TNS_ADMIN, Path). Also I can connect via telnet using service/host name + port - so this is unlikely a firewall issue.
After enabling Oracle client logging I can see following error related details in tnsping.trc:

  snlinGetAddrInfo: enrty snlinGetAddrInfo: getaddrinfo() failed with
  error 11001 snlinGetAddrInfo: exit

The only strange/suspicious thing about this machine is that it had multiple versions of oracle client installed (10, 11.1, 11.2) - removal of all versions apart from 11.1 & reinstall/repair of 11.1 haven't resolved this issue. Also it seems to me that v10 of client was not removed properly - it disappeared from Programs and Features, but files in installation folder had not been deleted.
Any advice on what to check / how to resolve this issue?

I know it is too late for the OP but here some advice to tackle such a  problem:

The error message says that the clients sends but does not get a response. Most of the time it is a firewall problem.

The windows getaddrinfo function is described here. Error number 11001 is WSAHOST_NOT_FOUND and is described here:

  Host not found.
  No such host is known. The name is not an official host name or alias, or it cannot be found in the database(s) being queried. This error may also be returned for protocol and service queries, and means that the specified name could not be found in the relevant database.

Linux/Unix error numbers can found in the appropriate header files.
I don't know if that windows error message is is the reason for your error. Even if you can access the listener port with telnet that does not mean that there is no firewall problem. I you used share servers or an oracle RAC with a scan listener the database will try to connect to a server by a different prot that can be blocked by a firewall.
TNS ping does only contact the listner. So first you should check if the tnsping arrives at the server. Can you see it in the listner.log file? Check if you use the right tnsname.ora file by adding the connect string with a completely new tns-name and do a tnsping to this new tns-name. Do you get the same error message?
If you want to remove all your Oracle installations on a windows server you can do that by hand. Remove all the directories and registry entries. Look for appropriate advice.


PostgreSQL: websearch_to_tsquery precedence / predicate ordering How does precedence work in websearch_to_tsquery? I have below example query:
websearch_to_tsquery('exp1 exp2 OR "multi word expression" -weak')

The issue I have is that the results include text containing 'weak' because they match 'exp1 exp2'.
However when I remove OR "multi word expression eg query being
websearch_to_tsquery('exp1 exp2 -weak')

then it works as expected and weak is not present in results.
I suspect the initial query is interpreted as
(exp1 and exp2) OR ("multi word expression" and not weak)

instead of my intention of
(exp1 and exp2) OR ("multi word expression") and not weak
So how would I write the later query using `websearch_to_tsquery?

This is how PostgreSQL interprets your query:
SELECT websearch_to_tsquery('english', 'exp1 exp2 OR "multi word expression" -weak');

                     websearch_to_tsquery
--------------------------------------------------------------
 'exp1' & 'exp2' | 'multi' <-> 'word' <-> 'express' & !'weak'
(1 row)

Since & ties stronger than |, your suspicion is correct.
I don't think that there is a simpler way to express what you want with websearch_to_tsquery than
exp1 exp2 -weak OR "multi word expression" -weak


Can I install an additional oracle database on one node of an oracle cluster (RAC)? Let's say I have a two-node Oracle RAC environment. Can I install an additional Oracle database on one of the node?

Thanks.

You can install another database on the same server that is already running one instance of a RAC database, yes.


Is there a value that I can use in a SELECT TOP that will return all rows? I'm allowing the end user to define how many rows are returned by a query (SELECT TOP (@x)). Is there a value that can be entered where all rows are returned? Or do I have to dynamically create the query without the TOP (@x) if they want all rows returned?

I'm using SQL Server 2012.

Well, it looks like TOP is a BIGINT if you aren't using a PERCENT.  That means you could pass in the max value of BIGINT,

SELECT TOP (9223372036854775807) * FROM table1

I seriously doubt you will ever see a table that large.  I'm not sure what kind of effect that would have on the query plan though.


Python Postgres psycopg2 ThreadedConnectionPool exhausted I have looked into several 'too many clients' related topic here but still can't solve my problem, so I have to ask this again, for me specific case.

Basically, I set up my local Postgres server and need to do tens of thousands of queries, so I used the Python psycopg2package. Here are my codes:

import psycopg2
import pandas as pd
import numpy as np
from flashtext import KeywordProcessor
from psycopg2.pool import ThreadedConnectionPool
from concurrent.futures import ThreadPoolExecutor

df = pd.DataFrame({'S':['California', 'Ohio', 'Texas'], 'T':['Dispatcher', 'Zookeeper', 'Mechanics']})
# df = pd.concat([df]*10000) # repeat df 10000 times

DSN = "postgresql://User:password@localhost/db"
tcp = ThreadedConnectionPool(1, 800, DSN)

def do_one_query(inputS, inputT):
    conn = tcp.getconn()
    c = conn.cursor()

    q = r"SELECT * from eridata where "State" = 'California' and "Title" = 'Dispatcher' limit 1;"

    c.execute(q)
    all_results = c.fetchall()
    for row in all_results:
        return row
    tcp.putconn(conn, close=True)

cnt=0
for idx, row in df.iterrows():

    cnt+=1
    with ThreadPoolExecutor(max_workers=1) as pool:
        ret = pool.submit(do_one_query,  row["S"], row["T"])
        print ret.result()
    print cnt

The code runs well with a small df. If I repeat df by 10000 times, I got error message saying connection pool exhausted
. I though the connections I used have been closed by this line:

tcp.putconn(conn, close=True)

But I guess actually they are not closed? How can I get around this issue?

Don't pass the parameter close=True. putconn already "closes" the connection. Think of putconn like "put connection back in pool", which is probably what you want.


Aggregation, joining, and filtering with many-to-many table Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.

Customers
+----+-------+
| ID | Name  |
+----+-------+
|  1 | John  |
|  2 | Larry |
+----+-------+

Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
|       1 |          1 |    10 | x        |
|       2 |          1 |    15 | x        |
|       3 |          1 |     6 | y        |
|       4 |          2 |     3 | x        |
|       5 |          2 |    25 | y        |
+---------+------------+-------+----------+

My Materials table originally looked like this

+----------+-------------------------+
| Material |      Applicability      |
+----------+-------------------------+
| x        | car, house, plane, bike |
| y        | car, bike               |
+----------+-------------------------+

When I need to display what materials John buys and which objects that material is applicable to, my query is this.

Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability

The result

+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material |      Applicability      |
+----+------+--------------+----------+-------------------------+
|  1 | John |           25 | x        | car, house, plane, bike |
|  1 | John |            6 | y        | car, bike               |
+----+------+--------------+----------+-------------------------+

The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.

Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.

+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x        | car           |
| x        | house         |
| x        | plane         |
| x        | bike          |
| y        | car           |
| y        | bike          |
+----------+---------------+

This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.

Example.

+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
|  1 | John |          25 | x        | car           |
|  1 | John |          25 | x        | house         |
|  1 | John |          25 | x        | plane         |
|  1 | John |          25 | x        | bike          |
|  1 | John |           6 | y        | car           |
|  1 | John |           6 | y        | bike          |
+----+------+-------------+----------+---------------+

Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.

The main problem is when i need to find out what John buys, but also filter by applicability.

Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability

If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.

How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.

if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).

I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?

select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material


How to solve blocking on tempdb I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2).

Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all the sessions' (blocker and the waiters) waitresource is "2:1:122".

I checked the SQL codes and saw that table variables are created in most of the sessions and I suspect that might be the culprit.

Any suggestions on how to diagnose further and alleviate this problem is appreciated.

PAGELATCH_XX - This indicates that SQL is waiting on access to a database page, but the page is not undergoing physical IO. This class of problem is caused by a large number of spids attempting to access the same physical page at the same time. The wait_resource is the page number (the format is dbid:file:pageno) that is being accessed. If diagnosing on a live server, the Performance Dashboard reports will run DBCC PAGE and display the output to tell you what object and type of page the contention is for (allocation, data, text, ...)

Since the pages that SQL is most frequently waiting on are in the tempdb database (page number in dbid 2), you may be facing tempdb allocation latch contention.

Tempdb allocation page latch contention can occur with workloads that create and destroy temp objects (including worktables/workfiles for sort or hash operations) hundreds or thousands of times per second.

Resolution -

1.Implement trace flag -T1118.

2.Increase the number of data files in tempdb to maximize disk bandwidth and to reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.


how to temporarily change the sql server settings in order to do a task and when finished revert back? when I run the script below all at once I get this error message:

  Msg 15281, Level 16, State 1, Line 58 SQL Server blocked access to
  STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed
  Queries' because this component is turned off as part of the security
  configuration for this server. A system administrator can enable the
  use of 'Ad Hoc Distributed Queries' by using sp_configure. For more
  information about enabling 'Ad Hoc Distributed Queries', search for
  'Ad Hoc Distributed Queries' in SQL Server Books Online.

But when I run this first and then run the full script then it is all fine.

exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed.

How can I alter this behaviour?

here is the full script:

    SET NOCOUNT ON;

    declare @prevAdvancedOptions int
    declare @prevXpCmdshell int
    declare @adhocdistque  int

-------------------------------------------------------------------------------------------

--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
-- because this component is turned off as part of the security configuration for this server.

--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
--For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

PRINT @prevAdvancedOptions
PRINT @prevXpCmdshell
print @adhocdistque

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if (@adhocdistque  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end

/* -----------------------------------------------------------------  do work - begin */

SELECT  *
      FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
      , 'set fmtonly off exec msdb.dbo.sp_help_job')
      --where name in
      --        ( 'WebFeed UKProductOffer Offers'
            --   ,'WebFeed USProductOffer Offers'
            --   ,'WebFeed DEProductOffer Offers'
            --   ,'WebFeed ATProductOffer Offers'
            --   ,'WebFeed FRProductOffer Offers'
            --   ,'WebFeed EUProductOffer Offers'
            --   ,'WebFeed AUProductOffer Offers')

/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

if (@adhocdistque = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end

I'm not sure if what you are building is a good idea or not but the code below should get it working.

You basically need to include the GO statement to separate the script into individual batches. Using a temporary table to store the original settings values allows access to the values by each of the batches.

You can't include the GO statement in a stored procedure so if you are looking to automate this process you will need to separate the script out another way (Agent job steps, SSIS, PowerShell, etc)

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

SELECT * FROM #Settings;

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries')  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end
go
/* -----------------------------------------------------------------  do work - begin */

    SELECT  *
          FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
          , 'set fmtonly off exec msdb.dbo.sp_help_job')
          --where name in
          --        ( 'WebFeed UKProductOffer Offers'
                --   ,'WebFeed USProductOffer Offers'
                --   ,'WebFeed DEProductOffer Offers'
                --   ,'WebFeed ATProductOffer Offers'
                --   ,'WebFeed FRProductOffer Offers'
                --   ,'WebFeed EUProductOffer Offers'
                --   ,'WebFeed AUProductOffer Offers')

/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------
go
if ((SELECT val FROM #Settings where Setting = 'xp_cmdshell') = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'Ad Hoc Distributed Queries') = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end

if ((SELECT val FROM #Settings where Setting = 'show advanced options') = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;


MySQL Session sql_mode vs Global sql_mode @@SESSION.sql_mode and @@GLOBAL.sql_mode is showing different options.

@@SESSION.sql_mode

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@GLOBAL.sql_mode

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

It seems like GLOBAL.sql_mode is taking precedence, is that right?

Why wouldn't a session setting overwrite a global setting?

Query ran:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

Result:

"Session" variables apply to the connection.  (Usually, there is exactly one connection per client.)  Two different clients (or different connections in one client) may have different values in the variables.

"Global" variables are defaults that are copied into "session" when you connect.  Then you, or your client code, or the 3rd party application, or whatever, could change the variables.

Note:  Some global variables are really global -- the "session" value always tracks the global copy.

If you display the session value from the same connection, then that is what the connection is using.  If you make a separate connection to read the value, then what you see may not be what is in effect.

There are hundreds of 3rd party packages out there; who knows what each of them does.  Furthermore, things change from version to version of that package.

sql_mode can be changed in a session.

In your example, something in the connection added STRICT_TRANS_TABLES to SQL_MODE.  And it should be in effect for the connection until it is changed again.


SQL Datetime / datetimeoffset(2) comparison & implicit cast wierdness I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, and the INSERT happend. However, the unique constraint rejected the data as it already existed in the database.

The problem was the data to be inserted was DATETIMEOFFSET(2) and the database field being inserted into was DATETIME.

To show you want I'm talking about, run the following:

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

It prints:

Equals does not match
Cast matches

The comparison (=) operator does not perform the same way the implicit cast does if you insert the data. The cast/convert operator actually throws away the offset! Madness.

Why does the comparison operator work differently to the implicit cast that happends during an INSERT?

Looks like the opposit is true: the implicit conversion  takes the offset into the equasion, but the cast/convert functions do not.

DECLARE @dt  DATETIME          = '2014-07-07 02:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.000 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

PRINT CASE WHEN @dt = CONVERT(DATETIME,@dto) THEN 'Convert matches'
           ELSE 'Convert does not match'
      END

Comparing this (deducted 7 hours from @dt) results in:

Equals matches
Cast does not match
Convert does not match

Did some more investigation, and stumbled on this article.

"When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly.
For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value."

So when you want to treat  '2014-07-07 09:49:33.000' and '2014-07-07 09:49:33.000 +07:00' as equal, your only option is to make an explicit conversion via cast or convert.
Since implicit conversions would only work when your server's timezone offset happens to be the same as the offset specified in @dto.


Unique ID for Row of a Set in One Table I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image.

user_id: 1, photo_id: 1

Now a second user makes a profile and uploads two images.

user_id: 2, photo_id: 1, photo_id: 2

In my photos table, how can a create a unique identifier out of the combination of user_id and photo_id? Neither are unique in the table but their combination should be.

First, photo_id should be unique on the table. Ignoring sound practice however, you can still do this with a composite PRIMARY KEY

CREATE TABLE photos (
  user_id  int,
  photo_id int,
  PRIMARY KEY (user_id, photo_id)
);

INSERT INTO photos (user_id,photo_id) VALUES (1,1),(1,2);

--duplicate already exists
INSERT INTO photos (user_id,photo_id) VALUES (1,1);

Composite natural PRIMARY KEY's are not bad, however I'm not a fan of having them on multiple surrogate keys. If you're going to create an ID, may as well make it unique.


3NF decomposition + database normalization I am studying for a test and would like to know if I am on the right track to get the following into 3NF with minimal number of keys for R:

Given relation R(A,B,C,D,E,F,G) and the functional dependencies
f = {A->DE, E->BF, AB->FG, G->D, F->D}

how can I derive the minimal key(s) of R ->
I thought of this f+={A→BDEFG, E→BF, G→D, F→D} but it is wrong I don;t know how
Is R in 3NF? If not decompose it into 3NF.

Every textbook I've ever seen gives you one or more algorithms you can use to determine every possible candidate key for R{ABCDEFG}. (It can be a fairly laborious and time-consuming job.) Based on the FDs you provided, I think R{ABCDEFG} has only one candidate key, {AC}.

Loosely speaking, for a relation to be in 3NF, it must be in 2NF and have no transitive dependencies. For a relation to be in 2NF, it must be in 1NF and have no partial key dependencies.

The only candidate key here is {AC}.
From A->DE we know that DE is dependent on A, which is only part of the candidate key.

Therefore, R{ABCDEFG} is not in 2NF (or 3NF) based on the FDs you provided.


Is there any benefit to defragmenting SQL indexes in a SAN environment? Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.

We have been running Ola Hallengren's index maintenance scripts weekly, and it runs for several hours each time. Based on the fragmentation threshold, the script will either reorganize or reindex an index. We have observed that during reindexing, the log files get huge which leads to an excessive consumption of bandwidth during the log shipping.

Then comes an article from Brent Ozar in which he says to stop worrying about SQL indexes:

  Your hard drives are shared with other servers that are also making drive requests at the same time, so the drives will always be jumping all over the place to get data. Defragging your indexes is just meaningless busy work.

Googling this question leads to varying opinions, most supported with arguments that seem too brief or weak. Our tentative plan is to adjust the fragmentation threshold in our maintenance script so that it reorganizes much more often than it reindexes.

What is the final verdict? Is it worthwhile to defrag SQL indexes on a SAN considering the burdens associated with running weekly maintenance jobs?

Defragmentation strategies help improve scan speed to/from disk.

The wide variety of opinions is because an environment's ideal defragmentation strategy should depends on many different factors. There are also multiple potential layers of fragmentation in play.

Saying that your databases are stored on a SAN isn't enough information. For example:

Are database files stored on separate physical RAID groups or the same RAID group? What other processes are active on that same device? Are your backup files ending up there, too? You may have to ask your SAN admin for this information, because it's not always transparent.
What are the access patterns for the databases? OLTP is generally random access, but sometimes an application is table-scan-happy and you can't change its behaviour (ISV app). Are the applications read-mostly, write-mostly, or somewhere in between?
Are there performance SLAs in play during a recovery/failover period?

Brent's post assumes there is one giant pool of storage and everything shares it. This means the physical disks are rarely idle, and hence most access is random. If that is your situation, then the advice applies, and I agree with it for the most part. While this type of strategy is much easier to manage, it isn't necessarily (a) what you have in your environment, or (b) what is the best solution for your environment.

If index maintenance is burdensome, consider doing it less aggressively, and/or amortize the cost over the week (i.e., run light maintenance once/day, instead of heavy maintenance once/week).

You can also turn on the SortInTempdb option to potentially reduce the amount of logging that takes place in the user databases.


To get rid of heavy archive logs in PostgreSQL 9.1 In our production server which is having PostgreSQL 9.1 installed, too heavy archive logs are being generated, approximately 41 GB/day. Heavy updates, deletes and inserts are the cause of this. Can anyone help us to reduce the amount of archive log generation in our production server?

If you don't need to analyze again that log. You can reduce the things that database will be log.
Some of them are:

log_statement: Controls which SQL statements are logged. You don't need to log all statement. It can be switched to none.
client_min_messages: can be set to ERROR
log_min_messages: can be set to ERROR
log_min_duration_statement: can be set to a -1 - this means disable it.

All of them are store in file configuration postgresql.conf

You can read more document here.

Hopefully this answer will help you.


Linking Fields From 2 Different Tables (Microsoft Access) I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer.

I have 1 table called "Employee Details" which contains the field 'Employee Names' and 'EmployeesID'

The 2nd table is called Schedules" which contains the field 'Employee Names' and'EmployeesID'

The Employees ID is set to a primary key for table 1 but I do not know how to link/create a relationship between the 2 tables so that in the 2nd table the primary key remains the same.

Overall I want to be able to link the 2 fields so that the ID number remains the same throughout the tables.

Please may I be advised of how to solve this issue.

Thank you.

In your scenario, you have two 'entities':

Employees. These are stored in table Employee Details.
Schedules. Stored in table Schedules. A schedule stores an Employee ID and other information.

What you want to do is make sure that every schedule contains a valid Employee ID. That, you want to make it impossible to create a schedule with an Employee ID doesn't exist, and also to delete an employee from the Employee Details table if that Employee ID is currently being used in a schedule.

These requirements are handled by what databases call a foreign key constraint. In Access, you can use a graphical tool ('Relationships') to 'link' the Employee ID column in the Schedules table to the Employee ID column in the Employee Details table so that the constraints I described above are always met.

You can read up on foreign key constraints and Access' Relationships manager tool to accomplish this.


Two paths between two entities In my db the entities are:

designed_object: a generic designed object
designed_object_param: a parameter related to the designed object with the type (param_type), the default value (param_value), the maximum and minimum value (param_max/min_value)
real_object: an instance of the designed object
real_object_param: the actual parameters of the instance (param_value)

They are related as follow (foreign key):

designed_object - real_object: 1..n
designed_object - designed_object_param: 1..n
real_object - real_object_param: 1..n
designed_object_param - real_object_param: 1..1

The problem is that there are two paths from real_object_param to designed_object:

real_object_param -> real_object -> designed_object
real_object_param -> designed_object_param -> designed_object

With this design, it is possible that, starting from the same real_object_param, the two paths point to two different rows if, for some bug, the real_object_param refers a wrong designed_object_param.
I know that this can be avoided through a trigger.
Is there any better design?

Thanks.

I renamed few things here, just to make it easier for me to think. Most of it is self-explanatory. In essence, the problem and the solution is very similar to the previous question, with more detailed answer.


How should I configure these disks on a SQL Server for a BI configuration? Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks

2 x 150 (10k)
6 x 150 (15k)

They are all local disks.

My requirements

My DB is 350gb and set to default 10% growth
My OS & SQL Server are Server 2k8R2 (C: drive OS + page + applications = 55Gb)
Log requirements are about 70gb and set to default 10% growth and is routinely truncated
My TempDb is about 12gb currently and set to default 10% growth

My problem is that I’m trying to understand where to best put the TempDB and OS and the Log. My experience is limited in optimal configuration of these two

This is not an online transactional system. It has heavy data write (new data + indexes rebuild/reorg) then heavy data read (I'm estimating at about 50/50) processing for about 13 hours, and then just quiet.

My understanding is that the TEMPDB is heavily used during normal processing compared to the log.

My idea is the following

2 x 150g (15k) Raid 1 = 150g for OS + TempDB
2 x 150g (10k) Raid 1 = 150g for LOG (note slower disks here)
4 x 150g (15k) Raid 5 = 150g for data

Does this sound like a good idea? I could then swap the Log + TempDB if needed.

Am I breaking a cardinal rules like never put TempDB on OS disk due to paging concerns, or perhaps never put log on slower disk than data?

Edit:

We also have a SSAS on the system and the end users access only the Cube. The 50% read above is based on the time it takes to process SSAS database.

2 * 10k RAID1 for OS, 6 * 15k RAID10 for everything else. Honestly, with this many disks 1 array is the safest and usually fastest bet.

If you've got time to test and have a real world, repeatable, measurable workload then by all means do a test run with your tempdb on the OS drive (caveat: limit tempdb file growth to ensure you don't splat the OS). Flip side, you may see moderate improvements to your data load and maintenance with the log there instead so worth a test run or two if time permits.


Autogrowth, shrink and database performance I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.

I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.

I have so many small databases which really have data of about 50 MB. But it's physical size is about 900 MB.(So 850 is free space). I have not shrunk this database. As if I shrink it then again when data gets added autogrowth occurs.  But adding data to this database is rare. So I think it will not grow more than 200MB for next one year.
So should I shrink the database? If I keep it as it is? Should that cause any performance problem? Or having more free space(more than 90%) in database will cause any problem? I think most of the people think having large physical size will cause performance problem. So is it true or not?

SQL Server 2008 R2 Express database size limit is 10 GB. So is it physical size of both mdf and ldf file together? Because I need to consider this if I should shrink database at any point.

Yes, picking an autogrowth size that makes sure you minimize or eliminate the number of growth events during your business cycle is ideal. I would venture to say that if 300 MB per month is that predictable, you should also - in addition to setting more reasonable autogrowth settings - to grow the file right now by 4 or 8 GB to allow for one or two years of growth without having to worry about interruptive growth events (even with instant file initialization, data file growths still cause transactions to wait while they complete). If you know you're going to grow by about 300 MB per month, why put off all those growth events? What are you going to do with the space in the meantime? Lease it out month-to-month?
You are probably fine shrinking that specific database to 200 MB. Be sure to do this with DBCC SHRINKFILE or ALTER DATABASE MODIFY FILE, not DBCC SHRINKDATABASE.
The Express limitation is on the data file only. You can have a 9.99 GB data file and a 600 GB log file, and SQL Server won't complain. Well, it will in other ways, but not due to exceeding any arbitrary file size limitation. That said, you should either make sure to be in simple recovery or back up your log at a reasonable rate. Log files should never be growing uncontrolled - and if you're worried about the log file on an Express instance, something isn't right. Please see the following posts for plenty of discussion:

SHRINKFILE best practices and experience
Sql Server - Best Practices for Growing Database Files
Why Does the Transaction Log Keep Growing or Run Out of Space?


Reinstall Oracle 11G2 Running Oracle 11GR2 instance (non-ASM). Plan to set it up again from the scratch(this time using ASM, Grid infrastructure). And I need the present database.

I am thinking of the simple plan:
1) Backup using RMAN the existing instance.
2) Deinstall the existing instance.
3) Install Grid.
4) Install Oracle DB software. (Same version)
5) Restore RMAN backup.

Is this how I should proceed, or is there any other plan of action I can look to.

Thanks

The Oracle docs for 11.2 ASM migration are here:

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG12000

These explain the process that you need to go through to migrate to ASM from filesystem storage.


Select like and unlike record pattern SQL Lets assume I got this table, that got file pathes :

id    file
 1     /etc/file.txt
 2     /etc/.delete.file.txt
 3     /etc/secondfile.txt
 4     /etc/four.txt
 5     /etc/five.txt
 6     /etc.delete.five.txt

I want to get all the files that are not deleted,

So if there is a file : .delete.file.txt -> I don't want to select both .delete.file.txt and also file.txt (always the prefix is .deleted)

Expected result after query :

id    file
 3     /etc/secondfile.txt
 4     /etc/four.txt

How can I do that?
(Using SQLITE)

Assuming that your file column is unique, you can do this:

SELECT
REPLACE(file, '.delete.', '') AS file
FROM your_table
GROUP BY REPLACE(file, '.delete.', '')
HAVING COUNT(*) = 1

You "delete" the ".delete." and then return only the rows that don't appear multiple times.


Difference Between a Staging and the Production DWH I have a general question on a Data Warehouse Architecture. I have heard a lot of a Staging and a Production, so that data should be loaded via SSIS first into a staging dwh and then via SSIS to the Production environment.

But is there any difference? I mean is it only a "copy" of the production data warehouse (to do some quality checks on data before pushing to production?) or of the source data in a different model or (different schema)?

From software development perspective i know that both are identical and the only purpose is to check the software version from all stakeholders and get the release to deploy on prod. Normally in the software case the staging is same (maybe only hardware sizing is different) to production.

I think this is a case of the same word being used to describe two different things.

The first being a staging environment. As you mention, this is a near copy of the production environment architecture and can be used to test releases that are due to be moved into production or to allow users to view upcoming features before they are released.

The second is a data staging area. Although it is called staging, it could actually be located within the production environment (as has been the case in most companies that I have worked for). This area is used to cleanse and validate data extracted from OLTP systems before being loaded into a data warehouse.

Generally, at least in my work history, I have found that production data may not be copied into the staging environment due to legal or logistical constraints. Mostly I have found that it is because developers/testers should not access sensitive data such as personal details, credit cards etc... but it can be copied into a data staging area in preparation of loading it into the data warehouse.

Over time, as you gain more experience it gets easier to tell which one an individual is referring to - but until then, just remember that live data should not leave the production environment and it is perfectly fine to have a dedicated area within production in which you can process your data.