How to add column to big table in MySQL I'm a PHP developer so don't be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB so what i tried:

Alter table in screen with table lock. Took ~30hours and nothing. So I just stopped it. First I made a mistake because I didn't end all transactions but the 2nd time was no multilock. Status was copy to tmp table.
Since I also need to apply partitioning for this table we decide to make dump, rename and make table with same name and new structure. But dump is making strict copy(at least I didn't found something else). So i added to dump a new column with sed and query it. But some strange errors began. I believe it was caused by charset. Table in utf-8 and file became us-ascii after sed. So I got errors(unknown command '\'') on 30% of data. So this is also a bad way.

What are other options to accomplish this and speed performance(I can do it with php script, but it will took ages). What will be performance of INSERT SELECT in this case.

Thanks for any advance.

Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
BULK LOAD the data from the old table to the new one:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.
EDIT: Read this article to get details about the commands used in the above sample query ;)


SQL Server - Log file not shrinking in AlwaysOn group We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode.
Main Database on Primary node, log file-size is 434GB, Used 99.91%. Log backups is happening every hour only on Primary node.

Ran following query on Primary and Secondary servers.

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'Main DB Name'

Result:

node        log_reuse_wait_desc
------------------------------------------------------------
primary     AVAILABILITY_REPLICA
Secondary-1 LOG_BACKUP
Secondary-2 AVAILABILITY_REPLICA

While other DBs (much smaller than main DB) in AO groups log usage is very minimum (1 to 10%) and can be shrunk, main DB log usage continuously showing more than 99% and not shrinking.
I am guessing since log_reuse_wait_desc is showing "AVAILABILITY_REPLICA" on Primary Node, means not all the log is transferred to Secondary replica, and so its still full.

I checked Redo Queue Size in Secondary-1 Node showing 27GB, Secondary-2 is showing nothing. So most likely Secondary 1needs to get all logs from Primary. The problem is Redo Queue Size in Secondary-1 Node is increasing and not decreasing as what I through it should. Also log-file on Primary node is slowly increasing which is going to fill-up whole disk-space.

Now Secondary-1 Node showing LOG-BACKUP. How do I solve this issue? Should I take Log backup on Secondary-1 Node and check?

The issue is resolved. Apparently one of programs connecting to same large DB had been running for more than 24-hours, seemed it was in "hung" state, had several open connections to secondary replica-DB caused blocking. I guess that was causing not completing log replication from primary to secondary, because when I ended those programs, immediately blocking were gone and Redo Queue Size started decreasing instead of previously increasing. After about an hour or so, log file in primary node showed more than 80% available free space.

dbcc opentran and sp_who2 didn't give that much details to track this issue. What helped was

select * from sys.sysprocesses where blocked<>0
dbcc inputbuffer(spid)

The first statement was to find out what was causing blocks, and second one to kill.
I am just sharing if it helps others in similar situation.

TLog backup is being taken for all user-Databases in every hour only on primary node as mentioned in my post. So far TLog physical file-size was small, wouldn't increase and most of the time had more than 90% available free space - which is what its supposed to be. So after the issue is resolved, only difference is file-size is very big which I understand because it kept on growing. Now I need to do a TLog shrink (we did before) and it should go back to initial size.

TLog back doesn't need to be taken on all replicas. Technically you can, but if you are taking on all replicas, in case of Database restoration you have to gather all TLog backups from all Nodes and restore them which may be complicated.


Find and Remove Duplicate Indexes I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on same set of columns then remove all except one.

sp_BlitzIndex from Brent Ozar will help you find and identify those:

https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/

After that You will have to check those indexes on your own and decide which of them are obsolete. This isn't a task I would try automating with any kind of script.


What is the correct way to enforce unique "IS-A" relationship? Let's say we have a Person relation, and with that Employee which IS-A Person,  Manager, which ISA Person as well.

What is the correct way to ensure that a Manager isn't an Employee and vice versa ?

I didn't know what to look for when asking the question, there most likely already exists an answer to that question.

Person (PK:personId, name, ...);
Manager(PK:#personId); -- A manager ISA person
Employee(PK:#personId); -- An employee ISA person

INSERT INTO Person (1,'Peter',...);
INSERT INTO Manager (1); -- OK
INSERT INTO Employee(1); -- Nope, error.

  Person cannot be at the same time an employee and a manager.

Maybe with another example.

Shape (shapeId);
Rectangle(shapeId, width, length);
Circle(shapeId, radius);

Circle is a shape, Rectangle is a shape, how do I ensure Rectangle is not a Circle (and vice versa)?

Hence my question, What is the correct way to enforce unique "IS-A" relationship?

Relation Application that refer to the Person and Position relations should have unique personID attribute and should have the single attribute positionID that can have one and only one value at once.


Getting a SEEK rather than SCAN on an Aggregate This is doing my headin, but I'm stuck on trying to get an Index Seek on the MAX aggregate within this query.

Here is the execution plan - and I'm wondering what I'm missing here, as currently as the query takes 3/4 seconds to complete which is too slow. I've indexed on StartDate, ClientID with no luck.

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

INSERT INTO #SearchWords
(
    Word
)
SELECT
    value
FROM
    STRING_SPLIT(@SearchWords, ' ')
WHERE
    TRIM(value) <> ''

SELECT
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode,
    MAX(V.StartDate) as LastVisitDate
FROM
    Client C
LEFT JOIN Visit V on
    C.ClientID = V.ClientID
INNER JOIN LookUp.Gender G on
    C.GenderID = G.GenderID
WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR
        (SELECT COUNT(*) FROM #SearchWords)=0 -- if we haven't any words
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode

You want your index to have ClientID as the leading key.  E.g.:

CREATE INDEX IX_Visit_ClientID_StartDate ON Visit(ClientID, StartDate)

This is because you need to join on ClientID before finding the most recent StartDate. Therefore ClientID needs to appear first.

Along with that index change... Since you only want to find the most recent Visit.StartDate you can change your LEFT JOIN Visit to an OUTER APPLY that gets the most recent date.

Try this:

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

DECLARE @SearchWordCount INTEGER;

INSERT INTO #SearchWords
(
    Word
)
SELECT
    value
FROM
    STRING_SPLIT(@SearchWords, ' ')
WHERE
    TRIM(value) <> ''

SELECT @SearchWordCount = @@ROWCOUNT;

SELECT
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode,
    V.LastVisitDate
FROM
    Client C
OUTER APPLY (
    SELECT MAX(StartDate) AS LastVisitDate
    FROM Visit AS V
    WHERE C.ClientID = V.ClientID
) AS V
INNER JOIN LookUp.Gender G on
    C.GenderID = G.GenderID
WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR @SearchWordCount = 0
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID,
    C.FirstName,
    C.LastName,
    C.FullName,
    C.DateOfBirth,
    G.GenderName,
    G.GenderIcon,
    C.VerificationCode

  Note:

  I've also made the @@ROWCOUNT improvement I suggested in my comment.

The GROUP BY is almost certainly unnecessary now too.  Remove it if it is.

Off Topic:

One final last comment which is technically unrelated to your question. You seem to be doing something that Full Text Searching may be a better solution for.


Alternative to xp_cmdshell for emailing a report as a CSV file I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

Export the content of the table variable to a CSV file.
Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use PowerShell or a similar technology as no-one here knows PowerShell, although I'm familiar with VB.NET, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

  Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

Because of this, and assuming that you won't have this process running more than once at any particular moment, you should dump the desired rows from the Table Variable into a Global Temporary Table (i.e. starting with ## instead of #).

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:

add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.
instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in tempdb):

SET NOCOUNT ON;

DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                  + CONVERT(NVARCHAR(36), NEWID()) + N']';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
EXEC (@SQL);

SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
EXEC (@SQL);

DECLARE @SomeTableVar TABLE ([Col1b] INT);
INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);

INSERT INTO dbo.TempDump ([Col1a])
  SELECT Col1b
  FROM   @SomeTableVar;

SELECT * FROM dbo.TempDump; -- only needed for debug

SET @SQL = N'DROP SYNONYM dbo.TempDump;';
EXEC (@SQL);

-- create the report query, ending with the DROP statement
DECLARE @ReportSQL NVARCHAR(MAX) = N'';
SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
print @ReportSQL;

-- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
--                      @query_result_separator = N',', @query_no_truncate = 1, ...;

If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).


Conditional ORDER BY based on NULL values in the column I have a table like this.
table(student_name nvarchar(20) not NULL, grades int).
When I write

select * from table
order by grades asc

It shows this

student_name | grades
---------------------
   Ann       |  NULL
   Bob       |  NULL
   Bob       |   10
   Jane      |   25
   Nelly     |   30

when select * from table
      order by grades desc

   student_name | grades
    ---------------------
      Nelly     |   30
      Jane      |   25
      Bob       |   10
      Ann       |  NULL
      Bob       |  NULL

But I need to order by like this. NULLs in the end but those who have grades order by ascending order, like

   student_name | grades
    ---------------------
      Bob       |   10
      Jane      |   25
      Nelly     |   30
      Ann       |  NULL
      Bob       |  NULL

How it is possible to do a conditional order by like this? How to specify NULL values to be at the end?

You could add a case when check to the order by to give the NULL's a lower priority and then filter on grades

SELECT *
FROM table
ORDER BY CASE WHEN grades IS NULL
THEN 2 ELSE 1 END ASC, grades ASC;

