Should I convert timezones when moving data to a report server/data warehouse I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I convert the data as I'm importing it with SSIS, or should I leave the data as is and just keep track of what time zone the server data is in and deal with it in my queries? Is there a reason not to convert the data as I copy it over?

Yes, there is a reason to not convert the timezone, and to keep the times in UTC. Times in UTC can be converted on-the-fly to any given timezone (and timezone's do occasionally shift, and Daylight Savings rules shift even more often). UTC is always accurate, whereas timezones and regions that allow for Daylight Savings Time have overlaps in the "fall-back" period when you repeat a particular hour. Save yourself a lot of pain down the road and keep the data in the current, more accurate format.

For the purposes of efficient searching, either convert incoming time ranges to UTC first, or, create a non-persisted computed column that represents Mountain Time (be sure to look up the historical rules governing start/end dates for DST) and create a NonClustered Index on that computed column.


Error AWR Operation failed: CATPROC not valid when creating database with dbca I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The problem I have now is when I try to create a test db using dbca. Everything goes well until the post creation DB part which popups an error. The error shows the same text as the /opt/app/oracle/cfgtoollogs/dbca/testingDB/postDBCreation.log:

   utl_recomp_begin: 09:34:38
   utl_recomp_end: 09:34:42
   BEGIN dbms_swrf_internal.cleanup_database(cleanup_local => FALSE); END;

  *
  ERROR at line 1:
  ORA-13516: AWR Operation failed: CATPROC not valid
  ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 206
  ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 239
  ORA-06512: at line 1

The creation of the DB cannot continue and it stops here...

I have searched everywhere in internet but not luck so far. I have no clues what it can be. Any suggestions?

Thanks in advance,

Try to rerun CATPROC on the database while connected as sys:

@?/rdbms/admin/catproc.sql

The recompile all invalid objects:

@?/rdbms/admin/utlrp.sql


How to Prevent Partitioned Columnstore Deadlocks on SELECT I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks on simple select statements from joins to these tables. Example query that deadlocks:

SELECT  TOP 33 r.tenantid
FROM    Table_r r
        INNER JOIN Table_cm cm ON r.MyKey=cm.MyKey
        INNER JOIN Table_pe pe ON r.MyKey=pe.MyKey
WHERE   r.TenantId = 69
        AND pe.TenantId = 69
        AND cm.TenantId = 69

Error message:

  Transaction (Process ID 56) was deadlocked on generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Clues:

If the query uses another index besides the CCI it does not deadlock.
If I remove two of the three tenantid filters it does not deadlock.
If I SELECT top 32 or lower it does not deadlock.
If I add OPTION (MAXDOP 1) it does not deadlock.
I can repro this in my scrambled PROD replica, PROD READ-ONLY Secondary, and PROD itself.
I cannot repro this behavior in DEV or INT.
It still deadlocks if I add WITH(NOLOCK) to all 3 table joins
The query deadlocks itself. It will deadlock when there are no other active processes.
Query plans without parallelism do not deadlock

Deadlock xml here

Our PROD Version:

  Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)   Jan 10 2019 18:51:38   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

How do I prevent deadlocks on this query?

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes:

FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017

(thanks to Denis Rubashkin for providing the link initially)

This was released as part of SP1 CU7.  If you're not up to that CU, you should give that a shot.  This fix would also be included in SP2 (any of the CUs).

In general, the two approaches for fixing intra-query parallelism deadlocks:

avoid parallelism (by tuning the query so that it doesn't go parallel, using a MAXDOP hint, etc.) - this is covered in the other answer by Thomas Costers
apply the latest service pack / cumulative updates to SQL Server


SQL Server 2014: Using gMSA for xp_cmdshell Proxy Account? We are running SQL Server 2014. We want to use a gMSA as the proxy account for xp_cmdshell.

I tried running the following:

EXEC sys.sp_xp_cmdshell_proxy_account 'gmsaNameGoesHere$', ''

However, it expects a password.

I tried running the following:

CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = 'gmsaNameGoesHere$'

It runs! However, when the user tries to run xp_cmdshell via the proxy account, they're told:

  Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 3 The
  xp_cmdshell proxy account information cannot be retrieved or is
  invalid. Verify that the '##xp_cmdshell_proxy_account##' credential
  exists and contains valid information.

Is it possible to use a gMSA as the proxy account for xp_cmdshell in SQL Server 2014? If so, how do I set it up as the proxy account?

Thanks!

However, it expects a password.

Correct, the password is encrypted and stored in the master database. When the account needs to be used, the password is decrypted and passed in.

  Is it possible to use a gMSA as the proxy account for xp_cmdshell in SQL Server 2014?

I don't believe so, and part of the reason why I included the quote about the password above.

The API calls used to do this expect a plain text password to be given. Since one does not exist for the gMSA in this instance the password would be blank. GMSA's do have passwords, but they aren't available in plain text and are base don generated values that rotate every so often, specified at creation time.

The code that does this expects a plain text password and does not check to see if the account is a gMSA or not, thus does not use any special API to retrieve the account password for storage.

My best guess would be, if we traced this all the way back to the calls you'd actually be receiving error 1326: The user name or password is incorrect.

Now What?

If you have very strong feelings that this would be helpful or that others would also like to use it, I'd invite you to create a Connect Item. If you do create one, please update your answer with a link to the item.


SQL Profiler tutorials for newbie The MSDN documentation tough to understand.

Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.

Try this free book from redgate:
http://www.sqlservercentral.com/blogs/aloha_dba/archive/2009/01/21/mastering-sql-server-profiler-e-book-available-for-free.aspx

Brad McGee also has some videos on his website:
http://www.bradmcgehee.com/videos/

This video on technet:
http://technet.microsoft.com/en-us/sqlserver/ee861095

This site also has more general SQL Server video tutorials (and a few profiler ones)
http://www.sqlshare.com/channel.aspx?cat=96b0a352-6498-dd11-bebd-001143eb4af9


Opened_tables value and tweaking table cache I've been reading on improving performance of my database, and ran into things called table-open-cache and Opened_tables . The MySQL doc told me the following:

Opened_tables
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Source
What do they mean by big? How much big is big enough to increase cache?
Okay, so I'm checking, how many tables are opened and seeing this:
 ---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Opened_tables | 711353 |

I'm increasing table-open-cache to 32768 (in fact, it was only 2000 before) and opened_tables is still growing.
An hour later, opened_tables is almost 750000.
And 20 hours later, it is 754000, so definitely the growth slowed down significantly, but still going.
Also, number of Open tables in mysqladmin status seems to have stopped increasing, or almost stopped. Well, it still increments by 1 every 5 minutes or so.
Currently mysqladmin status tells me this:
Opens: 754575  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754578  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754585  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912
Opens: 754589  Flush tables: 1  Open tables: 19725  Queries per second avg: 57.912

I've called it 4 times consecutively, so as you can see that Open tables is not growing, but Opens is growing.
How can it happen, if Open tables is still far away from table_opened_cache value which I set to 32768 about 21 hours ago?
Could you advice anything on tweaking this value and explain the results I got by this moment?
I have plenty of RAM for my purpose, and increasing table cache to 32k made server use 400MB more memory, but I still have 14GB free of 16.
What conclusions should I make at this point?

Dived Opened_tables by Uptime to get tables opened per second.  But even that is not very useful.

Much more useful are the 3 values of SHOW GLOBAL STATUS LIKE 'table_open_cache%';  More than a few misses or overflows per second is worth noting.

There are many other metrics; provide the data that Wilson mentioned; see can dig further.

Only one FLUSH TABLES since boot up?  No big deal.

mysqladmin status does not provide much useful info.

Be aware that the values in STATUS have different flavors, has many flavors:

Counters -- monotonically increasing since startup.  In most cases, dividing by Uptime is appropriate.  Eg: Opened_tables; Com_%.
High-water-marks -- eg:  Max_used_connections
Current values -- eg: Threads_running (usually 1, namely the query that is fetching the values)


Get the most present elements from many fields Here is my table:

id|item0|item1|item2
--+-----+-----+-----
1 | 10  | 12  | 12
2 | 12  | 68  | 13
3 | 15  | 13  | 10

I just want to get the most present number of all itemX columns. The query result should be: 12, 13, 10, 15, 68.

I know how to do this for one field:

SELECT item0 from finalBuild GROUP BY item0 ORDER BY COUNT(item0) DESC

But no for several at once.

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below.

Select item0 as item, count(*) as cnt from
(SELECT item0 from finalBuild
UNION ALL
SELECT item1 from finalBuild
UNION ALL
SELECT item2 from finalBuild) as item
group by item0
order by count(*) desc;


Understanding sys.dm_exec_query_stats execution_count vs creation_time I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV sys.dm_exec_query_stats.

According to the CPU report, my worst performing query is an insert statement that has run ~27K times.

When I take the query_hash for the query and put it into the query below, I get 62 results, each with the same query_hash and query_plan_hash but with different plan_handle values.

SELECT * FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE query_hash = 0x1561B563078359C1

According to BOL, the execution_count shows:

  Number of times that the plan has been executed since it was last compiled.

and creation_time shows

  Time at which the plan was compiled.

I am 99.9% sure the query runs once every 5 minutes, yet each query in the result set has a plan creation_time of this morning and yet execution_count shows ~400 for each one.

How does SQL Server determine this figure? Are the figures somehow linked by plan_hash or are they unique for each plan_handle?

sys.dm_exec_query_stats returns aggregated stats for plans, but with a row per query.

So if a procedure has 2 queries, then sys.dm_exec_query_stats would return 2 rows, but the plan-related columns (such as compile time, execution count, etc) are plan level stats, and cannot be further aggregated.


Is INSERT INTO ... SET standard SQL? I'm just wondering if this syntax is standard or where it comes from?

For example

INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00'

You can see it documented in MySQL but PostgreSQL does not support it.

Apparently this is a MySQL extension, the SQL 2011 spec defines the insert grammar as,

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>
14

<insertion target> ::=
<table name>

<insert columns and source> ::=
<from subquery>
| <from constructor>
| <from default>

<from subquery> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<query expression>

<from constructor> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<contextually typed table value constructor>

<override clause> ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE

<from default> ::=
DEFAULT VALUES

<insert column list> ::=
<column name list>

You can see this talked about on the Pg Lists


Defragmentation of Indexes - Higher value after defragmentaion I am trying to defragment indexes on SQL Server 2008 using ALTER INDEX--REBUILD. After defragmentation some indexes have fragmentation percentage value greater than its before defragmentation value. Is it possible or an issue ?

For indexes with small page_count this vaue is normal. Page_count can be seen from sys.dm_db_index_physical_stats. If page_count > 1000 only then go for index rebuild or reorganize. Sometimes when you rebuil index and use lower value of fill factor more pages are required and fragmentation might occur. Also when page count is low allocation to new pages are not done from uniform extent but from mixed so chances are that fragmentation will occur.But such fragmentation will not hamper any activity

If you are rebuilding index with ONLINE=ON option and used MAXDOP hint there can be more fragmentation as pointed in article from Bob Dorr from Microsoft have a look at this

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

PS: If you have small page count no need to worry


Data Flow Task Produces No Rows with Declare Statement I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I have also simplified the package to just do a row count for the purposes of this demo.

This issue I've having is that the query produces rows in SQL server but produces no rows in SSIS. The query is as follows.

DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)

