Index being ignored by Oracle I have a table for sensor data, simplified it looks like this:

DEVICE_ID NUMBER
SENSOR_ID NUMBER
DATA_TIME NUMBER
DATA_VALUE1 NUMBER

The table constains billions of rows and is partitioned by week number, DATA_TIME is in Unix time (milliseconds since 1970). I have two indexes on this table, IX1 on (DATA_TIME DESC) and IX2 on (DEVICE_ID,SENSOR_ID,DATA_TIME). IX2 was created yesterday.

My query wants to see the data for one sensor in the current month and goes like this:

SELECT /*+ PARALLEL (12) */
   data_time,
   data_value1
FROM table
WHERE device_id = 1041
  AND sensor_id = 202
  AND data_time BETWEEN 1383304859 *1000 AND 1385464859 *1000
ORDER BY data_time ASC

I was surprised to find that the optimizer ignores IX2 and comes up with the following plan:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                         |                              |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)                    | :TQ10001                     |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                        |                              |    65 |  1365 |     5  (20)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                          |                              |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE                      | :TQ10000                     |    65 |  1365 |     4   (0)| 00:00:01 |       |       |  Q1,00 | P->P | RANGE      |
|   6 |       PX PARTITION RANGE ITERATOR       |                              |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWC |            |
|*  7 |        TABLE ACCESS BY LOCAL INDEX ROWID| table                        |    65 |  1365 |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
|*  8 |         INDEX RANGE SCAN                | ix1                          |     1 |       |     4   (0)| 00:00:01 |   252 |   256 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("DEVICE_ID"=1041 AND "SENSOR_ID"=202)
   8 - access(SYS_OP_DESCEND("DATA_TIME")>=HEXTORAW('38FDD8C8BEA9A4FF')  AND SYS_OP_DESCEND("DATA_TIME")<=HEXTORAW('38FDD8DDFAA9A4FF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))>=1383304859000 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("DATA_TIME"))<=1385464859000)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

The statistics on the table and the indexes are recent. I found some SQL Baselines that were created for this statement and removed them.
And on top of that, the SQL tuning advisor recommended creating an index on (DEVICE_ID,SENSOR_ID,DATA_TIME,DATA_VALUE1).

I resolved the problem by dropping and recreating IX1. (The reasoning behind that was to invalidate any cached plan still using it.) The query now returns 420 rows after 11 seconds. If anybody can give me a good theory on what happened I will accept it as a correct answer.


Query for cross matching multiple joined tables I have four tables, such that:

CREATE TEMP TABLE country (
  countryid serial PRIMARY KEY,
  name text
);
CREATE TEMP TABLE city (
  cityid serial PRIMARY KEY,
  name text,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
  userid int,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
  userid int,
  cityid int REFERENCES city
);

I'm trying to write a query which will match users up based on their user_country and user_city entries, so that any one hit will produce a match if,

a user has any of the same user_country or user_city entries as another user.
given the relationship between city and country; if one user has an entry in the user_country table and another user has an entry in the user_city table with a city that is in the first users_country, this will also produce a match.

For example, given the following example data and the user id 1, the query will return the user ids 2, 3, 4: 2 is matched in the user_country table (both users have an entry containing the country_id 1) and user ids 3 and 4 are matched because their entries in the user_city table relate to London which has a country_id which matches user id 1's user_country entry. (In laymans terms, user 1 wants to go anywhere in England, so does user 2, producing a match; users 3 and 4 are more specific but because both London and Leeds are in England they both produce matches)

Given the same data and the user id 3, the query will return the user id 1 and 2, as the country_id in the city table relates to the country that both users 1 and 2 have selected, but not user 4. (in laymans terms, user 3 wants to go to London; users 1 and 2 want to go anywhere in England, so they match.  However user 4 only wants to go to Leeds, not London, so doesn't match)

INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',2);

INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2);

Can anyone help me with a single performant SQL query, that given a user id, returns a list of user ids which match the above scenarios?

Note: I'm using a recent version of postgres

EDIT
To clarify a few points: a user can have an entry in the user_city table without a corresponding entry in the user_country table for the country matching the city.  This is because a user may have an interest in visiting a city, but not want to see the rest of the country.  Being able to select a country is a shorthand way of selecting every single city in that country.
In layman's terms: just because a user wants to visit London, they don't necessarily want to visit all of the UK. So a user can have a user_city entry for London, but not have a user_country entry for the UK; also worth noting that if a user has a user_country entry for the UK, they won't have any user_city entries for any cities in that country (user wants to visit all of the UK, so doesn't make sense to list London too).

Separately a city will always be in a country, so will always have a country_id

EDIT 2
A bit more example data to replace the data above (not in addition to!)

INSERT INTO country (name) VALUES ('Germany'),('Spain'),('Italy');
INSERT INTO city (name,countryid) VALUES ('Berlin',1),('Madrid',2),('Rome',3),('Venice',3);

INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(5,3)
INSERT INTO user_city (userid, cityid) VALUES (3,2),(4,2),(6,3),(7,4)

Testing a match only on matching user_country table entries:

Entering user id 1 returns user id 2
Entering user id 2 returns user id 1

Testing a match only on matching user_city table entries:

Entering user id 3 returns user id 4
Entering user id 4 returns user id 3

Testing that a user_country matches all entries with related cities:

Entering user id 5 returns user ids 6 & 7

Testing that a user_city entry picks up a user_country entry:

Entering user id 6 returns user id 5
Entering user id 7 returns user id 5

Sample Data

This is actually very easy, first let's create your schema. Next time around, it's better and easier for everyone if you provide data like this.

CREATE TEMP TABLE country (
  countryid serial PRIMARY KEY,
  name text
);
CREATE TEMP TABLE city (
  cityid serial PRIMARY KEY,
  name text,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
  userid int,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
  userid int,
  cityid int REFERENCES city
);
INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',1);
INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(4,2);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2),(2,3);

Solution

SELECT DISTINCT
  user_city_country.userid AS user1,
  user_country.userid AS user2
FROM user_country
JOIN country USING (countryid)
JOIN (
  SELECT *
  FROM user_city
  JOIN city USING (cityid)
  JOIN country USING (countryid)
) AS user_city_country
  USING (countryid);

This breaks down easy... Path one is to resolve the user to the country..

SELECT DISTINCT user_country.userid
FROM user_country
JOIN country USING (countryid)

Path 2 is to resolve the user_city to the country.

SELECT *
FROM user_city
JOIN city USING (cityid)
JOIN country USING (countryid)

Then we just JOIN them. Note, we're not actually using the country at all. You have it in your schema. So I preserved it here.

This returns...

 user1 | user2
-------+-------
     4 |     2
     3 |     1
     2 |     1
     3 |     2
     4 |     1
     2 |     2

Now, if you want to know all matches, just add WHERE user_country.userid = '1' OR user_city_country.userid = '1';

 user1 | user2
-------+-------
     4 |     1
     3 |     1
     2 |     1


What MySQL data types REQUIRE a max length (size) declaration in a create table statement? I am converting a SQL Server table structure to a MySQL table structure, and I need to know how to properly declare the column data types within the CREATE TABLE statement.

I have a CSV file where each row gives me column info like so:

columnName,sqlServDataType,maxLength,precision,scale,isNullable,isPrimaryKey
idYear,int,4,10,0,0,1
...

I have a file / information on how I will programmatically map the SQL Server data type to the MySQL data type, but I need to know what MySQL data types require the max length declaration in a CREATE TABLE statement.

So for instance, in MySQL you can declare columnName INT or columnName INT(4) though there's really no point, from what I've read. However, doing something like VARCHAR(40) is important for both performance and data insertion concerns. Make a column too big for small data, it's inefficient. Make a column's max length too small for a given chunk of data, and the insertion will fail.

Ultimately for a given MySQL data type, I need to know if the max length e.g. VARCHAR(40) is required or not. I couldn't find much in the documentation.

Any pointers?

MySQL Perspective

@DerickDowney discussed this in his post INT(5) vs SMALLINT(5). He quoted from the MySQL Docs:

  The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

Given this statement, if you create a table with a column type INT it will appear as INT(11). Why 11 ? INT has a range of -2147483648 and 2147483647. It takes 11 spaces to display the smallest signed INT -2147483648. Nothing more, nothing less.

Character data is entirely different story. Defining a VARCHAR requires the table header knowing the maximum length in advance. Rather than go into a lengthy explanation, please read the following posts:

Fine Tuning The Table Definition (Percona)
InnoDB Physical Row Structure (MySQL Documentation)

From the information in these posts, I would confidently say that you have no choice but to define a length for a VARCHAR. If you not sure, just define all VARCHARs as VARCHAR(255). No wasted is there. Alter loading data you could always run the following

SELECT varcharcolumnname FROM yourtable PROCEDURE ANALYSE();

and let it tell you the proper VARCHAR size.


I need to insert data from a column from another table with different data type and the value is based on a lookup table I need to import data taken from table_1.column_1 (BOND.MOODYS_RATING) to table_2.column_2 (LCDOffering.KeyValidCreditRatingsMoody)

WHERE:

MOODYS_RATING data type is nvarchar(10) and KeyValidCreditRatingsMoody data type is int(4)
Each value in the column MOODYS_RATING represent a lookup value, being KeyValidCreditRatingsMoody the key for that lookup value
KeyValidCreditRatingsMoody values are equal to KeyValidCreditRatings, taken from  the table ValidCreditRatings, while MOODYS_RATING values are equal to the field CreditRating, also from the table ValidCreditRatings, subject to certain restrictions, which can be summarized in this query:

select * from Lookup..ValidCreditRatings
where KeyInstnRatingAgency = 108462
and KeyValidCreditRatings <=21 order by keyvalidcreditratings

Can you please help me build an insert statement that can fulfill all of these requirements?

You are not explaining very well what you mean by import (whether it is an insert or an update or a little bit of both), so it is hard to suggest a close enough prototype query for you to use directly or with minimal tweaking. However, the key issue, as I understand it, is to convert each nvarchar(10) value of BOND.MOODYS_RATING to a corresponding int key, looking up each string in ValidCreditRatings.CreditRating and taking the key from ValidCreditRatings.KeyValidCreditRatings.

That operation takes a simple inner join:

SELECT
  b.*,
  vcr.KeyValidCreditRatings
FROM
  BOND AS b
  INNER JOIN Lookup..ValidCreditRatings AS vcr
    ON b.MOODYS_RATING = vcr.CreditRating
WHERE
  vcr.KeyInstnRatingAgency = 108462
  AND vcr.KeyValidCreditRatings <= 21
;

The KeyValidCreditRatings column in the output of this query will contain the values that you can write to LCDOffering.KeyValidCreditRatingsMoody.

A join can be used both in an UPDATE statement and in an INSERT statement (the INSERT ... SELECT variation). Use the join in the above example query as a basis for the actual query that will be importing data, adding other filters and/or joins as necessary and putting the vcr.KeyValidCreditRatings reference where the source for LCDOffering.KeyValidCreditRatingsMoody should go. By the latter I mean, if for instance, yours is an UPDATE statement, then you will probably have in the SET clause an assignment like this:

UPDATE
  LCDOffering
SET
  ...
  KeyValidCreditRatingsMoody = vcr.KeyValidCreditRatings,
  ...
FROM
  ...

And if it is an INSERT ... SELECT statement, then you will have vcr.KeyValidCreditRatings in the select list at the position corresponding to the KeyValidCreditRatingsMoody column's position in the target column list:

INSERT INTO
  LCDOffering (..., KeyValidCreditRatingsMoody, ...)
SELECT
  ..., vcr.KeyValidCreditRatings, ...
FROM
  ...


InnoDB Tablespace critical error in great need of a fix Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG

Question: Could the error shown on the screenshot be the reason why my site is very slow?

Your free space in the current innodb file (ibdata[1] ?) is decreasing.
It doesn't necessarily mean that your website is slow.

Maybe the number of users (concurrent users) has increased in the last few days ? (also causing lots of inserts int he DB?)

There is no error in the print screen. it's just your monitoring system, reporting a value that have reached the critical threshold.


Install SQL Server 2012 missing vs_Setup.msi from Visual Studio 2010 I am trying to install SQL Server 2012 Enterprise with SQL Server Data Tools included.  I understand that SQL Server Data Tools is part of what was formerly known as BIDS.  The installation for SQL Server 2012 prompts me for the ms_setup.msi installation file from Visual Studio 2010.  However, I am using Visual Studio 2012 currently only.  The equivalent file in VS 2012 is called vs_professionalcore.msi and is located in Visual_Studio2012_Pro\packages\professionalcore\Setup.

How can I install SQL Server 2012 with Data Tools having only Visual Studio 2012 given the setup?  Is there a download or registry entry I need to make? Thanks for any pointers.

In my case, I had Visual Studio 2010 Enterprise on my system when I initiated the setup for SQL Server 2012 Standard. After the SQL 2012 got underway, it stopped and prompted for the VS 2010 setup.msi. At that point, I ejected the SQL Server disc and inserted the VS 2010 setup disc. Then I used the dialog presented by the SQL Server 2012 setup program to browse to the root of the VS 2010 DVD, selected the msi there, and opened it. The SQL 2012 setup continued and when I saw that it was again installing SQL components (rather than VS 2010 components), I ejected the VS 2010 disc (which caused the SQL 2012 setup program to display an error that there was no disc in the drive), placed the SQL 2012 setup disc back into the drive, then clicked the Try Again button. At that point, SQL Server 2012 resumed and after some time it completed successfully.


Does the target for Double-Take replication require an Oracle license? Where would Vision Solution's Double-Take product fall with regard to Oracle Licensing?  It is both a backup and a replication of the database.  Oracle's Software Investment Guide has a section on Backup, Standby, Failover, and Remote Mirroring.  My concern is that this product would fall under remote mirroring and the server would need to be licensed.

We had a conference call with our Oracle Technology Sales Manager and an Oracle Solution Consultant.  We discussed the Double-Take setup and they were able to confirm that based on the way we have our DR site setup and our primary site licensed, we do NOT need to license the DR site.  This is of course assuming we do not deviate from the setup we discussed with them.  This somewhat surprised me and should in no way be looked at as an indicator Oracle's stance on Double-Take for any other installation than ours.


T SQL Cte Previous record calcuation I'm using SQL Server 2016.

I have the below table:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         NULL
111     200         1            2           2           NULL       NULL
111     300         1            3           0           NULL       NULL
111     400         1            4           0           NULL       NULL

222     100         2            1           5          17          NULL
222     200         2            2           5          NULL        NULL
222     300         2            3           5          NULL        NULL
222     400         2            4           5          NULL        NULL

This is the desired result:

SKU     Shop      Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2          NULL        17
111     300         1            3           0          NULL        15
111     400         1            4           0          NULL        15

222     100         2            1           20           17        17
222     200         2            2           15          NULL       12
222     300         2            3           12          NULL        7
222     400         2            4           10          NULL        2

I need to update the 'Open_Stk Calc' based on the previous row:

'Open_Stk Calc' - IIF('Replen'<=IIF('Open_Stk'>=0,'Open_Stk',0),'Replen',0)

I am using a CTE to update a row based on a calculation of the previous rows.  This is my SQL:

    ;WITH CTE AS
    (
        SELECT
        SKU,
        [Shop],
        [Week],
        [Store_Priority],
        [Replen],
        [Open_Stk],
        [Open_Stk Calc],
        FIRST_VALUE([Open_Stk]) OVER ( PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS UNBOUNDED PRECEDING)
        -
ISNULL(SUM(IIF([Replen] <= IIF([Open_Stk]>=0,[Open_Stk],0),[Replen],0))
        OVER (PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS CurrentStock
    FROM [tblTEST])

    UPDATE CTE
    SET [Open_Stk Calc] = CurrentStock

However, this produces the following result:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2           NULL       17
111     300         1            3           0           NULL       17
111     400         1            4           0           NULL       17

And not the desired result - where have I gone wrong?

Test something like:

Open_Stk Calc = SUM(COALESCE(Open_Stk + Replen, 0)) OVER (PARTITION BY sku ORDER BY ShopPrioirty)

Or divide to two SUMs to avoid COALESCE() usage. Edit PARTITION expression if needed. - akina


Comparing SQL binary data types I am curious if there are any major differences with performance and/or conventionality regarding which binary data types one chooses. Let's say I have a table of metallic objects and I am considering to create a binary field to record whether or not the object is reflective.

reflective BIT NOT NULL DEFAULT 0 -- 0 for nonreflective 1 for reflective
reflective CHAR(1) NOT NULL DEFAULT 'F' -- False for nonreflective, True for reflective
reflective ENUM('N','R') NOT NULL DEFAULT 'N' -- N for nonreflective R for reflective.
reflective ENUM('nonreflective','reflective') DEFAULT 'nonreflective' -- I think this takes up the same amount of storage resources but is clearer

Question: ENUM aside, they all seem more or less equal in terms of readability,  and while I'm not exactly sure which would make for most efficient scaling and querying, I'd venture they would all be comparable. Am I right in this assumption; is the binary data structure largely arbitrary, or are there trade-offs I should be aware of?

Also I'd be interested to know if among these examples (or structures I did not include) there is a clear convention, and why.

No matter what you choose, you should pick a convention and stick to it.

Here are my recommendations

BOOL/BOOLEAN

If the data values are truly binary, use the BOOL/BOOLEAN data type.

CHAR/VARCHAR

YES/NO works well when you have lazy UI developers that want to show the result with out translating it.  The single character value ( Y/N ) works well also.

The downside: Multinational applications will still have to translate the text.  (eg はい/いいえ )

NUMBER(1)

For databases that do not support bool/boolean I recommend using a NUMBER(1).  This way, you can tell the UI developers that they will need to translate it into to the proper language.  Make sure you document what 1 and 0 mean.

enum

I do like the idea of using an enum to store the values.  This would allow you to store a greater range (eg somewhatReflective, mostlyReflective)

Proper datatype

I'm assuming the question is about data types whose values can be represented as binary.  (eg true/false)  This section is here to remind readers:  Always pick the proper data type for your column (eg DATE for dates, etc.)

The albedo of an object (relfectiveness) is measured as a dimensionless NUMBER whose value is BETWEEN 0 AND 1.  I would not use a BOOLEAN data type for the reflective column.


How to retrieve Oracle Key Performance Indicators in 5 minute intervals within a time range I have Oracle 11g attached to a Performance Test environment. A performance test runs for two hours. After the run I want to programmatically extract a variety of in key indicators from the database to store with the results.

What commands would I run before and after the test to collect key indicators, ideally grouped in 5 minute totals.

CPU
Memory
Total SQL requests
Logical IO
Physical IO

And bottleneck indicators, (various waits for locks and latches).

I will accept a handy link to an article or a collection of useful SQL commands to explore.

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page which requires the diagnostics package. There is a certain irony to running this script through SQL Server reporting services which is beyond the scope of this question.

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))
ORDER BY 1

This query is not as good as what Grid displays but it's close.