Since you are using integers you can also do this, which should be more performant

SELECT *
FROM table
ORDER BY -grades DESC;

Source

DB<>Fiddle


sqlplus Setting the exit code of a script How can I set the exit code of a script?

The following doesn't seem to work

WHENEVER SQLERROR EXIT 5

DECLARE
     retCode number := 0;

BEGIN
     retCode := 30;
END;
/
EXIT :retCode ;

Gives the usage for EXIT with an exit code of 1

Try it like this:

var retCode number
exec :retCode := 30;
exit :retCode

(See http://www.orafaq.com/forum/mv/msg/80574/233106/0/#msg_233106)

Bottom line is that retCode must be a variable defined in SQL*PLUS's scope. Your DECLARE is inside a code block, and SQL*PLUS can't see into it.


Is it possible to force an adaptive join with a query hint? Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.

I have a parameterised query which has a sub-query that typically selects almost no rows (0 to 4), but occasionally picks 100k.

I believe that this is exactly the type of scenario that an adaptive join is designed for, but SQL Server doesn't pick this as a query plan.

A query plan of always-hash-join is server-destroying, so my only recourse at the moment is to force the join to be nested loops, and accept that individual worst-case requests will take a bunch of extra seconds to run.

I have an Azure SQL database at compatability 150, and BATCH_MODE_ON_ROWSTORE=1

Further details

For some reason paste-the-plan doesn't think the XML for this query is legit.

The query at issue is:

with
hashes as (select h.[ContactHash]
    FROM HashTable h
        join someTable p on h.id=p.id
    group by [ContactHash]
    having count(distinct key) >1
        and count(distinct p.thing) between 1 and 20),
dupeContacts as
    (select distinct c.key,c.ContactHash from someTable c
    where c.[ContactHash] in (select t.[ContactHash] from hashes t ) and c.key =  @id
    )
    insert  into @MyTableVar
select * from dupeContacts

The two tables involved are similar to:

HashTable (
    int id ,
    ContactHash varbinary(16)
) -- millions, typically 4:1 to someTable by ID

and

SomeTable (
    int id Primary Key ,
    varchar(100) thing
)

As it says in the documentation, adaptive join currently supports SELECT statements only:

  Adaptive join eligible statements

  A few conditions make a logical join eligible for a batch mode Adaptive Join:

  The database compatibility level is 140.
  The query is a SELECT statement (data modification statements are currently ineligible).
  The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
  The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).


Query taking too long to execute My query takes a lot of time, 22 seconds and above. After 22 seconds, we get more than 900 000 records. We have created indexes on the table which are used in the query:

select * from ContactMaster
where ContactId in (
    select  ContactId
    from ListContactMaster
    where ListMasterID in (816,975,945)
    and ContactId not in (
        select  ContactId
        from ListContactMaster
        where ListMasterID in (0)
    ) and (
        isactive='true'
        or isactive is null
    )
)
and isactive = 'true'
and (
    isdeleted='false'
    or isdeleted is null
)
and emailaddress not in (
    select emailaddress
    from supresscontacts
)
and ContactMaster.ContactId not in (
    select DomineSupress.contactid
    from DomineSupress
    where DomineSupress.CampaignId=1186
)

Indexes:

IX_ListContactMaster_ContactId,
IX_ListContactMaster_IsActive_ContactId,
IX_ListContactMaster_ListMasterID,
IX_ListContactMaster_ListMasterID_ContactId,
IX_ContactMaster_ContactId_IsActive_IsDeleted

The ContactMaster table has more than 50 fields.

You should use joins to reduce lookups between tables and to make better using of indexes during query the data. As expecting your query on relational tables I suggest a pattern as follows.

SELECT CM.* from ContactMaster CM
INNER JOIN ListContactMaster LCM ON CM.ContactId=LCM.ContactId
LEFT JOIN supresscontacts SC ON CM.ContactId=SC.ContactId
LEFT JOIN DomineSupress DS ON CM.ContactId=DS.ContactId
WHERE SC.emailaddress IS NULL AND DS.contactid IS NULL AND DS.CampaignId=1186 AND LCM.ListMasterID IN (816,975,945)
AND LCM.ListMasterID <> 0 AND (LCM.isactive='true' OR LCM.isactive IS NULL)
AND CM.isactive = 'true' AND (CM.isdeleted='false' or CM.isdeleted is null)

Hope it will help you to make your query better.


Implementation of Rollback in PostgreSQL How is the rollback command implemented within the PostgreSQL database?

I wanted to know the implementation details and what exactly happens underneath when a rollback is executed, i.e. state of database storage, changes in write-ahead logs (WAL).

Scenario: I have a table, called employees, having disk size of, say, 4GB on the database. When I open a transaction on this table to update a single row, and then rollback the changes, what would happen inside the database? Would a new version of the table created on the disk but remain inactive due to rollback?

Apart from some side tasks like releasing locks, deleting files and similar, the main activity is to set the two bits that correspond to the transaction in the commit log (in pg_xact) to “aborted” and sync the file to disk.

A rollback does not touch the table at all, and it should always be fast.
The newly created row version simply remain in the table (to be cleaned up later by autovacuum), they just become invisible because the transaction that created them has been aborted. Similar with deleted data: the marker that the row was deleted by the transaction simply becomes invalid.


MySQL query performance question I have a MySQL database where I would like to return results based on multiple rows.

id | key        | value      | selector
---------------------------------------------------------
1  | username   | person     | 123459795181089198123
2  | password   | PPassword! | 123459795181089198123
3  | firstname  | John       | 123459795181089198123
4  | lastname   | Smith      | 123459795181089198123

Basically, I'm trying to avoid having many columns and altering the database tables, by creating the key/value/selector style instead.

Now the problem I'm having is with slowness on a server I use.  Right now, my SQL query looks something like this:

SELECT *
FROM users
WHERE selector IN (
    SELECT selector
    FROM users
    WHERE (`key` = 'username' && `value` = 'person') || (`key` = 'firstname' && `value` = 'John')) AND `key` = 'password';

This returns something like:

id | key        | value      | selector
---------------------------------------------------------
2  | password   | PPassword! | 123459795181089198123

It works great, except that I'm running this on 20,000 records which causes the performance to drop drastically.

My question is, is there a better way to do this while using the same type of data structure?

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would IMHO be far better off if you redesigned your table structure.

What you should be doing is something like this - and your performance should be more than acceptable.

CREATE TABLE MyStuff
(
  selector int,
  username VARCHAR(15),
  password VARCHAR(15),
  firstname VARCHAR(15),
  lastname VARCHAR(15),
  PRIMARY KEY(selector)
);

mysql>
mysql> INSERT INTO MyStuff VALUES(1234597951, 'person', 'PPassword', 'John', 'Smith');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT *  FROM MyStuff WHERE selector = 1234597951;
+------------+----------+-----------+-----------+----------+
| selector   | username | password  | firstname | lastname |
+------------+----------+-----------+-----------+----------+
| 1234597951 | person   | PPassword | John      | Smith    |
+------------+----------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql>


Wiki instance mirrored to another box I've never really done anything like this before, but I have a wiki instance setup on my internal network. Since it is a support wiki, in the case that our internal network is inaccessable through our vpn, we want to have the website readily available on a backup external site.

Our external system uses centos and so do our internal systems. So essentially I want to install a wiki instance onto the external system however I am curious how I would go about getting the mysql database connection going between the external site and our internal database.

I guess the question is.... what is the proper way to do this? Our database isn't huge, so some kind of sql cron job that transfers the database to our external box through sftp isn't out of the question.

Just my 2 cents:

You could set up a MySQL master-slave replication, the master being inside the internal network and the slave on the external site.

You could also do a nightly mysqldump of the wiki, sftp it to the external site via a cron job and restore it there, but it looks like an ugly and clunky solution to me.

Perhaps the simplest thing would be to give up mirroring and have the wiki on the external site only, so it would be accessible in case of internal network downtime.


Possible to add KEEPFIXED PLAN option to reduce the recompile in SQL Server 2005 in Production Env? I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env?

I'd know if we may gain performance after that or on the other hand it will be even worse.

For my case, I have a batch of stored procedures running in SQL Server 2005 and some one of need to run in a fixed loop condition.

Thanks!

Vance

Personally, I wouldn't use it.

There are better ways on managing this with asynchronous statistics or plan guides nowadays. I've only ever seen this hint used on some old trading database that was geared towards Sybase/very old SQL Server.

Also, you now have statement level recompilation that may fit your use case better

But really, you'll have to try it and find out in your situation...


Varchar index - will hashing value make it faster? I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in the IN ('...') list is going to be approx 100. The table will likely have millions of rows after the first few months. I understand that indexing this could slow down inserts and could create quite a large index.
QUESTIONS

Would it be faster to store a hash of the value as well and instead index and search on that?
Does that even make sense if the values are not guaranteed to be unique?
If hashing the values gives them a consistent length, would indexing that make queries faster?

I am running mysql 5.1 and using INNODB engine.

What you are asking is a little daunting. Here is why:

Would it be faster to store a hash of the value as well and instead index and search on that?