Please ignore the * as that is just for the purposes of this demo. The actual query has the same issue even though it does not have the *. When Executed in SQL server this returns 1 row from the calendar table as expected. However when executed against the same environment in SSIS I get no rows returned.

If I turn the query into a stored procedure and execute the sproc in SSIS I get a row count of 1.

If I alter the query to this.

SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' )) AS DATE)

Then I once again receive 1 row.

Is there any reason I'm seeing this behavior?

UPDATE:
Before I receive a ton of flack for this I have no choice on the NOLOCK nonsense.

Well, even though I don't actually know the reason behind this behavior, I remember reading about this some time ago, and what to do to fix it. To make this work, you just need to add SET NOCOUNT ON as the first line of your sql command:

SET NOCOUNT ON
DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)


Can InnoDB use a clustered index as a covering index? The scenario I have in mind is a query like:

SELECT id FROM table WHERE id = ?

or equivalently,

   SELECT 1 FROM table WHERE id = ?

So here's some background:

In a B+ tree (which InnoDB uses as its clustered index (PK) data structure),
internal nodes contain records which are essentially (key, *pointer-to-page) tuples (obviously simplifying a bit).
Formally, the key values in the internal nodes don't need to be extant, i.e. there doesn't have to be a corresponding row in a leaf node with that key.
But, in practice, they most often are.

Now not all key values exist in internal nodes, and in that case, obviously a leaf node will have to be loaded and searched for the asked-for key.  But if there was a matching key found in an internal node, and it is known to be extant in an actual leaf record, then InnoDB could take advantage of those facts and save loading the leaf page (and therefore potentially save an expensive disk read).

So my questions are:

Do InnoDB internal nodes only contain extant keys? (would need to deal with deletes and even updates of a primary key)
If so, does InnoDB take advantage of this in resolving queries of the above kind; or does it always load a leaf page?
Is there anyway using the existing MySQL profiling stats (SHOW STATUS . . ., SHOW VARIABLE, INFORMATION_SCHEMA, etc) to determine whether a particular id (e.g. SELECT 1 FROM table WHERE id = 345) actually elided a leaf-page load?

How indexes work in InnoDB.  This discussion is limited to InnoDB.

(Let me establish some ground work, then I will get to your question.)

The PRIMARY KEY is a BTree where the leaf nodes contain all the columns of the row.  That is, the PK and the data coexist in the same BTree.

Each secondary key is a BTree where the leaf nodes contain a copy of the PRIMARY KEY.  Hence, a second lookup is needed to actually get to the rest of the columns.

DELETEing a row requires removing a 'row' from each BTree.

UPDATEing a column that is not in any index requires (1) locating the record (in the data BTree), and (2) modifying the record.  (Plus lots of Multi Version Concurrency Control stuff.)

UPDATEing a column that is in some index(s) requires (logically) deleting the record from each index where the column was part of that index and inserting a row in another place in that BTree.

SELECTing a row via the PK (WHERE Id = ...) usually requires drilling down the BTree to the leaf node.  (You have presented a rare exception.)

SELECTing a row via a secondary key (WHERE other = ...) requires two steps: (1) drill down the secondary BTree to the leaf node, fetching the PK columns, and then maybe (2) drill down the PK BTree to get to the record.

The "maybe" depends on whether or not all the needed columns are found in the secondary index (keep in mind that that includes the columns of the PK).  If everything is found in the secondary BTree, it is called a "covering index" for that select.

An optimization is to add extra non-TEXT, non-BLOB columns to a non-UNIQUE index to make it "covering".  (There are diminishing returns if the columns you want to add are big, or there are "too many".  I suggest no more than 5 columns in an index, but there are exceptions.)

The next-to-bottom node in a BTree contains only the boundaries of the leaf node.  That is, for SELECT 1 FROM table WHERE id = 345, the "345" probably exists only in the leaf node.

The only case I know of for not going all the way to the leaf node is when ANALYZE TABLE is gathering statistics.

The + in B+Tree refers to the leaf pages being linked together, thereby making "range" scans (in table or in index) slightly more efficient.  A hundred (or so) rows are found in one leaf node block, then the link gets it to the next block for the next hundred.  Otherwise, it would have to come "back down" the tree to find the 'next' block.  An InnoDB 'block' is 16KB.

Back to your key question.  Why not optimize the case where 345 can be seen in a non-leaf node?  Because it is such a rare query that it does not warrant extra code, extra code to break, and might not average out as being better.  Some issues:

Only 1% (or so) of keys would be sped up.
The block is likely to be in cache already.
The block is likely to be needed -- Note that your example requires SELECT 1, which is a rather rare subset of selects.  It would take extra effort to discover this special case.
If the row needs to be locked (for MVCC, etc), then it needs to get to the leaf node.  This probably requires that the leaf node be fetched.

A Rule of Thumb of mine:  If some optimization is not likely to save 10% (by some metric), I should look elsewhere to improvements.


Entries count by date is giving wrong results I am getting some strange results with below queries:

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and  `StockUpdated` <= '2016-12-31'

The result is : 24446

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'

The result is : 33098

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and `StockUpdated` <= '2017-1-1'

The result is : 33098

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2017-1-1'

The result is : 0

My question is why the below query is not perfect?

SELECT count(id)
FROM `asins`
WHERE `StockUpdated` >= '2016-12-30'
and  `StockUpdated` <= '2016-12-31'

Looks like StockUpdated has a time part that makes the query not count the values on 2016-12-31 with time other than 00:00.

Try a non inclusive comparison instead. StockUpdated < '2017-01-01'


generated column in mySQL workbench I am a beginner and start designing small database. I've found that to automatically generate value from the table, I can either use a trigger or compute them when forward engineering.
Therefore, I've referred to:

https://stackoverflow.com/questions/5222044/column-calculated-from-another-column
https://gabi.dev/2016/10/17/understanding-generated-columns/

But what I am looking for is to compute the value from two tables. Is that possible to do SELECT to grab the value for generated values? Or I've done it wrongly. Any help or hint is highly appreciated.
So far, I've tried this, but it's wrong:
CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
`OrderNumber` INT NOT NULL,
`ShippingAddress ID` INT NOT NULL,
`OrderDate` DATE NOT NULL,
`Shipping Cost` INT NOT NULL,
`Customer_ID` INT NOT NULL,
 /* tried to get the value from two table */
`ShipWeight` INT GENERATED ALWAYS AS (
 SELECT SUM(Product.ProductCode*Quantity)
 FROM Order
 INNER JOIN Product on Product.ProductCode = Order.ProductCode) VIRTUAL,
`Quantity` INT NOT NULL,

The short answer is you can't. The rules for the expression of the generated column are (see create-table-generated-columns.html):

Generated column expressions must adhere to the following rules. An
error occurs if an expression contains disallowed constructs.

Literals, deterministic built-in functions, and operators are
permitted. A function is deterministic if, given the same data in
tables, multiple invocations produce the same result, independently of
the connected user. Examples of functions that are nondeterministic
and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

Stored functions and user-defined functions are not permitted.

Stored procedure and function parameters are not permitted.

Variables (system variables, user-defined variables, and stored
program local variables) are not permitted.

Subqueries are not permitted.

A generated column definition can refer to other generated columns,
but only those occurring earlier in the table definition. A generated
column definition can refer to any base (nongenerated) column in the
table whether its definition occurs earlier or later.

[...]


SQL Server Rollback being performed - how to stop it? I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table.  It's all wrapped in a transaction.  I thought this would be good at first, but realize it's a dumb mistake.

While it ran for 7 hours on my local instance machine, on the production DB it took over 2 days to run! Tired of waiting and realizing that my solution was probably not the best way to do this, I closed my query window and did a kill on the process.  Now, it's doing a rollback.

Meanwhile, I've run this: KILL 60 WITH STATUSONLY and EXEC sp_who2 'active'.  The status says "SPID 60: transaction rollback in progress. Estimated rollback completion: 62%. Estimated time remaining: 31849 seconds".  The seconds and diskio is going up, so I know it's trying to do the rollback.  It's extremely slow and going up a percentage complete every several hours.

My issue is that because of my actions, the database got huge and is taking up disk space.  Furthermore, I don't care about these two new tables I tried to copy into.  I'd like to truncate/drop them and start over with a much better method of solving this issue.  However, I can't truncate/drop them in the middle of the rollback?  I could care less what happens to these new tables.  They are also in their own file group and .ndf files.  I just want to delete these as quickly as possible so I can resume my better method.  I'm on SQL 2014 and need urgent help as it's Saturday and I'd rather not bother my boss! :)