How can I stop SQL from validating an OPENROWSET query when its not going to be executed? I have a problem where a stored procedure is throwing an error because it's trying to run/validate an OPENROWSET query inside it that it doesn't actually have to execute.

The stored procedure that looks something like this:

IF (@flagA = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT A, B, C
    FROM (
        SELECT *
        FROM OPENROWSET(
            'Microsoft.ACE.OLEDB.12.0',
            'Text; HDR=Yes; Database=\\server\tmpFiles',
            'SELECT * FROM FileA.txt')
    ) as T
END

IF (@flagB = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT D, E, F
    FROM (
        SELECT *
        FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
        'Excel 8.0;Database=\\server\tmpFiles\FileB.xls',
        'SELECT * FROM [Sheet$]')
    ) as T
END

IF (@flagC = 1)
BEGIN
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT G, H, I
    FROM (
        SELECT *
        FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
        'Excel 8.0;Database=\\server\tmpFiles\FileC.xls',
        'SELECT * FROM [Sheet1$]')
    ) as T
END

Note that each OPENROWSET references a different file, in a different format, with different column names.

My problem is that if one of the files doesn't exist, or exists in the wrong format (such as someone uploading a FileB as FileC by mistake), I get an error when the procedure tries to run, even though that OPENROWSET query is never meant to get executed.

For example, if FileB doesn't exist and you try to run the procedure with @flagB = 0, it will throw an error saying it can't find the linked server for FileB

Or if a user uploads a FileB as a FileC by mistake, then corrects themselves by removing FileC from the form, it will throw an error saying Cannot process the object "SELECT * FROM [Sheet1$]" next time the procedure runs because FileC won't have a sheet named Sheet1, even though @flagC = 0

How can I prevent SQL from validating the OPENROWSET queries if they're not the ones getting executed?

There are a couple of options available.  One of them is to use dynamic SQL for your insert calls.  That way, validation doesn't actually occur until the execution occurs.  For your example, it could look like:

declare @sql nvarchar(max);

IF (@flagA = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT A, B, C
    FROM (
        SELECT *
        FROM OPENROWSET(
            ''Microsoft.ACE.OLEDB.12.0'',
            ''Text; HDR=Yes; Database=\\server\tmpFiles'',
            ''SELECT * FROM FileA.txt'')
    ) as T
';
END

IF (@flagB = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT D, E, F
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'',
        ''Excel 8.0;Database=\\server\tmpFiles\FileB.xls'',
        ''SELECT * FROM [Sheet$]'')
    ) as T
';
END

IF (@flagC = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT G, H, I
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'',
        ''Excel 8.0;Database=\\server\tmpFiles\FileC.xls'',
        ''SELECT * FROM [Sheet1$]'')
    ) as T
';
END

execute sp_executesql @sql;

In this case, validation wouldn't occur until runtime.  The downside is that troubleshooting gets harder because you're now trying to debug dynamic SQL.

You might also be able to merge the several separate statements into one.

A second alternative is to split the call out into separate procedures.  I would put this at a distant second-place, mostly because it's easy to abuse this and start pretending that T-SQL stored procedures are equivalent to C-style methods, forgetting that there can be significant performance differences.  Anyhow, move the INSERT statements into three separate procedures and pass in any necessary parameters, like so:

IF (@flagA = 1)
BEGIN
    exec InsertFlagA @parms1;
END

IF (@flagB = 1)
BEGIN
    exec InsertFlagB @parms2;
END

IF (@flagC = 1)
BEGIN
    exec InsertFlagB @parms3;
END

You couldn't do this with a temp table, so that might throw this option out unless you had a permanent "temporary" table available (or used a global temp table).

If you did want to use a temp table, you could change the exec statement to insert into #tmpData exec InsertFlagA @parms1.


xp_logevent Invalid Parameter Type I'm working with SQL Server 2012 Enterprise Edition SP2 on a Windows 7 32bits.

I have this code inside a stored procedure.

Begin try
    -- EXEC CLR Stored Procedure
    exec dbo.MyStoredProcedureCLR @baseUrl, @orderNumber
end try
begin catch
    Declare @message nvarchar(max);

    set @message = ERROR_MESSAGE()
    EXEC master..xp_logevent 60000, @message, informational
end catch

MyStoredProcedureCLR is a SQL CLR Stored Procedure that make a PUT to a RESTful web service.

Sometimes, I get a 404 Not Found exception, and I want to catch it and log it. This is the message I get:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNewCodesICODECLR":
System.Net.WebException: Error en el servidor remoto: (404) No se encontró.
System.Net.WebException:
   en System.Net.HttpWebRequest.GetResponse()
   en StoredProcedures.GetNewCodesICODECLR(SqlString baseUrl, SqlString orderNumber, SqlString lineCode, Int64 quantity, Int64 codesPrinted, Int64 codesCleared)

But, when I get that exception, I get another exception in xp_logevent:

'Error executing extended stored procedure: Invalid Parameter Type'

How can I know if ERROR_MESSAGE() is a valid parameter? OR what's happening?

By the way, I'm running a that SQL CLR Stored Procedure inside a Service Broker Queue.

From the xp_logevent topic in the documentation:

  ' message '
  Is a character string with a maximum of 2048 characters.

So, I would change this:

Declare @message nvarchar(max);

To this:

Declare @message nvarchar(2048);

Or possibly even non-Unicode (the docs aren't clear):

Declare @message varchar(2048);

Also, severity should be delimited as a proper string (in spite of the terrible example in the docs), and in upper case (in case it matters on a case-sensitive collation, who knows what those cowboy XP coders might have done):

EXEC master.sys.xp_logevent 60000, @message, 'INFORMATIONAL';

Your CLR procedure is not relevant here...


does sequence of columns in where condition matters the sequence of composite key? in Oracle Database here, In the code where exists clause contains all primary key columns in where, I want to know that the sequence of this columns in it, matters in query performance.

  update temp_fd_dtl_svj a
  set a.LAST_INT_PAID_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('I')
                            )
  , a.LAST_PROV_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('P')
                            )
   , a.LAST_TDS_DT = ( select max(b.TRAN_DT) from temp_fd_int_svj b
                              where a.COMP_CD = b.comp_cd and a.BRANCH_CD = b.BRANCH_CD and a.ACCT_CD = b.ACCT_CD
                                and a.ACCT_TYPE = b.ACCT_TYPE and a.fd_no = b.FD_NO and b.Tran_type in ('T')
                            )
  where exists (select *
                from temp_fd_int_svj c
               where a.acct_cd = c.acct_CD
                 and a.comp_cd = c.comp_cd
                 and a.BRANCH_CD = c.BRANCH_CD
                 AND a.ACCT_TYPE = c.ACCT_TYPE
                 and a.fd_no = c.FD_NO
               )
  ;

No, it will not matter. Make sure:

Data types are same in both tables
All columns are indexed  Index
Index statistics are not stale.

SELECT stale_stats FROM user_ind_statistics
WHERE index_name = upper('&index_name');


Strategy to deal with multi billion row simulation results and partitioning I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the results are seen and discarded as not something we look for) and some need to be preserved. Dealing with them outside of partioning is hard.

Data is currently MOSTLY in a 3 table hierarchy (trade--order--update) with a trade having multiple orders which get multiple updates each. There is a 4th table (parameter) that contains the parameter for every simulation. This is small and unproblematic though.

We right now write the data to 3 staging tables and analyze there - temporary solution.

I would like some people to review this idea.

Partition the staging tables with x "buckets". A simulation assigns a bucket (smallint) ad then writes into this bucket. This allows fast deletion of a simulation. AS we only run about 100 simulations per week, a 1000-20000 partition set on the tables is enough to keep the data as long as we need (initial review).
When data is ok, we move it from the staging (via stored procedure) into final data warehosue tables. Again, we need to partition them, and we will use a similar bucket approach. As mulitple simulations will run into identical buckets (updating the data) this is a relatively small number of buckets.

Anyone done that?

The idea behind the bucket approach is that I can pregenerate the buckets and do not have to modify the partitioning function. Sadly SQL Server, contrary to Oracle, has no auto partition, otherwise I could use a simple ID field. I really try to avoid dynamically modifying the partitioning schema here. This way I can have a simple smallint "bucket id", a prepared partitioning schema and can basically assign every simulation / run a bucket id -easy to join. Any negatives?

We were doing something not unlike this with clickstream data.  We unplugged SQLServer and plugged in Vertica (a columnar analytics ANSI-SQL compliant RDBMS)...we've never looked back.  Multi-minute queries dropped to millsecond queries, and data loads dropped from hours to seconds. If/when you start outgrowing it, add more nodes and rebalance online. Very nicely done product.

