sql architect regarding one to many relationship between table I have theoretical question.

I have two tables. as follows:

Table A

--------------------------
 id | name | type  | bid |
--------------------------
 1  |  x   | round | 1   |
--------------------------
 2  |  y   | square| 1   |
--------------------------

bid is foreign key to table B. Table A to Table B is one to many relationship.

Table B

-----------------------------
 id | home_zip | office_zip |
-----------------------------
 1  |  6993    | 6666       |
-----------------------------
 2  |  5867    | 6993       |
-----------------------------

I am doing search and finding information on home_zip and office_zip column and dumping into another table C. Table B to table C is many to one relationship

Table C

------------------------
 id | zip | information|
------------------------
 1  | 6993| 66668      |
------------------------
 2  | 6666| 98237      |
------------------------

My question:

Is it is good to add column b.id in table C, and create duplicated record? or don't include b.id in table C.

I would just add b.id into table C and avoid columns [zip] and [information] at all because you can easily find [zip] and [information] by joining table B via b.id (which is a PK)


MYSQL Return lines whose sum of one field equals another tables field I have a table of job payments, and when matching to transactions need to match up payments on the same day for the same customer that equal a single transaction for that day. I have been able to match, but only return the aggregated total, not each row.

I understand a join will be required but haven't been able to get it right.

DB:

create table jobs(
  jobid int(7),
  datein date,
  total numeric(5,2),
  chargeto int(5)
);
create table payments(
  payid int(7),
  paymentamount numeric(5,2),
  paymentjobno int(5),
  paymentdate date,
  paymenttype int(1)
);

insert into jobs (jobid, total) values (1000, 100, 4);
insert into jobs (jobid, total) values (1001, 50, 4);
insert into jobs (jobid, total) values (1002, 25, 4);
insert into jobs (jobid, total) values (1003, 220, 6);
insert into jobs (jobid, total) values (1004, 200, 7);

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, 01/10/2019, 1);

Code I have:

$groupmatches = $con->query("
SELECT j1.*, j2.*
FROM jobs AS j1
JOIN (SELECT jobs.jobid, payments.paymentamount, payments.paymentjobno, COUNT(*) AS count
  FROM (jobs LEFT JOIN payments ON payments.paymentjobno = jobs.jobid)
  WHERE payments.paymentdate = '$dt' AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
  GROUP BY jobs.chargeto
  HAVING SUM(payments.paymentamount) = '$at' AND COUNT(*) > 1) AS j2
ON j1.jobid = j2.paymentjobno;
");

It is matching and returning a single line with a count value of how many lines added up to a match, but I can't return each of those lines. What am I doing wrong?

I think I've grasped your problem - which I solved like this (see the fiddle here):

Your data isn't quite correct in your sample above. I modified it slightly thus:

jobs table:

insert into jobs values (1000, '2019-10-01', 100, 4);
insert into jobs values (1001, '2019-10-01', 50, 4);
insert into jobs values (1002, '2019-10-01', 25, 4);
insert into jobs values (1003, '2019-10-01', 220, 6);
insert into jobs values (1004, '2019-10-01', 200, 7);

The datein and paymentjobdate are the same - same day payments from your question: - need to match up payments on the same day for the same customer that equal a single transaction for that day.

payments table:

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);

Job 1000 - note that there are 4 separate payments of 25 = jobs.total is 100, so the SUM matches.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1);

Job 1001, one single payment which matches the total.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 10, 1002, '2019-10-01', 1);

Job 1002 - total is 25, but payment is only 10 - not a match.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1);

Single matching payment for jobid 1003.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 100, 1004, '2019-10-01', 1);

Finally, job 1004 - total is 200, payment is only 100 - no match for SUM.

Then, I performed the following SQL (1st step):

SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
FROM payments p
-- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
-- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
GROUP BY p.paymentjobno;

Result:

p_jobid the_sum
1000    100.00
1001    50.00
1002    10.00
1003    220.00
1004    100.00

Now I have both the jobid and the SUM, so now can join to payments on
both the jobid and that SUM using this as a subquery. The SQL:

SELECT j.*, p.*  -- choose your fields as required!
FROM jobs j
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p
  -- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
  -- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
  GROUP BY p.paymentjobno;
) AS t1
  ON j.jobid = t1.p_jobid
AND j.total = t1.the_sum
JOIN payments p
ON j.jobid = p.paymentjobno;

Result:

jobid   datein  total   chargeto    payid   paymentamount   paymentjobno    paymentdate paymenttype
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1001    2019-10-01  50.00   4   2   50.00   1001    2019-10-01  1
1003    2019-10-01  220.00  6   4   220.00  1003    2019-10-01  1

Which is, if I've understood you correctly, what you were looking for. You have a match on the total for jobid 1000's 4 payments, and the single payments of the total for jobids 1001 and 1003 but not on 1002 and 1004 which haven't been fully paid on that day!

If this doesn't correspond to your request, please let me know and I will try and fix it. May I recommend that you use underscores, i.e. python_case_for_variables to separate your variables (table and column names) into legible parts and use UPPER CASE for SQL - it makes things much easier to read - if you don't like that, search for "SQL coding standards" and find one that appeals and then stick to it! May I also suggest that you use singular names for tables - a table is a set of things!

Finally, always include a version number with your question - with MySQL especially, it can make a huge difference! Also, what could greatly help would be a working fiddle - it's things like this that get you quality answers quickly! Help us to help you. My profile has a few articles on how to ask questions here - you might want to take a look? Finally, as I said in comments - welcome to the forum! :-)

EDIT in the light of OP's comment and fiddle:

What you want is GROUP_CONCAT! Fiddle here.

SQL:

SELECT
      j.jobid,
      GROUP_CONCAT(p.paymentamount SEPARATOR ', ') AS payment_hist
FROM jobs j
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p GROUP BY p.paymentjobno
) AS t1 ON
  j.jobid = t1.p_jobid AND j.total = t1.the_sum
JOIN payments p ON
  j.jobid = p.paymentjobno
GROUP BY j.jobid;

Result:

jobid.  payment_hist
1000    25.00, 25.00, 25.00, 25.00
1001    50.00
1003    220.00

I would advise strongly that you avoid like the plague anything to do with comma separated lists when working in SQL. They are contrary to the relational model and breach first normal form. They are also tricky to deal with despite the existence of constructs like GROUP_CONCAT, STRING_AGG and LIST_AGG. If you are going to be doing a lot of this kind of work and you have the possibility, I would change to PostgreSQL - it's much more powerful for this sort of work.

One "fly in the ointment" though!

I hope the above anwers your query but I'm not sure that it corresponds to your own fiddle. For example, you have

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, '2019-10-01', 1);

and for jobid 1000, you have

insert into jobs values (1000, '2019-10-01', 100, 4);

However, the payment above is the one and only payment corresponding to the full amount for jobid 1000, but in your result set, you have

jobid     payment_hist
1000,     100, 175

The first payment is the 100, but I'm at a loss to understand where the 175 comes from - it's not from other payments and all of your jobids appear to have been paid in full in a single payment, so no partial payments which is what I thought your question was about?


select data which is bigger than buffer pool size Imagine we have a table which has 100 Million rows and 80 GB size.

Every rows has Text column and multiply integer columns.

we set Innodb_buffer_pool_size to 40G

I executing this Query:

select text,id,like_count from example where time > 'xxx-xx-xx' and time < 'xxx-xx-xx'

So if this query need to read 50G of data file (because of query) and move to Buffer Pool. I want to know how buffer pool handle this data.

because We set buffer pool to 40G but query need 50G to handle it.

Your scenario sounds exactly like what a mysqldump will do : Push every data and index page out of the InnoDB Buffer Pool. You can see that this is the case because when a mysqldump is in progress, the processlist will have something like SELECT /*!N SQL_NO_CACHE */ from .... See an example of the appearance of a SELECT from a mysqldump in MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */
It makes sense that any full table scan will push every data page from the table into the Buffer Pool, causing every old page in the Buffer Pool to get evicted. If you push 50G of data pages into your 40GB Buffer Pool, your Buffer Pool will contains the last 80% of that table because the first 20% would have been pushed into the Buffer Pool and then get pushed right out. I mentioned about 4 months ago (See my old post Is it safe to run parallel innodb single-transaction dumps of individual tables?)
SUGGESTION
If your goal is to keep Buffer Pool's contents looking the same before and after a huge query, there is only one thing you can do: Dump the map of the Buffer Pool to disk, run your big query, and restore the Buffer Pool from the map.
mysql> SET GLOBAL innodb_buffer_pool_dump_now = 1;
mysql> ( Your Big 50GB query )
mysql> SET GLOBAL innodb_buffer_pool_load_now = 1;

Please see MySQL Documentation on these options

innodb_buffer_pool_dump_now
innodb_buffer_pool_load_now
Saving and Restoring the Buffer Pool State


MongoDB Fragmentation affect the Performance in WiredTiger Im using MongoDB 3.6 I have a huge number of fragmentation in my collections. 300+ GB on one collection. WiredTiger is the storage engine.

I know its bad for the OS and much wasted space. This affects the memory as well if we use MMAP engine. But I don't know how the fragmentation affects the WiredTiger?

It will be less efficient to fetch data if you have a lot of space available for reuse. However, that space could be reused if you continue to insert or update documents in the collection.

If you've dropped a significant amount of data (which sounds like ~300GB in one of your collections) you can reclaim space by either:

re-syncing members of a replica set (recommended if you want to reclaim space from all collections)
running the compact command (important caveat: this will block all operations for the database the operation is running on)

For either approach I would use rolling maintenance to maintain availability.