Creating a hash column and indexing sounds like a great idea. I have suggested that back on March 03, 2013 : Possible INDEX on a VARCHAR field in MySql (See Suggestion #3)

Does that even make sense if the values are not guaranteed to be unique?

This would depend on the cardinality of that hash column. Since you said you will have millions of rows, let me express this in numerical terms:
Run SELECT COUNT(DISTINCT hashcolumn) ... against the table. For a one million row table, this count should be greater that 20. In other words, each distinct value should have no more that 50,000 rows (5% of the table rows). Any value that has more that 50,000 rows will cause the MySQL Query Optimizer to dismiss the index from being used and make a full table scan the preferred method for that hash value.

If hashing the values gives them a consistent length, would indexing that make queries faster?

I would say Yes and Perhaps at the same time. Why two answers? Indexing and using a hash column in place of a long column sounds brilliant against a MyISAM Table. You said you are using InnoDB.
When it comes to using Fixed vs Variable text, I would go with MyISAM over InnoDB

Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

EPILOGUE
If the table is fairly-to-heavily used in Transactions, the table must stay InnoDB. You can take better advantage of your idea in MyISAM. You can go forward with the hash idea. Please make sure the PRIMARY KEY is single integer column (BIGINT if you know you will exceed 2 billion rows. Otherwise, INT). I would do a major RAM upgrade and increase the InnoDB Buffer Pool size accordingly.


Relations between a imeid, sim chip, and account from a cellphone company? I've never really had a cellphone (that I owned the account to...long story), but at work we have them, and we want to get a series of relations between accounts, devices (mifi's, tablets, cellphones), sim chips, data plans, etc straightened out at work.

My personal theory on this, is that we need to put all the information into several tables in a database until the issues are resolved and the information there is valid.

And later maybe we could even add a history table if need be to remember who had them, where, and which plan / which department, and how much each plan cost.  We have plans in two companies Verizon and AT&T.

So what I am asking is, what are the basic relations between this information?

From what I understand the device id's (the IMEIDs) should be the unique key that uniquely identifies a device.

I'm not certain what identifies a plan, maybe an account number...maybe a phone number...

I'm not sure what all the variables are related to this, but I'm pretty sure I could normalize a database for it, if I would find out all the unique keys involved.

I also have an HR database that keeps track of our employees that I can do a join on to record a history of plans, phones, devices etc...

Here are the layers of abstraction that you need to go through to track who has (pays for) a GSM phone:

Note that this is good for a point in time.  If you need to track changes over time then everything becomes many-to-many with start/end dates in the intersection entities.


SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)? PostGIS provides a function called ST_MakeLine(pt1,pt2). It's great for constructing lines. MySQL does the same with LineString(pt1,pt2). What do you call the SQL Server analog that takes two Points and constructs a LineString?

I looked up LineString, but didn't see it mentioned.

Here is a test case on dbfiddle.

Sample data,

CREATE TABLE #tmp (
  pt1 geometry,
  pt2 geometry,
);

INSERT INTO #tmp(pt1,pt2) VALUES
  (geometry::Point(1,1,4326), geometry::Point(2,2,4326)),
  (geometry::Point(2,2,4326), geometry::Point(5,5,4326)),
  (geometry::Point(3,3,4326), geometry::Point(4,4,4326)),
  (geometry::Point(4,4,4326), geometry::Point(3,3,4326)),
  (geometry::Point(5,5,4326), geometry::Point(4,4,4326));

Query

SELECT pt1, pt2
  'My Line' AS line -- what goes here
FROM #tmp;

And I'm wanting "My Line" to be the LineString.

pt1         pt2         line
POINT (1 1) POINT (2 2) My Line
POINT (2 2) POINT (5 5) My Line
POINT (3 3) POINT (4 4) My Line
POINT (4 4) POINT (3 3) My Line
POINT (5 5) POINT (4 4) My Line

Try ShortestLineTo():

SELECT
  pt1.ToString(),
  pt2.ToString(),
  pt1.ShortestLineTo(pt2).ToString() AS line -- what goes here
FROM #tmp;

Returns

POINT (1 1)   POINT (2 2)   LINESTRING (1 1, 2 2)
POINT (2 2)   POINT (5 5)   LINESTRING (2 2, 5 5)
POINT (3 3)   POINT (4 4)   LINESTRING (3 3, 4 4)
POINT (4 4)   POINT (3 3)   LINESTRING (4 4, 3 3)
POINT (5 5)   POINT (4 4)   LINESTRING (5 5, 4 4)


Migration query - Oracle I have two physical servers, hosting 6 two node RAC databases running in 11.2.0.3 version (both grid and database).

I'm told to have the grid software in 11.2.0.4 version and the databases in the same 11.2.0.3 version since there could be future plans to upgrade the database to a higher version.

Plan is to create new grid software (11.2.0.4) and create a new ASM instance with new luns and create diskgroups with the new luns, create a new database and then export/import from old database to this new database. So that the new database will have the Grid and ASM running in 11.2.0.4 and database in same 11.2.0.3.

Perform the same activity for all 6 databases, post that uninstall 11.2.0.3 software and binaries, resulting in the required result wherein only one ASM instance will be running (11.2.0.4).

Is the above mentioned activity feasible? I'm not in a situation to upgrade 11.2.0.3 grid to higher version as I cannot afford downtime. But then the above mentioned activity will have two ASM instances running at the same time for some period.

I can add/clear doubts if the mentioned activity is unclear.

I'm sure that such approach is feasible. Technically Grid 11.2.0.4 is fully compatible with RDBMS 11.2.0.3. You just need to be accurate in some parameters.
I believe that in a new 11.2.0.4 grid you won't use very specific "compatible.rdbms" or "compatible.asm" attributes' value ('11.2.0.4.0', e.g.) which might conflict with 11.2.0.3 database. Ideally you should use the same default '11.2.0.0.0' value in target GI.

You can clone databases from old to new GI, copy specific tablespaces if your schemas are compatible with transport_tablespaces approach. You can use logical dump created with datapump.
It might be very useful to use dbms_file_transfer package to copy datafiles (and dumpfiles created by datapump as well) directly from source ASM to target ASM diskgroups.

Good luck.


Prevent copying to temp table (sql) I have two tables - comments and votes :

comments(id, text, user_id, page_id)
votes(id, value)

There are 2,000,000 rows in the votes table.

I created the following query:

SELECT SUM(votes.value),
       comments.text,
       comments.comment_id
FROM comments, votes
WHERE comments.comment_id = votes.comment_id AND comments.page_id = $page_id
GROUP BY comment_id;

The query runs for about 12 seconds, is this normal?

SHOW PROCESSLIST says that its copying to a temp table. This seems slow, is it necessary to use a temp table?

In the world of RDBMS's, temp tables are a fact of life. It only rears its ugly head in JOINs.

Even the worst case of a JOIN is the degenerate JOIN, a query of one table.

Since temp tables are always making its way into our queries (into our lives), the best thing you can do is starve temp tables. Making them as compact as possible. What do I mean ???

Here is your query :

SELECT SUM(votes.value),
       comments.text,
       comments.comment_id
FROM comments, votes
WHERE comments.comment_id = votes.comment_id AND comments.page_id = $page_id
GROUP BY comment_id;

You query will actually create a table resulting from a join of comments and votes whose rowcount is the number of comments times 2,000,000. Since temp table have no indexes, there are always cartesians joins occuring with the temp tables. The WHERE clause is applied along the way, then the GROUP BY summation. Don't forget, the temp table will also contain the text column. That's a lot of text data to drag around in the JOIN phase.

Let's refactor your query

You can head off the WHERE clause at the pass, so to speak. Here is how:

SELECT comment_id FROM comments WHERE page_id = $page_id;

This query only has the necessary keys from the comments table.

Next, collect comment_ids from votes

SELECT comment_id,SUM(value) sumofvalues FROM votes;

This is actually the worst part. With 2 million rows and 4 bytes per comment_id and 4 bytes for the sum, that's an 16MB table in the absolute worst case.

Next, combine the keys of comments with the matching keys in votes.

SELECT BB.* FROM
(SELECT comment_id FROM comments WHERE page_id = $page_id) AA
INNER JOIN
(SELECT comment_id,SUM(value) sumofvalues FROM votes GROUP BY comment_id) BB
USING (comment_id);

Now that keys from comments and the sum of the values from votes are retrieved, the final part is to connect the comment-ids back to the original comments table and get the text fields.

SELECT
    B.sumofvalues,A.text,A.comment_id
FROM
    comments A INNER JOIN
    (
        SELECT BB.*
        FROM
            (SELECT comment_id FROM comments WHERE page_id = $page_id) AA
        INNER JOIN
            (SELECT comment_id,SUM(value) sumofvalues
            FROM votes GROUP BY comment_id) BB
        USING (comment_id)
    ) B
USING (comment_id);

Before this refactored query can work as fast as possible you will need proper indexing.

Here are the indexes you need:

ALTER TABLE comments ADD INDEX pageid_commentid_ndx (page_id,comment_id);
ALTER TABLE votes ADD INDEX commentid_value_ndx (comment_id,value);

You want the first index because it will group rows by page_id. You want the second index because It will group rows by comment_id. In fact, both these indexes are called covering indexes. Why is that important ??? It is important because the subqueries will retrieve needed data from the index only, never touching the main table. The comments table is only accessed once when all needed keys are compiled together in the subqueries.

Give it a Try !!!

If any syntax is not working, please comment on the question and let me know !!!


overwrite table with data from another table - SQL Our users want to refresh a QA database from production but want two tables (lets call them T1 and T2) to retain the original QA data. So I copied two tables from QA (DB1) to a temp QA database (DB2). Then refreshed DB1 from production. After the refresh, I want to overwrite T1 and T2 data from DB2 to DB1 so it can contain pre-refresh QA values.