@MaxVernon already gave  all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in seconds may increase while the process runs. After running a query the SQL Server can't grant more memory to the process. For big calculations your tempdb will most probably be used. And yes this will lead to a disk intensive call.  That's why it's nearly always best to keep the transactions small.

Also please note that restarting SQL Server is in most cases a bad idea, since it has multiple negative side effects. It will for example rebuild the TEMP DB and also clear statistics...
I


Minimizing/avoiding data repetition and choosing between compound keys and unique indexes I am absolutely new to database development and i'm trying to build a question bank basically.

The questions have to be categorized based on Class, Subject and Topic.
Since each Class can have many Subjects and each Subject can be in many Classes I made a junction table called tb_SubjectClass with Subject and Class being primary keys.

To uniquely identify any Chapter I would need a combination of Subject and Class so I created tb_Chapters with Chapter, Subject and Class as primary keys with Subject and Class coming from tb_SubjectClass. I then repeated the same thing with tb_Topics as each topic can only be identified with a combination of Subject, Class and Chapter.

I have placed the relationship diagram below. It may also be relevant to know that I am expecting around 50000 questions (with equations and diagrams) to be stored in this database.

Main question: If I decide to stick to compound/composite keys, is my design proper or can it be improved further ?

Side question: what would be the best approach in my case, with the following considerations in mind:

expected records around 50000
questions table (tb_QnA) is going to be the most queried table (for selecting and for adding/deleting data)
the tables tb_Class, tb_Subjects and tb_SubjectClass are not going to change at all
ease of writing queries when connecting the database to a c# winform application (for data entry and generating reports)
the project is going to be desktop based

I have mainly been following the Database design tutorial on tekstenuitleg.net and video tutorials by WiseOwlTutorials. I have also recently started reading the book Relational Database Design Clearly Explained by Jan L. Harrington.

Does making foreign keys lead to data duplication / repetition or is
  it just a reference / pointer to data stored in the parent table ?

Foreign key restricts the possible values in one table's column accordingly to the presence of the same values in another table's column. Values are stored in the both tables so they are duplicated. To avoid (in fact - to reduce) duplications normalization should be used. That's means you have the reference table where all distinct values are stored and all other tables contains only IDs of that values.

  Should compound keys be avoided wherever possible ?

No. Even more, compound keys are the only way to speed up the query with complex conditions that involve multiple columns. Compound keys can be avoided by the price of performance.


PostgreSQL Update Column From Lookup I'm trying to replace a column with a lookup from another table.  Seems simple, but I'm thinking PostgreSQL is getting in the way for some reason.  I've seen that some syntax that evidently doesn't work in PostgreSQL

UPDATE A
SET userid = (
SELECT pid FROM B WHERE B.pid = A.userid
)   # => You will need to rewrite or cast the expression (pointing to "(SELECT"

I think this SQL is valid, and I don't know what I am missing.  How can I get this column to do a simple lookup?

You don't have a correct syntax.

UPDATE table1 set user_id = table2.user_id from table2 where table1.pid = table2.pid

You can also update data from the same table, just join the same table with a new alias

UPDATE table1 set user_id = (table2.json->>'user_id')::bigint from table1 as table2 where table1.pid = table2.pid


How do I show zero when I use count by using self left join? I am trying to use count() to count the number of the module where the particular student got the average mark below 40.  It also needs to show the number 0 if the student does not have any assessment mark lower than 40.  However, my query does not show any 0 number at all.
CREATE TABLE assessment (
assessmentId text,
mark integer,
studentId text,
moduleId text,
PRIMARY KEY (assessmentId, studentId, moduleId),
FOREIGN KEY(studentId) REFERENCES student(studentId),
FOREIGN KEY(moduleId) REFERENCES module(moduleId)) ";

in each record, it shows the module that the assessment is from, and show which student took the assessment.
this is my query
 "select count(m.moduleId), avg(e.mark), m.studentId
from assessment e
left join assessment m on m.assessmentId = e.assessmentId
and m.studentId = e.studentId
and m.moduleId = e.moduleId
group by m.studentId
having avg(e.mark)<40";

the module could have more than one assessment, therefore the avg(e.mark) is to find out the average mark for each module for each student.  Therefore it is group by studentId
so what i want the query to show is:

studentId|avg(e.mark)|count(m.moduleId)
1        | 50        | 0
2        | 20        | 2

However I am getting the result below

studentId|avg(e.mark)|count(m.moduleId)
1        | 20        | 2

I really don’t know where the problem is

To answer your question, I did the following:
Edit:
Sorry, I missed the bit about 0 modules failed for students who had never got a grade below 40 in any module - please see revised code below (note to self - read the question!). Luckily, the DDL and table insert DML remain the same.
I reworked your schema slightly as follows (fiddle here):
CREATE TABLE assessment
(
  assessment_id INT,
  mark INTGER,
  student_id TEXT,
  module_id TEXT,
  PRIMARY KEY (assessment_id, student_id, module_id),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (module_id) REFERENCES module(module_id)
);

Populate it:
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'),
(2, 20, 2, 'French1'),
(3, 50, 3, 'French1'),
(4, 40, 1, 'English2'),
(5, 60, 2, 'English2'),
(6, 90, 3, 'English2'),
(7, 30, 1, 'Maths1'),
(8, 10, 2, 'Maths1'),
(9, 80, 3, 'Maths1');

OR (see fiddle here):
INSERT INTO assessment VALUES
(1, 10, 1, 'French1'),
(1, 20, 2, 'French1'),
(1, 50, 3, 'French1'),
(2, 40, 1, 'English2'),
(2, 60, 2, 'English2'),
(2, 90, 3, 'English2'),
(3, 30, 1, 'Maths1'),
(3, 10, 2, 'Maths1'),
(3, 80, 3, 'Maths1');

I'd say assessment_id should be an INTEGER, but I know SQLite has a weird thing going on with data types!
And then run the query above:
SELECT
  COUNT (a2.module_id) AS "Mod. cnt",
  CASE
    WHEN
      MIN(a1.mark) <= 40
    THEN
      COUNT(a2.module_id)
    ELSE
      0
  END AS "Failed modules",
  ROUND(AVG (a1.mark), 2) AS "Avg grade",
  a2.student_id AS "Student id"
FROM assessment a1
  LEFT JOIN assessment a2
  ON a2.assessment_id = a1.assessment_id
  AND a2.student_id = a1.student_id
  AND a2.module_id = a1.module_id
GROUP BY a2.student_id
HAVING avg(a1.mark) < 40 OR (SELECT MIN(a1.mark) >= 40)
ORDER BY a2.student_id, avg(a1.mark) DESC; -- ALL outer queries should have an ORDER BY!

Result (same for either set of data):
Mod. cnt    Failed modules  Avg grade   Student id
       3                 3      26.67            1
       3                 3         30            2
       3                 0      73.33            3

I suggest that you have a separate module table and that you JOIN using INTEGERs as your PRIMARY KEYs - AFAIK, it's faster than TEXT - the SQL may become a wee bit more complex, but it'll be worth it in the long run - normalisation! You may wish to look at the SQLite COALESCE function for students who haven't taken any assessments?
Using GROUP_CONCAT(), you can do the following (just last two lines of query shown - see fiddle here):
a2.student_id AS "Student id",
GROUP_CONCAT(a2.module_id || ', ' ||  a2.mark,  ', ') AS "Grades"

Result:
Mod. cnt    Failed modules  Avg grade   Student id  Grades
       3                 3      26.67            1  French1, 10, English2, 40, Maths1, 30
       3                 3         30            2  French1, 20, English2, 60, Maths1, 10
       3                 0      73.33            3  French1, 50, English2, 90, Maths1, 80


Lost data during PostgreSQL minor version upgrade I had PostgreSQL 9.4.5 installed on MacOS which was done using Homebrew. I wanted to update it to the latest minor version of 9.4, so I did this (if I remember correctly):

brew uninstall postgresql
brew install postgresql@9.4

which installed PostgreSQL 9.4.17 fine, however it seems I lost all my data during the process. I saw initdb was run at one point, is it what caused the deletion?

Note: It was not a production database, so nothing was really lost however I was surprised with this outcome, so I would like to know the reason.

Indeed, you should not have uninstalled it and re-installed it, as PostgreSQL initdb starts a new database store from scratch, hence empty.