A resync would be the most effective option. The compact command is riskier to run as a replica set member may become stale while running compact offline (and require resync); this operation may also not be able to free as much disk space as a resync and needs to be run per collection.


Trying to insert only unique items into one table and then create a many to many relationship in a join table only if that join does not exist Probably best to explain this with an example.

I have 3 tables two of which are 'regular' tables and the third which is a many to many relationship table joining the first two.

I have a stored procedure that creates a temp table, Then inserts the values from the temp table into an existing table B only if they dont exist, then creates a many to many relationship between table B and the other regular table. This works fine until I hit a value that has been inserted into B previously and has an existing relationship in the many to many table as well, then I get a constraint violation.
Where I'm having issues is trying to get the insert into the many to many join table to only occur if there is not existing entry.

CREATE TABLE A
    (ID INT PRIMARY KEY,
     Number INT)

CREATE TABLE B
    (ID INT IDENTITY(1,1) PRIMARY KEY,
     Name nvarchar(100))

CREATE TABLE AtoB(
[AId] [int] NOT NULL,
[BId] [int] NOT NULL,
 CONSTRAINT [AtoB_PK] PRIMARY KEY
(
[AId] ASC,
[BId] ASC
),
CONSTRAINT FKtoA FOREIGN KEY (AId) REFERENCES A(ID),
CONSTRAINT FKtoB FOREIGN KEY (BId) REFERENCES B(ID),
)

some initial values

insert into A ( ID, Number) Values (1,1000),(2,1001),(3,1234);

A Working (first time) insert

DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @tableAid INT
DECLARE @tableBid INT

SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')

INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS
( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable
T ON B.Name = T.firstname

The last part of this statement will fail ( as expected ) if you run it again but with some different values

INSERT INTO @temptable (firstname) VALUES ('John'),('Ted'),('Alice')

What I want to happen would be that on a second run Ted and Alice would be inserted with many to many relationships.  I've tried

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable
T ON B.Name = T.firstname WHERE NOT EXISTS
SELECT ( * FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name =
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id

and several variations but I usually end up with no values inserted into the many to many table at all.

Ideas on whats wrong?

Thanks

Long reply to answer below
That means that only if none of the items exist you need to insert.
Obviously, that's not what you want" - This is exactly what I want. In table B I want one one entry for each name. and this portion of the stored procedure works correctly
    INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT
    EXISTS
    ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

The problem starts when I get to the next portion where I want to create the many to many relationship but only if its a new name value in table B. If the values passed in that fill the temptable contain an item what was previously associated between A and B the statement is terminated and the new ( and previously uninserted into B ) values do not get entered.

You could use NOT EXISTS as your first insert (or LEFT JOIN / IS NULL) for the second insert to work like the first, insert only when the row doesn't exist. Another way is with EXCEPT:

INSERT INTO B (Name)
SELECT firstname FROM @temptable
EXCEPT
SELECT Name FROM B ;

INSERT INTO AtoB (AId, BId)
SELECT @tableAid, B.Id FROM B JOIN @temptable T ON B.Name = T.firstname
EXCEPT
SELECT Aid, Bid FROM AtoB ;


Running Excel Macro Code by SSIS package configured with SQL Server Job agent I am trying to execute an Excel macro. It executes successfully on Visual Studio Data Tools 2012. But When I configured this package with SQL Server Job agent and try to execute this, it throws an error.

  Executed as user: *****. Microsoft (R) SQL Server Execute Package
  Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft
  Corporation. All rights reserved.    Started:  1:07:32 AM  Error:
  2016-05-20 01:07:32.99     Code: 0x00000001     Source: Trigger Macro
  in BAC_HE Excel      Description: Exception has been thrown by the
  target of an invocation.  End Error  DTExec: The package execution
  returned DTSER_FAILURE (1).  Started:  1:07:32 AM  Finished: 1:07:32
  AM  Elapsed:  0.297 seconds.  The package execution failed.  The step
  failed.

And Error logged in log file is

  OnError,CEAGPDS0102,*****,Trigger Macro in BAC_HE
  Excel,{1aed82a9-3da5-4044-b1f1-573385d7c8b7},{8AAF675E-DA4F-454D-8CF7-61E6B511C0AA},5/20/2016
  6:57:18 AM,5/20/2016 6:57:18 AM,1,0x,Exception has been thrown by the
  target of an invocation.

Does the service account for the SQL Server Agent have access to the directories for the objects being modified by the Excel macro?  My experience is when code works in your development tools but fails when running through the SQL Server Agent, it is because the service account for the SQL Server Agent is missing permissions that your user account has when running interactively in the development tools.


Should I use the deprecated MD5 function in SQL Server? We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this dilemma of using of risking it by using this function or incurring storage and performance overhead of using SHA_256.  It's frustrating Microsoft decided to deprecate these functions even though they are still useful in certain scenarios.

This project isn't a critical component of the business. We'll likely go with SHA_256 but is this the right choice? Should new development always avoid deprecated functions?

For context - daily will be about 1-2 million upserting into a 400 million row table comparing hashbytes on the fly. about 30 columns wide

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-2017

Choosing the right algorithm in HashBytes function

From Microsoft's Documentation:

  When a feature is marked deprecated, it means:

  The feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features.
  We strive not to remove a deprecated feature from future releases to make upgrades easier. However, under rare situations, we may choose to permanently remove the feature from SQL Server if it limits future innovations.
  For new development work, we do not recommend using deprecated features.

Another part of that page says:

  The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been determined.

Depending on how you're using MD5 and HASHBYTES, you may have other options available aside from using SHA_256.

You may be interested in using this methodology I documented to determine which rows have changes.  It uses a rowversion column in the source table to allow you to instantly see rows that are new or have changes.  This avoids using a deprecated feature, and is much faster than any hashing function will ever be.


comparison operator in count() is not working The query (SELECT 15 > 14 FROM Duel) Working in MySql, but when I tried in MS-SQL Server getting following error:

  SQL Error(102): Incorrect syntax near '>'

I have a requirement where I want to see if at least 1 record exist return 1 or else 0

SELECT count(emp.name) > 0
        FROM ****
        WHERE *** IN (***)

Check the db fiddle

I have a requirement where I want to see if at least 1 record exist return 1 or else 0

You can do that with following query in MS-SQL (db-fiddle)

SELECT CASE WHEN count (*) >= 1 THEN 1 ELSE 0 END
FROM new_table_name

Not sure how mySQL treats this SELECT 15 > 14 FROM Duel, but in MS-SQL it's not complete statement (even columns exists with the name 15 and 14), operators (=, <>, !=, >, <, LIKE, IN, NOT IN) usually used in WHERE and ON clauses. When it used in SELECT clause it must complete the Boolean logic (CASE, IIF).


Why are constraints applied in the database rather than the code? Why are constraint applied in Database? Will it not be more flexible to put it in the code?

I'm reading a beginners book on implementing databases, so I'm asking this as a beginner. Let's say I have designed a database, including this entity model:

 entity type    |   sub-types
----------------+--------------------------------------------
   Person       |   Employee, Student,       ...
   Student      |   Graduate, Undergraduate, ...
   Employee     |   Teacher,  Administrator, ...

Current constraints:

A registered person on the system can only be a Student or an Employee.
Person entity requires uniqueness of social number, which we presume every person holds only a single unique one (aka, a good enough primary key). (see #1)

Later we decide to remove the number 1: If one day the college decides that the Teacher (the Employee sub-type) can also be Student, taking courses in their free time, it's much harder to change database design which could have thousands, millions, billions, zillions of entries rather than just changing the logic in code: just the part which didn't allow a person be registered both as a student and an employee.

(It's very improbable but I can't think of anything else right now. Apparently it is possible).

Why do we care about business rules in database design rather than in code?

#1: A note 7 years later, a real life example:
I have seen a government where because of a mistake, issued SSNs were duplicated: multiple people, same SSN. Those designing the original DB definitely made that mistake of not applying this uniqueness constraint in the database. (and later a bug in the original application? multiple applications using the shared database and not agreeing where to put, check and enforce the constraint? ...).
This bug will go on to live in the system and all the system developed after which rely on that original system's database, for many many years to come. Reading the answers here I learned to apply all the constraint, as many of them as possible, wisely (not blindly) in the database to represent the real physical world out there as good as I can.

Because:

I want all the data in the database to be subject to the same constraints, not just the new data to be subject to the constraints in the version of the code that's running today.
I want declarative constraints, not programmatic constraints.
Data in the database often outlives the code that's written to interact with it today. And that data -- not the code -- is the organisation's asset.
My code becomes much simpler when I know that all data is subject to rigorous constraints. I no longer have to consider special cases which I know that the database guarantees to be impossible.

Just some reasons that are important to me.


Missing Index Not Displaying In Execution Plan I recently installed SQL Server Management Studio 2014 (Developer Edition), and I noticed that I am no longer seeing the missing index feature.

What I have done:

Ran the workload through Database Engine Tuning Advisor, so I know that the table is missing an index.
Found nothing online about enabling the feature
Sifted through every option that seemed related.

Any suggestions would be greatly appreciated. I was previously running SQL Server 2012 Developer Edition, prior to 2014.

UPDATE

I am able to get it to display the missing index in certain cases. Take a look at these two queries:

For this query, no missing index is displaying in the execution plan. The WHERE Clause field is a varchar(512) and has no indices on this column.

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

For this query, it is showing a missing index in the execution plan. Age is an INT

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

Missing Index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

I also tried a query with a VARCHAR(500) field, and it did return a missing index. I am really scratching my head now.

Well just while your Tuning Advisor displays a missing index, this won't mean, that your current running query would benefit from this index. The missing index will just be displayed if the current plan would take a benefit from an index.

You can also take a look at one of these DMV's, they will display missing indices for the current db.

SELECT *
FROM sys.dm_db_missing_index_details

If you want additional information, you can also use this query from me:

SELECT  id.index_handle, DB_NAME(id.database_id), id.object_id, id.statement AS object_name,
        gs.avg_user_impact, gs.avg_total_user_cost,
        id.equality_columns, id.inequality_columns, id.included_columns,
        gs.unique_compiles, gs.user_seeks, gs.last_user_seek, gs.last_system_seek,
        gs.user_scans, gs.last_user_scan, gs.system_scans
FROM sys.dm_db_missing_index_details as id
INNER JOIN sys.dm_db_missing_index_groups as g
        ON id.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as gs
        ON g.index_group_handle = gs.group_handle
ORDER BY gs.avg_user_impact DESC

Edit based on your last update

Well it's quite easy to determine why you won't get a suggestion.

First of all, why you'll get an proposed index for this query:

SELECT SomeVarchar20 FROM SomeTable WHERE Age=30

If you create the index, as suggested below, you'll completely serve the index out of the index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SomeTable] ([Age])
INCLUDE ([SomeVarchar20])

The SQL Server will be able to search for a data page using the Age, as defined in your index. In addition, due to the fact, that you only select the column SomeVarchar20, it is proposed to be added to the leaf level pages of your index (defined by the INCLUDE). This will help the SQL Server to find the right pages based on the Age in the B-Tree architecture. After he found the last row, which holds a pointer to the HEAP or the Clustered Index where all other properties lies, he also gets to the included columns on the leaf level pages. Due to the fact, that he found everything to display in the index itself, he won't go further to your HEAP or your Clustered Index. This will drastically improve your read (!) performance.

The fact, why you won't get a suggestion to add an index to this column is quite clear to me:

SELECT SomeVarchar FROM SomeTable WHERE Name='Adam'

You say that your Column Name is an varchar(512) this means one value can be 512 Bytes long. In fact a SQL Server page can hold round about 8060 bytes. This means you may store up to 16 names (maybe more if you don't use the 512 bytes all the way) per page. This won't improve performance drastically, as SQL Server might even need to iterate (seek) over many many pages. In fact, he needs to maintain all those pages additionally on each insert/update/delete.

If you just use SomeVarchar, which may be a varchar(512) too, it would drastically raise the leaf level pages, which will make the index even inefficient. This is the reason why your SQL Server might not provide a missing index hint for your SELECT.

Hopefully this will clear up all other things in your mind. Otherwise let me know.


Making a recursive CTE like query, but for many records I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code :

declare @Id integer

;WITH CTE AS
(
    SELECT  a.[Id],
            a.[ParentId]
    FROM    [Area] a WITH (NOLOCK)
    where a.[Id] = @Id

    UNION ALL

    SELECT  a.[Id],
            a.[ParentId]
    FROM [Area] a WITH (NOLOCK)
    INNER JOIN CTE cte ON cte.[ParentId] = a.[Id]
)

SELECT top 1 a.[Id]
FROM CTE a
WHERE a.ParentId is null

It works as long as I'm working with one id, here @Id.

But I can't find a way to do the same but for a list of Ids, without using cursors, as CTE seems to be OK only when y'oure working on one record.

Could someone point me at the right direction ?

Many thanks

Assuming the original table design is somewhat like this:

CREATE TABLE dbo.Area
(
    RowID integer PRIMARY KEY,
    GroupID  integer NOT NULL,
    ParentID integer NULL,
);

Sample data:

INSERT dbo.Area
    (RowID, GroupID, ParentID)
VALUES
    (1, 1, NULL), -- Root
    (2, 1, 1),
    (3, 1, 2),
    (4, 2, NULL), -- Root
    (5, 2, 4),
    (6, 2, 5),
    (7, 2, 6);

The following recursive CTE query finds roots for the RowID list given in the anchor:

WITH R AS
(
    SELECT
        A.RowID,
        A.RowID AS OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    WHERE
        A.RowID IN (3, 7)

    UNION ALL

    SELECT
        A.RowID,
        R.OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    JOIN R
        ON R.ParentID = A.RowID
)
SELECT
    R.OriginalRowID,
    R.RowID
FROM R
WHERE
    R.ParentID IS NULL
ORDER BY
    R.OriginalRowID
OPTION (MAXRECURSION 0);

The output shows each supplied RowID and the root for that group:


Suggested ways to route traffic between master / slave I have multiple Postgres servers up, running in master / slave streaming replication mode.

I was wondering what the suggested methods of routing write traffic to the master, and the read traffic to the slave nodes.

PgPool-II can do this, with some limitations, as outlined in its user manual.

The best way is to make the application aware of which queries are read-only/poolable, and which are write queries that must go to the master.

There aren't really any other alternatives at this point.


What is the difference between membership and securables? Does one override the other? When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

From what I've read in the MSDN documentation (Securables and Database-Level Roles), I gather 'Membership' options are database-wide, whereas 'Securables' are more specific. If this is the case, which would be overridden if there was a conflict?

For example, if I wanted to set a user's access for a particular table to be read-only, but all others read-write, would I select 'db-datareader' and 'db-datawriter', and then specify a securable for the read-only table (with all options except 'Select' set to 'Deny')?

When setting permissions for a database user, what is the difference between 'Membership' and 'Securables'?

Membership is what the user belongs to... Since users are a database principal this could be database level roles, application roles, etc.

Securables are what the name implies, items that are authorized access to.

So, let's say we have a login which is a server level principal. The login can belong to a server level role.. it can be a member.. of that role. Then we give the server level role access to server level securables, such as and endpoint. Maybe we grant control endpoint to this role. Since the login has membership in this role it is granted the control endpoint permission against the endpoint securable.


Why is it taking longer to create an index after column size increases? Our vendor changed column widths on almost every column in the entire database.  The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before the vendor's upgrade we could create the index in 2 hours. Now it takes days. What they have done is increase any varchar(xx) size to varchar(256).  So most columns used to be varchar(18) or varchar(75), etc.

Anyway the primary key consists of 6 columns that combined width was 126 characters. Now after the upgrade, the primary key is 1283 characters which violates SQL Servers limit of 900 characters.  The entire table column width went from a total combined varchar count of 1049 to a total combined varchar count of 4009.

There is not an increase in data, the table doesn't take up any more "space" than it did before all the column width increase, but performance to create something as simple as an index is now taking an unreasonable amount of time.

Can anyone explain why it is taking so much longer to create and index when the only thing done was increasing the size of the columns?

The index we are trying to create is nonclustered since the pk is the clustered index.  After several attempts to create the index, we gave up. I think it ran 4 or 5 days without completion.

I tried this in a non-production environment by taking a file system snapshot and brought the database up on a quieter server.

Remus has helpfully pointed out that the max length of the VARCHAR column impacts the estimated row size and therefore memory grants that SQL Server provides.

I tried to do a bit more research to expand on the "from this on things cascade" part of his answer. I don't have a complete or concise explanation, but here is what I found.

Repro script

I created a full script that generates a fake data set on which index creation takes roughly 10x as long on my machine for the VARCHAR(256) version. The data used is exactly the same, but the first table uses the actual max lengths of 18, 75, 9, 15, 123, and 5, while all columns use a max length of 256 in the second table.

Keying the original table

Here we see that the original query completes in about 20 seconds and the logical reads are equal to the table size of ~1.5GB (195K pages, 8K per page).

-- CPU time = 37674 ms,  elapsed time = 19206 ms.
-- Table 'testVarchar'. Scan count 9, logical reads 194490, physical reads 0
CREATE CLUSTERED INDEX IX_testVarchar
ON dbo.testVarchar (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

Keying the VARCHAR(256) table

For the VARCHAR(256) table, we see that the elapsed time has increased dramatically.

Interestingly, neither the CPU time nor the logical reads increase. This makes sense given that the table has the exact same data, but it doesn't explain why the elapsed time is so much slower.

-- CPU time = 33212 ms,  elapsed time = 263134 ms.
-- Table 'testVarchar256'. Scan count 9, logical reads 194491
CREATE CLUSTERED INDEX IX_testVarchar256
ON dbo.testVarchar256 (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

I/O and wait stats: original

If we capture a bit more detail (using p_perfMon, a procedure that I wrote), we can see that the vast majority of the I/O is performed on the LOG file. We see a relatively modest amount of I/O on the actual ROWS (the main data file), and the primary wait type is LATCH_EX, indicating in-memory page contention.

We can also see that my spinning disk is somewhere between "bad" and "shockingly bad", according to Paul Randal :)

I/O and wait stats: VARCHAR(256)

For the VARCHAR(256) version, the I/O and wait stats look completely different! Here we see a huge increase in the I/O on the data file (ROWS), and the stall times now make Paul Randal simply say "WOW!".

It's not surprising that the #1 wait type is now IO_COMPLETION. But why is so much I/O generated?

Actual query plan: VARCHAR(256)

From the query plan, we can see that the Sort operator has a recursive spill (5 levels deep!) in the VARCHAR(256) version of the query. (There is no spill at all in the original version.)

Live query progress: VARCHAR(256)

We can use sys.dm_exec_query_profiles to view live query progress in SQL 2014+. In the original version, the entire Table Scan and Sort are processed without any spills (spill_page_count remains 0 throughout).

In the VARCHAR(256) version, however, we can see that page spills quickly accumulate for the Sort operator. Here is a snapshot of the query progress just before the query completes. The data here is aggregated across all threads.

If I dig into each thread individually, I see that 2 threads complete the sort within about 5 seconds (@20 seconds overall, after 15 seconds spent on the table scan). If all threads progressed at this rate, the VARCHAR(256) index creation would have completed in roughly the same time as the original table.

However, the remaining 6 threads progress at a much slower rate. This may be due to the way that memory is allocated and the way that the threads are being held up by I/O as they are spilling data. I don't know for sure though.

What can you do?

There are a number of things you might considering trying:

Work with the vendor to roll back to a previous version. If that's not possible, let the vendor that you are not happy with this change so that they can consider reverting it in a future release.
When adding your index, consider using OPTION (MAXDOP X) where X is a lower number than your current server-level setting. When I used OPTION (MAXDOP 2) on this specific data set on my machine, the VARCHAR(256) version completed in 25 seconds (compared to 3-4 minutes with 8 threads!). It's possible that the spilling behavior is exacerbated by higher parallelism.
If additional hardware investment is a possibility, profile the I/O (the likely bottleneck) on your system and consider using an SSD to reduce the latency of the I/O incurred by spills.

Further reading

Paul White has a nice blog post on the internals of SQL Server sorts that may be of interest. It does talk a little bit about spilling, thread skew, and memory allocation for parallel sorts.


Get IDs of similar rows by criteria I basically want to query this table:

CREATE TABLE tbl (id, thing, price, type);
AS VALUES
  (1, 'apple', 1.00, 'fruit'),
  (2, 'orang', 1.00, 'fruit'),
  (3, 'banan', 1.00, 'fruit'),
  (4, 'potat', 2.00, 'veg'),
  (5, 'carro', 2.00, 'veg'),
  (6, 'onion', 2.00, 'veg');

I want to grab the ids of records with the same attributes (in this case, price and type) so 1, 2, 3 and 4, 5, 6.

I don't want to have to specify the $1.00 or fruit values, just group by whatever shows up in those columns, if that makes sense.

You can use the aggregate function array_agg() for this purpose.

select price, type, array_agg(id) ids
from   tbl
group by price, type

price | type  | ids
----: | :---- | :------
 1.00 | fruit | {1,2,3}
 2.00 | veg   | {4,5,6}

dbfiddle here


Recreate ##MS_SSISServerCleanupJobLogin## user as [##MS_SSISServerCleanupJobUser## During an install of SQL Server 2014 Service Pack 3 we received the following error:

Error: 15151, Severity: 16, State: 1.

Cannot find the user '##MS_SSISServerCleanupJobUser##', because it does not exist or you do not have permission.

Error: 912, Severity: 21, State: 2.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 15151, state 1, severity 16.

On the instance there is the SQL login [##MS_SSISServerCleanupJobLogin##]

On the SSISDB database there is a SQL user linked to this login called [##MS_SSISServerCleanupJobLogin##] Created the day after the login.

The SSISDB user should be [##MS_SSISServerCleanupJobUser##] and created at the same time as the login.

The solution appears to be to drop the [##MS_SSISServerCleanupJobLogin##] user and create the correct [##MS_SSISServerCleanupJobUser##] user.

USE [SSISDB]
GO
DROP USER [##MS_SSISServerCleanupJobLogin##]
GO

USE [SSISDB]
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##]
GO
USE [SSISDB]
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] WITH DEFAULT_SCHEMA=[dbo]
GO

Can anyone see any problems arising from dropping and recreating the SSISDB user?

This is a production instance. All test instances have the login and user configured correctly so the service pack installed successfully.

Many Thanks

Simon

I think the solution will work. Don't forget to grant permissions:

USE [SSISDB]
GO
GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO [##MS_SSISServerCleanupJobUser##]
GO
GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO [##MS_SSISServerCleanupJobUser##]
GO


Mysql table column to row i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given,
is there any way to output table something below output table?

My MySql query following

SELECT a.`person_id` AS STUDENT_ID,q.`questions`,a.`answer` as ANS,q.`question_id`
FROM
questions q
left join answer a on a.`question_id`=q.`question_id`
left join table_info t on q.`question_id`=t.`question_id`

after this query table look like this

 student_id   Question    Answer    Question_Id Ans_id
------------------------------------------------------------
    1           Name        Jonathan     1       1
    1           Age         12           2       2
    1           Sex         Male         3       3
    1           Area        LA           4       4
    2           Name        Mary         1       5
    2           Age         11           2       6
    2           Sex         Female       3       7
    2           Area        NY           4       8
    3           Name        David        1       9
    3           Age         12           2       10
    3           Sex         Male         3       11
    3           Area        NY           4       12

I need output like following

Student_Id   Name       Age     Sex       Area
-----------------------------------------------
1            Jonathan   12      Male      La
2            Mary       11      Female    NY
3            David      12      Male      NY

Questions table:

CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `questions` varchar(250) DEFAULT NULL,
  `questions_cat` int(11) DEFAULT NULL COMMENT 'which category falls this questions. when load questions, then we can load questions by category',
  `question_order` int(11) DEFAULT NULL COMMENT 'sequence/order of the Q',
  `questions_status` varchar(45) DEFAULT NULL,
  `questions_type_id` int(11) NOT NULL,
  `text_question_top` varchar(200) DEFAULT NULL,
  `text_question_bottom` varchar(200) DEFAULT NULL,
  `input_class` varchar(100) DEFAULT NULL,
  `input_id` varchar(50) DEFAULT NULL COMMENT 'eg id="idName"',
  `input_placeholder` varchar(50) DEFAULT NULL COMMENT 'PlaceHolder',
  `input_onchange` varchar(200) DEFAULT NULL COMMENT 'Onchange, like JS',
  `input_other` varchar(200) DEFAULT NULL COMMENT 'text only, eg ng-click="callFunction"',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  `view_text` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Answer table:

CREATE TABLE `answer` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `answer` varchar(300) DEFAULT NULL,
  `question_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `answer_other` varchar(100) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`answer_id`,`question_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8

table_info table:

CREATE TABLE `table_info` (
  `tableInfo_id` int(11) NOT NULL AUTO_INCREMENT,
  `table_link` varchar(200) NOT NULL,
  `function` varchar(100) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`tableInfo_id`),
  KEY `fk_tableInfo_Questions` (`question_id`),
  CONSTRAINT `fk_tableInfo_Questions` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

SELECT Student_Id,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Name' ) AS Name,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Age' ) AS Age,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Sex' ) AS Sex,
       ( SELECT Answer FROM t
             WHERE student_id = x.Student_Id
               AND Question = 'Area' ) AS Area
    FROM ( SELECT DISTINCT Student_Id FROM t ) AS u
    ORDER BY Student_Id;

Performance would benefit from

INDEX(Student_Id, Question, Answer)


Restoring backup from mysqldump does not remove additions I am restoring a backup from a mysqldump and noticing that if I made additions (in the form of new tables) to the database they persist after the restore.  I can go back in and remove those tables to get back to the original state, but as I need to test migrations, I'd like to bring the DB back to its state during the dump without manually going in and removing tables.

I suppose I could do a DROP Database XXX before the restore, but will that hamper my current restoration methods?

mysqldump has an --add-drop-database option that will put those in the file for you.

It will add this, before each database in the file:

/*!40000 DROP DATABASE IF EXISTS `accounting`*/;

For the benefit of anyone unfamiliar with magic comments, this isn't a comment.  It's a statement that will be executed by MySQL 4.00.00 and higher, disguised as a comment for backwards-compatibility.

And, of course, this assumes your database, like mine, is called "accounting."

Note, though, that for --add-drop-database to work, you need to specify the database(s) to back up using either --all-databases or the --databases YOUR_DB_NAME command line option to mysqldump.  You "can" make a backup of a single database with just the database name on the command line by itself, without the --databases option preceding it... that also sort of works but is really not a good way to go, unless you intend to restore the backup into a different database name than the one you got it from, which, thanks to the way that option works, you might do even if it wasn't your plan.

Using --databases YOUR_DB_NAME or --all-databases automatically adds CREATE DATABASE and USE your_db_name; statements to the top of each schema in the file.  This means you don't have to specify it when you're restoring, and if you forget or get it wrong, the data still goes to the right place -- the "right" place being the place the file says it should go.

The --add-drop-database option adds the DROP statement to clear the way for the new one, immediately before the CREATE and USE statements.


SQL Server Installation choices I'm installing SQL Server 2008 R2 which would be helpful in

A. querying database.

B. Analysis Services

C. Reporting services and BIDS (SSRS)

D. Powerpivot in Sharepoint

During installation I get two options:

a. SQL Server feature installation: this would install database engine, analysis services, reporting services etc

b.  SQL Server for powerpivot for sharepoint: This would be helpful for powerpiviot in Sharepoint. Not sure this option would install all the options mentioned in first option - SQL Server feature installation.

My Question: Which option I should select? If i go for option-a at present, can I later change it to powerpivot option i.e option b.???

Install the tools you need now, and no others.  You can always add more features to the installation later.

If you're not installing reports or PowerPivot into your existing SharePoint installation, don't install them.

When most people say that they want to get "SQL Server" installed, they mean just the database engine.  If that sounds like you, install the Database Engine and Management Tools, and you'll have a basic SQL Server up and running.


Select different combinations for same table msSQL I have a table which has 2 fields OS_ID and OZ_ID. I want to select combinations of OS_ID for different OZ_ID. What would be the query for that?

Input

OS_ID   OZ_ID
----    ----
1       1
2       1
3       2
4       2
5       2
6       2
7       3
8       3
9       3

Output

combination row

1,3,7
1,3,8
1,3,9
1,4,7
1,4,8
1,4,9
1,5,7
1,5,8
1,5,9
1,6,7
1,6,8
1,6,9
2,4,7
2,4,8
...

Looks like you want Cross Joins:

SELECT t1.OS_ID, t2.OS_ID, t3.OS_ID
FROM tab AS t1
CROSS JOIN tab AS t2
CROSS JOIN tab AS t3
WHERE t1.OZ_ID = 1
  AND t2.OZ_ID = 2
  AND t3.OZ_ID = 3


Backup suddenly is taking too long to complete We have an SQL Server 2014 Enterprise Edition database around 3 TB in size. We are taking compressed full backup weekly, it was working fine till last week and now suddenly it is taking 18 hours to complete.

When backup completes the backup size is around 550 GB.

Backup drive has around 950 GB disk space free.

What could be the issue?

There's basically 4 things involved with backup speed:

Reading from the data files - you can test this by doing a backup to NUL:, as in this example, which won't break your log-backup chain for full recovery model databases:

BACKUP DATABASE xyz TO DISK = 'NUL:' WITH COPY_ONLY;

Compressing the data - watch CPU pressure during the backup.
Getting it through the network path to its final location (optional, only for network backups) - test this with a network file copy.
Writing it to the disk on its final target location - test this with something like CrystalDiskMark or DiskSpd, but has to be done locally (not over a network pipe, since you want to avoid #3 affecting your results).

Third party backup products like Quest LiteSpeed, Idera SQLsafe, and Red Gate SQL Backup (disclaimer: I'm independent, but I've worked with all of those companies in the past) have wizards that will test each component of that, and then tell you where the bottleneck is. If you want to find it yourself, you're going to have to test each of those components, and then find the weakest link in the chain (the lowest throughput).

For example, if other SQL Servers are writing to the same target location, they may have suddenly had an increase of data or added more servers, thereby slowing down the target.


Update value in table based on condition in other table with trigger I have 2 tables, first stores the details of all the races 1:  and second the users who have joined which races 2:

I want to update the race_status to Full and record_status to 2 in race table, if race_id count in second table is 5 per race_id. I have created a MySQL trigger for it here is the trigger code for it:

CREATE TRIGGER `user_race_count` BEFORE INSERT ON `tbl_map_race`
    FOR EACH ROW BEGIN
    DECLARE a1,a2 INT;
    SELECT tbl_map_race.race_id, COUNT(tbl_map_race.race_id) INTO a1,a2
    FROM `tbl_map_race`
    GROUP BY tbl_map_race.race_id;
    IF ((a1 = NEW.race_id) AND (a2>=5))
    THEN
        UPDATE tbl_race
        SET tbl_race.race_status = 'Full', tbl_race.record_status = '2'
        WHERE tbl_race.race_id = @r_id;
    END IF;
END

In the trigger tbl_race is the first table and tbl_map_race is the second table.
Trigger is successfully getting created but when I run query to join the race with trigger working behind I'm not able to update values in first table, but if I drop the trigger and run the query I'm able to insert a record in second table. Any help to this would be appreciated. Thank You.

CREATE TRIGGER `user_race_count`
AFTER INSERT
ON `tbl_map_race`
FOR EACH ROW
BEGIN
    IF 5 <= ( SELECT COUNT( /* DISTINCT */ user_id)
              FROM tbl_map_race
              WHERE race_id = NEW.race_id)
    THEN
        UPDATE tbl_race
        SET race_status = 'Full'
          , record_status = '2'
        WHERE race_id = NEW.race_id;
    END IF;
END


changing the value of a column in a table based on the value of another column I have the following table that is the "Class" table:

class_size
class_number
student_id

2
4005

1
4007

2
4001

3
4010

I want the "class_size" column to be generated automatically based on the other two columns.
I have tried the following SQL statement, but it doesn't work:
update Class
set class_size=totalSize
where totalSize,Cnum in (Select class_number as Cnum ,count(student_id) as totalSize
from Class
group by class_number)
and class_number = Cnum;

I really appreciate it if someone can help me with this problem, as I tried many different statements and still I cannot get it right.

here is the syntax :
 update class
 inner join (
    select class_number, count(*) totalSize
    from class
    group by class_number
 ) cc on class.class_number = cc.class_number
 set class.class_size = totalSize


Get username and/or IP address responsible for a query I use the following query to find performance improvements in queries:

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1),
  qs.execution_count,
  qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads,
  qs.max_logical_reads, qs.total_physical_reads, qs.last_physical_reads,
  qs.min_physical_reads, qs.max_physical_reads,
  qs.total_elapsed_time / 1000000 As total_elapsed_time,
  qs.last_elapsed_time / 1000000 As last_elapsed_time,
  qs.min_elapsed_time / 1000000 As min_elapsed_time,
  qs.max_elapsed_time / 1000000 As max_elapsed_time,
  qs.last_execution_time, qs.creation_time, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
AND last_execution_time >= DATEADD (MINUTE , -5 , CURRENT_TIMESTAMP )
ORDER BY qs.total_logical_reads DESC
--ORDER BY qs.total_physical_reads DESC

The issue is that I've found a query that appears to be thrown repeatedly from time to time and requires enhancement, but I'm not able to determine where it comes from (could be a user or a program, but in any case should have a login or username). How can I get this user or track this query so I can apply improvements to it?

You can use the below query to pull currently executing requests and the corresponding session/connection information:

select
    r.session_id,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
--where st.text like '%your query string to search for%';

What you are querying in your question, sys.dm_exec_query_stats, is cumulative and aggregated statistics that aren't by session/connection.  In other words, that information isn't stored there, nor could it easily be retrieved.

You could put in place a SQL Trace or XE session to grab this data that you're looking for, basically monitoring for the occurrence of your offending query(ies).

There are a couple of ways to get the data you're looking for, but unfortunately with your given query there isn't an easy way to mold that into what you're looking for.  Simply different types of data (you provide aggregated data, but you're looking for snapshot and granular data).


How do you perform a MYSQL After Update Trigger Insert Loop? I have 3 mysql tables, one called batches, one called base_formula and one called raw_materials_stock.

I want to perform an after update trigger on the batches table to insert information into the raw_materials_stock table based on information from the base_formula table.

Batches Table

batch_id, complete_dt, base_id

Base Formula Table

base_id, raw_material_id, percentage

Raw Material Stock Table

batch_id,raw_material_id,use_date,quantity

Batches Trigger

IF OLD.complete_dt is NULL and NEW.complete_dt is not null and (select b.raw_material_id from base_formula b where b.base_id=new.base_id) is not null  THEN

INSERT INTO raw_materials_stock (batch_id,raw_material_id,use_date,quantity)
VALUES(
new.batch_id,
(select b.raw_material_id from base_formula b where b.base_id=new.base_id),
now(),
(select ((new.volume/100)*b.percent)*-1 from base_formula b where b.base_id=new.base_id));

END IF

Issue

This issue i have is the base_formula table has more than 1 row with the same base_id. How can i use a loop to insert all of the rows with the same base_id into the raw_material_stock table?

You can restructure your query

INSERT INTO raw_materials_stock
  (batch_id,raw_material_id,use_date,quantity)
    SELECT
       new.batch_id,
       b.raw_material_id,
       now(),
       ((new.volume/100)*b.percent)*-1
    from base_formula b where b.base_id=new.base_id;


Oracle 12 - Reporting on Roles Is there a way to report on all the custom roles and/or responsibilities created within Oracle, Outside of SQL?

I am looking for a dashboard that shows how many have x role, y role that could be sent to senior level management.

If you want to list all the roles of a particular user:

select granted_role,admin_option,default_role from dba_role_privs where grantee='&user';

If you want to list all users with all roles assigned:

select b.username,a.granted_role,a.admin_option,a.default_role from dba_role_privs a join dba_users b on (a.grantee=b.username);


Oracle INSERT with If statement for values I'm trying to find a way to put together an INSERT which will insert a column value based on the value of another column, so for exampke

INSERT INTO table1
(col1a,col1b)
SELECT
  (dependant of the value of col2b),
  col2b
FROM table2;

so:

if col2b is 1 then col2a is A
if col2b is 2 then col2a is B
else col2a is C

Thanks in advance

KS

This has nothing to do with the insert.

select
  decode(col2b, 1, 'A', 2, 'B', 'C'),
  col2b
from table2;


Find which numbers in [1, 161] are not in the result set? How to find which two number in [1, 161] is not in the result set?

Can I find this two numbers using sql command?

mysql> select blog_id from wp_blogs;

...

|     149 |
|     150 |
|     151 |
|     152 |
|     153 |
|     154 |
|     155 |
|     156 |
|     157 |
|     158 |
|     159 |
|     160 |
|     161 |
+---------+
159 rows in set (0.25 sec)

The sequence is from 1 to 161, there should be 161 numbers, but only 159 numbers are in the resultset.

mysql> select count(blog_id) from wp_blogs;
+----------------+
| count(blog_id) |
+----------------+
|            159 |
+----------------+
1 row in set (0.24 sec)

How to find the missing numbers?

To find gaps in a number range:

Test table and data:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

Query:

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);
+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+


Is WAL Database log on separate disk good idea on RAID10? I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array.

I use the WAL in sync-mode with a big buffer and do a lot of bulk inserts and updates, so that buffer is hopefully used.

Is it an good idea to split the log onto a extra disk, not in the raid10? I could stuff in a RAID with 2 disks, but would that really improve anything?

I looking forward if someone could name a few pros and cons.

You asked for pros and cons, the con that few people mention when giving the standard advice to separate out the logs is that it will slow your reads because you now have fewer spindles to dedicate to reads.  Which way you go depends on the ratio of reads to writes in your database.  If it is 99% read (say a web server scenario) then you may end up not gaining anything by separating out your logs.

The other possible gotcha that is rarely mentioned is that putting your logs on a separate spindle only gives you sequential writes if you only put a single databases logs on that spindle.  If you have a server with 10 active databases then putting all of their logs on the same disk does not give you sequential writes, they will be spread about on the different log files.  Then again putting them on 10 separate mirrored drives means you need to dedicate 20 drives to logs which also may not be possible.  A good solution in this case is to use SSD storage for your log files which performs MUCH better on random writes when you find your environment just does not have sequential writes.


Retrieve archived data MSSQL database has previously been archived on another database. Approximately 70m rows (includes sales information.)

Now, I am asked to move this archived data to live database. Because, customer can't see order history older the 3 years.

Is it reasonable to do this? I think it will be slow. Are there other solutions?

Live DB size : 250GB
Archive DB size : 50GB
CPU : 8 Core
Memory : 64GB
Version : Microsoft SQL Server 2016 (SP1)

If there's no duplicate data to worry about, adding another 50 GB of data to a 250 GB database is not going to matter much, especially since the business requirements need the archived data in the live database.

Make sure you have good covering indexes. Perhaps you can import the historic data into a separate filegroup and then mark it as read-only. Perhaps you could use a table partition.

There are lots of options to think of. @AMG's answer covers some more business-related questions for your ongoing SLA as well.


Setting up filtering for server-side tracing I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd...

I was trying to set up a filtering on both the duration of the SQL batch or stored procedure (filter anything >= 1000 ms), but I also wanted to filter on the database name involved.

When I export the trace definition from SQL Profiler to a *.sql file, I however don't seem to find any filtering on the database name:

-- lots of other stuff up here - setting up trace events and so on...

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 100479e4-6eeb-4608-af17-64141f1631ec'
set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

....

So what's up with that? Why can I specify a database name to filter on - but the trace definition doesn't contain it, in the end?

Does that mean the server-side trace is always only checking the database it's been creating in?
But then why can I specify a filter by database name?
Does this also mean there's no way to do cross-database server-wide tracing on e.g. deadlocks?

Update: after some more toying around, I came to the following conclusion:

the filter on the database name was not applied to the Create Trace script in my case since I ended up selecting only the Locks / Deadlock Graph event - and that event doesn't expose a Database Name as an event column
if I pick other events, like RPC completed that do expose the Database Name as an event column, I can filter on the Database Name and that filter is also properly added to the Create Trace script.

I can't figure out how I was able to actually define a filter on Database Name, since normally, if an event doesn't expose a column, that column is also not available in the Column filter dialog....

Anyway - for whatever reasons that are beyond me (for now, at least), Deadlock Graph doesn't expose the Database Name and thus I cannot filter on that property, either....

Thanks all that helped and provided insightful comments!

Does that mean the server-side trace is always only checking the database it's been creating in?

No, the server-side trace has the scope of just that:  the server.  When I create (what I think is) the exact trace definition and export it, I get the following snippet:

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

I am using SQL Server Profiler 2008 R2 as well.  If you do not filter on the database name, then you will be capturing trace events for all of the instance databases.  As to why your export didn't seem to include that portion of the filter, I can't say for sure.  I know this goes without saying, but I'd check your trace definition within Profiler before you export to ensure the database name is in fact included as a filter (again, I know you've probably double and tripple checked this, but I had to say it).


How to find the time taken to execute a script As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert.

Is there any way to find out the time taken to execute that insert statement.
Since its a huge data it's not possible to populate data again.

Try to use sys.dm_exec_query_stats. Find your query, if it's cached:

set transaction isolation level read uncommitted

select
    top 100
    creation_time,
    last_execution_time,
    execution_count,
    qs.total_elapsed_time/1000 as TotDuration,
    convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
    case
        when sql_handle IS NULL then ' '
        else(substring(st.text,(qs.statement_start_offset+2)/2,(
            case
                when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
                else qs.statement_end_offset
            end - qs.statement_start_offset)/2  ))
    end as query_text
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by AvgDur desc


What are the best field data types for a table of musical keys I'm fairly new to relational databases. I understand how fields may have int or text data structures, but when it goes beyond that, I feel a bit out of my element.

I have a table called: keys

It is supposed to hold information on which key a classical music composition was written in. An example entry could be: E minor.

What I did is create two columns for the key table: name which is the name of the key, taking on values ranging from A-G, as well as tonality which tells us whether the composition was major or minor.

It currently has this form:

Question: What is the best way to construct my keys table (in terms of efficiency/scalability) and what data structures should the fields have?

I think it would make sense to have the tonality field be a binary. But does binary in this case need to be 1's and 0's? Or is there an efficient way to include the whole string minor or major? I'm not sure if I need to create another table to relate it, or if it can be efficiently done with 1. Also is text an appropriate field type for key.name? It can only take on 7 values: A, B, C, D, E, F, G. (My data do not include sharps or flats).

This is a 'primarily opinion-based' question.

I would go for a table with the following fields:

key_id    integer
key       char(1)
tonality  char(1)
name      varchar2(50)

The first field is the primary key. The second and third field make up a unique key. For key there is A to G possible. For tonality I would use - for minor, + for major, and 'space' for 'normal'. For both those fields you can make a check constraint to prevent wrong values to enter.

Since there are not many rows in this table it be read completely into memory so it will not influence response times that much.

If you do not like the 'extra' primary key then you can also use:

key   char(2)
name  varchar2(50)

The key is primary key and holds A-, A, A+, etc.


Very slow WHERE IN query on very large table - postgresql 9.6 My table (with 140 000 000 rows) :

CREATE TABLE attendees(
  attendee_id                 VARCHAR NOT NULL,
  name                        VARCHAR NOT NULL,
  status                      VARCHAR NOT NULL,
  event_id                    VARCHAR NOT NULL,
  PRIMARY KEY (attendee_id, event_id)
);

CREATE INDEX attendees_event_id ON attendees(event_id);
CREATE INDEX attendees_attendee_id ON attendees(attendee_id);

My query :

SELECT event_id FROM attendees
where attendee_id IN (SELECT attendee_id FROM attendees where event_id=$eventId)

This query can take several minutes.
I saw here: Optimizing a Postgres query with a large IN I can change the IN by IN (VALUES (..)...) but I don't know how to change this sub query :

SELECT attendee_id FROM attendees where event_id=$eventId

to have (VALUES (..)...)

So, my question is, what is the best way to make this query faster ?

edit (I run the explain (analyze, verbose) on smaller table):

Hash Join  (cost=5296.72..14097.38 rows=209116 width=16) (actual time=177.516..362.851 rows=121564 loops=1)
  Output: attendees.event_id
  Hash Cond: ((attendees.attendee_id)::text = (attendees_1.attendee_id)::text)
  ->  Seq Scan on public.attendees  (cost=0.00..5051.21 rows=230721 width=33) (actual time=0.572..90.302 rows=230721 loops=1)
        Output: attendees.event_id, attendees.attendee_id
  ->  Hash  (cost=4664.54..4664.54 rows=50574 width=17) (actual time=176.611..176.611 rows=49650 loops=1)
        Output: attendees_1.attendee_id
        Buckets: 65536  Batches: 1  Memory Usage: 2917kB
        ->  Bitmap Heap Scan on public.attendees attendees_1  (cost=1288.37..4664.54 rows=50574 width=17) (actual time=14.141..151.031 rows=49650 loops=1)
              Output: attendees_1.attendee_id
              Recheck Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
              Heap Blocks: exact=595
              ->  Bitmap Index Scan on attendees_event_id  (cost=0.00..1275.73 rows=50574 width=0) (actual time=13.265..13.265 rows=49650 loops=1)
                    Index Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
Planning time: 1.611 ms
Execution time: 371.431 ms

To Speed up the query,

We may use join instead of subquery

Try this query:

SELECT a1.event_id
FROM attendees a1
JOIN attendees a2
    ON a1.attendee_id = a2.attendee_id
        and a2.event_id = $eventId

To make is faster check that if you can create CLUSTER on that table with index on Primary key

Note: cluster a table according to an index reference


Delete shared memory segments left over by an Oracle instance We're using Oracle Enterprise 11rR2 running on Solaris.

How can I delete/remove allocated shared memory segments using ipcrm?

I'm getting this error:

 ORA-01041: internal error. hostdef extension doesn't exist

I hope that you know what you are doing....

First find which segment would be a candidate to drop because it was not cleaned up by an Oracle crash:

ipcs -bom

Take note of the owner and group, they should be the owner of the Oracle rdbms processes. Next check the NATTCH column, should be 0 processes attached to the segment. If you still want to remove that segment (which is pretty safe when there are no processes attached), use the ID column as input for

ipcrm -M {your_ID}

Normally this does not solve a problem. There was a problem that made the instance crash .... check the alertlog, it could have some clues in it.


Truncating a table with a foreign key constraint - SQL Server I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure:

Attendance

AttendanceID
TimeTableID
AttendanceDate
UserName

StudentAttendance

StudentAttendanceID
AttendanceID
StudentID
IsPresent

I run the following query and it works:

TRUNCATE TABLE StudentAttendance

But the following query gives me an error:

TRUNCATE TABLE Attendance

  Msg 4712, Level 16, State 1, Line 4 Cannot truncate table 'Attendance'
  because it is being referenced by a FOREIGN KEY constraint.

Why do I get the above error even though the Attendance table is empty?

The documentation for TRUNCATE TABLE (Transact-SQL) is fairly clear on this topic.

Referencing the Restrictions:

  You cannot use TRUNCATE TABLE on tables that:

  Are referenced by a FOREIGN KEY constraint. (You can truncate a table
  that has a foreign key that references itself.)

Regarding your comment

  I don't want this operation to be logged in transaction log that's why
  I wanted to use truncate instead of delete operation.

The TRUNCATE TABLE command is fully logged, just not at the row level.  Within a transaction, it can be rolled back.

Referencing the REMARKS section of the documentation.

  Less transaction log space is used.

  The DELETE statement removes rows one at a time and records an entry
  in the transaction log for each deleted row. TRUNCATE TABLE removes
  the data by deallocating the data pages used to store the table data
  and records only the page deallocations in the transaction log.


SQL Server NULL values UNDO I mistakenly updated the null values in my table to a string similar to an already present string. For e.g. I changed the NULL values to 'WL:5' and there were records for 'WL:5' already present. I want to change that string back to null values now, but if I update the table now, the other values which have the same name will also change. Any ideas?

Use backup file in order to recovery your data.


Multi-threaded replay of master activity for load/performance testing Does anyone know of any existing tools/products that accomplish what I'm trying to do?

After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is someone else looking for the same thing http://forums.mysql.com/read.php?24,192250,192250

The idea is I'd like to capture all the traffic to my master to save for a replay log against a snapshot of the entire database taken when the monitoring started.  Bin logs won't serve what I want since they only include writes.  I want read activity to realistically view the effects of proposed changes with "real" production traffic.

Real production traffic meaning everything from all applications that are hitting the database to be modified.  If there's some application level change, tests running just that app don't account for other activity going on in the system at the time.  Running all applications in a test environment aren't guaranteed to have the same state of the database.  I could take a test snapshot as a starting point as I fire them up but the applications don't have the ability to do an exact replay of their own activity.

I've use the tcpdump script from http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/ to monitor activity but this doesn't tell me which queries are coming from which connections.  Part of the playback I'm wanting is a multi threaded approach that replays the activity from the same number of threads that were actually in use.

I can't afford to turn on general query logging b/c my production master wouldn't be able to handle the performance hit for that.

The whole snapshot part of the process is to have a golden start point database to ensure everything is the same during the start of each test run.

I actually have a suggestion that will take some work but is doable.

THEORETICAL SCENARIO

You have 3 databases: db1, db2, db3
Production DB had binary logging enabled

What you need are the following to create enough traffic:

mysqldump of Production DB two weeks old (TwoWeekOldData.sql) of db1, db2 and db3
Binary logs on Production DB that are two or more weeks old
Four DB Servers running MySQL

DBServers 1-3 are Slaves to Production DB
DBServers 1-4 have Binary Logging Enabled
DBServer1 has replicate-do-db=db1 and has only db1 data loaded
DBServer2 has replicate-do-db=db2 and has only db2 data loaded
DBServer3 has replicate-do-db=db3 and has only db3 data loaded
DBServer4 has all data from TwoWeekOldData.sql loaded

You will need the following:

Run mysqlbinlog against all binary logs on Slave1 and collect SQL into db1traffic.sql
Run mysqlbinlog against all binary logs on Slave2 and collect SQL into db2traffic.sql
Run mysqlbinlog against all binary logs on Slave3 and collect SQL into db3traffic.sql
Run these three traffic SQL files in parallel like this

mysql -hdb4 -u... -p... -A -Ddb1 < db1traffic.sql &
mysql -hdb4 -u... -p... -A -Ddb2 < db2traffic.sql &
mysql -hdb4 -u... -p... -A -Ddb3 < db3traffic.sql &

You can use DBServer4 to measure

server load
query performance
I/O utilization
Whatever you want to measure

As I mentioned in the outset, this takes some setup work along with access to old data and all binary log entriers since the old data's last backup.

I hope this gives you some guidance on producing real traffic rather that simulating ordinary load.

GIVE IT A TRY!!


Table design: paired values of genetic data I'm trying to decide the appropriate way to design my tables to store my genetic data. Basically, I have samples, and each sample has multiple loci (a locus is basically a point on the genome), and each locus has a pair of values representing different alleles, or variants (basically, one allele is obtained from the father, and one from the mother).

There are potentially dozens or even hundreds of loci, depending on the project. The two alleles may or may not have the same value, and there is no meaning in the order they appear.

This is what the raw data might look like, and is also typical of what some programs expect for import:

On the plus side, it's still relatively easy to visually interpret and manually enter or change individual values without some other tool. On the negative side, there is no relationship between allele1 and allele2, they just get entered in a random order. This can make querying the data somewhat more cumbersome in some situations, and ends up involving a union. What really bothered me, though, was that it just doesn't seem to meet normalization rules with having alleles spread between multiple columns. It'd be like having pet1, pet2, pet3... as columns in a table listing owners and their pets.

So, I came up with this, which is what I'm currently using:

I like it more because it seems like it's properly normalized, but the downside is that it is visually harder to work with. I don't mind this, because although I'm a biologist, I have a relatively strong CS background and am used to thinking about data abstractly. But my colleagues do not have that background, making it harder for them than it is for me.

Ultimately, I can write scripts to convert the raw data to the proper format for import, as well as converting data exported from the table for use in other programs, but this all depends on finalizing the table design. So, have I properly designed the table for this data? Or am I mistaken about it being normalized? Is there some other alternative I'm missing?

Thank you.

Your first attempt makes by far the most sense - (the one with 4 fields).

As a rule, tables should be "tall and slim" rather than "short and fat"

It means that you can easily compare different alleles (for the same locus) belonging to the same individual - which is MUCH more difficult with your second schema.

The second schema means that you will have to use windowing/analytic
functions far more frequently - and if you're using MySQL (which is very
common in biology unfortunately) you won't have that facility.

Speaking as someone with both genetic and computer science degrees, I'd go with the first schema - I've worked with similar data (FragileX) - comparing different alleles - it was important for us to distinguish the father and the mother (we were doing multi-generational pedigrees).

I would recommend you include (arbitrary) - allele1 as the father's and allele2 as the mothers as part of your design - you never know when this knowledge might come in useful. Maybe you could add another field (isParentKnown) so that you can distinguish between those samples for which the parents are known  from the ones for which they are not.


cannot access my DB server on ubuntu server OS: Ubuntu 14.04
MYSQL Server version: 5.5.43-0ubuntu

The output of:

select User, host from user

is:

db    %
db    localhost

In my /etc/mysql/my.cnf, I made sure that bind-address is set to the real IP address of the server. I can connect to the server from my laptop using sftp and ssh

Yet, it refuses to connect me when I try to connect using mysqlbench error: can't connect to MySQL server on 'xxxxxxx' 10061

I can also access the web server fine from a browser, it displays the default ubuntu page.

What am I missing?

Update:

I was able to connect when I did: bind-addess=0.0.0.0

This obviously creates a security risk, as you can connect from any IP address, but there was an issue with the way Amazon EC2 assigns internal and external IP addresses, so I have to figure out the correct IP address to bind to.

If you copied and pasted that output from your SELECT statement, you have a typo in 'localhost'. That could be causing your problem.


Table Diagram and Access File of Contact Database for Church I have volunteered to design and develop an MS Access database for contacts at my local church in Jamaica, which has no budget to hire a database expert. I have attempted to design a database for the contacts, based on some requirements that I gleaned from the church leadership and administrators.
The church really wants to have a central place to store records on members, which they can delete/archive when members die or go away. They also wish to maintain records on marriages and anniversaries and emergency contacts. So I decided that the best approach is to focus on developing a contact database, that would include members, emergency contacts, non-member spouses of members, and other contacts that may be of service to the church and in turn the community served by the church.
Please help me to design a useful database by reviewing the table diagram attached. Any assistance would be useful.
Kind regards,
Kerry-Ann

The design looks OK to me, though I wouldn't store phone numbers as INTEGERs, because in reality they are character sequences.
Since you want to track marriages of members, may be that should the parent for marriages, and not contacts.
If a member passes away, you may still want to maintain his/her record with a flag, instead of deleting it. This will preserve marriage and reference records, as well as emergency contacts. To accommodate that you'd want to add the membership end date and the termination reason attributes.


List full text indexes in Oracle database Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index.

How can I do something similar using Oracle? Is it possible?

There are 4 types of Text indexes in Oracle, owned by the CTXSYS user (installed optionally), so you can simply query [DBA|ALL|USER]_INDEXES and filter rows by the ITYP_OWNER and ITYP_NAME columns:

select
  *
from
  dba_indexes
where
  ityp_owner = 'CTXSYS'
  and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH')
;

Index types can be found in the DBA_INDEXTYPES view.


Large database - too much time spent opening and closing tables I have a MySQL server (5.5.31) with 450 databases of some 200 tables each. (Yes, 90,000 tables)

When we're under high load, there's over 100 concurrent queries, of which 70% are either opening or closing tables. They're all only open for small queries.

The machine is heavily swapped, so obviously I can throw some RAM at it.

But before that, is there anything in the config I should be looking at? We've used various tuning tools, but they seem heavily geared to a more normal database setup where you might realistically have all tables open at any one time. Last time we changed our settings to match those recommended by a tuning tool, the entire system came to a halt.

What particular tuning should I be looking to to be able to get maximum performance before we throw RAM at it?

Generally throw RAM at it - especially given that you do not even say how much RAM you have. DAtabases love WARM and unless you are in the way to change the number of tables (90k is QUITE a lot) then basically thow RAM at it.

At least until you reach the low end of a low end database serer - which at these days would be 32gb memory (full memory loadout for a low end single processor Xeon).


MYSQL - Why does this SELECT get slower the further down the table? My table:

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `registration_date` (`user_email`,`user_registered`),
  KEY `user_registered` (`user_registered`)
) ENGINE=MyISAM AUTO_INCREMENT=176329 DEFAULT CHARSET=utf8;

In a stored procedure I need to loop over every user in the table and preform some other queries and I noticed as it gets further in the loop the queires take longer and longer to preform. What I would expect to take a few hours is now taking days...

To loop over one row at a time I am using this query:

SELECT `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT @count,1;

@count starts at 0 and works its way up to the number of users in the database. At a count of 0 the query takes only a few milliseconds but as soon as the count starts increasing the time it takes to do the same query rises to when Im on a count of 170003 for example the query takes over 1 second...

Doing and explain I see:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;
1   SIMPLE  wp_users    index   NULL    PRIMARY 8   NULL    1

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
1   SIMPLE  wp_users    ALL NULL    NULL    NULL    NULL    175862  Using filesort

Why is this happening?

I have found that splitting the query into two is quicker:

SELECT `ID` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
SELECT `user_email` FROM `wp_users` WHERE ID = 170469;

and only takes a few milli seconds again...

Is doing it the two queries the only quick way of doing this?

FIRST QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;

Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;
1   SIMPLE  wp_users    index   NULL    PRIMARY 8   NULL    1

How many rows do the Query Optimizer See? 1

SECOND QUERY

SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;

Now, look back at the EXPLAIN plan for it:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
1   SIMPLE  wp_users    ALL NULL    NULL    NULL    NULL    175862  Using filesort

How many rows do the Query Optimizer See? 175862

When the Query Optimizer sees too many rows to have to process, it decide to "throw indexes under the bus". MySQL will then do a full table scan, as indicated by the expression ALL in the EXPLAIN plan. The filesort occurs because you had ORDER BY ID.

SUGGESTION

Usually, it is recommended to retrieve rows by cursors. Personally, I hate using cursors. I have an alternative that is a little unorthodox. I would create a temp table with all the email addresses, treating it like an array but accessing it like a table.

Here is that paradigm

DELIMITER $$
DROP PROCEDURE IF EXISTS ProcessEmail;
CREATE PROCEDURE ProcessEmail()
BEGIN

    DROP TABLE IF EXISTS EmailArray;
    CREATE TABLE EmailArray
    (
        ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_email varchar(100) NOT NULL DEFAULT ''
    ) ENGINE=MEMORY;
    INSERT INTO EmailArray (user_email) SELECT user_email FROM wp_users;

    SET @ndxnext = 0;
    SELECT COUNT(1) INTO @ndxlast FROM IDArray;
    WHILE @ndxnext < @ndxlast DO
      SET @ndxnext = @ndxnext + 1;
      SELECT user_email INTO @GivenEmail FROM EmailArray WHERE ndx = @ndxnext;
      ...
      ... Process whatever you need with the @GivenEmail
      ...
    END WHILE;

    DROP TABLE IF EXISTS EmailArray;
END;
$$
DELIMITER ;

GIVE IT A TRY !!!


Size of an empty database Continuing with my school-project, now I have to estimate the size of the database from here to 5 years. They give me a formula, so, in that formula, one of the values is the size of the empty database (when it is created, and doesn't have any tables, index, etc.).

For example, for MySQL, I found here that it is 10 MB.

But, for Oracle, what is the size?

I don't have Oracle or any RDBMS to make queries, it is just a work of research. I've googled a little, but I haven't found for Oracle.

Any version of Oracle (9 or later) is okay.

First, are you using "database" in the Oracle sense of the term?  Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term?

If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP tablespaces.  On a small laptop system, that's probably on the order of 2 - 3 GB but it could be much bigger.

If you are using "database" in the sense that other database vendors use it, that's what Oracle calls a schema.  An empty schema (a schema with no objects), by definition, consumes 0 bytes of storage.  That's because space is allocated to tablespaces which are independent of schemas-- a single schema can have objects in many different tablespaces, a single tablespace can have objects owned by many different schemas, and a single object (if you're using partitioning) can be spread across multiple tablespaces.


Delete query does not appear to open space on the SQL Server Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually buying more storage.

The delete queries did not appear to open space - the size of the drive and available space doesn't change.

It is my understanding that even though available space has not changed, available space is created by the delete query. So new records imported can populate places where data was deleted. Is this correct?

I understand the Shrink Database is not a good choice.

Should I run -  sp_clean_db_file_free_space based on the following advice?

http://www.experts-exchange.com/questions/27905050/Freeing-up-disk-space-by-deleting-records-in-a-SQL-Server-2008-table.html

Deleting rows from a table does not automatically free up space, unless you happen to delete a large chunk of rows that are stored on the same pages, and every row on each such page was marked for deletion. You would need to rebuild the indexes in order to reclaim the space.
Even if you rebuild the indexes, SQL Server doesn't reduce the size of the data file(s) just because you've removed some data inside the file. The assumption is typically that if the database got that big once, it will get that big again, so why shrink the file only to grow it again later? Both shrink and growth operations are extremely disruptive to normal activity. Please read this thread in full to understand why you want to be very careful about freeing up space on your drive (even on a cloud-hosted server):

Why Does the Transaction Log Keep Growing or Run Out of Space?

Also see:

Why you should not shrink your data files


Query specific key fields from x-number of tables? I'm building an insurance database and I need some help with best practice on fast quering data.
Let's say I have three insurance types ... Car, Home and Travel.
Common for all three types, I have the "Insurance" table:
[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL
)

And then I have the three insurance types:
[dbo].[InsuranceCar] (
    [InsuranceCarID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Model] [uniqueidentifier] NOT NULL
)

[dbo].[InsuranceHome] (
    [InsuranceHomeID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Address] [uniqueidentifier] NOT NULL
)

[dbo].[InsuranceTravel] (
    [InsuranceTravelID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Destination] [uniqueidentifier] NOT NULL
)

All three insurance types have an "key search field" that I use for quering an insurance - in "Car" it is "Model", in "Home" it is "Address" and in "Travel" it is "Destination".
So the way I have solved it, is to create a "_Search" column in the "Insurance" table ...
[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [_Search] [nvarchar(MAX)] NULL
)

... and when any of the insurance type table rows is created/updated, then I copy the "key seach field" from the incurance type table into my "Insurance" tables "_Search" column (using trigger).
This way I can quickly query any key fields from the three insurance type tables by quering the "_Search" field in the "Insurance" table.
Problem is that it takes up more space in the database to do it like this.
Any better way to do this? Best practice?
Thanks.

Understand the problem first, worry about performance later.
This question is too broad and not a good fit for this venue.  However, as someone who works in the property and casualty industry I feel compelled to say... pump the brakes on this for a second.  If this is a hobby project... there are hobbies that are more relaxing.
This is not an easy undertaking, and I can say that I've seen many attempts at this, and they all get something wrong, especially when it comes to the problem you're describing - having multiple types of insurance contracts.
Ultimately that is what you are modeling here - a contract.  Insurance contracts have rules, they can be altered, premium must be calculated and balanced correctly, and they are highly regulated.
So the best practices here are:

Enforce as many contract provisions as possible through the data model
Anything that cannot be enforced through the model must be enforced through transaction logic
Make sure you are aware of the reporting and audit requirements of your jurisdiction

Please read this, then refer to the diagram below.  It's a head start.

This model doesn't even being to address some major concepts you will need address, such as:

Coverages/insurance lines/reporting codes
Governing jurisdictions
Limits/deductibles
Forms/endorsements
Managing customer information across policy types
Premium and policy accounting

As an aside, "fast queries" and GUIDs rarely occur together.  You'll also notice they are absent from the above data model.


When postgresql sequences get out of sync? For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be inserted from my application. So I would like to know possible causes for the sequence to be out of sync like that. Because I have no idea why this problem is occurring.

There are a few possible cases where things can get out of sync.

Very old versions (unsupported) used to sometimes fail to set sequences on backup restore.  If you have manual backup and restore routines, this is somewhere to look.
setval('sequence_name', 1) will set to to 1.

Those are your only two possibilities unless you have a short cycle, and are cycling.


Exclusion With Many to Many Relationships I have created a select statement that show all the topics that are assigned to a book. Now I am trying to create a Select statement that shows all the topics that are not assigned to the book. Book and topic have a many to many relationship.

Problem: I am getting the topics that are assigned to other books because technically they are not assigned to my book.

In depth look: I have a database full of books. A book can have many topics and a topic can have many books. I have create a user interface for adding a book. This interface shows all the topics available when the user choose let us say three topics three records are created in the books_has_topics table one field has an id for for the book the other for the topic. I also have a user interface for updating the book. I want the user to be able to change the topics.

Code:

this works it give me a list of all the assigned to the book based on the books id.

SELECT  *
    FROM  topic T
    INNER JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.Book_book_id = [bookid];

This sort of works Not only do i get all the topics not assigned to the bookid I also get all the topics assigned to other books.

SELECT  DISTINCT topic_id, topic_name
    FROM  topic T
    LEFT JOIN  book_has_topic BHT  ON T.topic_id = BHT.topic_topic_id
    WHERE  BHT.book_book_id IS NULL
       or  BHT.book_book_id <> [bookid];

I'm more of a MS SQL guy so please excuse me if my syntax is a bit off for mysql, but I would probably go with something like this.

SELECT topic_id, topic_name
FROM topic T
WHERE NOT EXISTS (
  SELECT TOP 1 1
  FROM book_has_topic BHT
  WHERE BHT.topic_topic_id = topic_id
    AND BHT.book_book_id = [bookid])