I have done the following:

Use

select *
INTO D1.dbo.T1
FROM D2.dbo.T1

Then refreshed D1 from prod
Then truncate T1 with the following step:

SELECT COUNT(*) AS BeforeTruncateCount
FROM T1;
GO
TRUNCATE TABLE T1;
GO
SELECT COUNT(*) AS AfterTruncateCount
FROM T1;
GO

Now when I go  back to copy data from D2.T1 to D1.T1, I get the error that there is already an object named T1 in the database.

Should I drop the table and copy?

Or is there any better method for the whole procedure?

Best is to use SSIS (data Import/export) to do this. It has the functionality to just transfer selected tables from source to destination. Optionally, you can even save the package so that it can be reused for future/repeated transfers.

EDIT: Adding more screenshots for clarity.


mySQL aggregate query with hireachy Article Table:

ID | INT(10)
Category | INT(10)

Category Table:

ID | INT(10)
Parent | INT(10)

What I want to generate is a tree that includes a count of articles at all levels e.g.

A (30)
--> B (20)
--> --> C (10)
--> --> D (10)
--> --> --> Q (6)
--> --> --> L (4)
--> E (5)
--> F (5)
G (8)
--> H (8)
--> --> I (7)
--> --> J (1)

I'd really like to be able to generate this output in a single SQL query; however I can do anything to the database structure if that helped. At the moment the only way we can see to do this is:

SELECT COUNT(*) FROM articles GROUP BY category

and then create the hireachy in code. That's what we are trying to avoid.

The only other approach I can think of is a complicated IF() statement which is auto generated based on a pre-computed list of ID hierachies.

Nope.

The one query is a SELECT that fetches everything, then your application code does all the formatting work.

Or you make a messy Stored Procedure to do all that work.  And you spend longer than you would like debugging it.


Why would a job call a batch file with dtexec.exe instead of just calling the SSIS package directly? I've inherited a SQL 2012 server with a number of jobs that call SSIS packages.

Instead of the more obvious solution (calling them directly from a "SSIS Package" job step), these jobs use a CmdExec step to call a batch file, which contains a single line:

dtexec.exe /FILE "D:\path\PackageName.dtsx"

Why would the previous DBA have done this? Permissions? Personal preference?

And why the double hop? If you wanted to use dtexec.exe, couldn't you just put that command directly in the job step?

I know there can sometimes be an issue with 32-bit vs 64-bit dependencies in packages, if there are MS Office components used in the package. How can I figure out if that's what is going on here?

Before SQL Server 2012 and the project deployment model, the only way to run an SSIS package from the context of SSMS was either a SQL Agent job or xp_cmdshell.

SQL Agent is calling DTEXEC.exe as it has a special parameter /X86 that is only respected when passed from SQL Agent. Otherwise, you'll want to specify the explicit path to your dtexec.exe to control whether it's a 32 vs 64 bit operation.

  This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

Option A

That could be a reason why they use a batch file to kick off the SSIS packages. They want to ensure the correct dtexec is being used to run the package. Shamless self promotion on determining whether it's 32 vs 64 http://billfellows.blogspot.com/2015/02/is-my-ssis-package-running-in-32-or-64.html Also https://stackoverflow.com/a/13092260/181965

Option B

Another reason I've seen batch scripts used as they didn't use configuration (file, table, whatever) but wanted to get a different behaviour per environment. Assuming I had a ConnectionString variable in my SSIS package and the design time value was Dev but now I'm in production, one way to change that value is to always set the property when we run, psuedocode is

dtexec.exe /file MyPackage /SET ConnectionString;Prod

If I'm lazy, I could use the same job definition of across all my environments but change the contents of the /SET value as need be. I don't see it being that useful but some people view tasks with varying degrees of difficulty.

The above doesn't have to be just for /SET. They can set logging or anything else up really. Maybe they want to log the output to an execution log because they didn't know SSIS supports that natively. There might be /REP EWIP > CurrentRun.txt in the dev batch file but production only have /REP E

Option C

Another reason would be the illusion of security. At a former employer, they used the batch script approach to run SSIS packages. Developers were not granted access to SQL Agent. xp_cmdshell - oh hell no to that. Nor could we be trusted to log onto the pre-production boxes (Load, stage, UAT) but we still had the need to run SSIS packages on demand.

So, after a few weeks of submitting urgent request tickets every time the packages need run, they created a pair of stored procedures that provided a gated package execution experience. RunSSISPackage and RunSSISPackage32 which would take the name of the package to be run. For every package we created, they would create a batch script that ran it. Lots of busy work for all parties but at the time it was the only way they were letting us run packages. The procedures used xp_cmdshell and EXECUTE AS to allow us to run the packages as they would be - with full sysadmin rights. By the way, when you allow people to deploy SSIS packages without auditing, they could add an Execute SQL Task in there that adds the developer group into the sysadmin role. I think. Maybe. Hypothetically.

Option D

Not SQL Agent. I've seen other scheduling tools used at clients that weren't SQL Agent. The support for SSIS specifically has been spotty but they've all supported batch files so perhaps you have some Enterprise scheduler that wasn't compatible with SSIS.

Otherwise, if it's just dtexec.exe /file foo.dtsx ... I got nothing


DELETE statement execution cancellation in SSMS I ran a delete statement with wrong WHERE condition (which deletes all records from table instead of just 10 records) but during execution I found it and CANCELLED the execution in Management Studio,  which is still in query execution cancellation process (its a big table).

As far as I know if my cancellation completes successfully I don't lose any data, is my understanding correct? if not can anyone please tell me is there a way to recover deleted data from transaction log. The database is in simple recovery model.

Assuming it is a single statement, the operation will either succeed or fail as a whole - there is no way some of the rows will get deleted and some will remain. (And if the delete succeeds, as @Shanky suggested above, in simple recovery there is not much you will be able to do to recover the data aside from reverting to a prior backup.)

That said, do not get impatient and try to kill the user forcefully, or take the database offline, or shut down the service, or disconnect the network cable, or disconnect the power cord. You will need to wait for the rollback to complete - even if it's blocking other users - to be sure your data will be intact. Note that a rollback operation can take longer than the initial operation because a rollback is generally single-threaded, but the delete might have gone parallel.

In the future, you should issue a BEGIN TRANSACTION; prior to running ad hoc / uncontrolled / cowboy DML against a production or otherwise critical system. This way no matter when you realize your mistake, you can wait for the operation to complete and then issue a ROLLBACK TRANSACTION; instead of madly and repeatedly slamming the stop button in SSMS. This will save you from deleting data that you can't easily recover, but there's a caveat the other way - if you did the right thing, you need to remember to COMMIT TRANSACTION;, since blocking the entire table while you're on lunch, home for the evening or on vacation is probably also an employment-terminating event.


Result set with multiple rows based on max date and not NULL Staff changed their profiles as shown in this table:

The result of latest information should be like this:

I applied the technique from this question for embedded Firebird of LibreOffice Base, the query has error.

SELECT DISTINCT StaffCode,
       FIRST_VALUE(FirstName)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN FirstName IS NOT NULL
                               THEN EffectiveDate
                               END DESC) FirstName,
       FIRST_VALUE(LastName)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN LastName IS NOT NULL
                               THEN EffectiveDate
                               END DESC) LastName,
       FIRST_VALUE(MarriageStatus)
           OVER (PARTITION BY StaffCode
                 ORDER BY CASE WHEN MarriageStatus IS NOT NULL
                               THEN EffectiveDate
                               END DESC) MarriageStatus
FROM SourceTable;


WHERE NAME LIKE ( A SET OF NAMES ) I would like to drop all the jobs related to a set of specific databases.

So I have added all the names into a table variable and I was trying to generate the scripts to drop the jobs.

how can I achieve this?

use msdb
go

DECLARE @R TABLE ( NAME VARCHAR(108) NOT NULL PRIMARY KEY CLUSTERED)

INSERT INTO @R(NAME)
SELECT
'UK15SUMMProduct' UNION ALL SELECT
'US15SUMMProduct' UNION ALL SELECT
'DE15SUMMProduct' UNION ALL SELECT
'AT15SUMMProduct' UNION ALL SELECT
'FR15SUMMProduct' UNION ALL SELECT
'EU15SUMMProduct' UNION ALL SELECT
'AU15SUMMProduct' UNION ALL SELECT
'UK15SUMSProduct' UNION ALL SELECT
'US15SUMSProduct' UNION ALL SELECT
'DE15SUMSProduct' UNION ALL SELECT
'AT15SUMSProduct' UNION ALL SELECT
'FR15SUMSProduct' UNION ALL SELECT
'EU15SUMSProduct' UNION ALL SELECT
'AU15SUMSProduct'

SELECT S.*
FROM SYSJOBS S
WHERE NAME LIKE ( SELECT NAME FROM  @R)

something like the select above, I want to see all the jobs that have the database names in them and delete them, for example, like in the script below:

exec sp_delete_job @job_name = 'WebFeed UK15SUMMProduct', @delete_unused_schedule = 0

Here is the whole query:

Declare @job sysname
Declare jobs_cursor Cursor For
    Select S.name
    From msdb.dbo.SYSJOBS S
    Inner Join (values
        ('UK15SUMMProduct')
        , ('US15SUMMProduct')
        , ('DE15SUMMProduct')
        , ('AT15SUMMProduct')
        , ('FR15SUMMProduct')
        , ('EU15SUMMProduct')
        , ('AU15SUMMProduct')
        , ('UK15SUMSProduct')
        , ('US15SUMSProduct')
        , ('DE15SUMSProduct')
        , ('AT15SUMSProduct')
        , ('FR15SUMSProduct')
        , ('EU15SUMSProduct')
        , ('AU15SUMSProduct')
    ) as n(name) on S.name like '%'+n.name + '%'

Open jobs_cursor
Fetch Next From jobs_cursor into @job

While @@Fetch_Status = 0
Begin
    Print 'Remove: '+@job
    exec sp_delete_job @job_name = @job, @delete_unused_schedule = 0
    Fetch Next From jobs_cursor into @job
End

Close jobs_cursor
Deallocate jobs_cursor

Part of it is similar to previous answers.

You can replace values(...) by a table variable or a real table.


MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by using JDBC or MySQL Administrator GUI,

my questions are

1) is this feature by default and I have to remove CURRENT_TIMESTAMP from Table settings and update this column standard way(s)

2) is there some option / setting that allows me to switch on/off this feature

Sql engine is MySQL 5.1.29-rc community

According to the manual, a TIMESTAMP can be auto-initialized, auto-updated, both, or neither. The apparent caveat is this:

  With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

So, if you don't explicitly give the column a default/on update clause, it will do both. If you don't want to default or auto-update, give the default a '0':

ts TIMESTAMP DEFAULT 0


How to link Similar-but-distinct Models of Medical Imaging Data? I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two similar, but distinct ways of recording data about a given scan: the first being done by a study coordinator at the scanner, writing description, order and comments about each image, and the second by scanning the actual images' metadata a day or so later when they are transferred to us and have become available. Both models (the image metadata and the hand-written log) are about the same thing (each image), but are entered at different times.

My question is: How should I connect the two models? Since they would both be the same order belonging to the same scan, I can match them up programmatically, but that seems like it would be a lot of overhead, and simultaneously not very flexible. I could connect them with a join table or a foreign key for one or the other, but that might lead to some internal inconsistencies. I can't model them as exactly the same model, because there are some extra tasks that behave like images but aren't images, in the hand-written log, and they don't have images or metadata.

Is it better just to be rigid and link them programmatically, without any keys relating them? The link would depend on the data being correct (but I guess that's always the case).

Since you said that there are always 2 types of data I would do it like this

This might use the wrong terms since I don't know what exactly is available, but I think you will get the idea.

image (the data that is common to both scanes/images)
---------------------------
    image_id
    date

coordinator (the one that is inserted by hand)
---------------------
    image_id (PrimaryKey and ForeignKey)
    comment
    description
    order
    [...]

metadata (that you get programmatically)
----------------------
   image_id (PrimaryKey and ForeignKey)
   {whatever data that is}

This way, you have both images connected but have the freedom to model the details differently.

Additionally, you could move the metadata into a seperate table if the data is similar. This might be useful for later comparisson/collection/...

metadata (that you get programmatically)
----------------------
    image_id (PrimaryKey and ForeignKey)
    metadata_id

metadata_description
-------------------------
    metadata_id
    description
    name
    {whatever the metadata is}


Get only the oldest entry in a join So I have a table bugs

|bugid| title|

the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the date.

bugnotes:

| bugnotesid | bugid | datetime | text |

I want to list all bugs, showing the date of the oldest entry in bugnotes if available

I think that you want something like this (see below for table defs). Something appears to have happened my SQLFiddle demo.

SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext
FROM bugs bs, bugnotes bn
JOIN
(
  SELECT bugnotesid, MIN(notetime)
  FROM bugnotes
  GROUP By (bugid)
) mytab
ON bn.bugnotesid = mytab.bugnotesid
WHERE bs.bugid = bn.bugid;

CREATE TABLE bugs values
(
  bugid int,
  bugtitle varchar(34)
);

INSERT INTO bugs values(1, 'the first bug');
INSERT INTO bugs values(2, 'the second bug');
INSERT INTO bugs values(3, 'the third bug');

CREATE TABLE bugnotes
(
  bugnotesid int,
  bugid int,
  notetime datetime,
  notetext varchar(400)
);

INSERT INTO bugnotes values(1, 1, '2014-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(2, 1, '2014-03-15', 'afasdfasdf');
INSERT INTO bugnotes values(3, 1, '2014-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(4, 1, '2014-03-17', 'afasdfasdf');
INSERT INTO bugnotes values(5, 2, '2014-02-14', 'afasdfasdf');
INSERT INTO bugnotes values(6, 2, '2014-02-20', 'afasdfasdf');
INSERT INTO bugnotes values(7, 2, '2014-02-22', 'afasdfasdf');
INSERT INTO bugnotes values(8, 2, '2014-02-25', 'afasdfasdf');
INSERT INTO bugnotes values(9, 2, '2014-02-28', 'afasdfasdf');
INSERT INTO bugnotes values(10, 3, '2013-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(11, 3, '2013-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(12, 3, '2013-03-19', 'afasdfasdf');
INSERT INTO bugnotes values(13, 3, '2013-03-20', 'afasdfasdf');
INSERT INTO bugnotes values(14, 3, '2013-03-27', 'afasdfasdf');

[EDIT - after profiling the two queries offered as solutions here]

This bit is really a question to Joshua - not sure if I should make it a new post or keep going here - I can always move it if requested.

Hi Joshua,

I'm sort of starting with MySQL so I took advantage of our exchange to profile our two responses to the OP. I'm a bit puzzled by what I found. Mine takes ~ 200 us, whereas yours appears to take more than double that - and there are far more rows in yours (more info?). I'm curious as to why? Do you know what's going on.

I hasten to add that I did a RESET QUERY CACHE before running the second (mine) query. I also ran them in reverse order and obtained the same result.

I'm baffled!

Joshua's query.

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000186 |
| checking permissions | 0.000011 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000043 |
| init                 | 0.000069 |
| System lock          | 0.000019 |
| optimizing           | 0.000012 |
| statistics           | 0.000038 |
| preparing            | 0.000057 |
| Creating tmp table   | 0.000056 |
| Sorting result       | 0.000008 |
| executing            | 0.000006 |
| Sending data         | 0.000218 |
| Creating sort index  | 0.000060 |
| end                  | 0.000011 |
| removing tmp table   | 0.000029 |
| end                  | 0.000009 |
| query end            | 0.000013 |
| closing tables       | 0.000018 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000024 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

My query.

mysql> show profile for query 2;
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000131 |
| query end      | 0.000010 |
| closing tables | 0.000007 |
| freeing items  | 0.000020 |
| cleaning up    | 0.000031 |
+----------------+----------+
5 rows in set, 1 warning (0.00 sec)

mysql>


Partial updates with functions My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided.

Let's take a posts table as an example:

+-----------------------------------+
|             post table            |
+-----------------------------------+
| post_id | SERIAL PRIMARY KEY      |
| title   | VARCHAR(100)            |
| content | TEXT                    |
+-----------------------------------+

The application can update the data when one or any combination of fields change. For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

This is a simple example, but it applies to all tables with many more columns, etc.

There are some ways I can think of:

I could make a function for every column: post_update_title(changed_title, post_id), post_update_content(changed_content, post_id). Each of them only change the specific column they are responsible for. But this approach gets messy really quickly when there are tables with many columns.
Create a general function that takes in the column name, changed value and post ID: post_update_column(column_name, changed_value, post_id). This approach makes me share information specific to the database with my applications. This is something I don't want.
Create a general function for updating and set a default NULL value to parameters. This way I could detect which columns have to be changed. But this introduces a problem when there are columns that can be set NULL themselves. Then I couldn't say if the column was not updated or was explicitly set NULL.
Call UPDATE queries directly from inside the application. This approach I dislike the most: I don't want to couple the application with the DB this way.

Has anyone faced something similar? How did you solve it?

For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

Why? When either of them changes just update both of them. It's an MVCC database and any change to the table will result in a whole new row being written and the old one being marked as dead.

UPDATE post
SET title = $2, content = $3
WHERE post_id = $1
  AND title <> $2
  OR content <> $3;

And don't use VARCHAR(100) use text


What is the best architecture to run Oracle sucessfully run on VMWare? Due to a data center move we are mandated to migrate all servers to virtual machines. We currently have a 3 Node Oracle RAC running on physical servers. There seems to be a great debate about the suitability of running Oracle on virtual machines especially as RAC would not be part of the new system. What is the best architecture to run Oracle successfully run on VMWare?

You can run Oracle under VMware just fine.  The big question is what is the current hardware spec and what VMware version and edition will be hosting the database?


How to design a schema to store search keywords I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema:

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Date           DATE            NOT NULL
);

In this case, search term will be redundant in the table and make the database grow faster. I want to avoid data redundancy.

Also I am not sure if using the date data type or unix timestamp will make a difference in retrieving the data faster.

So, should I create a new table to store a date that links to master table by id? For example,

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
 DateID         INTEGER         NOT NULL
);

CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 Date           DATE            NOT NULL
);

My concern is performance as the database grows. If the database is not designed properly , it would be leading to performance and bottleneck issues.

One of my friends has done an analytic site, but it is not going well. I figured out that there are 3 million records in the table and his query is taking more than 3 minutes to return results.

And only one table he created, then save “Event Name”, “Start data”,” End date” something like that. When generating monthly report, it could not show the report due to timeout issues (server time out, query time out, jquery response time out).

His SQL query is too expensive. He use group by date in sp to get how many users daily and monthly.  Then passes the result to populate flash based report. I fixed it to see report by increasing command time out. But I don’t think it is a good idea. Data is continuing to grow, it would be 4 mill records in end of this year. At the end of the day , increasing command time out will not work. This issue is a result of weak database design. I don’t want to follow it.

Please advise me and share your experience to avoid these issues. How should I design search term db schema?

First, let me address your concern about data redundancy. I agree with you that the second schema is more likely to reduce the redundancy, and is probably closer to what I would go with. One thing to be aware of, though, is this will be unique search terms. So unless you have some way of normalizing data, misspellings will get through and be viewed as different search terms.

Also, I would switch the way you link the searchTerm to the date searched, so that dateofSearchTerm links to the search term (not the other way around).

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
);

CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 searchID       INTEGER         NOT NULL,
 Date           DATE            NOT NULL
);

It would also be a good idea to make searchID a foreign key that references searchTerm.ID to ensure there are no orphan rows (rows in DateofSearchTerm that do not have a matching parent in SearchTerm).

As for your anecdotal story about your friend, the biggest thing I see you missing in your designs are indexes. A table 3-million strong should have no problem running quickly with proper indexes.

Using your design, you would want an index on the searchTerm.searchTerm column, and on DateofSearchTerm.date.

I think if you follow the advice to make searchID a foreign key, that will also become a proper index. I don't use SQL Server, so I don't know if foreign keys require an index, so make sure that it does.


The datediff function resulted in an overflow. But should not I have this stored procedure in SQL-Server 2014

USE [provbank]
GO
/****** Object:  StoredProcedure [dbo].[GetHBSeconds]    Script Date: 2018-10-05 08:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetHBSeconds]
AS
BEGIN

DECLARE @hbdatetime datetime
DECLARE @diffSec int

SET @hbdatetime = (SELECT HBDateTime from HBData WHERE ID=1)

SET @diffSec = datediff(second,@hbdatetime, convert(datetime,convert(char(19), getdate(),126)))

SELECT @diffSec AS DiffInSeconds , @hbdatetime AS 'LastHB', convert(datetime,convert(char(19), getdate(),126)) AS 'CurrentTime'

END

And every now and then I get this error:

  The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

What I have in the database is one row with a datetime column and an id.

I have a separate program that writes in this row the current timedate like '2018-10-05 09:58:30.000'. And then I have the SP to calculate between current time and the timestamp to get # of seconds in difference between now and the timestamp. I don't think having the diff in seconds would be too narrow/too precise.

Is there something I can change in my SP to avoid this error?

Because the difference in the table and getdate() on the same machine/server shouldn't be more then a few seconds apart if all works, which it is, when I get this error.

First: the expression convert(datetime,convert(char(19), getdate(),126)) is redundant (except for the GETDATE(), of course). So, just replace that with GETDATE().

We need something to test with. The error message is clear, you clearly have a value for @hbdatetime which is too old, so that the number of seconds compared to GETDATE() overflows an int.

You can calculate the approx earliest allowable date compared to GETDATE() using:

  SELECT DATEADD(SECOND, -2147483647, GETDATE())

When I run this, I get: 1950-09-17 07:36:00.107.

So, you want to search for rows in your HBData table where HBDateTime is earlier than that.

Alternatively, you can use DATEDIFF_BIG() instead of DATEDIFF(). It returns the number of seconds as bigint instead of int. It was introduced in SQL Server 2016.


Ternary relationship: what's the difference between having single table and having multiple tables? Consider the following ternary relationship:

Suppose that all entities have only two attributes (PK and Name).

Here are the tables I derived (5 tables):

Sector
-------------------------
ID_Sector    SectorName
-------------------------

Product
-------------------------
ID_Product    ProductName
-------------------------

Company
--------------------------------------
ID_Company    ID_Sector    CompanyName
--------------------------------------

Relationship 1 (R1)
-------------------------
ID_Sector    ID_Product
-------------------------

Relationship 2 (R2)
-------------------------
ID_Company    ID_Product
-------------------------

Question:

Is it a good solution for that ternary relationship? What's the difference between having 2 tables (R1 and R2) instead of the following single table:

Ternary table
-------------------------------------
ID_Sector    ID_Company    ID_Product
-------------------------------------

To me, it looks like that having 2 separate tables for each relationship (R1 and R2) is a better solution when compared to having a single table, but I don't know if that's actually true or if it's a good practice.

The two solutions model different rules.  With the ternary table you are saying that a company may only have certain products in a particular sector.  In a different sector there will be a different set of products, though the two sets may overlap, of course.

With the binary tables you are stating that sector has no influence over what products a company my relate to.  Similarly the company has no influence over what products are in which sector.

The choice between these alternatives will be determined by your business rules.  It cannot be answered by an abstract, academic discussion.  I have found it best to name the relationship between the entities.  Saying that company is related to product, say, is interesting; saying why company is related to product is even better.  "Company buys product" is a different piece of information to "company makes product" or "company does not have security clearance to use product".  By doing this I often discover new relationships, attributes and entity types.  You may end up needing both the binary and ternary tables!

Edit: For the rules

a Company produces many products / each product is produced by exactly one company
a Company reported in exactly one sector / each sector reports on many companies
a Product is sold in exactly one sector / each sector has available many products.

I would have these entity types

Sector - SectorID

Company - CompanyID, SectorID

Product - ProductID, CompanyID

If any of your rules are many-to-many then you'll need the binary association tables.

As an aside, the relationship names "has", "belongs to" and "is a" often hide more than they illuminate.  If you find your BAs using these ask them to have another think.


mongodb - "replication oplog window" possibly reduced due to profiling - when profiling disabled no change? PROBLEM

I have a 3 member replica set (no sharding) running mongodb version 2.4.8.

Using MMS (Mongo Monitoring) I can see  that on 7th July 2014 at 12:00 my "replication oplog window" dropped from 2777.78 hours to 68 hours at its lowest point.

Looking back at my log of changes on this date I can see database profiling was enabled that morning.

"db.setProfilingLevel(0)" to disable profiling was executed an hour ago on all collections and on all members of the replica set (PRIMARY FIRST).

QUESTION

An hour later my "replication oplog window" counter is still showing only 102 hours. Will this take time to increase again or should I be looking for another cause ?

Thank you
Scott

OK, i think i know the answer. The OPLOG collection is a capped collection. It overwrites over time. The profile level was set to 2 for a short period of time logging all operations. I guess it will take time to overwrite these operations on a capped collection again and as a result increase the op log window.

Would be interested in anyone elses take on this issue.

Thank you.


Use of FROM keyword on DELETE statement The BOL and many other sources point out:

  FROM
  Is an optional keyword that can be used between the DELETE keyword and the target
  table_or_view_name, or rowset_function_limited.

I'm used to write DELETE sentences without the FROM. I've made some searches but can't find where the FROM is mandatory. Can anyone point out a situation where it should be mandatory? Or is always optional?

Here is one case where it would be hard to write it without:

DELETE /* FROM */ t1    -- this FROM is optional
  FROM dbo.t1           -- this FROM is mandatory
  INNER JOIN dbo.t2 AS t2
  ON t1.key = t2.key
  WHERE t2.key IN (1,2,3);

Or:

DELETE /* FROM */ t1    -- this FROM is optional
  FROM dbo.t1           -- this FROM is mandatory
  WHERE EXISTS
  (
    SELECT 1 FROM dbo.t2
    WHERE key IN (1,2,3)
    AND key = t1.key
  );

The FROM in the initial spot (e.g. DELETE **FROM** t1 FROM dbo.t1 ...) is optional, just as Books Online says. Why do you think it ever should be mandatory? Borrowing from Thomas' answer, which he gracefully deleted, the following are identical:

DELETE Sales.SalesOrderDetail WHERE SalesOrderID = -1;

DELETE FROM Sales.SalesOrderDetail WHERE SalesOrderID = -1;

This is similar to INSERT - the following are absolutely 100% equivalent, and you don't ever have to write INTO:

INSERT dbo.t1(key) VALUES(1),(2),(3);

INSERT INTO dbo.t1(key) VALUES(1),(2),(3);


You may find this surprising but that's the behavior of InnoDB.

The InnoDB storage engine does cardinality approximation by walking a few levels down the nonleaf BTREE nodes and takes an educated guess.

I wrote about this a long time ago based on mysqlperformanceblog.com

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Aug 03, 2011 : When should I rebuild indexes?
Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables?

Perhaps you could try disabling that behavior by running this:

SET GLOBAL innodb_stats_on_metadata = 0;

Setting innodb_stats_on_metadata  should stabilize the approximate cardinality and you should get the same cardinality over and over again.


SQL Server Encryption with "localhost" certificate Using SQL Server 2014/2016:

I'm trying to encrypt my connections from my application to my local SQL Server instance.  As I read the documentation, SQL Server seems to very much prefer certificates issued to a fully qualified domain name (not just localhost). SQL Server Configuration Manager only seems to see certs issued to the machine's FQDN.

I found this article which seems to indicate that you can use wildcard certificates as well.

But I am connecting from a local app to a local SQL Server instance that is not exposed to the network.  Is there any way to associate a certificate issued to localhost with a SQL Server instance?  This is doable with IIS Certificates (Encrypting HTTP traffic to localhost).