BTW it may happen that your data is still there, but in another old directory. See for example this article (https://keita.blog/2016/01/09/homebrew-and-postgresql-9-5/) that shows an upgrade between 9.4 and 9.5 but it may apply in the same way in your case.

If you look at the PostgreSQL formula you can see:

def post_install
    (var/"log").mkpath
    (var/name).mkpath
    unless File.exist? "#{var}/#{name}/PG_VERSION"
      system "#{bin}/initdb", "#{var}/#{name}"
    end
end

so initdb should be called only if directory is not already populated, so in your case the database just got initialized in a new directory, empty, while your data is still in another directory.

But things have changed recently.

Homebrew 1.5, released on January 19th 2018 has this in its changelog:

  brew postgresql-upgrade-database is a new command to simplify upgrading PostgreSQL databases between major versions.

It is a wrapper around PostgreSQL pg_upgrade tool, that can be used to upgrade a database in place, hence not loosing anything, including between major versions.

This article (https://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/) can give you a lot of information on how to upgrade PostgreSQL, and what happens when you use brew upgrade.


SQL: Merge rows when value doesnt change in continuous time I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during a continuous time (EndDate of row is same as StartDate of the other), for example, for this case:
Dataset

I need to output this:
Desired Output Table

I cant come with a solution yet since I think I cant use Group By, I need to merge rows with specific SerialNumber when the StatusId doesn't change in a continuous time.

create table StatusTable
(
SerialNumber varchar(15),
BeginDateTime datetime,
EndDateTime datetime,
StatusID int,
TimeInStateInMilliseconds int
)
go

insert StatusTable
values
('DASimulator1','2/25/20 16:15','2/25/20 16:16',0,60000)
,('DASimulator1','2/25/20 16:14','2/25/20 16:15',0,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',5,60000)
,('DASimulator1','2/25/20 16:13','2/25/20 16:14',0,60000)
,('DASimulator1','2/24/20 23:20','2/24/20 23:21',3,60000)
,('DASimulator1','2/24/20 23:19','2/24/20 23:20',3,60000)
,('DASimulator1','2/24/20 23:18','2/24/20 23:19',3,60000)
,('DASimulator1','2/24/20 23:17','2/24/20 23:18',3,60000)
go

with base as
(
select a.*
    , CASE when b.SerialNumber is not null or c.SerialNumber is not null then 1 else 0 end as GroupStatus
from StatusTable a
        left join StatusTable b on a.BeginDateTime = b.EndDateTime and a.StatusID = b.StatusID
        left join StatusTable c on a.EndDateTime = c.BeginDateTime and a.StatusID = c.StatusID
)

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 1
group by SerialNumber, StatusID

union all

select SerialNumber, MIN(BeginDateTime) as BeginDateTime, MAX(EndDateTime) as EndDateTime, StatusID, SUM(TimeInStateInMilliseconds) as TimeInStateInMilliseconds
from base
where GroupStatus = 0
group by SerialNumber, StatusID

order by EndDateTime desc
go

here's what i came up with based on only the data set that you've shared. if there are other data variations in your full data set then you need to adjust for it.

firstly, i'm a bit confused on your desired output result set. the third row EndDateTime value matches the first row BeginDateTime value and they both have the same StatusID value? so based on what you said about continuous time, wouldn't these two rows be merged together?

my query behaves as such, if you run my code, it will merge row 1 and 4 together.

also, shouldn't the fourth row in your result set have a BeginDateTime value of 2020-02-24 23:17:00.000 or are you just grabbing the EndDateTime value and making that the BeginDateTime value if it matches?


Are there any extension/add-on tools to improve report-writing in SSRS? My company is implementing some software which uses SSRS as the engine for report presentation both in and out of the application, so we'll be customizing a lot of SSRS reports soon.

Microsoft has put a good amount of functionality in SSRS but, frankly, Report Builder et al are still not the easiest tools to use for report-writing...

My Google searches seeking extensions or add-ons or other third-party tools to help with the report and query writing tasks doesn't find anything except what Microsoft has provided... Is this really the case, that there are no tools to improve on SSRS?

SQL Server Data Tools is typically used to author SSRS reports when Report Builder is insufficient. You can download and install SSDT as a standalone tool (runs a cut down version of Visual Studio) or if you have VS installed already it installs as an extension.

Make sure you download the correct SSDT version for the version of Visual Studio you have, then you can create and deploy advanced SSRS reports/projects from Visual Studio.

SQL Server Data Tools

Older versions for previous VS releases


Progress Open Edge Union I am attempting to union together 2 queries in progress DB
Unfortunately, this is returning me a syntax error.
Are union queries unsupported in progress DB?
     SELECT 0, 'All'
     UNION ALL
     SELECT custcode, custname
     FROM pub.pv_customer

That may depend on what version of OpenEdge you are querying. Reasonably modern releases do support UNION:
https://www.progress.com/docs/default-source/openedge/progress-openedge-sql-compliance-to-core-sql-2011.pdf?sfvrsn=0
(this applies to OpenEdge 11.5 and above)


Combined or separated tables: File and Message? Is this the best approach to design this table in the purpose of sharing the file online between my customer and I, or should I separate Message table and File table or combined them together?

Combined table approach:
FILE table

ID
CustomerID
FilePath  (files'path from both parties: customer and I)
Content   (Message)
Flag      (Read/Unread + ISent/CustomerSent)
Status    (Viewed,Reviewed,Accepted,and etc)
Timestamp (Current DateTime of file changed)
CreationDate (Date of file created)
Dateline  (Set the deadline of an action corresponding with the file)

Pros: 1 single table

Cons: 2 actions sharing the same table, in each record, either "FilePath" or "Content" could be null in case customer wants to communicate by writing a message without having attachment (FilePath) or customer uploads any files without having any message (Content) written.

2 tables approach:

FILE table

ID
CustomerID
Name
Type
Size
Data
Created

Message table

ID
CustomerID
Message
Attachment
Created

Pro: No Null field

Con: They are serving the same purpose in these two tables but one focusing on Message and others on File.

What would you recommend?

I would definitely go with two separate tables.

  customer wants to communicate by writing a message without having
  attachment (FilePath) or customer uploads any files without having any
  message (Content) written.

But with the way you're modeling it right now, there really isn't any relationship between file and message, as they both are dependent on Customer_ID.  If you tried to query all files and messages for each customer, you wouldn't be able to tell when the two are related (except maybe by looking at the date).

And it sounds like a customer can have a file without a message (and vice versa).  I think it makes more sense to create a "Communication" table (for lack of a better word).  Essentially it allows you to keep track of all types of communication (message and file) for your customers, but also ties message with file (attachments) when appropriate.

table: Communication
communication_id   int
customer_id        int
created            datetime

That would allow you to further normalize by taking "created" and "customer_id" out of the file and message tables.  Likewise, you'd want to add "communication_id" to both File and Message.  Then to report on them, you could do something like this:

SELECT *
FROM Customer cu
INNER JOIN Communication co ON cu.customer_id = co.customer_id
LEFT JOIN File f ON f.communication_id = co.communication_id
LEFT JOIN Message m ON m.communication_id = co.communication_id
ORDER BY cu.customer_id, co.communication_id


SQL update next row in select I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data.

Each row has a field for KM (mileage in KM metric unit), prevMileage (mileage of previous row) and nextMileage (mileage of the next row). The prev and next fields are calculated in a list before this one. The problem with this is that if the KM for a row is NULL the prevmileage of the next row and the nextmileage of the previous row will be NULL as well.

To make my calculation correct I need to add the previous mileage to the calculated value I make for each row. But if there are multiple NULL rows after each other, this is impossible.

What I need now is a way to update the 'previousmileage' field of the next row.

This is the code I have so far and a preview of the table:

Date      |     KM     |   prevmileage    | nextmpileage
--------------------------------------------------------
01/01/2008|     5000   |       4500       |     55000

Code

    , CalculatedList as (
SELECT
    PNL.MonthDate,
    PNL.FK_CarID,
    PNL.AllocationID,
    PNL.CO2,
    PNL.KM,
    PNL.beginMonth ,
    (CASE   WHEN PNL.KM IS NULL
            THEN (CASE  WHEN PNL.PrevMileage IS NULL
                        THEN (CASE  WHEN PNLnext.PrevMileage IS NULL --Here I want to update the prevmilage of the next row
                                    THEN (CASE  WHEN PNL.NextMileage IS NULL
                                            THEN 0
                                            ELSE ((PNL.NextMileage / DATEDIFF(DAY, PNL.PrevMonthDate, PNL.NextMonthDate)) * DATEDIFF(DAY, PNL.PrevMonthDate, PNL.MonthDate))
                                            END)
                                    ELSE 0
                                    END)
                        ELSE (CASE  WHEN PNL.NextMileage IS NULL
                                    THEN PNL.PrevMileage
                                    ELSE (CASE  WHEN PNL.PrevMileage = 0 OR PNL.NextMileage = 0
                                                THEN (CASE  WHEN PNL.NextMileage = 0
                                                            THEN 0
                                                            ELSE (PNL.NextMileage / 2)
                                                            END)
                                                ELSE ((PNL.NextMileage - PNL.PrevMileage) / DATEDIFF(DAY, PNL.NextMonthDate, PNL.PrevMonthDate)) * DATEDIFF(DAY, PNL.MonthDate, PNL.PrevMonthDate)
                                                END)
                                    END)
                        END)
            ELSE PNL.KM
            END ) as Calculated,
    PNL.NextMileage,
    PNL.PrevMileage,
    PNL.PrevMonthDate,
    PNL.NextMonthDate,
    (CASE   WHEN PNL.KM IS NULL
            THEN 'N'
            ELSE 'Y'
            END) AS RealData,
    PNLnext.PrevMileage as test
FROM
    PrevNextFullList as PNL outer apply
     (select top 1 ml2.*
      from PrevNextFullList ml2
      where ml2.FK_CarID = PNL.FK_CarID and
            ml2.MonthDate > PNL.MonthDate
      order by ml2.MonthDate desc
     ) PNLnext
)

Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer.

SELECT  MonthDate
        , CarId
        , KM
        , LAG(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) Prev
        , LEAD(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) [next]

FROM    PrevNextFullList

If you do need to persist the columns then use this statement to create a VIEW

UPDATE You would need to upgrade to SQL 2012 to do this though.


Why is Excel File Opened Exclusively By Another? I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source.

When I edit my Excel source, I create a new connection for my Excel source using a directory where the package is going to use when the package is completed. SSIS is able to load the Excel file into the new connection and preview the columns as well data.

At this point, SSIS can load and read the file. It can access the directory and everything is good to go from this point on.

The next thing I do is create a new package variable that is a string with the value of the same exact path of the Excel connection. Remember, this is the same path that I loaded to create the connection. SSIS can access the directory and read the Excel file.

I then assign the new variable to the Excel connection under the Expression section. I set the Expression property to ExcelFilePath and assign my variable that has the Directory path of the Excel files I want to loop.

After this, I get the error:

  It is already opened exclusively by another user, or you need
  permission to view and write its data.".

What gives? The package is 32-bit and can access the file...

Excel file is not open.
I closed/Re-opened Business Intelligence Studio to    ensure it's not opened in
memory.
I checked the file permissions, all look good.
File is not in read only mode.

Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file.

*.xls versus *.xls*


Tsql Query speed slow due to Or within where clause, causing index scan instead of seek I have been working on making this query work more efficiently.

I found that the number of Ors within the where clause are the biggest problem within this query.  This query sits in a stored procedure.

I am to the point where the only options I can think of is. :

Creating 16 different queries for all the different inbound parameter possibilities.
Creating a dynamic sql query but I don't believe this will be any faster
Reverting back to string sql, but I don't ever like to do this as these do not perform as quickly as stored procedures.

I am sure others have ran into this issue before.  The query performance isn't terrible to begin with at roughly a second or less, but there are instances where it is hit numerous times causing a lag of up to 5 or 6 seconds.

Query below. :

DECLARE @PERSON_ID AS INT
DECLARE @ITEM_ID AS INT
DECLARE @ITEM_VERSION AS INT
DECLARE @ITEM_SUB_NAME AS VARCHAR(250)
DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250)

--DEFAULTS
SET @PERSON_ID = 0
SET @ITEM_ID = 0
SET @ITEM_VERSION = 1
SET @ITEM_SUB_NAME = NULL
SET @ITEM_SUB_SUB_NAME = NULL

    SELECT ID, PERSON_ID,
           ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
           ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME',
           ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME',
           ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE',
    FROM PERSON_TBL s WITH (NOLOCK)
    WHERE    ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 )
    AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL ))
    AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 )
    AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL )
    AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL )
    ORDER BY PERSON_ID, ITEM_SUB_NAME