The Community Edition is free for 3 nodes and 1Tb of data (which gets compressed a LOT more than you would expect, so 1Tb is quite a lot of data), and the commercial version (unlimited nodes) is about half the cost of SQLServer EE for TB-size data (it's licensed by data size).  You might want to give it a look. ;-)

Btw, I have no affiliation with Vertica or HP, I'm just a very pleased customer/data architect.

Cheers,
Dave Sisk


Access: Number format for VBA in queries of variant type Lets say I have two queries like

select iif(condition, col, null) from table

select f(col) from table

where col is a double-valued column f is a VBA-public function(value as double) as variant. Somewhere later, I alter the format of the respective field to be displayed with a certain number format, for instance as a percentage. I have decided that f should be a variant since it is supposed to return Null if appropriate. However, in the former case, the format I specify is respected, but if I switch to the latter, the number format is totally ignored. Why?

The answer can be found here in Allen Browne's page:

  in a query, a column can be only be ONE data type. JET therefore treats Variants as Text, since anything (numbers, dates, characters, ...) is valid in a Text column.

Numeric formatting options will not affect the text values which are automatically converted from the VBA Variant, even if the original value was coded as numeric.

His recommended solution is to do what you have already discovered: Use the IIF statement.  The Jet engine uses datatype clues from the parameters to establish the returned column's data type.

Here's some code that will reveal the automatically-selected column type:

Dim rs As Recordset2
Set rs = CurrentDb.OpenRecordset("SELECT f([Col]) FROM table")
Debug.Print "Is text? " & (rs.Fields(0).Type = dbText)
rs.Close


JOIN 3 Tables without Union ALL nor nested queries with multiple IDs Not sure if this is possible, but I would like to join multiple tables with multiple IDs without UNION ALL or nested queries. You can see the related question here which shows everything in table view. I would like to create a table view but am not allowed to use what was mentioned above. I've already figured out how to do it with UNION ALL.

So, I have the Main table which reference a table called Fact. The Fact table contains data that can be referenced by LanguageID, ClientID, or StatusID (revision). The Main and Fact tables are related by an intermediary table called FactLink (not shown for simplicity's sake).

The end result MainView indexable table I would like to get looks like:

MainView

MainID | StatusOrder | LanguageID | ClientID | Description   | Disclaimer  | Other
-------+-------------+------------+----------+---------------+-------------+------
50     | 10          | 1          | 1        | Some text     | Disclaimer1 | Blah
50     | 10          | 2          | 1        | Otro texto    | NULL        | Blah
50     | 20          | 1          | 2        | Modified text | NULL        | Blah
55     | 10          | 1          | 1        | Some text 2   | Disclaimer2 | Blah Blah
55     | 10          | 1          | 2        | NULL          | Disclaimer3 | Blah Blah

DROP TABLE IF EXISTS #main
CREATE TABLE #main
(
    ID INT NOT NULL,
    DescriptionID INT NOT NULL,
    DisclaimerID INT NOT NULL,
    Other NVARCHAR(500)
)

Drop table if exists #fact
CREATE TABLE #fact
(
    FactID INT NOT NULL,
    LanguageID INT NOT NULL,
    StatusID INT NOT NULL,
    ClientID INT NOT NULL,
    Description NVARCHAR(MAX)
)

DROP TABLE IF EXISTS #status
CREATE TABLE #status
(
    StatusID INT NOT NULL,
    [Order] INT NOT NULL
)

INSERT INTO #main
    (ID, DescriptionID, DisclaimerID, Other)
    VALUES
    (50, 1, 2, 'Blah'),
    (55, 4, 3, 'Blah Blah')

INSERT INTO #fact
    (FactID, LanguageID, StatusID, ClientID, Description)
    VALUES
    (1, 1, 1, 1, N'Some text'),
    (1, 2, 1, 1, N'Otro texto'),
    (1, 1, 3, 2, N'Modified text'),
    (2, 1, 1, 1, N'Disclaimer1'),
    (3, 1, 1, 1, N'Disclaimer2'),
    (3, 1, 2, 1, N'Disclaimer3'),
    (4, 1, 1, 1, N'Some text 2')

INSERT INTO #status
    (StatusID, [Order])
    VALUES
    (1, 10),
    (2, 100),
    (3, 20)

Here's what I have so far. But it doesn't include the last entry and hasn't accounted for the StatusOrder. I'm not sure where to go from here.

SELECT
    t.ID, t.Other,
    fDescription.Description,
    fDisclaimer.Description Disclaimer,
    COALESCE(fDescription.LanguageID, fDisclaimer.LanguageID) LanguageID,
    COALESCE(fDescription.ClientID, fDisclaimer.ClientID) ClientID,
    COALESCE(fDescription.StatusID, fDisclaimer.StatusID) StatusID
FROM #main t
JOIN #fact fDescription
    ON fDescription.FactID = t.DescriptionID
LEFT OUTER JOIN #fact fDisclaimer
    ON fDisclaimer.FactID = t.DisclaimerID
    AND fDisclaimer.ClientID = fDescription.ClientID
    AND fDisclaimer.LanguageID = fDescription.LanguageID
    AND fDisclaimer.StatusID = fDescription.StatusID

I will answer the question as asked but I expect that it won't solve your actual problem. You want to translate the query that you have in the other post into a query that can be turned into an indexed view. The biggest problem that I see is that your data model requires that data from multiple rows of the "fact" table be combined together into single rows. Such a requirement fights against the indexed view functionality. Many of the T-SQL restrictions almost seem designed around stopping that type of operation (along with others):

The most important entries in that list for your problem are the prohibitions on MAX, MIN, self-joins, subqueries, APPLY, and UNPIVOT. All of those can be used to rewrite your UNION ALL query but none of them are allowed in indexed views. There is a way to rewrite it but you'll just get stuck further down the line. You started with the following query:

SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  f.Description,
  NULL Disclaimer,
  Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DescriptionID
JOIN Status s ON s.StatusID = f.StatusID
UNION ALL
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  NULL Description,
  f.Description Disclaimer,
Main.Other
FROM Main
JOIN Fact f
ON f.FactID = Main.DisclaimerID
JOIN Status s ON s.StatusID = f.StatusID;

There is at least one way to rewrite that query in a way that's a valid indexed view definition. First create a dummy table with just two rows:

CREATE TABLE DIM_NUMBERS (NUM INT NOT NULL);
INSERT INTO DIM_NUMBERS VALUES (1), (2);

You can then define your view like this:

CREATE VIEW dbo.X_VIEW
WITH SCHEMABINDING
AS
SELECT
  Main.ID,
  s.[Order],
  f.LanguageID,
  f.ClientID,
  CAST(CASE WHEN d.NUM = 1 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) [Description],
  CAST(CASE WHEN d.NUM = 2 THEN f.Description ELSE NULL END AS NVARCHAR(2000)) Disclaimer,
  Main.Other
FROM dbo.Main
CROSS JOIN dbo.DIM_NUMBERS d
JOIN dbo.Fact f
  ON (f.FactID = Main.DescriptionID AND d.NUM = 1) OR (f.FactID = Main.DisclaimerID AND d.NUM = 2)
JOIN dbo.Status s ON s.StatusID = f.StatusID;

CREATE UNIQUE CLUSTERED INDEX IDX_X_VIEW
    ON dbo.X_VIEW  (ID, [Order], LanguageID, ClientID, [Description], Disclaimer, Other);

I have rewritten your query not to use UNION ALL but I did not include the GROUP BY because MIN and MAX are not allowed in indexed views. There aren't any aggregate functions that will help you here because you have an NVARCHAR column. If you had a numeric column then you could probably pull this off with a SUM(COALESCE(COLUMN_NAME, 0)) column but you can't apply the SUM aggregate to NVARCHAR.

Instead of trying to shoehorn a query into an indexed view definition I would try to think of other solutions. Could you just store the results in a table instead? Depending on the length of the description column the space difference between that and an indexed view might not be that large. Can you change the data model in some way that helps?


How to use the result of an insert as foreign key in another insert? I have a temporary table with lots of data. I want to populate two persistent tables with excerpts of that data, preferably in one go.

Problem: after insert on tableA, I want to reuse the generated id as foreign key and add it to the insert for tableB. But is that possible at all?

Pseudocode for separated inserts:

INSERT INTO tableA (id, col1, col2, col3)
    SELECT(col1, col2, col3) FROM tmp_table
    ON DUPLICATE KEY UPDATE id = last_insert_id(id);

INSERT INTO tableB (fk_id, col4)
    SELECT (fk_id_from_above, col4) FROM tmp_table
    ON DUPLICATE KEY UPDATE...

These are my tables:

//a simple temp table with lots of data
CREATE TABLE tmp_table (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 col... varchar(20) NOT NULL,
 colN varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should be filled from tmp_table and return the generated id for each row
CREATE TABLE tableA (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 col1 varchar(20) NOT NULL,
 col2 varchar(20) NOT NULL,
 col3 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

//this table should reference the id from tableA (foreign key)
CREATE TABLE tableB (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 fk_id bigint(20) NOT NULL,
 col4 varchar(20) NOT NULL,
 PRIMARY KEY (id)
)

I want to create a single INSERT, that first inserts into tableA, then reuses the generated id and persist it together with only some other columns in tableB.

I think you can use the LAST_INSERT_ID() function, with something like this:

INSERT INTO tableA
    (col1, col2, col3)
SELECT col1, col2, col3
FROM tmp_table
ORDER BY id ;

@first_id = LAST_INSERT_ID() - 1 ;

INSERT INTO tableB
    (fk_id, col4)
SELECT @first_id = @first_id + 1, col4
FROM tmp_table
ORDER BY id ;

The ORDER BY id is needed in both insert statements because we want the rows from the temp table to be inserted in the same order to both tables.

The first insert (into tableA) will give consecutive id values to tableA.id column. The first of those values is the returned by the following LAST_INSERT_ID() call.

The second insert then uses that value (and increasing it one by one) to provide the same values (as tableA.id) to tableB.fk_id column.


Actual moving of tablespaces in Postgres, not copy-delete I found that

ALTER DATABASE databasename SET TABLESPACE tablespacename

Copies the data and (I believe, couldnt check it) deletes the source after all is done. I need actual movement, because I have a large database that want to move to its own tablespace and doing this takes all the remaining storage.

Is any workaround for this?

No, there is no such way.

PostgreSQL does not know about the underlying storage, so it doesn't know when to perform a hard link instead.

Besides, it makes no sense to have two tablespaces on the same file system, and with different file systems movement means copy + unlink anyway.


SSIS - This error is thrown by Connections collection when the specific connection element is not found I am writing a package to upload result of query to FTP as CSV file.

Package is working fine on local machine, but after transferring .dtsConfig and .dtsx on another environment it throws following error message after executing all tasks in package.

  Description: The connection "{008A2775-60D6-44B1-8790-69958EB74371}"
  is not found. This error is thrown by Connections collection when the
  specific connection element is not found.  End Error  Error:
  2014-11-24 07:32:05.79     Code: 0xC001000E     Source: PackageName
  Description: The connection "{008A2775-60D6-44B1-8790-69958EB74371}"
  is not found. This error is thrown by Connections collection when the
  specific connection element is not found.  End Error  DTExec: The
  package execution returned DTSER_FAILURE (1).  Started:  7:32:05 AM
  Finished: 7:32:07 AM  Elapsed:  1.81 seconds.  The package execution
  failed.  The step failed.

any idea what will be the reason?

The problem lies in the referencing. You might have copied the package from an older one and are making the changes or you might have edited the connection managers. The connection managers are internally used using the GUID that you have mentioned. You might have changed most of the references from the UI, but still there are some objects and tasks, that are referencing the deleted or older connection managers. The probable one would be the SQL Server Logging in the package.

Solution is: Open the code of the package, by right clicking the package in the solution explorer and clicking on View Code which shows the package code in XML format. Now you are able to see, which tasks are referencing the old connection manager. Do the needful changes and this should do it!

Let me know if the problem persists after this!!


What are the best practices for triggers to maintain a revision number on records? I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number to each table to make idempondency checks more efficient.

As it stands, the trigger that captures all the data is AFTER UPDATE. My understanding is that  if I want to update the row I'm operating on, I will need to use a BEFORE trigger. ie:

CREATE TRIGGER `Table_A_Audit_Trigger` BEFORE UPDATE ON `Table_A`;
FOR EACH ROW BEGIN;
SET NEW.revision = OLD.revision + 1;

Would it be better to have two separate triggers for this, or to move the insert into the BEFORE trigger as well?

EDIT: The code for the AFTER UPDATE trigger (as well as the table DDL itself) is created by the application layer, then run on the database (generally when the model is created or updated in the software). It would not be difficult to add creation of this trigger at the same time.

As I understand it, the downside to combining into a BEFORE trigger would be inconsistency if the audit insert succeeds, but the actual update fails. Putting everything in an AFTER trigger would require the use of an additional UPDATE on the target table, which further compromises performance, and can cause cascading issues as well.

Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.


Extended Event not catching queries executed on my .NET application I created that extended event to identify long running queries on my database:

CREATE EVENT SESSION [queriesLentas]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE sqlserver.sql_statement_completed.duration > 1000
    )
ADD TARGET package0.asynchronous_file_target(
  SET filename='C:\queriesLentas.xet', metadatafile = 'C:\queriesLentas.xem'
  )
GO

ALTER EVENT SESSION queriesLentas ON SERVER
STATE = START
GO

But when I start this session, it only gets queries executed on SSMS, and do not get queries executed on my .NET application(so that I can easily track those long queries).

Is there a way to see queries executed from my .NET application through this extended event?

The problem you might be running in to is with the filter on duration. Extended Events only trigger when filters are met, so if no queries take 1000 microseconds or longer, they won't show in the Extended Events session data.


Is there any way to perform atomic updates of stored procedures in MySQL? I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588).

In our project multiple store procedure call within a second, we provide high availability service to client. but due to this bug are not updating any stored procedure during production hours. If we update any thing in stored procedure MySQL simply drops the entire procedure and recreates it again which creates downtime issue for our client.

It's also possible that a client calls a stored routine in exactly the moment between a DROP and a CREATE which create major issue for us.

Anyone facing this kind of issue? If yes if you have any way to resolve this kind of issue then please share.

Thanks

There is no way to solve this problem. It's one of many issues that arise over MySQL not having real transactions: the transactions do not cover DDL.

It's not just stored procedures either. That's a tremendous understatement. MySQL has fundamental problems with concurrency. It's MVCC model is non-existent with DDL: transactions are a function of Inno not MySQL. And, even at that the transaction model is broken with DML too.

I suggest migrating databases. I know that's steep. But shy of that nothing can done.


Find unmatched rows between two tables dynamically I have a function here that is supposed to take two tables as arguments and check if they are the same.

create or replace function testing.equal_tables(
    varchar,
    varchar)
    returns void as
$$
begin

    execute 'select *
     from
     (select * from ' || $1 ||'
     except
     select * from ' || $2 || ') a
     union
     (select * from ' || $2 || '
     except
     select * from ' || $1 || ');'
    ;

end;
$$ language plpgsql;

When I call it with these two tables, one with 20 rows and one with 10 rows, I get only the empty set, which is not the correct result:

select testing.equal_tables('ee1', 'ee2');

When I modified the function to return a string of the statement, it came back correctly, but that still doesn't help because I'd like to execute the return string using a function, prepared statement, or something. Is there any way to make this function work?

I derive from your example that you only want to match tables with the same structure (compatible row types).
Base query
First of all, your base query is needlessly complex and possibly incorrect. Consider instead:
(TABLE a EXCEPT ALL TABLE b)  -- ALL keeps duplicate rows and is faster
UNION ALL
(TABLE b EXCEPT ALL TABLE a);

I doubt you want to fold duplicates. Typically, tables do not hold complete duplicates to begin with, and omitting the ALL key word would instruct Postgres to try and fold duplicates in every step, which is just a waste of time. Details:

Select rows which are not present in other table

Even if completely duplicate rows are possible, it might be best to return all of them to avoid misleading answers. If you want to fold duplicates in the result anyway, a single UNION does the job:
(TABLE a EXCEPT ALL TABLE b)
UNION
(TABLE b EXCEPT ALL TABLE a);

However, a query with NATURAL FULL OUTER JOIN is more efficient, doing almost the same:
SELECT *
FROM   a NATURAL FULL OUTER JOIN b
WHERE  a IS NULL OR b IS NULL;

There are two subtle, exotic corner cases:

This returns rows that are all NULL to begin with. It would be an odd table design that allows such rows, though.
This does not return duplicate rows that are not matched with the same number of duplicate rows in the other table: a single match is enough to eliminate all - which is subtly different from folding duplicates in the result! Have a look at the fiddle below. Again, it would be an odd table design that allows completely duplicate rows.

Function
What you have so far does not work for several reasons.
To return actual table rows dynamically (and not just a count or a text representation), you need to use a polymorphic type.
Since the second table is bound to have a compatible row type (as per my assumption) it's enough to hand in just the table name for that.
CREATE OR REPLACE FUNCTION f_tbl_diff(_tbl1_type ANYELEMENT, _tbl2 text)
  RETURNS SETOF ANYELEMENT AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %1$s NATURAL FULL OUTER JOIN %2$I
      WHERE  %1$s IS NULL OR %2$I IS NULL'
    , pg_typeof(_tbl1_type), _tbl2);
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_tbl_diff(NULL::a, 'b');
Note the special syntax for the first argument! We hand in an actual row defining the return type, not just a table name. Related answer on SO with ample details (scroll to the last chapter):

Refactor a PL/pgSQL function to return the output of various SELECT queries

This returns all rows that have no exact, complete match in the other table
SQL Fiddle also demonstrating exotic corner cases.
About the dynamic SQL:

SQL injection in Postgres functions vs prepared queries


MySQL select with left join query I have following code.

select class.name as classname, class.hours as hours, teachers.first_name
as teacherFirstName, teachers.last_name as teacherLastName
from teachers,class
left join class_teachers cl on class.id = cl.class_ref
left join class_teachers cl1 on teachers.id = cl1.teachers_ref

I got following error.

#1054 - Unknown column 'teachers.id' in 'on clause

When I interchanged class and teachers the error was:

#1054 - Unknown column 'class.id' in 'on clause

Do you guys have an idea whats the problem?

edit

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `hours` int(3) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `class_teachers` (
  `class_ref` int(11) NOT NULL,
  `teachers_ref` int(11) NOT NULL
)

CREATE TABLE `teachers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)

class_teachers is an intermediary table. This database should make possible that a class should have multiple teachers. For example PE class can have separate teachers for boys and girls. The query should show list of class with teachers names, class name and number of class hours

class table

id  name     hours
1   science     3
2   math        5

teachers table

id  first_name  last_name
1   Ronald      Manlapao
2   hello       goodbye
3   elise       elise
4   me          me

class_teachers table

class_ref   teachers_ref
2                   2
1                   1
1                   2

I tried the following:

select c.name as classname, c.hours as hours, t.first_name as teacherFirstName,
t.last_name as teacherLastName
from teachers t, class c
left join class_teachers cl on (c.id = cl.class_ref and t.id = cl.teachers_ref)

But still the got same error.

problem solved using:

select class.name as classname,
       class.hours as hours,
       teachers.first_name as teacherFirstName,
       teachers.last_name as teacherLastName
from class_teachers cl
left join class on class.id = cl.class_ref
left join teachers on teachers.id = cl.teachers_ref

edit - moved to separate answer by OP

updated class_teachers table to class_teachers_section to include section. the idea is a class can include different sections with different teachers. for example a grade 1 PE class can include all sections (lets pretend 3 sections) ... teacher for boys will be different for girls

select class.name as classname,
   class.hours as hours,
   teachers.first_name as teacherFirstName,
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls
 left join class on class.id = cls.class_ref
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname,
   class.hours as hours,
   teachers.first_name as teacherFirstName,
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls
 left join class on class.id = cls.class_ref
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'


mysql: display data shiftwise I have this example table:

Table1

+-------+----------------+-----------+------------+
| tkno  |      dete      |   shyft   |   Name     |
+-------+----------------+-----------+------------+
| 1251  |     13-04-2018 |    Shift1 |         Foo|
| 1252  |     13-04-2018 |    Shift1 |         Foo|
| 1253  |     14-04-2018 |    Shift2 |         Bar|
| 1254  |     14-04-2018 |    Shift2 |         Bar|
| 1255  |     15-04-2018 |    Shift3 |         Bar|
| 1256  |     15-04-2018 |    Shift3 |         Joe|
| 1257  |     15-04-2018 |    Shift3 |         Joe|
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

When I use this query,

SELECT shyft,
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1
GROUP BY shyft;

I get results like this:

+-------+----------------+-----------+------------+
| shyft | Todays Tickets | 1 Day Ago | 2 Days Ago |
+-------+----------------+-----------+------------+
|Shift1 |             13 |        33 |         34 |
|Shift2 |             13 |        33 |         34 |
|Shift3 |             13 |        33 |         34 |
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

I want to display the number of tickets in each shift for current day and last 2 days. For the above query, it is showing the same count on row 2 and 3. Please help me with where am I going wrong. Thanks!

The subqueries in your query are al counting all rows of the table. They should probably only be counting for the "shyft" from the main table; something like:

SELECT shyft,
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1 x
GROUP BY shyft;


Transferring Auto Incremented Data Let's say we have two identical tables in two different databases.
Each have a primary key field that is auto incremented. For example:

TestDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

ProdDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

We add some records and delete some records from test, so they no longer match:

TestDB: Table1
Key     Value
1       Mike
4       Jacob
5       Lily
6       Laura

ProdDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

We need the data on production to match what is on test. Due to auto increment, we can't simply delete all the records and then push them in fresh, their keys would be 4,5,6,7 instead of 1,4,5,6. We can't reseed after, that would make them 1,2,3,4.

We could drop the table and just copy the whole thing over again, but from what I am told doing that too many times could corrupt the database.

I had thought that we could simply turn off auto increment, update the records, and then turn auto increment back on, but my DBA tells me that wouldn't work either. There must be a fairly standard process for moving data between test and production, so what are we missing?

You cannot update an IDENTITY field, so you have to insert new records into the table if you want to manipulate the IDENTITY value.  You are able to adjust the IDENTITY SEED throughout the process though to fit your needs or outright ignore it (so long as you do not insert duplicate values) with SET IDENTITY_INSERT [tableName] ON.  Here's an example script that hopefully illustrates what you can do better than my explanation:

CREATE TABLE #tmp
(
      ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , MyValue VARCHAR(255)
)

-- Insert Test Data
INSERT INTO #tmp (MyValue)
VALUES ('one')
      ,('two')
      ,('four')
      ,('three')
      ,('five')
      ,('del')
      ,('seven')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('thirteen')

-- Purge some test data to make a pocket of missing IDENTITY values
DELETE FROM #tmp
WHERE MyValue = 'del'

-- Check Current IDENTITY value
DBCC CHECKIDENT(#tmp)

SELECT * FROM #tmp

-- purge ID 3 & 4 to reinsert/fix their IDs
DELETE FROM #tmp WHERE ID IN (3, 4)

SET IDENTITY_INSERT #tmp ON

--Re-Insert 'three' and 'four' records with matching IDs
INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
      ,(4, 'four')
-- Add Values Outside the Range
      ,(21, 'twenty one')
      ,(101, 'one hundred one')
SET IDENTITY_INSERT #tmp OFF

SELECT * FROM #tmp

-- As you can see adding values outside the expected range auto-advances the IDENTITY
DBCC CHECKIDENT(#tmp)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred two')

SELECT * FROM #tmp

DBCC CHECKIDENT(#tmp)

--Advance the Identity to 130
DBCC CHECKIDENT(#tmp, RESEED, 130)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty one')

SELECT * FROM #tmp

--decriment the Identity
DBCC CHECKIDENT(#tmp, RESEED, 5)
INSERT INTO #tmp (MyValue)
VALUES ('six')

SELECT * FROM #tmp

-- caution this will fail as IDENTITY 7 already exists
BEGIN TRY
INSERT INTO #tmp (MyValue)
VALUES ('seven')
END TRY
BEGIN CATCH
    DECLARE @myMessage NVARCHAR(4000)

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

-- caution this will fail as IDENTITY 3 already exists
BEGIN TRY
SET IDENTITY_INSERT #tmp ON

INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
END TRY
BEGIN CATCH

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

SET IDENTITY_INSERT #tmp OFF

-- Be sure to Reset IDENTITY back to max value otherwise you risk an IDENTITY value collision
DECLARE @maxID INT
SELECT @maxID = MAX(ID) FROM #tmp

DBCC CHECKIDENT(#tmp, RESEED, @maxID)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty two')

SELECT * FROM #tmp

-- Cleanup
DROP TABLE #tmp

Some notes, DBCC CHECKIDENT requires db_ddladmin privileges or higher to use.  Because of this, I couldn't include a DB Fiddle example.  Also, and this may be something you want to dig into as well, but I believe that pushing data from a "test" environment qualifies said environment as "production worthy" which may affect your licensing.  Licensing is out of scope, but this could bite you depending on how you have your server licensing setup, so just be wary of promoting data up your environment stack.


Research Database: Access and Ownership Policies I'm a PhD student in an academic research laboratory. One part of my project involves collecting samples and generating data. Another part involves establishing a research database to store the results. As the project has grown, a Masters student and two post-doctoral fellows are also working on the samples and generating data. All data relating to the samples I collected is to be added to the database, regardless of who generated the data. Each entry in the database has an "owner" field to keep track of this. Our supervisor would like the database to be used more extensively by people working on other projects as well.

I am meeting some resistance from members of our group (both those workings on "my" project and on their own/other's projects) regarding submitting data/results to "my" database. At a recent meeting, I explained once again the concepts of a (central) database, that queries can easily extract data, that the "ownership" field keeps track of whose data point it is, and reminded everyone of the benefits of a central database which stores all data generated. I highlighted the kinds of analyses which can be undertaken comparatively on different data sets, how meta-analyses can be done, and so on. I then offered to do some research around the issue of database management and ownership. Some people in the group do not want to submit data to the database until their papers are published, despite the fact that it is impossible for anyone else in the group to simply throw together a "competing" paper from the data alone and publish it "first". This is also impractical because it means that data which is never published is never added to the database. It also means that no-one else has access to it UNTIL the paper is published, thereby slowing down other research. Internal sharing of this kind of data should be routine.

I would appreciate any suggestions of resources or comments around these issues. Should everyone sign a "waiver" regarding their data going INTO the database and another one regarding the use of data FROM the database? We're not a particularly mistrustful group, but everyone else in the group are "wet lab" researchers, so the concepts of databases and sharing data is possibly a little foreign to them.

Welcome to office politics.  Things like NDAs regarding the disclosure of data can help with this.  Your adviser and the universities legal department should be able to help you get these papers drawn up.


MySQL - fine-tuning full-text search How can I fine tune MySQL full-text search for my information domain?

For example, I might have a keyword like js that I would like the search to recognize, but I don't think allow a minimum word length of 2 be a good idea.

Another scenario I have: I'd like to ignore 3 if that was searched, but would like it to be recognized if the search was css 3. Not all single-characters are insignificant, particularly when combined with another string.

Is there a way I can give MySQL a list of words I do not want it to exclude either through it's stop words list or minimum word length variable?

Assuming your table is ENGINE=InnoDB, here are some tips:

Set innodb_ft_min_token_size = 2; I don't think it is 'bad' idea.  Note:  If you are changing this value, do so in the config file, restart MySQL, then rebuild any FT indexes.  (Otherwise, the "2" won't take effect.)
Programmatically add a + in front of each word, and add IN BOOLEAN MODE.
Programmatically:  Either remove 1-character 'words' from the search string, or at least don't add a + in front of them.

Here's a trick that is useful sometimes:

WHERE MATCH(col) AGAINST('+css 3' IN BOOLEAN MODE)
  AND col LIKE '%css 3%'

The MATCH will happen first (even if it is not first in the WHERE).  It will find rows with the word "css", but ignore the "3".  Then the LIKE will double check that there is exactly "css 3" somewhere in the string.  Even this is imperfect, since "css 31" will be found.


Reporting Services DBs not appearing in Management Studio, but can be queried? I've set up a server with native reporting services, connected to a database server which hosts its two databases. I'm trying to run a backup of the two databases so I can add them to my availability group, but neither of them are showing up in the "Databases" folder in the object explorer so I can't get into their properties or run tasks on them. Is there a way to show them, or is there another way to run tasks on a database?

They are both showing up in the availability group wizard and I can run queries on them (although without Intellisense) so I'm definitely connected to both of them.

You can also use T-SQL

use master;
go

alter availability group [ag_name] add database ReportServer

backup database ReportServer to
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    WITH FORMAT, INIT, COMPRESSION, STATS;
GO

backup log ReportServer to
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak';
GO

Now go to another replica and restore:

restore database ReportServer from
disk = N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    with replace, norecovery;
GO

restore log ReportServer from
disk=N'\\some\shared\location\available\to\all\nodes\ReportServer_HADR.bak'
    with norecovery;
GO
alter database ReportServer set hadr availability group = [ag_name]
GO

However, what you really need to do is ensure that you're actually connected to the replica that currently holds the ReportServer* databases before you try adding things to the availability group. If you've somehow stumbled upon the secondary, or if you're connecting to the listener and the AG has failed over to what was formerly the secondary node (i.e. the replica that does not have the databases you need), then you're not going to see the ReportServer* databases.

Finally, please make sure you read the documentation.


How to use variables in file paths in MySQL statements? I have now a problem that I have to set paths through variables in queries like

load data infile  '/my/file/path/to/file.txt'
into  table  database1.table2
fields terminated by ''
lines terminated by '\r\n'
( id, name_en, name_de, value_en, value_de);

The part "/my/file/path/to/" should be set with a variable, but the only way I have found is to use prepared statements, which is not possible with this type of queries.

Is there any other option to keep the path in a variable?

You cannot keep the path in a variable because variables are interpreted by the server, and path names are interpreted by the mysql client.
Related question:

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error

However, there are a couple of different work-arounds that will allow you to accomplish what you are trying to do, and they are explained in the article.

I'd like to add one more idea:

If you call the mysql script from a Bash script, copy your file into a temp file, so the script always calls the same file name, like "work_file.csv".

After the mysql script runs, you can delete "work_file.csv".

Your original file is kept intact, and the data from that file is now uploaded.


LOAD DATA INFILE decline in performance Query for insert:

LOCK TABLE tbl WRITE;
ALTER TABLE tbl DISABLE KEYS;
load data local infile "E:/One.txt" into table tbl fields terminated by "\t" lines terminated by "\n" (user_id, sexid, cityid);
ALTER TABLE tbl ENABLE KEYS;
UNLOCK TABLES;

First 5 million inserted per 2 min, second 3 million per 20 min, after 200k per min.
Why decline perfomance?
How to recover insertion speed?
~200 million of records.
Type table InnoDB

Indexes are not disabled in InnoDB using DISABLE INDEX
(from http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):

For MyISAM tables, key updating can be controlled explicitly. Use
ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique
indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing
indexes

For InnoDB, you're stuck with these hints: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

I think you should also remove the indexes and re-create them after you populated the table (the same thing you tried to do with DISABLE/ENABLE KEYS)


MySQL - can't get the result of non-matching records I am trying to get the result of a record which has a missing entry in the other table. I already tried using NOT IN and NOT EXIST but I cannot get the desired result. The output should be Kevin and US Visa since "Kevin" has no US Visa on the table tbl_personnel_documents but I always get an empty row. It should be like this:

name  | documentName  |
----------------------
Kevin   US Visa

Note: I am trying to get the result by using only the vessel_id and lastJoinedVsl which in this case is id number 4.

Here's my code and sample data: http://sqlfiddle.com/#!9/46790e/1

Please help. Many Thanks

So I think what your looking for is this:

I used this to find the abandoned the abandoned records:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
;

Returns

TPD_ID, TPI_NAME, TD_ID,     TD_DOCNAME
-------------------------------------------
   '1',  'Kevin',   '1',    'PASSPORT'
   '2',  'Kevin',   '2',    'AUSTRALIAN VISA'
  NULL,     NULL,   '3',    'US VISA'

The reason you will not get the result

name  | documentName  |
----------------------
Kevin   US Visa

Is because there is no value linking them.

Let me know if this is what you are looking for.

😁

EDIT 1:

If you want to filter them out you can use an INNER JOIN on your joins.

That will strip out all values that don't have a linking value, based on the query in your SQL Fiddle:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
INNER JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
INNER JOIN tbl_documents AS td ON td.id = tpd.document_id
;

If you want to see only the missing records you can use:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
WHERE tpd.id IS NULL;


Sql Server - Best Practices for Growing Database Files I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.

The DB files auto growth is set to 5MB and I would like to try a different approach, so I am looking for suggestions and or comments.

There is a tuning task that runs every-week on Sunday night at 1:30 AM. The task will:

Check Database Integrity
Shrink the Log File – (This is ok because logging mode is Simple)
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History

I would like to add two more steps to the weekly tuning plan:

Grow the database file by 500 MB if the used space reaches a certain threshold or total size.
Grow the log file by 250 MB (after the shrink) if the used space reaches a certain threshold of total size.

By placing the growth burden in offline hours, I hope to gain performance by reducing the number of auto-growth events during heavy loads.

I have two questions relating to auto growing files.

The best place to put the file grow steps would be prior to the current steps or after?
If I use the ALTER DATABASE|MODIFY FILE to grow the file then how can I determine if SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.

Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.

If recovery model is simple, there is no way on earth you should need to grow your log file by 250 GB. The used space in the file will clean itself out automatically over time, unless you started a transaction a month ago and have no intentions of ever committing it or rolling it back.

So my advice would be:

Auto-grow the data file manually during a quiet period to a size that will accommodate several months of growth. What are you saving it for in the meantime?

Set the auto-growth increment for the data file to something relatively small (so that it doesn't interrupt users when it does happen), and alert on this event (you can catch it in the default trace, for example, or through extended events). This can tell you that you are hitting the high point you estimated and it is time to grow manually again. At this point you will want to keep this manual in case you want to add a new file / filegroup on a different drive to accommodate the space, since eventually you will fill the current drive.

Auto-grow the log file to, say, twice the largest it's ever been. It shouldn't auto-grow further unless there is some abnormal transaction holding things up. You should monitor for this event as well, so that you know about them.


Outer Join vs Dummy Rows I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived performance benefit.

However, is it appropriate to use it in a relational database? Should we create dummy rows in our base tables so that our relation table can attach to it? Besides being unnatural for RDBMS, it causes unnecessary database bloat. Just wondering if I am NOT seeing something.

All in the spirit of avoiding OUTER JOINs.

Why do you wish to avoid outer joins? Using these does have a specific meaning which is "return me all the values from the driving table regardless of whether there is a matching row in the outer joined table." This gives a clear statement to future developers who maintain your code which would be lost if using a dummy value.

There are some things to bear in mind however:

(Note - discussion is based on Oracle; other major RDBMSes are likely to be similar but may have different/better optimizations which affect how outer joins are executed).

From a performance perspective, outer joining restricts the options available to the query optimizer. When constructing the query plan, the optimizer determines which is the smaller rowset and uses that as the driving table. When you specify an outer join, then the table you're outer joining from will become the driving rowsource.

Using the classic emp and dept tables supplied with Oracle, we can see this in action:

explain plan for
  select *
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------------------
| Id  | Operation                    | Name    | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |
|   1 |  MERGE JOIN                  |         |    14 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |
|   4 |   SORT JOIN                  |         |    14 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |
--------------------------------------------------------

explain plan for
  select *
  from   scott.emp e, scott.dept d
  where  e.deptno = d.deptno (+);

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |
|   1 |  HASH JOIN OUTER   |      |    14 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |
-------------------------------------------

When we fully join the two tables, Oracle chooses the smaller of the two (dept) to be the driving table. However, when we outer join to it, the order is switched. This is because it knows we need to return all the rows from emp no matter what.

As query plans are generally most efficient when starting with the smallest rowset, working up to the biggest, this could cause issues with multi-table joins outer joining from a "large" table to a "small" one. In some cases this can cause queries to be very slow. In an OLTP application most queries should only be returning a handful of rows though, so all rowsets should be small (even if coming from a large table), meaning you shouldn't encounter this problem very often in a well designed (OLTP) system.

To convert your queries to become full joins, you'll need to decide what value to use instead of null. This is a tricky issue as you're converting something which means I don't know into whatever value you assign "null" to be. This can cause confusion for future developers down the line and introduce performance issues of it's own (e.g. single column indexes don't include the null rows). See this for some discussion around null.

Personally I wouldn't attempt to create dummy values to represent null. If you find that you have performance issues related to outer joins then I would start by looking at the affected queries and tuning those individually. If this issue is affecting a lot of queries I may start looking at using dummy values, but only as a last resort.

Of course, you should always ask whether you genuinely mean to use an outer join or if you're using it "just in case"!


Indexing to improve performance of range queries I've got a relation in a Postgres 8.2 database similar to this:

CREATE TABLE foo (
    foo_id varchar(160) NOT NULL,
    bar_id varchar(160) NOT NULL,
    created bigint NOT NULL,
    PRIMARY KEY (foo_id, bar_id)
);

Let's leave aside for the moment the fact that the PK is composite and uses varchars (not my choice, legacy product, etc. etc.).

Currently, our application never issues queries that contain created in a WHERE clause, so it's not indexed. However, we've got a new requirement that requires us to query on a range of created values. The proposed query is along the lines of:

SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789

The foo table is easily the largest in our application, but even then it's quite likely to have fewer than 50,000 rows even in the largest deployment, and there are rarely more than a dozen or so rows with the same foo_id.

My question is, should an index be added on the created column, considering that the foo_id is part of the PK? If so, does it make sense to index only the created column, or to index on (foo_id, created)?

My EXPLAIN on the above statement shows that the PK is being used, and then a FILTER operation is being applied. Using test data, the performance seems fine. My concern is performance if the tables grow to a massive size.

Thanks!

If you have an index on created then the planner will need to choose between an using that index or the PK (or a full table scan) - it will not benefit from both at the same time.

--EDIT

As pointed out by @jug in the comments below, this is not accurate at least since 8.1: the planner may choose to build two in-memory bitmaps and combine them to get the result set. This gets more expensive as the tables get bigger, so the planner may choose not to do this depending on the size of the table and the estimated cost of using one index and then filtering.

--END EDIT

The new index will only be helpful if in some cases using it is more efficient than access via the PK. The kind of things that could make this likely include:

A large number of (...) in SELECT * FROM foo WHERE foo_id IN (...) AND created > 1234 AND created <= 6789
A small range, eg created > 6780 AND created <= 6790

Unless one or both is likely to happen, you should not create the secondary index - if they might, it would be best to test each scenario with and without the index to see if any performance benefit is worth the cost (eg increased storage and overhead for insert and update operation)


MongoDB replica set over ssh tunnels I'm trying to archive something like this

The MongoDB instance on dc1.com is currently a standalone, and On-Site periodically pulls a backup via mongodump over a ssh tunnel.

I now want to create two instances, one On-Site and one in a second datacenter, and make the three instances become a replica set, so that the backup is almost instant. This way I would have an On-Site backup and one in the second datacenter, which would always be up to date.

If the main MongoDB instance in dc1.com fails, then that's it, no data can be served, this will then need to get fixed manually, as the On-Site instance and the one in the secondary datacenter should not take over. Nor are those two meant to be queried. They are there just as a backup.

My problem is that when I add --replSet to the main database, and then a rs.initialize(), that this instance is then somehow not recognized as bound to localhost, but to 172.17.0.1, which is the docker interface, which I need to bind to so that the containers can connect to MongoDB. If I then try to add a member via rs.add("localhost:2001"), which is the instance on dc2.com, I get an error about not being able to mix localhost and non-localhost databases. The main instance is recognized as 172.17.0.1 instead as localhost.

  "Either all host names in a replica set configuration must be
  localhost references, or none must be; found 1 out of 2"

I then used https://stackoverflow.com/questions/28843496/cant-initiate-replica-set-in-ubuntu in order to issue a rs.initiate({_id:"rs0", members: [{"_id":1, "host":"127.0.0.1:1000"}]}) to force the initialization of the replica set on dc1.com to be bound to localhost. But when I then try to add a member via rs.add("localhost:2001") I still am not able to do it. It's no longer the hostname error, but another one, which I can't quite remember, as I got tired of trying and had to rollback everything. It was something like connection rejected. When I do a mongo --port 2001 I get disconnected after a warning about something related to "isMaster".

Is it possible to create this kind of setup? All I'm trying to do is to avoid using TLS on MongoDB and binding the backups to globally accessible interfaces (the On-Site instance would be behind a firewall, so 0.0.0.0:1002 would not be publicly accessible)

The following setup is possible, without using TLS.
Datacenter 1 is the primary datacenter. All clients connect only to this one.
Datacenter 2 is only for realtime backups via replication.
On-Site is also only for realtime backups via replication.
This setup is NOT meant for failover, only for disaster recovery.
Datacenter 1:
MongoDB

mongod --bind_ip 127.0.0.1,172.17.0.1 --replSet test --port 2000
--rest --httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

127.0.0.1 is for SSH access, 172.17.0.1 is for access from Docker containers.
SSH Tunnels (this server is responsible for creating the tunnels to Datacenter 2):

autossh -f -N -L 2001:localhost:2001 mongodb@dc-2.example.com
autossh -f -N -R 2000:localhost:2000 mongodb@dc-2.example.com

Datacenter 2:
MongoDB

mongod --bind_ip 127.0.0.1 --port 2001 --replSet test --rest
--httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

On-Site:
MongoDB

mongod --bind_ip 127.0.0.1 --port 2002 --replSet test --rest
--httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

SSH Tunnels (On-Site is responsible for creating the tunnels to Datacenter 1, no persistent connection exists between Datacenter 2 and On-Site)

autossh -f -N -R 2002:localhost:2002 mongodb@dc-1.example.com
autossh -f -N -L 2000:localhost:2000 mongodb@dc-1.example.com

The server in Datacenter 1 contains all the data which is to be replicated, Datacenter 2 and On-Site are empty databases.
On the machine in Datacenter 1 I enter mongo --port 2000, then I issue a
rs.initiate(
   {
      _id: "test",
      version: 1,
      members: [
         { _id: 0, host : "localhost:2000", priority: 1, votes: 1 },
         { _id: 1, host : "localhost:2001", priority: 0, votes: 0, hidden: true },
         { _id: 2, host : "localhost:2002", priority: 0, votes: 0, hidden: true }
      ]
   }
)

I guess priority: 0, votes: 0, hidden: true on the secondaries is optional here, it does what I want, just have the two secondaries be a part as silent, invisible data collectors for backup purposes. I'm not sure if changing this would have any side-effects as it could be possible that then a connection between both secondaries should be available, which it is not, in this case (they can't see each other).
I could use a slaveDelay on one of the hidden members in order to make it lag for let's say half a day, but since I will be making plenty of backups from both hidden databases via mongodump, it's not really neccessary.
In order to be able to connect to the secondaries via mongodb tools I needed to issue a rs.slaveOk()
Nothing had to be changed in the clients, they connect to 172.17.0.1:2000.
I tested a reboot of the machine in Datacenter 2 and during the reboot I inserted new items in the DB, after about 30 seconds the rebooted machine synced to Datacenter 1 as expected.
Datacenter 1 has a fast server with lots of RAM and CPU and only a little bit of SSD storage, Datacenter 2 has a little bit of RAM but lots of HDD, so this seems to be a good solution.


GRANT EXECUTE ON SomeProc TO SomeRole AS dbo I have a role ExecSP which I use to manage who can execute stored procedures. EXECUTE permission is granted on all SPs to this role.

I also use SQL Server Data Tools (SSDT) to manage MS SQL Server database schema.
In the file where my SP is defined, I have this:

CREATE PROC SomeProc AS
.
.
.

GO

GRANT EXECUTE ON SomeProc TO ExecSP

However, when I apply the change to the target database, and do the schema comparison, it reports differences in SP permissions, where on the DB side I have this:

GRANT EXECUTE ON SomeProc TO ExecSP AS dbo

I've tried to understand what AS dbo part represents, but I couldn't.

My questions:

Does it mean that the SP will be executed with dbo rights, regardless of who is executing it? (I guess/hope it doesn't.)
Why is this added automatically?
I want this SP to be executed with the rights the caller has - how do I achieve that?

From BOL:

  Use the AS principal clause to indicate that the principal recorded as
  the grantor of the permission should be a principal other than the
  person executing the statement. For example, presume that user Mary is
  principal_id 12 and user Raul is principal 15. Mary executes GRANT
  SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; Now the
  sys.database_permissions table will indicate that the
  grantor_prinicpal_id was 15 (Raul) even though the statement was
  actually executed by user 13 (Mary).

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql

So in this case the engine is recording the grantor as dbo, rather than the account running the actual code.

I'm unsure why this would be - perhaps due to some aspect of the schema compare, dbo is used to ensure there are no orphaned records in terms of the grantor? Perhaps someone else can shed some light.
It should do this anyway - do you mean in this case the caller is the ExecSP role or the user themselves? Either way this should already be happening, if you've assigned the role or not.


Remove duplicate values from query result I am using Postgres 9.3.9. I have 2 tables :

f_agent :

CREATE TABLE f_agent (
  f_agent__id bigserial NOT NULL,
  f_agent__total_users integer NOT NULL,
  f_agent__dim_time_id integer NOT NULL,
  .... other unnecessary columns
);

dim_time :

CREATE TABLE dim_time (
  dim_time__id bigserial NOT NULL,
  dim_time__date date NOT NULL,
  dim_time__month_start_date date NOT NULL,
  dim_time__week_start_date date NOT NULL,
  dim_time__quarter_start_date date NOT NULL,
  dim_time__year_start_date date NOT NULL,
  dim_time__guid uuid NOT NULL DEFAULT uuid_generate_v4(),
);

Now, f_agent__dim_time_id field in f_agent is FK referencing dim_time__id of dim_time.

My dim_time table has a date and its related week_start_date, month_start_date etc. For example: if dim_time__date is 14-07-2015 then dim_time__week_start_date will be 13-07-2015 (monday is start of week) and start_of_month is 01-07-2015 (always the first).

I have to find  f_agent__total_users from f_agent given a date range and a dimension (week / month / quarter).

What I've tried:

SELECT dim_time__week_start_date, f_agent__total_users
FROM f_agent
JOIN dim_time
    ON f_agent__dim_time_id = dim_time__id
WHERE dim_time__week_start_date IN (
    SELECT generate_series('2015-07-06', '2016-07-03', '7 day'::interval)
);

The output:

 dim_time__week_start_date   f_agent__total_users
"2015-07-13";                      3
"2015-07-13";                     33
"2015-08-10";                     12

But I only need the first value in case 2 week_start_dates are same. The output I am expecting here is:

 dim_time__week_start_date   f_agent__total_users
"2015-07-13";                      3
"2015-08-10";                     12

How can I do this?

The super quick fix will be to apply DISTINCT ON() to your query.

DISTINCT ON

Postgres allows the use of the DISTINCT qualifier, but allows it to be applied across a subset of your returned columns from your SELECT query.

In this case, you could simply rewrite your query as

SELECT DISTINCT ON(dim_time__week_start_date)
dim_time__week_start_date, f_agent__total_users
FROM f_agent
JOIN dim_time
ON f_agent__dim_time_id = dim_time__id
WHERE dim_time__week_start_date IN (
SELECT generate_series('2015-07-06', '2016-07-03', '7 day'::interval)
);

Refer to the Postgres documentation on DISTINCT ON within SELECT for more info.

A word of caution:  By applying DISTINCT ON, it will keep only the first row, regardless of how the order is returned by your query.  Unless you apply some ORDER BY criteria, you may end up with inconsistent results!

In effect, you could run the query once and get f_agent__total_users as 3, but run it later and get f_agent__total_users as 33.  I would highly recommend some additional predicate criteria to ensure proper and consistent results are returned.