Internal security policies require that any inter-process communication with the database, even on a local machine, be encrypted.  From what I can tell there are no encryption options for Named Pipes. We are disabling all other protocols and only allowing TCP/IP, because it is the only option that supports encryption, as mandated by our security policies.

The problem is these are 300+ instances of SQL Express on individual workstations. The certificate management if we can't use just one localhost cert will be a nightmare.

Just for fun, I tried to do this with a wildcard certificate. The fact that anyone can do this with a self-signed certificate, for a local application, being forced to use SSL when shared memory would work much more efficiently, is kind of bogus. This adds nothing to the security of the environment.
Create A New Cert
New-SelfSignedCertificate -Subject "CN=*,DC=Dublin,DC=local" -KeySpec KeyExchange
-KeyFriendlyName "SQLWildcard" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1"
-CertStoreLocation "Cert:\LocalMachine\My"

Note that a FQDN of localhost and a short name of localhost both seemed to work as well. It still doesn't make anything any more secure. If anything, forcing it through the TCP stack adds in more places for filter drivers and other items to inspect and attack.
Give Permissions To The Private Key
Here I am giving the service account access to the private keys. Since most of the time SQL Server Express Edition is running as Local* account you can skip this step if you've put the cert in the local machine's MY location. Put this in for completeness.

Setting The Instance To Use The Certificate For SSL
This won't show up in the certificate picker, because the issued-to and subject don't actually match the server. Plus, if you have 3000+ instances to do this on, chances are you aren't going to do it through the GUI.
The location of the registry key is: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\*InstanceID*\MSSQLServer\SuperSocketNetLib with the value name of Certificate which takes the thumbprint of the certificate.

Double Checking
If everything went well, you'll see that the certificate thumbprint was successfully loaded for SQL Server on the next restart of the service.
The certificate [Cert Hash(sha1) "F830C76FDDAC472807AF46A1C44450BBA56443D7"]
was successfully loaded for encryption.

If something went poorly, you'll see that there was a failure to initialize SSL. It will be followed by a fairly verbose output of the error code and status code which will help you in determining why the failure occurred.
Unable to load user-specified certificate [Cert Hash(sha1) "ED3D2F4360780AF71ABCE83A1C3A6F3BE13E1B96"].
The server will not accept a connection.
You should verify that the certificate is correctly installed.
See "Configuring Certificate for Use by SSL" in Books Online.

Alternate DB technologies that are out-of-process will have the same fate. You can use localhost as I explained above.


optimizing a select query of 1.5 million entry I have the following query:

SELECT s0_.id, s0_.lowresimageurl
FROM app_instagram_shop_picture s0_
FORCE INDEX ( idx_deleted_shopId )
LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id
AND (
s1_.deletedAt IS NULL
)
WHERE (
s1_.expirydate IS NULL
)
AND (
s0_.deletedAt IS NULL
)
ORDER BY s0_.checked ASC
LIMIT 0 , 30

the table of app_instagram_shop_picture has 1.5 million entry and the query above takes about 10 seconds to finish. Here's the structure and indexes that is in that table:

http://cl.ly/image/3E3T2B001W0Q

Here's the structure and indexes of the app_instagram_shop table.

I am running out of ideas on how I can further optimize this again.

Would that work?

SELECT s0_.id, s0_.lowresimageurl
FROM app_instagram_shop_picture s0_
FORCE INDEX ( idx_deleted_shopId )
WHERE EXISTS (SELECT 1 FROM app_instagram_shop s1_
            WHERE s0_.shop_id = s1_.id
                AND s1_.deletedAt IS NULL
                AND s1_.expirydate IS NULL
                AND s0_.deletedAt IS NULL)
ORDER BY s0_.checked ASC
LIMIT 0 , 30


Simplify a SQL table result with less rows I have a table where there are some names with 3 sports. If the person practices the sport it shows a '1' else '0'.

How can I change the table with SQL so that it only shows a line per person, like this:

Regrads,
Elio Fernandes

This is referred to as an aggregate query.  See the MySQL dev page for details about the various aggregates you can use.

This query will return a sum total for each sport, for each Name.

SELECT Name
    , SUM(Football) AS Football
    , SUM(Darts) AS Darts
    , SUM(Diving) AS Diving
FROM mytable
GROUP BY Name;

See this SQL Fiddle for a working example.


Cannot find slow (frozen) running query in Query Store I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes sense as the query is active but not doing anything.

The select from the query takes ~2 minutes to finish, while the procedure uses 3+ hours (I usually cancel job at 3 hours due to batch issues)

In general I would look for regressed/resource expensive in Query Store and analyze what has changed causing this query to take so much time, I cannot find it however.

My questions are:

What reasons makes the query stop from increasing CPU time? I assume this can me memory spill such as disk reads or sorting, but I do not know for sure.
Is it possible to find a query id (for tracking) when I cannot find it in Query Store? I have execution plan, SPID etc. I also know procedure name. How can I find it?
The procedure has paramteres as input, one which is used for a later join. Should I SET parameters in the procedure instead of just using them as input in procedure?
Any other suggestions regarding tracking and monitoring of a given procedure? I have heard about trace flags and extended events, but I do not have any experience with this.

As a note, we tried force 2 join hints since these joins were used in fast select. Plan changed but performance did not change. I know this is a last resort solution.

EDIT:

I tried to reduce parameter sniffing issues by declaring and setting values from SSIS-input in the procedure, and this seemed to solve issues in test environment, however test environment chose bad plan when recompiling.

Production environment has bad plan as estimate, how can I ensure that it chooses the solution and not the problem plan?

Not a full answer to all your questions but:

  What reasons makes the query stop from increasing CPU time?

As well as heavy IO (though I would expect CPU time should slowly tick up in that case too), there is the possibility that it is sat idle waiting for locks to be released by one or more other transactions. Check the relevant output of sp_who2 (built in but undocumented) or sp_whoisactive.

  I have heard about trace flags and extended events, but I do not have any experience with this.

Trace flags are not something to play with without a specific and well understood need. Extended events will be useful for tracking plan execution amongst many other things, but a full run-down is almost certainly out of the scope of a DBA.SE answer. There are plenty of examples out there, and presumably official documentation (though that tends to be optimised for reference, not initial learning).


Can this SQL be made more efficient and combined into one select? I have the 4 SQL queries below that I combine into one result set at the end.

I am guessing that since I am operating on the same tables in each of the SQL sections, there is a cool way to get my results out of one unified SQL query.

Currently I am doing it like this:

select
[date] = CONVERT(DATE, M.crdate),
[tally sent to smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_To_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =2 and left(mr.EmailAddress,4)='smc+'
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;
--select * from #tmp_Attachments_Sent_To_smc

select
[date] = CONVERT(DATE, M.crdate),
[tally sent from smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_from_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =1 and left(mr.EmailAddress,4)='smc+'
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;

select
[date] = CONVERT(DATE, M.crdate),
[grand total sent to smc] = count(*) ,
[total MB size sent to smc] =  sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_to_smc
from Message M
where MessageSourceId=2
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;

select
[date] = CONVERT(DATE, M.crdate),
[grand total sent from smc] = count(*) ,
[total MB size sent from smc] =  sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_from_smc
from Message M
where MessageSourceId=1
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;

select   A.* ,
         B.[tally sent from smc w/ attachment] ,
         B.[total MB size],
         C.[grand total sent from smc] ,
         C.[total MB size sent from smc],
         D.[grand total sent to smc] ,
         D.[total MB size sent to smc]
from #tmp_Attachments_Sent_To_smc A
join #tmp_Attachments_Sent_from_smc B on A.date = b.date
join #tmp_Sent_from_smc C on A.date = C.date
join #tmp_Sent_to_smc D on A.date = D.date

drop table #tmp_Attachments_Sent_To_smc
drop table #tmp_Attachments_Sent_from_smc
drop table #tmp_Sent_from_smc
drop table #tmp_Sent_to_smc

As a first pass, I'd see whether you can eliminate the temporary tables. I like CTE's but they don't always provide better performance.

;WITH tmp_Attachments_Sent_To_smc AS
(
    -- Find all the emails to smc+
    -- that had attachments
    -- and something else
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent to smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 2
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Attachments_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [tally sent from smc w/ attachment] = COUNT(*)
    ,   [total MB size] = SUM(CAST(AD.Size AS decimal(10, 2))) / 1024 / 1024
    FROM
        AttachmentDetail AD
        INNER JOIN
            MessageAttachment MA
            ON AD.AttachmentId = MA.AttachmentId
        INNER JOIN
            MessageRecipient MR
            ON MA.MessageId = MR.MessageId
        INNER JOIN
            [Message] M
            ON MR.MessageId = M.id
    WHERE
        AD.isinline <> 1
        AND MR.RecipientTypeId = 1
        AND LEFT(mr.EmailAddress, 4) = 'smc+'
    GROUP BY
        CONVERT(date, M.crdate)
)
, tmp_Sent_to_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent to smc] = COUNT(*)
    ,   [total MB size sent to smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 2
    GROUP BY
        CONVERT(date, crdate)
)
, tmp_Sent_from_smc AS
(
    SELECT
        [date] = CONVERT(date, M.crdate)
    ,   [grand total sent from smc] = COUNT(*)
    ,   [total MB size sent from smc] = SUM(CAST(Size AS decimal)) / 1024 / 1024
    FROM
        [Message] M
    WHERE
        MessageSourceId = 1
    GROUP BY
        CONVERT(date, crdate)
)
SELECT
    A.*
,   B.[tally sent from smc w/ attachment]
,   B.[total MB size]
,   C.[grand total sent from smc]
,   C.[total MB size sent from smc]
,   D.[grand total sent to smc]
,   D.[total MB size sent to smc]
FROM
    tmp_Attachments_Sent_To_smc A
    INNER JOIN
        tmp_Attachments_Sent_from_smc B
        ON A.date = b.date
    INNER JOIN
        tmp_Sent_from_smc C
        ON A.date = C.date
    INNER JOIN
        tmp_Sent_to_smc D
        ON A.date = D.date;

Otherwise, what is probably hurting your performance are the conversions in your WHEREs and GROUP BY (CONVERT(date, M.crdate) CAST(AD.Size AS decimal(10, 2). Those are likely resulting in table scans which hurts you. You could also look at persisted computed columns if you can't modify the existing data types as a means to store those computed items.

I'm staring at the LEFT(mr.EmailAddress, 4) and I think that'll also force a table scan but I think it can be fixed with a mr.EmailAddress LIKE 'smc+%' That should at least be SARGAble.

At that point, you should be able to look at your query plans and determine whether an index could help.

Also, prefix your tables with the appropriate schema. I presume it's dbo but as I understand it, that will help with plan cache reuse.


Order In WHERE Clause Matter For SARGability? Suppose that I have a table with 1000 records and I have the below query, which would return 7 total records:

SELECT *
FROM MyTable
WHERE IndexedColumn > 5000
     AND OtherIndexedColumn = 2

Since the two columns have up to date indexes, SQL Server can make assumptions and find the values faster and both queries are SARGable (ideally fewer reads).  However, suppose that I need to make sure that the value is not equal to another value, let's say 12 for a different column, so I would have to add

AND AnotherIndexedColumn <> 12

If that's the last statement in the WHERE clause, does SQL Server use the SARGability in the first two WHERE statements to first filter, get the 7 rows, then look to see if each row of the 7 are not equal to 12, or does it apply the <> to every row in the original data set of 1000 rows?

The reason that I'm asking is because I'm aware that I could use a subquery or CTE to do the first part of the SARGable filter, then of the 7 rows, look and see if each is not equal, but is the query optimizer already doing that behind the scenes, or is it best to do it myself?

In theory, and particularly in modern versions of SQL Server, the order of the WHERE clauses makes absolutely no difference. SQL Server will generally process the filters in the most efficient order, and will pick the index that it determines will allow for the most efficient query. A difference that may occur is when the WHERE clause includes JOIN criteria or applies filters to outer joined columns; other differences can come into play when options like FORCE ORDER or specific trace flags are in use.

Re-writing as a CTE to "force" SQL Server to process a filter inside the CTE first doesn't work - SQL Server is going to re-write it anyway, and process it in the order that it deems best. This is why, for example, you can't escape invalid conversion errors by using a CTE or a subquery to first filter out the bad data - SQL Server might still present the bad data to the conversion before any filter does anything.

(Erland complained about this in Connect #537419.)

So, again generally, don't re-write your SQL code to try to out-smart the optimizer or prevent it from doing something stupid. There are occasions where it can pick the wrong index that might be resolved by changing the query itself, but deal with those in situations where you already believe that the optimizer has picked the wrong index (and before solving those, eliminate more likely causes like parameter sniffing, bad statistics, etc.).


How to list Oracle objects that have not been used in the last month? I have an Oracle database that has more than 1000 tables, sequences, indexes, and other object.

I want to clean up database object. Some objects are not used.

I want to list all objects that have not been use in more than 1 month so I can delete them.

There is no reliable way to do this. Even with auditing enabled, indirect object access is not recorded in some cases. For example if you have an ON DELETE CASCADE foreign key defined, and you delete rows from the parent table, the deletion of rows from the child table does not appear in the audit trail.

As far as I know, the only object type that has support for this, is the index. You can enable index monitoring by:

alter index owner.index_name monitoring usage;

Once you have enabled monitoring for an index, later you can check if the index was used in the view V$OBJECT_USAGE (when logged in as the owner of the index).

Starting with 12c, you need not to log in as the owner to query this, the new DBA_OBJECT_USAGE view can be used for this.

When you do not want the index to be monitored anymore, disable monitoring by:

alter index owner.index_name nomonitoring usage;


Running CheckDB on a clone My company are looking to bring in Redgate's SQL Clone to deploy live databases into other environments. I wanted to see if running CheckDB on these clones was a viable option instead of restoring a database to a non production environment or running checkdb in production.

I am not 100% sure of the technology they use but their documentation speaks of virtual disk files. I have recently implemented a similar home grown solution using virtual disk files to store restores of databases as images with differencing disks hosting the clone databases. Any write action will go to the differencing disk but reads, unless to an updated page, will go to the base image virtual disk.

Any DBCC commands run against a clone will read from the base image virtual disk file. So, in theory DBCC checks against a clone should be as valid as restoring to a non production environment.

Assuming that data corruption will be backed up and restored, it should be a valid check of production.

Running DBCC checks used to be important when storage hardware was less reliable. The need for them is reduced with more modern storage technology. That is not to say that you should not run them, you really should! Just less often. In previous employment we reduced our DBCC checks to monthly or less. Although, we did have a considerable amount of databases and the IO requirement to peform the checks meant we our hands were forced to reduce the frequency.


Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes.  My problem comes to figuring out which other tasks I want to do and how often I should do them.

So, so far I have this in mind.  Correct me if there are any flaws in my thinking or a better way to do this.

Backup - All Tables, Full Backup (daily)
Backup - Selected Tables, Full Backup (hourly)
Backup - Transaction Logs (every 15 minutes)
Check database integrity (daily)
Reorganize index (daily)
Update statistics (daily)
Shrink database (weekly)
Rebuild index (weekly)
Maintenance cleanup (daily)

I remembered reading some time ago (when I set up a similar plan at another job) that some of these tasks don't need to be run on a daily basis or should not be run daily.  As to which ones, it escapes me.  I could use a little guidance on creating a better maintenance plan that will reduce data loss in an disaster, but won't tax the system when running during peak hours (and also increase the performance).

Josh,

This is a very common task for all DBAs and the right answer is NOT the same for every one and for each server. As lot of other things, it depends on what you need.

Most definitely you don't want to run "Shrink Database" as already suggested. Its EVIL to performance and the below ref will show you why. It causes disk and as well as index fragmentation and this can lead to performance issues. You are better off by pre-allocationg a big size for the data and log files so that autogrowth will NOT kick-in.

I didn't understand your #2. selected tables full backup. Can you elaborate more on this?

Coming to Index reorganize, update statistics and index rebuilds, you need to be careful on how you do this otherwise you will end up using more resources and also end up with performance issues.

When you rebuild indexes the statistics of the indexes are updated with fullscan but if you do update statistics after that, then those will be updated again with a default sample (which depends on several factors, usually 5% of the table when table size > 8 MB) which may lead to performance issues. Depending on the edition you have, you may be able to do online index rebuilds. The right way of doing this activity is check the amount of fragmentation and depending on that either do index rebuild or index reorganize + update statistics. And also you may want to identify which tables need to update stats more frequently and try to update stats more often.

Maintenance Plans are OK but its hard to get the best out of them doing these customizations unless you can login to SSIS and tweak the MP's. that's why I prefer NOT to use them and use Ola Hallengren's free scripts that are more robust than MP's. Also, I would recommend to catch up on the referenced article by Paul Randal on this topic.

Ref: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

This is NOT a comprehensive answer to your question but a good starting point. HTH and let us know if you have any additional questions/comments.


Mysql table doesn't exist error, references wrong table name I have a table that I can query fine...

SELECT * FROM search.books LIMIT 1;

Returns a value correct.

However, when I try and update the table...

UPDATE search.books SET title='sometitle' WHERE id='12345'

It returns

ERROR 1146 (42S02): Table 'search._books_new' doesn't exist

I'm not sure why it's referencing '_books_new' and not the one specified ? It's possible the table was copied previously as it's a dev server.

Engine is MyISAM

I have tried a

check table search.books

Also have restarted mysqld, but no difference.

It sounds like there is a trigger on the search.books table that is attempting to insert into the non-existent table, search._books_new

This query will display a list of triggers in the database:

SELECT *
FROM INFORMATION_SCHEMA.TRIGGERS


Using Identity Insert on a linked server We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following syntax:

insert into [Azure].[budget].dbo.Bill

I wanted to set identity insert on and have tried the following:

SET IDENTITY_INSERT [Azure].[budget].dbo.Bill ON
insert into [Azure].[budget].dbo.Bill

I was then informed that I would have to use dynamic SQL for this so I changed to the following:

EXEC [Azure].[budgetenergy].dbo.sp_executesql N'SET IDENTITY_INSERT Bill ON;insert into Bill ([FIELD LIST]) select [FIELD LIST] from [DUOS3NF].dbo.bill where dateOfIssue > ''10 mar 2015''';

The [DUOS3NF] is a database that is on the SMSS box it is not a linked server. Using this approach is there any way that I can insert into the linked server the results of the select statement of another database?

When I execute the query the error it does not recognise the database.

Linked servers aren't usually a good idea from a security perspective. See distribution statistics section here

I think SSIS would provide a better way of doing this.