This is what I call the "kitchen sink" stored procedure - you want one procedure that handles all possible combinations of search criteria that a user might enter. It is virtually impossible to make SQL Server derive a single execution plan that will be optimal and efficient for all of these combinations - I don't care what kind of tricks you might think ISNULL can pull that COALESCE or OR can't.

The solutions I usually attempt, in this order, are:

Add OPTION (RECOMPILE) to the query. Yes, you pay a compilation cost every time, but you will get the right plan given the parameters that are supplied and their values.
Use dynamic SQL. Now you will be able to cache multiple different plans based on the different parameters passed. Combine this with the server-level setting optimize for ad hoc workloads (more info from Kimberly Tripp here and here) so that only versions of the plan that are used more than once are fully cached. Example:

DECLARE
  @PERSON_ID         INT = 0,
  @ITEM_ID           INT = 0,
  @ITEM_VERSION      INT = 1,
  @ITEM_SUB_NAME     VARCHAR(250),
  @ITEM_SUB_SUB_NAME VARCHAR(250);

DECLARE @sql NVARCHAR(MAX) = N'',

SET @sql = N'SELECT ID, PERSON_ID,
   ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
   ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
   ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
   ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
FROM dbo.PERSON_TBL AS s WITH (NOLOCK)
WHERE ITEM_VERSION '
+ CASE WHEN @ITEM_VERSION <> 1 THEN
   N' = @ITEM_VERSION' ELSE N' IS NULL' END
+ CASE WHEN @PERSON_ID <> 0 THEN
   N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
+ CASE WHEN @EMPLOYEE_ID <> 0 THEN
   N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
+ CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
+ CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
ORDER BY PERSON_ID, ITEM_SUB_NAME;';

EXEC sys.sp_executesql @sql,
  N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
    @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
  @PERSON_ID, @ITEM_ID, @ITEM_VERSION,
  @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;

If you find that parameter sniffing is still an issue even when you have individual plans for each combination of parameters provided (due to vastly different cardinality based on the actual values), you can add OPTION (RECOMPILE) inside the dynamic SQL, too - you once again pay the compile cost but it should be better when you have a much simpler where clause.

Some resources about this pattern:

The "Kitchen Sink" procedure (video)
#BackToBasics: An Updated Kitchen Sink Example

Paul White has a great post worth reading too:

Parameter Sniffing, Embedding, and the RECOMPILE Options

As an aside:

Stop using single quotes for alias delimiters. It makes them look like strings and is deprecated in some scenarios, never mind that delimiters are not even necessary here. (I talk about this issue here.)
Stop using ambiguous, regional date formats like m/d/yyyy (or is that d/m/yyyy?).
Always use the schema prefix.
Be very careful about using NOLOCK as a magic turbo button.
My thoughts on COALESCE vs. ISNULL.


Alter auto-increment of table with 16 million rows Learned this the hard way. Need to alter a table's auto-increment with 16 million rows.

Now, it's taking quite sometime.

Any estimate how long it would take? Sorry.

It depends, could be an hour or so. Perhaps you may like to give percona toolkit a try; pt-online-schema-change.

pt-online-schema-change alters a table’s structure without blocking reads or writes.
Specify the database and table in the DSN. Do not use this tool before reading its
documentation and checking your backups carefully.

Source: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html


How to dump / restore mysql.user I'm able to dump the table of users from one server with

mysqldump -u root -p -h [host address] mysql user > user_table_dump.sql

but how can I restore these users into my localhost enviroment? I tried without success:

mysql -u root -p mysql.user < user_table_dump.sql

mysql -u root -p mysql user < user_table_dump.sql

mysqldump -u root -p -h localhost mysql user < user_table_dump.sql

Following the comment by Akina:

  Edit dump file, remove all DDL statements from it. Restore data only - LOCK TABLESuserWRITE; INSERT INTOuserVALUES ... ; UNLOCK TABLES;.

I did it by taking only the data saved on the file and insert them into mysql.user like

INSERT INTO mysql.user VALUES(...)


Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?) We have a table with spatial data and some attributes and need to aggregate it so it could be used in various queries. We have the mechanism to do the aggregation on-the-fly via CLR stored procedures but I was wondering what would be the best way of caching this aggregated data in SQL Server 2008 R2 as the aggregation is very slow.

So we have table like this:

[TableId] [int] NOT NULL,
[FeatureId] [int] NOT NULL,
[MeasureFrom] [float] NOT NULL,
[MeasureTo] [float] NOT NULL,
[Value] [smallint] NOT NULL,
[Timestamp] [datetime2](7) NOT NULL

FeatureId, MeasureFrom and MeasureTo basically define an interval and Rating is the value of this interval. Note that FeatureId actually references a real lines (shapes) from a different table. We have about 250k rows in this table currently with overlapping intervals and various timestamps (historical records). Our aggregation process tries to find the latest Value across all intervals so it needs to cut and join intervals based on timestamp and keeps the related value.

As the on-the-fly querying is slow and takes a lot of resources, we thought we might need to create another database storing the results of this aggregation. The source table is constantly updated (but not too often, once in a couple of days).

What would be the best way of creating this database and keeping the aggregated values up-to-date? I can think of using triggers, or scheduled task. I have no experience with SSAS but would that be more suitable?

Just to note, we have a few similar tables storing slightly different values (some with more that one Value column) and hence the reason for a separate database rather than another table in the original database.

How quick would be the querying of this separate database from the original database using cross-database joins?

Edited:
To demonstrate what our "aggregation" does, here is some sample data:

FeatureId | MeasureFrom | MeasureTo | Value | Timestamp
1         | 1           | 20        | 2     | 2015-01-01
1         | 5           | 15        | 3     | 2015-01-02
1         | 9           | 10        | 8     | 2015-01-03

And the result we are getting:

FeatureId | MeasureFrom | MeasureTo | Value | Timestamp
1         | 1           | 5         | 2     | 2015-01-01
1         | 5           | 9         | 3     | 2015-01-02
1         | 9           | 10        | 8     | 2015-01-03
1         | 10          | 15        | 3     | 2015-01-02
1         | 15          | 20        | 2     | 2015-01-01

As you can see, the result actually contains more rows than the original data. Basically we need the latest Value at any point within the area. The latest value is determined based on value in the Timestamp column. When there is a gap within intervals, this should propagate to the resulting dataset.

The code is actually written in .NET and it is quite complicated. The algorithm is as follows: Get all data for a feature of interest, order the records by timestamp descending, process all records in a loop. Take the interval (MeasureFrom, MeasureTo), intersect it with the result that you have, if you get some part that you don't have already, add it to the result. Continue with the next record. Data represents surveys done regularly so further you go in time, the more data will be thrown away as you will already have newer Value for the same Interval.

Surveys are coming in with random Timestamps so it is not always the latest one that comes in. Also same process is then repeated for other features as you might be interested in getting values for a couple of features at once. And this is the result people would query often.

Ideally we would like to keep the aggregation result as close to being real-time as possible.

To explain the required algorithm a bit more, see the link posted by @dnoeth (in the comment below) http://sqlmag.com/sql-server/packing-intervals-priorities . It solves similar problem with the difference of using priorities instead of Timestamps and the solution described uses features from SQL Server 2012.

The aggregate table can probably stay in the same DB. You can however create it on a separate filegroup and disk.

Full update of a new aggregate table

One way of doing it using pure SQL and your sample:

WITH list ([Type], [FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure])
    FROM (
        SELECT 0, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM data
        UNION ALL
        SELECT 1, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM data
    ) l([Type], [FeatureId], [Measure], [Value], [Timestamp])
)
SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
    , [Value] = CASE WHEN l1.Type = 0 THEN l1.Value ELSE l2.Value END
    , [Timestamp] = CASE WHEN l1.Type = 0 THEN l1.Timestamp ELSE l2.Timestamp END
FROM list l1
INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
;

With this really small sample, I am not sure it covers all your needs. It may help to add a bigger sample with more data.

It does 4 table scans. Since you have 250k rows, it may not perform so well. It will probablibly better to run it in batch of X consecutive FeatureIds.

This could be done using a job and a SSIS package with either a single update or batch update. You would have to truncate the aggregate table first.

Trigger on each new row in main table

For new rows, using a trigger, this query could be used:

WITH new([FeatureId], [MeasureFrom], [MeasureTo], [Value], [Timestamp]) as (
--    SELECT 1, 1, 20, 2, '2015-01-01'
--  SELECT 1, 5, 15, 3, '2015-01-02'
    SELECT 1, 9, 10, 8, '2015-01-03'
), gap([FeatureId], mn, mx) as (
    SELECT n.[FeatureId], mn.mn, mx.mx
    FROM new n
    CROSS APPLY (SELECT mn = MAX([MeasureFrom]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureFrom] < n.MeasureFrom) mn
    CROSS APPLY (SELECT mx = MIN([MeasureTo]) FROM data3 WHERE [FeatureId] = n.[FeatureId] AND [MeasureTo] > n.MeasureTo) mx
), list (x,[FeatureId], [Measure], [Value], [Timestamp], [ID]) as (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY [FeatureId] ORDER BY [Measure])
    FROM (
        SELECT 0, d.[FeatureId], d.[MeasureFrom], d.[Value], d.[Timestamp]
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND (d.MeasureFrom >= g.mn AND d.MeasureFrom < g.mx)
        UNION ALL
        SELECT 1, d.[FeatureId], d.[MeasureTo], d.[Value], d.[Timestamp]
        FROM data3 d
        INNER JOIN gap g ON d.[FeatureId] = g.[FeatureId] AND d.MeasureTo = g.mx
        UNION ALL
        SELECT 2, [FeatureId], [MeasureFrom], [Value], [Timestamp] FROM new
        UNION ALL
        SELECT 3, [FeatureId], [MeasureTo], [Value], [Timestamp] FROM new
    ) l(x, [FeatureId], [Measure], [Value], [Timestamp])
)
MERGE data3 AS target
USING (
    SELECT l1.FeatureId, [MeasureFrom] = l1.Measure, [MeasureTo] = l2.Measure
        , [Value] = COALESCE(d.[Value], l1.[Value])
        , [Timestamp] = COALESCE(d.[Timestamp], l1.[Timestamp])
    FROM list l1
    INNER JOIN list l2 ON l1.FeatureId = l2.FeatureId AND l1.ID+1 = l2.ID
    LEFT JOIN data3 d ON d.MeasureFrom = l1.Measure OR d.MeasureTo = l2.Measure
) as source(FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
ON (target.FeatureId = source.FeatureId AND target.[MeasureFrom] = source.[MeasureFrom])
WHEN MATCHED THEN
    UPDATE SET target.[MeasureTo] = source.[MeasureTo]
WHEN NOT MATCHED BY target THEN
    INSERT (FeatureId, [MeasureFrom], [MeasureTo], [Value], [Timestamp])
    VALUES (source.FeatureId, source.[MeasureFrom], source.[MeasureTo], source.[Value], source.[Timestamp])
;

You must replace the new CTE by the values of the new inserted row in the trigger and use it to merge with the aggregate table.

Scheduled update of newly added rows

If you can find a way to get a list of all newly added rows to the main table since the last update of the aggregate table, you could schedule a job every x minutes or hours and only update what is necessary based on what has been recently added.

The trigger query will work as well with scheduled updates.

This can be run as a schedueled job and within a SSIS package.


Will OS Patching affect the SQLLOG file Growth in SQL Server Last Sunday we have the OS Patch for the month of June.

Can someone help me understand if the monthly OS patching increases the SQLLOG file in SQL Server ?

Is there any linkage between the OS Patch and SQL Log file first of all ?

What could be the main reason for SQL Log file rapid growth in SQL Server all of a sudden in AlwaysOn AGs environment?

Is it Maint plans, Backups .....

I don't think there is any direct link between OS patching and SQL Server. The last part of your question when you mention AlwaysOn AGs which could explain the growth.

If you take a replica offline then I believe the log file cannot be truncated even after a log backup because the log file is used to ship the data to the replicas. It will stay in the log file until the replica has confirmed it has the data. So if you have a high-transactional workload and you take the replica offline, the amount of log stored on the primary will grow.

File size increases are also replicated between nodes, so if your logfile grows bigger on the primary then it will also grow on the secondary (once it comes back online).

There's not enough details in the question, but my guess would be that you took the replica offline to patch and during that time the log file on the primary grew as it was waiting for the replication to happen. The growth was then replicated to the replica after it reconnected.


Better Indexing for MySQL? Is it better to index each column individually with it's own key or to index multiple columns under the primary key?

I've seen it done both ways in many different applications and I don't see a clear difference.

First of all, your PRIMARY KEY depends on the logical nature of your data and will be indexed automatically by the DBMS. If the PK happens to be composite, it will automatically generate a composite index underneath it. The same goes for UNIQUE constraints and FOREIGN KEYs.

On top of that, under InnoDB, PK also acts as a clustered index.

As for the non-key fields, index based on the expected queries. Assuming your table T has several fields: A, B, C, D (etc.), for a query such as...

SELECT * FROM T WHERE A = '...' AND B = '...'

...create a composite index on {A, B} (or {B, A}), but not on C, D etc.

For the...

SELECT * FROM YOUR_TABLE WHERE A = '...' OR B = '...'

...you'll need a separate indexes on {A} and on {B}.

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...' OR B = '...'

...you'll need indexes on {A, B} and {B} (or {B, A} and {A}).

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...'

...you'll only need an index on {A, B}.

For...

SELECT * FROM T WHERE A = '...' ORDER BY B

...you'll need an index on {A, B}.

Etc, etc...

Do not create indexes that you don't need, since they incur a penalty in storage space and in INSERT/UPDATE/DELETE performance. For more on indexing (and other database performance considerations), I warmly recommend reading: Use The Index, Luke!


What is lock pinger in mongodb Can somebody explain me what is lock pinger in MongoDB

  [LockPinger] cluster XXXXXXX:27019 pinged successfully at Wed Feb  5
  04:46:01 2014 by distributed lock pinger
  'XXXXXX:27019/ip-XX-XX-XX-XX:27017:1391512883:1804289383', sleeping
  for 30000ms

There are distributed locks used in a sharded environment.  The balancer takes a lock (only one migration is active at a time), the shards will take out meta data locks when doing splits also.  Those live in the config.locks collection.

As for the lock pinger, the config.lockpings collection keeps track of the active components in the cluster, so it is an informational collection.  The lock pinger is what populates this collection and you have pasted what looks to be the result of a successful ping.

Note: please do not use these collections for anything in your application or elsewhere.  These collections (as noted on the linked pages) are considered internal only and can (and will) be changed/removed/updated without notice.


Disable MySQL binary logging with log_bin variable Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled:

log_bin = /var/log/mysql/mysql-bin.log

When I want to disable binary logging on such installation, comment out the line in my.cnf works of course, but I wonder if there is a way to disable binary logging by setting explicitely log_bin to OFF, in a debian style, I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf, so default my.cnf is not changed and can be easily updated by apt, if necessary.

I tried "log_bin = 0", "log_bin = OFF" or "log_bin =" but none works...

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here.
The key part of the question is:

I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf

You can do this from an included option file using the option skip-log-bin. E.g. you might create /etc/mysql/conf.d/disable_binary_log.cnf with the following contents:
[mysqld]
skip-log-bin

You can add skip- as a prefix to other options to disable them in this way too.
Related Q & A: To clear currently accumulated logs, see this answer.


Correct Model for Users in a Collection with Items in that Collection Sorry for my vague title, but I didn't really find how to succinctly formulate my issue. I have the following story I want to model:

There are Users that belong to a certain Group. These Users can place Items in that group, and they are 'visible' to all other Users in that Group only.

How I would initially do it: have a table for User, Group and Item. I would then place a UserGroup mapping table to map the Users in their respective Group, and the Item would have a Foreign Key to Group and User. Now I'd like to enforce also that when a User places an Item within a Group, that that user would actually belong to that Group in the first place, as to not have that Items could be placed in a Group by Users who don't belong to that particular Group, and I am having trouble wrapping my head around how to cleanly do this.

Can/should I enforce this on the DB level? If so, what would be an elegant solution to this? I am using SQL Server if this matters.

Edit: after thinking a little more on it, I would also add the following requirements: an Item can only be created by a User, so it shouldn't be able to be spawned into existence on its own. However, upon deletion deletion of a User, the Item should still exist, showing the User has been deleted. On deletion of a Group, the Item shouldn't necessarily 'stay alive'.

I think you are looking to use a composite foreign key from the table that models the items, to the table that models the group relationship, not to the parent users and groups, something along the lines of (pseudo code):

CREATE TABLE Users (UserIdentifier, (other attributes));
CREATE TABLE Groups (GroupIdentifier, (other attributes));
CREATE TABLE Items (ItemIdentifier, (other attributes));

CREATE TABLE Users_Groups (UserIdentifier REFERENCES Users, GroupIdentifier REFERENCES Groups);

CREATE TABLE Itmes_Users_Groups (Item REFERENCES Items, UserIdentifier, GroupIdentifier, (UserIdentifier + GroupIdentifier REFERENCES User_Groups))

HTH


How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity? I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm working with are numpy.arrays of floats with length 100 <= L <= 1000.

I looked into the cube module for similarity search, but it is limited to vectors with <= 100 dimensions. The embeddings I am using will result in vectors that are 100-dimensions minimum and often much higher (depending on settings when training word2vec/doc2vec models).

What is the most efficient way to store large dimensional vectors (numpy float arrays) in Postgres, and perform quick lookup based on cosine similarity (or other vector similarity metrics)?

I'm not aware of any existing modules that will do this for you.  It will be hard to index this in a traditional sense of jumping to a specific and small part of the index which covers all possibly-qualifying rows.

Will the vectors be centered?  Normalized?  Could you get away with representing them as integers with an implicit divisor, rather than true floats?

Your best hope may be to implement an index somewhat like the Bloom extension, in which it always scans the full "index" but does it in a way which is much faster than it would it be to scan the table, both because the index is smaller, and because the data is stored in a way that doesn't have to go through the type abstraction machinery but rather uses the CPUs more directly.


Is it a good idea to move high-volume and highly-accessed tables to a separate database? Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3.  There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the application, have huge volume of records stored compared to other remaining tables and are heavily accessed (both read and write operations) by application Users.

To give a picture/statistics on the volume of records available, currently there are 8 million records in one table and 3 million records in another table.  We're expecting a immediate increase/traffic in the volume of transaction (again both read and write transactions) to these tables in the near future, since we're coming up with some exciting features within this specific module.

My question with requirements are,

because we're expecting a huge traffic to this specific module, we don't want to let down the Users accessing other modules in the application to be affected because of any performance issues that it may cause, though.
separating/isolating heavily accessed tables is one solution that came off the top of my head.  Is it a good idea to separate/move to a different database? What are the pros and cons of this approach?
any solution, comment, approach, suggestion are welcome and appreciated.

You will gain a lot more with regards to performance if you move the table to a different harddisk. As long as the "busy" table and the rest are located on the same disk, moving that table into a different "file" (by moving it into a different database) won't change anything with regards to (I/O) performance.

Distributing the I/O load to a different harddisk (and a different harddisk controller) will most probably give you a better performance for the remaining data because it is not affected by the I/O done on the busy table.

To move the table to a different harddisk, you need to create a new tablespace (which of course is located on that disk) and then move that table to that tablespace.


Self-JOIN with occurrence count I use PostgreSQL 9.4 and I have a table called certificates containing the following columns:

CREATE TABLE certificates (
    id serial PRIMARY KEY,
    common_name varchar(255) NOT NULL,
    state varchar(255),
    expires_on date,
);

CREATE INDEX index_certificates_on_common_name ON certificates (common_name);
CREATE INDEX index_certificates_on_expires_on ON certificates (expires_on);

I'd like to fetch all the certificates that have not been renewed yet, which means all the certificates that

don't have another certificate matching the common_name OR
have another certificate matching common_name, but the state is not issued or expires_on is past.

I created the following query. It works, but I wonder if it can be optimized to be more efficient.

SELECT
    C.*
FROM
    certificates as C
WHERE
    C.expires_on - 30 <= 'date-of-today' AND
    (
        SELECT COUNT(id)
        FROM
            certificates AS N
        WHERE
            certificates.common_name = N.common_name AND
            N.expires_on > certificates.expires_on
            AND N.state = 'issued'
    ) = 0;

Specifically, my concern is the nested query that I believe is run once for each record in C.

I tried by self-joining the table but the resulting query may return duplicates (e.g. if a certificate has 2 or more renewals) and I feel like using DISTINCT or GROUP BY may not be the best option.

SELECT C.id, N.id
FROM
    certificates AS C LEFT JOIN certificates AS N ON C.common_name = N.common_name
WHERE
    (C.expires_on - 30 <= 'date-of-today') AND
    (N.id IS NULL OR (N.state = 'issued' AND N.expires_on > C.expires_on));

Any suggestion?

Two major improvements:

SELECT *
FROM   certificates c
WHERE  c.expires_on <= current_date + 30  -- sargable!
AND    NOT EXISTS (
   SELECT 1
   FROM   certificates
   WHERE  common_name = c.common_name
   AND    expires_on  > c.expires_on
   AND    state = 'issued'
   );

Make the first predicate sargable, so that an index can be used.
You second condition is that no qualifying row exists. So don't count, that would be expensive because Postgres has to scan all rows unconditionally. Use a NOT EXISTS anti-semi-join. Postgres can use an index again to quickly verify non-existence and can stop looking further as soon as the first qualifying row has been found. Typically much faster (depending on data distribution). There are several related techniques possible for this:

Select rows which are not present in other table

Ideally, you have at least two indexes:

on (expires_on):

CREATE INDEX ON certificates (expires_on);

A partial multicolumn index.

CREATE INDEX foo ON certificates (common_name, expires_on DESC)
WHERE  state = 'issued';

If state = 'issued' is true for most rows, then adding the condition to the index won't help.

Answer to additional question in comment

If the number of days for tolerance is variable and stored as integer with each row, use a functional index:

CREATE INDEX ON certificates ((expires_on + expiring_days));

And adapt the query accordingly:

SELECT *
FROM   certificates c
WHERE  expires_on + expiring_days <= current_date  -- can use index
AND    NOT EXISTS (
   SELECT 1
   FROM   certificates
   WHERE  common_name = c.common_name
   AND    expires_on  > c.expires_on
   AND    state = 'issued'
   );

Aside: A column like state typically only allows a couple of distinct values. If so, it's a candidate for normalization: add a table certificate_state listing allowed values and use state_id int REFERENCES state in the main table. Just like outlined here:

Low cardinality fields: integer or string


Is innodb_log_file_size not relevant if you are not using transactions in MySQL? I am trying to understand the variables:

innodb_log_file_size
innodb_flush_log_at_trx_commit

As I understand the innodb_log_file_size is for undo and redo purposes for when cancelling a transaction and for when server crashes. Does this mean that I can kind of ignore this if I don't plan on writing queries of the form:

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 98;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 42;
COMMIT;

I also heard somewhere that

  Every INSERT, UPDATE, DELETE is a transaction if autocommit=1.

What does this mean?

If I do zero transactions, will I gain any performance by setting innodb_flush_log_at_trx_commit=0? Since I won't need to sync after every transaction commit. But again I heard on youtube that if autocommit=1, then every insert, update, and delete "is a transaction".

I am confused.

If you are not in a transaction (i.e. if autocommit is 1 and you didn't BEGIN a new transaction), then every query you run implicitly starts its own transaction, and the transaction is committed or rolled back (depending on circumstances, such as when certain errors occur) immediately when the query finishes executing.

In other words, all DML is done in a transaction... it's only a matter of whether it's implicit or explicit.  In InnoDB, everything (except DDL of course) is a transaction.

So, whether you control transactions yourself or not doesn't impact the range of appropriate settings, here.  Like almost all other variables, you should leave innodb_log_file_size at the default value unless you have a specific and valid reason to change it and understand the rationale for the change... and note that the suggestions output by tuning scripts do not constitute a valid reason to change anything. As a rule, stay away from those.

innodb_flush_log_at_trx_commit controls strict ACID compliance, and should be set to 1 if the database is critical (typical setting for master), 2 if it's important (typical setting for replicas) or 0 if you don't care all that much.  It's a little more complex than that, but safest to less safe, the order is 1, 2, 0.  Fastest to slowest is generally the opposite, 0, 2, 1.  2 is a relaxed version of 1 but the difference between 1 and 2 is typically more significant than the difference between 2 and 0.  The impact of this variable is also fundamentally the same whether your transactions are explicit or implicit because, again, everything in InnoDB is a transaction.


How mysqldump replaces special characters during dumping records? i am trying to dump a particular row using java.

So that i retrieve the data by using record sets and i manually written sql script code by appending the dynamic records.

But my problem is i could not write the script for blob data fields.

Because there are some special characters are present inside blob type fields.

i need to know how mysqldump.exe is replacing special characters ?

If i know the detail means i can replace those characters as same as mysqldump process.

There are two ways: string and hexadecimal notation, e.g.:

'abc\' as string will be like this -> 'abc\\', all special characters are escaped
'abc\' as hexadecimal will be like this -> 0x6162635C, you can test it using HEX function - SELECT HEX('abc\\');

You can choose one of those methods. Also, try to play with --hex-blob option and check results.


Changes to main Database during Transactional Replication Can you please advise how can I keep slave database up to date while it's in transactional replication with master.

I mean what If I change indexes, columns, tables or sthm else in master dabase? What should I do in this case?

Should I make this changes to both databases simultaneously or transactional article is enough to handle it?

By default, replication will push out the following DDL changes to subscribers:

ALTER TABLE
ALTER VIEW
ALTER PROCEDURE
ALTER FUNCTION
ALTER TRIGGER

And by default, I mean unless you have specified otherwise when you made a call to sp_addpublication when setting up the publication (even if done through the GUI, this is the underlying stored procedure that is called).  There is a parameter for that stored procedure, @replicate_ddl, that is set to 1 by default, meaning that the above DDL commands will replicate to subscribers for the articles in the publication.

When you made your initial call to sp_addarticle, you would have specified (again, even if through the GUI where all the article options are set) which indexes (clustered and nonclustered) were generated for subscribers to create for initialization as well.

Reference :: How to: Replicate Schema Changes


MySQL / MariaDB granular row size Is it possible in MySQL / MariaDB to calculate row size like in PostgreSQL ?
I'm looking to calculate in a multi tenant application how much space did the user use for the database but I couldn't find anything relevant about this.

In phpmyadmin you see the size of every table at the end . after you select a database.

if you want to do this in a query do, you need only the put in your own database name instead of testdb

SELECT SUM(Table_Size_MB) 'database_size_MB'
    FROM
        (SELECT table_name AS "Table",
        ROUND(((data_length + index_length + DATA_FREE) / 1024 / 1024), 2) AS "Table_Size_MB"
        FROM information_schema.TABLES
        WHERE table_schema = "testdb"
        ORDER BY (data_length + index_length) DESC) tablessizes;


Select unique records from multiple records based on max date I have table like below

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  1 | PHYSICAL | Y   | 10-Oct      |   8403 |
|  1 | BILL     | Y   | 6-Oct       |    135 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 8-Oct       |   3673 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  57923 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

need to check below multiple conditions before populating the data

If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).

Below was the expected result.

Can anyone plug me some ideas to achieve this.

here is the sqlfiddle:  http://sqlfiddle.com/#!4/d0098/1

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

For the first condition

count() Over (Partition by id, addr, Ind)
max(update_date) Over (Partition by id, addr, Ind)

For the secontd condition

row_number Over (Partition by id, addr, Ind and update_Date)


Converting HSTORE to an EAV in a view using "each" or "lateral" I have a table with ~100M rows, all of which contain HSTORE fields.  I am creating a view that will return (convert) the hstore column to key | value columns and also add a few fields for filtering.

id | hstore                         id  | user_key | user_value
--------------------------          --------------------------------
11 | "a" => "2", "b" => "z"   =>    10  | a        | 2
                               >    10  | b        | z

This view will be used by an export tool which knows sql, but doesn't grok hstore or json.

There are two reasonably nice solutions I have found, both using each, but I am curious if there are any drawbacks to one approach over the other, especially considering the desire to remove empty rows.

$1) using each

To filter rows without user data I would need to compare the underlying hstore to empty (user_data != '' :: hstore).

CREATE VIEW v1 AS
SELECT
    t.id,
    t.filterable_column,
    s.owner,
    (each(t.user_data)).key,
    (each(t.user_data)).value
FROM
    public.hugetable AS t
        LEFT JOIN public.smalltable s ON s.id = t.s_id

$2) using lateral left join

In this case I can use an INNER join on the lateral to remove empty rows (rows with no user data).  Seems cleaner.

CREATE VIEW v2 AS
SELECT
    t.id,
    t.filterable_column,
    s.owner,
    u.key,
    u.value
FROM
    public.hugetable AS t
        LEFT JOIN LATERAL each(t.user_data) AS u ON TRUE
        LEFT JOIN public.smalltable s ON s.id = t.s_id

I can do explains on them, but I am not sure how to interepret since the difference seems small.  Perhaps they are the same, essentially, or one is fundamentally worse than the other (more work, loops, or inefficent).  I've no familiarity with each or lateral joins, but am reading up as I can.

Experimental approach

Tested on PostgreSQL 9.6.2, with out of the box configuration, on Mac OS X 10.12, with SSD as storage.

We create the small and huge tables (I call them s and t, for simplicity).

CREATE TABLE s
(
    s_id integer primary key,
    owner text
) ;

CREATE TABLE t
(
   t_id integer primary key,
   s_id integer REFERENCES s(s_id) ON UPDATE CASCADE ON DELETE RESTRICT,
   filterable_column integer,
   user_data hstore
) ;

I've assumed that t_id and s_id where primary keys of the respective tables (t and s) and that s_id references s(s_id). This setup creates proper indices.

We populate the tables with some data (2 M rows on t, to make timings a bit more reasonable; I've tested also with 1 M rows, comparisons continue to hold).

-- 19999 rows into s
INSERT INTO
    s
    (s_id, owner)
SELECT
    s_id, 'owner' || s_id AS owner
FROM
    generate_series(1, 19999) s0(s_id) ;

-- 2 M rows into t
INSERT INTO
    t
    (t_id, s_id, filterable_column, user_data)
SELECT
    t_id,
    (random()*19998+1)::integer AS s_id,
    (random()*19998+1)::integer AS filterable_column,
    ( 'a => ' || (random()*99+1)::integer ||
     ',b => ' || (random()*99+1)::integer)::hstore AS user_data
FROM
    generate_series(1, 2e6) AS s0(t_id) ;

We create now the two views:

CREATE VIEW v1 AS
SELECT
    t.t_id,
    t.filterable_column,
    s.owner,
    (each(t.user_data)).key,
    (each(t.user_data)).value
FROM
    t
    LEFT JOIN s ON s.s_id = t.s_id ;

CREATE VIEW v2 AS
SELECT
    t.t_id,
    t.filterable_column,
    s.owner,
    u.key,
    u.value
FROM
    t
    LEFT JOIN LATERAL each(t.user_data) AS u ON TRUE
    LEFT JOIN  s ON s.s_id = t.s_id ;

We perform a sanity check to make sure v1 and v2 give actually the same results:

-- Sanity check: are both views the same?
-- Check that the views are the same...
SELECT
    (SELECT count(*) FROM
      (
        SELECT * FROM v1
        EXCEPT
        SELECT * FROM v2
      ) AS s0
    ) = 0
    AND
    (SELECT count(*) FROM
      (
        SELECT * FROM v2
        EXCEPT
        SELECT * FROM v1
      ) AS s0
    ) = 0   AS v1_equals_v2 ;
-- Response: yes, they are

At this point, we compare how long does it take to read both views:

EXPLAIN ANALYZE
SELECT * FROM v1 ;

EXPLAIN ANALYZE
SELECT * FROM v2 ;

The execution plans and timings are this for v1, and this for v2. The timing for v1 is 18,857 s, and for v2 13,697. The ratio is, approximately 10:7.  v2 performs better when you need to check all the data.

When you need to check for a range of values (for t_id):

EXPLAIN ANALYZE
SELECT * FROM v1
WHERE t_id BETWEEN 200000 and 210000 ;

You get this plan (v1), and this plan (v2). Timings are: 125 ms and 89 ms. Again, about a 10:7 ratio.

Conclusion

So, from a purely experimental point of view, I would use v2, it is about a 30% faster.

You can check all the tests (with less data) at rextester. (The difference in timings is much less when the number of data points is reduced.)


Clustered index scan in query with non clustered index filter I have an Operations table with a nonclustered index on my foreign key to a Organizations table; however, when trying to run the following query, SQL Server does a clustered index scan instead of using my non clustered index:

SELECT OpType, UserId, OpTime
FROM Operations
WHERE OrganizationId = 'some uniqueindetifier'

I tried doing a join or using in instead and the result was the same. If I change the query to SELECT OrganizationId FROM... then I actually get my intended nonclustered index seek, but naturally returning only this column would be pointless and adding any other column goes back to a clustered scan.

There's dozens of millions of rows in the table but there should be around only 50 000 rows with the OrganizationId I'm looking for, so I'm sure that a query plan that uses the non clustered index would be faster.

I executed the same query for a different OrganizationId (that returned about 30 000 rows) and got the result in less than 2 minutes, while doing this query for the OrganizationId that does a clustered scan didn't finish in 8 minutes even though it should return around 50 000.

I don't have any columns included in the Organization index but I don't want to double the size of this fairly large table by adding all columns to the index as there's a lot of rows getting inserted and I only want to extract all columns from it once a month for a report.

So aside from including columns to the index, what else can I do to make SQL Server use my non clustered index?

One way to coax the optimizer to use narrow nonclustered indexes is to use a CTE:

WITH fix_me AS (
SELECT OrganizationId
FROM Operations
WHERE OrganizationId = 'some uniqueindetifier'
)
SELECT *
FROM Operations AS o
JOIN fix_me AS f
ON f.OrganizationId = o.OrganizationId

I'm guessing that your nonclustered index isn't covering, meaning the optimizer has a choice between doing a Key Lookup, or scanning the Clustered Index.

Using a CTE in this way can help guide the optimizer to use a narrow index to get only the necessary rows, but may also be ignored.

Since you gave an incomplete picture of your query and table design, feel free to adjust that to fit your needs.

I talk about this technique more in this presentation:

Improving SELECT * Query Performance


Weird tables with numbers and underscores in PostgreSQL execution plans I'm debugging performance problems with my DB and see some unusual entries in the DB log:
2021-04-21 01:07:33.857 UTC [14823] user@db LOG:  duration: 933645.469 ms  plan:
Query Text: select systemv0_.id as id1_11_0_, systemv0_.createdTs as createdT2_11_0_, systemv0_.systemType as syste3_11_0_, systemv0_.foundProblemsCount as foundPro4_11_0_, systemv0_.groupid as groupid8_11_0_,... (rest omitted for brevity)

What are these strange identifiers with numbers and underscores? Normally an execution plans has clear names and identifiers such as systemview.id.
My hypothesis is that there are several concurrent queries and the performance issues are due to the DB desperately trying to remain consistent under low memory conditions. I noticed a couple suspicious things in other queries - several Recheck Cond and Materialize tree nodes, which appear to be often happening due to insufficient size of some DB buffers (caused by low host memory?). It would be nice if someone could confirm or debunk my theory.

Those are not the table names, but aliases. You can use them to make a query more readable:
SELECT some.col1, another.col2
FROM some_really_long_table_name AS some
   JOIN another_terrible_table_name AS another
      USING (col3)
WHERE some.col4 = 42;

But of course you can also abuse aliases to obfuscate and inflate a query, as some object-relational mappers do.


Modelling two dependent many-to-many relationships I have a user who may have 0 or more foo's and bar's. Here is a diagram:

Each foo has a unique integer ord.

If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.

My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:

Query: Check if bar relationship exists. If it does, get foo with ord=1

Query: If no bar bar relationship exists, get foo with smallest ord column.

Is there maybe a more convenient database structure for this scenario?

Schematically:

CREATE TABLE users (id,
                    name,
                    PK (id));

CREATE TABLE user_foo (user_id,
                       foo_id,
                       PK (user_id, foo_id),
                       FK (user_id) REF users (id),
                       FK (foo_id) REF foo (id));

CREATE TABLE foo (id,
                  name,
                  ord,
                  PK (id),
                  KEY (id, ord));

CREATE TABLE foo_bar (foo_id,
                      ord AS (1) VIRTUAL,
                      bar_id,
                      PK (bar_id, foo_id),
                      FK (foo_id, ord) REF foo (id, ord),
                      FK (bar_id) REF bar (id));

CREATE TABLE bar (id,
                  name,
                  PK (id));

  One foo may also have many users so the pivot tables are necessary.

Taken into account.

  foo and bar are ManyToMany relations

Taken into account.


load_file() producing null I'm trying to read the content of /etc/passwd from mysql using the following query: select load_file("/etc/passwd");
However, I always seem to get NULL as the output.

mysql> select load_file("/etc/passwd");
+--------------------------+
| load_file("/etc/passwd") |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.00 sec)

Is there a reason for this? the permission for the passwd file is the default permission.

If the SELECT works for you, then it will work for a hacker.

Think twice about breaking through this security barrier.