texts: Retrieve XML of large plan from plan cache

texts:
I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results.  This is the query I'm using:

SELECT TOP 1
    size_in_bytes,
    query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY size_in_bytes DESC

I've tried it in SSMS and when I click on the XML in the results grid I get the Unable to show XML. Unexpected end of file when parsing Name has occurred.  Line 1, position 2097154. If I copy and paste the XML it is indeed truncated.

I've tried manually using SET TEXTSIZE 2147483647 just because, and that didn't do anything.  (I know that's the default value for the SQL Native Client providers, was just trying to force the issue).

I've tried running the statement via SQLCMD from a .sql file and outputting to a text file but that gives me even less.

I've tried using SQLCMD interactively, with :XML ON and got this error:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>

The plan is 157 MB.

Is there any way to get the full plan out so that I can see it?


texts: Is an AWS RDS postgres snapshot read consistent - the same as a consistent export in oracle? What happens to transactions in play?

texts: When a snapshot is requested, do all transactions in play get held open (not committed) during the duration of the snapshot creation - only writing the previously committed data to the snapshot?


texts: The difference between using parallelism "/*+parallel(4)*/" in the subquery or the outer query in terms of performance in Oracle

texts:
I have the below query :
select /*+parallel(4)*/ ---> First Location
       <Desc_1> Amount,
       <Desc_2> Amount,
       <Desc_3> Amount
from (select /*+parallel(4)*/ ---> Second Location
             <Column_1>,
             <Column_2>
      from <Example_Table>
      where <Conditions>
      )
pivot(sum(Column_1) Amount
for <Column_2> in (1 as <Desc_1>,
                   2 as <Desc_2>,
                   3 as <Desc_3>)
                   );

The point is that <Example_Table> is a very big table and I want to ask where is the best to location to use the /*+parallel(4)*/ ? Is it better to use it in theSubQuery or in the outer select? What if we use /*+parallel(4)*/ in both outer and inner select? Is it bad for performance?


texts: Optimization by replacing subquery with named subquery having INLINE hint

texts:
Let's have these two tables:

create table table_x(
  x_id varchar2(100) primary key
);

create table table_y(
  x_id varchar2(100) references table_x(x_id),
  stream varchar2(10),
  val_a number,
  val_b number
);

create index table_y_idx on table_y (x_id, stream);

Suppose we have millions of rows in each table and table_y contains from 0 to 10 rows per each x_id.

The queries in the following examples return 200 rows by filter substr(x_id, 2, 1) = 'B'.

It's required to optimize the query:

QUERY 1

select
    x.x_id,
    y.val_a,
    y.val_b
  from table_x x

  left join (select
                 x_id,
                 max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
                 max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
               from table_y
              group by x_id
   ) y on x.x_id = y.x_id

 where substr(x.x_id, 2, 1) = 'B'; -- intentionally not use the primary key filter

------
PLAN 1
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 1 |   HASH JOIN OUTER      |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 2 |    TABLE ACCESS FULL   | TABLE_X |   10000 |   120000 |   669 | 00:00:09 |
|   3 |    VIEW                |         |   10692 |  2437776 | 22029 | 00:04:25 |
|   4 |     SORT GROUP BY      |         |   10692 |   245916 | 22029 | 00:04:25 |
|   5 |      TABLE ACCESS FULL | TABLE_Y | 1069200 | 24591600 | 19359 | 00:03:53 |
----------------------------------------------------------------------------------
* 1 - access("X"."X_ID"="Y"."X_ID"(+))
* 2 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

There's a way of significant optimization, so QUERY 2 returns rows 2-3 times faster than QUERY 1. The INLINE hint is сritically important, because without it the second performs as slow as the first one.

QUERY 2

with
  table_y_total as (
    select --+ INLINE
        x_id,
        max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
        max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
      from table_y
     group by x_id
)
select
    x.x_id,
    (select val_a from table_y_total y where y.x_id = x.x_id) as val_a,
    (select val_b from table_y_total y where y.x_id = x.x_id) as val_b
  from table_x x
 where substr(x.x_id, 2, 1) = 'B';

------
PLAN 2
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 10000 | 120000 |  669 | 00:00:09 |
|   1 |   SORT GROUP BY NOSORT         |             |     1 |     19 |  103 | 00:00:02 |
|   2 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   1900 |  103 | 00:00:02 |
| * 3 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
|   4 |   SORT GROUP BY NOSORT         |             |     1 |     20 |  103 | 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   2000 |  103 | 00:00:02 |
| * 6 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
| * 7 |   TABLE ACCESS FULL            | TABLE_X     | 10000 | 120000 |  669 | 00:00:09 |
-----------------------------------------------------------------------------------------
* 3 - access("X_ID"=:B1)
* 6 - access("X_ID"=:B1)
* 7 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

Since the first query uses less code duplication I would prefer to keep it.

Is there a hint or another trick to meet the following conditions both?

keep the first query code (QUERY 1)
force optimizer use the second plan (PLAN 2)


texts:
I am really new at building databases. I created a table that reads from other tables. However, I needed to add another table and I placed the input field into the form.

My problem is that the form isn't reading any of the records already on the tables. How did this change and how do I fix it?

Now every time I enter a new record this message comes up.

  The Microsoft database engine can not find a record in the table "  " with key matching fields "  "


texts:
I have a table with data as

A   51 12.01pm
A   53 03.00pm
B   51 04.00pm
C   51 11.55am
C   51 12.00pm
C   53 12.05pm
D   53 03.00pm
D   51 04.00pm
E   53 05.00pm
E   53 05.02pm
F   51 03.21pm
F   51 09.32pm

and my output should be the count of data having the latest status 53 with the latest time

Output should be A and C


texts: Why does MySQL allow HAVING to use SELECT aliases?

texts:
In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate)
HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5
Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?


texts: Mongo takes too much memory when insert data

texts:
I'm using mongo as a database log. I store on it all the logs of an application, so I have a high rate of inserts, for example 100 inserts per second. To accomplish that I developed a part of the application dedicated to store in mongo the data. this app only uses a mongo connector.

But the memory consuption is incredible high, I mean after start to use mongo the heap memory has increased in about 300MB and the CPU takes over 90%. Anyone knows how I can decrease this memory consuption or why it is happening?. I've tried to use Mysql and the heap memory keeps normal (only is increased in 20MB) and Cpu doesn't increment as mongo situation.

The data to store is very simple.
in fact I used the following json for separate test with the same results.

{testData:'something i'}

Is there any way to create a datasource with mongo?


texts: Oracle JDBC TLS connection fails (handshake_failure) after OS upgrade

texts:
I have an Oracle Database 12.1 server running on Oracle Linux 6.

Secure (TLS) connections were working fine until I decided to update the underlying operating system (yum update) and reboot the server. A total of 62 packages were updated (yum update log) including the kernel and OpenSSL.

Now, secure connections, from the JDBC clients and from SQL*Plus fails with handshake_failure or ORA-28860.

I have a verbose TLSv1.2 log from my JDBC client but I can't figure out what is wrong with the TLS connection.

listener.ora and sqlnet.ora

sqlnet.ora and listener.ora do not contain SSL_CIPHER_SUITES

I am sure that nothing was changed or updated on the client (either JDBC or SQL*Plus). Few minutes before the upgrade/reboot, there was no problem establishing the TLS connection.

The server runs Oracle 12.1.0.1. The TLS connection issue occurs with all tested clients which previously succeeded in establishing a secure connection:

ojdbc8.jar 12.2.0.1
ojdbc7.jar 12.1.0.2
SQL*Plus 12.1.0.2.0.

The SQL*Plus trace file contains an ORA-28862 ("peer closed the connection") which may be more specific than the previous ORA-28860. I will try to get the same trace file on the server side.


texts: How do I determine what is activating my SQL service broker proc?

texts: I have several queues.  One of which has suddenly started acting oddly.  It activates repeatedly while nothing is in it's queue.  I even manually removed old conversations and this thing is still spinning up new spids to pull on the empty queue.  I can see in my logger that it runs the transaction to check the queue,  finds it to be empty, then closes.  A new spid will immediately appear and check the queue again even if nothing has changed.  Another weird thing is that it's only 1 spid at a time.  This Activation is set to allow 10 readers.


texts:
I queried an Extended Events session and returned the XML. I am confused on what frame level 1, 2, 3 mean.  As well as line, offsetStart and offsetEnd?
I am however able to convert the sql handle to the sp name.

The level seems to be in reverse order of how they are called.  So the object listed in level 3, calls the object in level 2, which calls the object in level 1.


texts:
I am creating ETL where I rely on the database to refuse duplicate values for a business key. In SQL Server this works like a charm. The database gives an error, the ETL ignores the error, continues and the next row gets inserted just fine. With PostgreSQL my UNIQUE constraint (or index, they show the same behaviour) blocks any further inserts. This makes this solution useless and PostgreSQL a less useful candidate for the datavault I'm creating for my client.

I find a solution with using a rule, but this turns out to be extremely slow. The performance is multiple times worse. This makes using a rule also no good way to have the database deal with doubles.

Is there any way to have PostgreSQL keep on inserting new rows after a UNIQUE constraint violation?


texts: get table and field names from union in execution plan

texts:
I'm trying to find performance issues on query which is based on other queries etc.

I found that I have a union query which is slow, but I have no idea which union query/column it is.

All I get in the execution plan are nodes like:

 <SeekPredicates>
                                              <SeekPredicateNew>
                                                <SeekKeys>
                                                  <Prefix ScanType="EQ">
                                                    <RangeColumns>
                                                      <ColumnReference Database="[mydb]" Schema="[dbo]" Table="[Sales]" Column="SaleID" />
                                                    </RangeColumns>
                                                    <RangeExpressions>
                                                      <ScalarOperator ScalarString="[Union1399]">
                                                        <Identifier>
                                                          <ColumnReference Column="Union1399" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </RangeExpressions>
                                                  </Prefix>
                                                </SeekKeys>
                                              </SeekPredicateNew>
                                            </SeekPredicates>

How can I know which fields/tables make up this "union1399"? So can index it or remove it or otherwise optimize the query.

Thanks!


texts:
I have searched in Google, in StackOverflow, I have tested many methods, but I really don't understand why the query is extremely slow.

I have a CSV file with this structure with 94K rows:

name;email;employer;city;state;born;active;group;

with two load data queries. One for group (with dummy columns and with name in the last) and other query to import the columns ignoring the last.

--- FIRST (It is running for 12 hours and did not finish yet.)
LOAD DATA INFILE 'C:/path/file.csv'
    REPLACE INTO TABLE group
    FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    (@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, name)
    SET id = NULL, active = 1;

--- SECOND (not begin because the first not ended)
LOAD DATA INFILE 'C:/path/file.csv'
    REPLACE INTO TABLE subscriber
    FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    (name, email, employer, city, state, born, active, @dummy)
    SET id = NULL;

Ok, I don't have the best computer, but the file not is too large.
I have a Windows 64bits with 4GB RAM, using XAMPP;

In my.ini I have this settings:

innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M

Please, can someone help me? Sorry my english and thank you!

Edit

CREATE TABLE group (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    active TINYINT(1) UNSIGNED DEFAULT "1",
    PRIMARY KEY (id),
    UNIQUE KEY name_UNIQUE (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC


texts:
I have MySQL Replication working between two servers in master-master mode.

Replication has been working from day 1.

The IO Thread and the SQL Thread has never died. However, the Slave SQL thread dies very, very often.

The log only says:

120913 17:58:50 [Note] Slave I/O thread killed while reading event
120913 17:58:50 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.004109',
    position 101146947
120913 17:58:50 [Note] Error reading relay log event: slave SQL thread was killed

I've Googled for the problem, but found no indication of what to look for or how to resolve the issue.

What could be causing the thread to die?


texts: What is a 'clean' way of getting the output required?

texts:
I need to aggregate the total for when the case when statement returns true. But I need to do this per partition. My data looks something like this:
Product|Variation_code|Total_product_variations|Discount_code
A      | x21          |          3             |     OFF
A      | x22          |          3             |     LIQ
A      | x23          |          3             |     NON
B      | x4           |          2             |     LIQ
B      | x9           |          2             |     LIQ
C      | x34          |          4             |
C      | X36          |          4             |     OFF
C      | x39          |          4             |     NON
C      | x37          |          4             |     OFF

I want the total count per product where Discount_code = 'LIQ'
I have tried CASE WHEN(Discount_code = 'LIQ' then 1 else 0 end) AS total_liq but it returns 1 or 0 in each row.
Similarly, COUNT(CASE WHEN Discount_code  = 'LIQ' THEN 1 ELSE 0 END)AS total_liq returns a bunch of single 1's like so:
Product|Variation_code|Total_product_variations|Discount_code | total_liq
A      | x21          |          3             |     LIQ      |    1
A      | x22          |          3             |     LIQ      |    1

There is a group by 1,2,3,4,5 at the end of each of these. How do I go about achieving this?
I can't roll variation_code into product or filter as I need to be able to see them later.


texts:
I have been replicating data from a table to 2 different locations and was just looking at sp_spaceused to see the size of the tables.  I have noticed that the replicated tables are smaller than the original table for the column Data.

As I understand it the Data column is the actual amount of data space used not including indexes so I was expecting these to be the same.  Can anyone advise why these are different?

Thanks


texts: Best approach to have a Live copy of a table in the same database

texts:
I have a table where 15,000 to 20,000 rows are being inserted every hour.

Table Schema is something like

ColumnName   DataType
ID           BIGINT IDENTITY(1,1)
Column1      INT
Column2      INT
Column3      DATETIME DEFAULT = GETDATE()

No row is ever update nor deleted. As you can imagine table grows huge in very short time.

The table is queried as well time to time. SELECT statements with filtering on DATETIME column and sometimes other filtering on INT column too.

To my knowledge, I have two options

indexing columns (Datetime, some int columns) to get better performance on select statements and get a performance hit on my INSERTS

OR

Keep indexing minimum, Primary key Clustered index and a Datetime index and keeping the inserts very fast and get a performance hit on selects.

Another option that was suggested to me, was to create another table, Populate that table from this current table and index the living hell out of it to help all the possible select queries. Read data from this duplicated table.

Keep the original table as it is with minimum indexing, for quick inserts.

Since I am duplicating the data, I know this option violates the basic rules of normilazation, but this sounds like a good option for keeping inserts and reads as fast as possible.

The problem is how can I maintain this near real time copy of this table inside the same database.

I do not want to use any After Insert triggers as this will end up firing 15,000 to 20,000 times an hour.

What other options I have to keep this near real time copy of table in the same database??? Or maybe another approach altogether, any suggestion or pointers in the right direction are much appreciated.


texts:
I'm investigating the big switch from MySQL to a NoSQL DBaaS and I've run into an issue trying to forecast expenses. Essentially, I can't figure out how many queries my current MySQL server handles per day to try and estimate the number of requests I'll be using with Cloudant, which charges $0.015 per 100 PUTs, POSTs, and DELETEs and $0.015 per 500 GETs and HEADs.

I've found a lot of information about using SHOW STATUS and SHOW GLOBAL STATUS to get the stats that MySQL collects on itself, but there's no timeframe reference.

For instance, SHOW GLOBAL STATUS returns the following:

Queries                           | 13576675

Which is great, except I have no idea the timeframe that wraps that number. 13 million queries when? Per month? Year? Since the beginning of time?

The MySQL docs don't really elaborate too much:

  Queries

  The number of statements executed by the server. This variable includes statements executed within stored programs, unlike
  the Questions variable. It does not count COM_PING or COM_STATISTICS
  commands. This variable was added in MySQL 5.0.76.

Thanks in advance for any help.


texts: Gotchas converting latin1_swedish_ci to utf8mb4_unicode_ci

texts:
I have a MySQL 5.5.31 database which has approx 220 tables - of these 220 tables, around half of them are already using utf8mb4_unicode_ci but the "older" tables are still using latin1_swedish_ci. The fields in the tables are a mix of integer, varchar, longtext, date, datetime and decimal and there are no views or stored procedures.

I now have a requirement to support Russian text so I need to convert the remaining tables to UTF8. Based on my reading, I have opted for utf8mb4_unicode_ci as I believe it offers the broadest support including Chinese.

I have tested as much as I can with individual tables in a test environment without any issues and now need to bite the bullet and do this on my production database - I will, of course, take a full backup just before I make any changes.

I have read of some issues with converting TEXT and CHAR fields as well as some considerations on index sizes for VARCHAR fields but nothing else significant.

I know how to do this but I wanted a final sanity check to make sure there is nothing major I am missing before taking the plunge?


texts: Why can the target of the OUTPUT INTO clause not be a view?

texts:
Here is my statement:

DELETE FROM table1
OUTPUT deleted.col1, deleted.col2
INTO view1;

It gives me this error:

The target 'view1' of the OUTPUT INTO clause cannot be a view or common table expression.

The view is simple and updatable. Why can I not insert into an updatable view using the OUTPUT clause?


texts: High CPU in SQL Server supporting SharePoint databases while running FAST search crawl

texts:
I have a a SQL Server 2012 (SP1) #cu5 (X64) instance used as SharePoint 2010 back end and for the most part use only 10 - 30% CPU but there is a high CPU usage when running FAST search crawl(both incremental and full ).

Following Microsoft recommendation for SharePoint SQL instances Auto Create and Auto Update Statistics are turned off and SharePoint is supposed to take care of statistics and index maintenance using health analyzer rules and timer jobs. For the most part it does ok job but up on investigating the queries creating high CPU usage I found out there are missing statistics but i cant create them since modifying SharePoint databases will make them unsupported.

Please let me know if you have been in a similar situation and any helpful advice will be appriciated. Also let me know if you think https://sharepoint.stackexchange.com/ is the best place to post this instead of DBA SE.

SQL Server Environment:
HP ProLiant DL385p Gen8, Microsoft Windows Server 2008 R2 Enterprise SP1, AMD Opteron(tm) Processor 6204(single Socket, 2 Core, HT enabled)
Microsoft SQL Server 2012 (SP1) #cu5 (X64)

SharePoint Environment(I don't have much detail on this right now):
SP 2010 SP2(2 app server, 2 Web server, 1 FAST search server)


texts: Combine Results in Same Column to get Average

texts:
Lets say I have a table that looks like the following:

Average Score
Team
Month

2
New York Team
May

3
San Fran Team
May

2
Chi Team
May

3
Texas Team
May

2
New York Team
June

3
San Fran Team
June

2
Chi Team
June

3
Texas Team
June

I want to get the average for certain combinations of teams. How would I get the average of New York Team + San Fran team in May, and the average for the Chi team + Texas team?
Basically I want New York + San Fran to = Tier 1 (average)
and Chi + Texas to = Tier 2 (average)
Thanks!


texts: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '=' and why?

texts:
Hello I got error in mariadb. In my command  two columns have different collate. one is utf8_unicode_ci (a) other is utf8_turkish_ci (b). Why I got error when I compare these columns ( where a = b )  both of them utf8.
Error:
    Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '='


texts: Transactional replication failure handling

texts:
Web Application is High school record management system (HSRMS). Users of the application are

High school management (Normal Users)
Data verifying users (Admin)

User group 2 will allow the User group 1 to submit data periodically and then user group 2 will validate it and ask for corrections. Hope the system flow is quite clear.

Replication Plan (Proposed)

I have setup a proposed transactional replication as

Publisher (Server A)
Distributor (Server B)
Subscriber (Server(s) i.e C,D,E etc).

Note: All the servers will be in the same area/location.

What I understood is that the publisher should be the database server which should be connected to the HSRMS application, the distributor contains the transactions(updates) and is responsible to carry forward to subscribers, but I need to record a server status on any agent which should notify the sysadmin to update their DB server address in case of server failure (due to any reason) and the subscriber must be responding meanwhile.

Is my approach and understanding correct or I need to dive deep into it?
Is there any mechanism to keep the application server aware of the failure and act accordingly?
Dynamic Server connection upon failure of any db server? I.e Instead of sys admin the server connection should be automatically mapped to the active running db server?
What could be the role of subscribers if the application is just connected to the Server A i.e Publication for updates? Are subscribers there only for the DB backup and will not interact with the web application?


texts: Representation of circular references constraint

texts:
We have a database where we store commands and properties for devices that may be controlled via a network.

For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains commands and properties.

We have the ability to get values of the properties of devices by analyzing responses on commands; therefore there is a relationship between commands and properties. A problem arises when we trying to link commands from one DeviceType to properties from another DeviceType - this kind of link is useless and confusing.

How can I prevent this 'circular reference' through integrity constraints?

Here is a SqlFiddle for my scenario.


texts: Correlated subquery with common table expressions in SQLite

texts:
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers out of 0 - 999, and count how many lucky numbers are drawn.
CREATE TABLE IF NOT EXISTS natural_numbers AS
WITH RECURSIVE natural_numbers(x) AS (
    SELECT 0
    UNION
    SELECT x + 1
    FROM natural_numbers
    LIMIT 1000
)
SELECT x
FROM natural_numbers;

.eqp   ON
.timer ON

WITH lucky_numbers(lucky_number) AS (
    SELECT x
    FROM natural_numbers
    ORDER BY RANDOM()
    LIMIT 5
)
SELECT SUM(EXISTS(
        SELECT 1
        FROM lucky_numbers
        WHERE x = lucky_number
))
FROM natural_numbers;

WITH lucky_numbers(lucky_number) AS (
    SELECT x
    FROM natural_numbers
    ORDER BY RANDOM()
    LIMIT 5
)
SELECT SUM(x IN lucky_numbers)
FROM natural_numbers;

The output is
QUERY PLAN
|--SCAN TABLE natural_numbers
`--CORRELATED SCALAR SUBQUERY 2
   |--CO-ROUTINE 1
   |  |--SCAN TABLE natural_numbers
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--SCAN SUBQUERY 1
4
Run Time: real 0.095 user 0.094819 sys 0.000000
QUERY PLAN
|--SCAN TABLE natural_numbers
`--LIST SUBQUERY 2
   |--SCAN TABLE natural_numbers
   `--USE TEMP B-TREE FOR ORDER BY
5
Run Time: real 0.001 user 0.000289 sys 0.000007

It seems that EXISTS re-executes the common table expression lucky_numbers for each x (and is consequently much slower), whereas IN only executes it once for the whole table (the result is always 5, no more, no less). This is kind of surprising because common table expressions are supposed to be a view instead of a temporary table, so I would expect the behavior of EXISTS.

To complicate things further, if you replace LIMIT 5 with LIMIT X where X >= 8 in the EXISTS clause, its execution plan would become
QUERY PLAN
|--SCAN TABLE natural_numbers
`--CORRELATED SCALAR SUBQUERY 2
   |--CO-ROUTINE 1
   |  |--SCAN TABLE natural_numbers
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (lucky_number=?)

and the result will always be X instead of a random variable for X <= 7. I suspect this is because the common table expression lucky_numbers is no longer re-run for each row, but instead cached in the AUTOMATIC COVERING INDEX. In other words, the query planner is changing the result of our query here!

So, when are common table expressions run for each row / the whole table? Looks like with IN it's guaranteed to run only once for the whole table (which is somehow inconsistent with the document's words that an ordinary common table expression is essentially a pre-packaged SELECT statement), but with EXISTS things are more complicated and the decision is left to the query planner.


texts: mariadb slow to response due to high query

texts:
i have a g7 server that use as database server
and i installed window server 2016 and maria db version 10.1.39-MariaDB on it

my hardware have 2 CPU  Intel(R) Xeon(R) CPU E5620 @ 2.40GHz and 64 gig of ram and 4*128 gig hp ssd 6gbps raid 10

but when my load is going up to 1000 query per second it slow down and crashed..
all my tables are innodb and i have 500-600 mil record and db size is about 40-50 gig

my config is

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
innodb_file_per_table=1
max_connection=4000

table_open_cache = 1536
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 256k
myisam_sort_buffer_size = 64M
thread_cache_size = 256
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12

max_allowed_packet=16M

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

#innodb_dedicated_server = true

innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 24

which of my config is wrong and how can i get the best performance!?

update:when database stoped ram usage is 4-5 gig and when database in load ram go up to 40-45 gig,also my used my connection is about 3500 connection..


texts: How to do incremental backup every hour in Postgres?

texts:
Trying to do an hourly incremental backup of a single Postgres server (Win7 64).
I have the following setup in postgresql.conf:
max_wal_senders = 2
wal_level       = archive
archive_mode    = on
archive_command = 'copy "%p" "c:\\postgres\\foo\\%f"'

(restart)
I did a base backup with pg_basebackup -U postgres -D ..\foo -F t -x
Which made a big base.tar file in the foo folder and added some 16,384 KB files, which I assume are WALs.
What I don't understand is why the WALs in foo don't change. The WALs in data/pg_xlog change. Is pg not supposed to copy them over? How does it decide to do so?
Perhaps I need to set archive_timeout=3600 ?
I've seen several sites (pg's mailing lists, bacula's postgres page) that say you need to call pg_start_backup() and pg_stop_backup(), but I believe that those are not required. Is that true?
Secondary questions:

How often do the WALs in data/pg_xlog get written? What triggers a write?
It seems to update a WAL if I do some DML then \q in psql. Or edit a table in pgAdmin then close the window. I figured it would write on commit.

Best practices? pg_basebackup once a week? Archive WALs to same machine as PG or a remote machine?


texts: Migration from Cassandra 2.x to 3.x: schema_keyspaces table is gone. How do you query system keyspaces?

texts:
I just migrated from Cassandra 2.x to 3.x (specifically, 3.4). Now the system tables have completely changed, and these changes are not in the documentation. If you look at the current CQL docs for system tables, they only describe the system schema as it was in 2.X.

CQL 3.3 Docs for Cassandra 2.X and greater

Does anyone out there have access docs that detail this change? How do I query to see what keyspaces are available?

There is a new system keyspace called "system_schema", but there are only two tables in it: system_schema.tables and system_schema.columns. So I'm at a loss as to where to look for the information I need (I need to be able to effectively do a DESCRIBE KEYSPACES command, but outside of cqlsh).

Is the answer this: SELECT keyspace_name FROM system_schema.tables; the new way to do this?


texts:
I have Oracle 10g XE database locally. I export schema with something like

exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp

Exp is 10.2.0.1.0 version - so is the DB.

Than I take this dump to another machine and try to import it to another remote machine. With

imp %USER%/%PASSWD%/'192.168.0.100/%SID%' ignore=y file=dump.dmp full=y

Oracle client with imp is version 11.2.0.1.0 and Oracle DB (normal, not express) is version 11.2.0.2.0

The problem is that after printing several messages about importing tables it suddenly stops:

. . importing table                      "TABLE_1"      17180 rows imported
. . importing table                      "TABLE_2"          0 rows imported
. . importing table                      "TABLE_3"         49 rows imported$:

and does not even give a newline symbol in the end.

The problem persists with rows=N and with only selected tables from this schema.

So can anybody tell:

Where can be some error logs to know what is wrong?
Some workaround (still using exp/imp utilities preferably) - we need to import dumps regularly (it is for continuous integration server), we need to do it from remote host (so impdp is hardly an option with need to mount so remote volumes), dependency on other DB is highly undesirable (dblink).

UPD: Unfortunately for all curious we have solved the problem switching continuous integration system to Oracle XE, where is no such problem. Sysadm has already switched off this server, so I cannot try anything. Sorry.


texts: What is the best way to execute a MySQL script periodically on Linux

texts:
I want to execute some SQL everyday in a Linux server using MySQL to UPDATE some column's values. In order to do that I use the UPDATE ... SET ... WHERE ... statement.

I want to know which is the best way to do that update automatically?

Is there any method apart from using the cron daemon?


texts: How to select from 3 tables while using count on 1, and sum on another?

texts:
I'm stuck on this one query. Here is my setup:

table 1 (shows)

show_user
show_count

table 2 (views)

view_user
view_views

table 3 (users)

user
user_name

What I need to is produce results that contain:

sum(show_count), count(view_views), user_name

So far I have tried the following:

select sum(s.show_count), count(v.view_views), u.user_name
from users u
left join shows s on u.user = s.show_user
left join views v on u.user = v.show_user
group by u.user

The query is taking an eternity to run and returns neither the right count nor the sum.

Individually, the following queries work properly:

select sum(show_count), show_user from shows group by show_user;
select count(view_view), view_user from views group by view_user;


texts: How to minimize log operations in SQL Server to avoid "log full" error

texts:
I have a database in production which constantly get log file filled, its a data warehouse and have many jobs/queries running for obvious. Below is the error which I am getting

Msg 9002, Level 17, State 4, Line 7
The transaction log for database  is full due to 'ACTIVE_TRANSACTION'.

Now this make sense and I understand that SQL can not perform action cause its log file is filled up. I have two log files

D drive with unrestricted growth and autogrowth enabled [D drive size 180 GB]
E drive with static size  [E drive 120GB, log file size: 20 GB]

I did some research about this issue and found of possible solution to it: Source

Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.

Now, assuming that I have limited space (i.e. is 180 GB + 20 GB) which I believe is good enough for a database in SIMPLE RECOVERY MODE. How possibly can I identify this issue and do a rectification before it occurs?

Replication:

I have tried to replicate this scenario via creating new sample database with below setting

and below query to get million rows and insert them into table

SET NOCOUNT ON;
DECLARE @SET_SIZE INT = 500000000;

CREATE TABLE dbo.Test500Million (N INT  PRIMARY KEY CLUSTERED NOT NULL);

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SET_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9)
INSERT INTO dbo.Test500Million(N)
SELECT N FROM NUMS;

DROP TABLE dbo.Test500Million

Now, while validating sys.databases and looking on error message I am finding both CHECKPOINTS & ACTIVE_TRANSACTION.

My Questions:

On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE  & not for SELECT statement in any case. Am I correct?
How can I minimize logging while inserting updating records? I already have SIMPLE RECOVERY MODE.
I have found that average throughput during this period went from 25 MB approx. to 1.2 MB. What does it mean?
Is there any other way to troubleshoot this issue, other than increasing disk space?
If only option is to shrink a file, then when should I do it? Can I do it when any active transaction is ON? [Production environment]

Kindly let me know if you need more stats which I can collect.


texts: How to restrict deletion of specific row of SQL server table

texts:
I want to restrict deletion of specific row. For example if someone wants to truncate a table or delete all the records from table then an error message should be thrown, for particular id it is not allowed. I have used trigger for the same but it is of no use, as it does not fire at truncate

Create TRIGGER vendorTrigger
    ON [Vendor]
    INSTEAD OF DELETE
AS
    IF EXISTS(SELECT * FROM Vendor WHERE VendorId = 515)
    BEGIN
        RAISERROR ('Is not allowed to delete VendorId: 515 as it is of type corpnet',16, 1)
    END
    ELSE
    BEGIN
       Delete from Vendor Where VendorId in (Select VendorId From Vendor)
    END


texts:
I am running MS Sql Server 2008 on a Windows Server 2008 box. I noticed a slow down and random moments where my Sql instance failed to connect, and looking at the event log showed repeated (1-2 times per second) attempts from an IP address in China to access my "sa" account.

I added the IP address to the block list in my firewall, but the next day the IP address changed and the attack resumed.

Is there any way to automatically add an IP address to my block list if it attempts multiple failed logins? I cannot create a white list, because there are valid connections that come from unknown IPs.


texts: How to detect if a postgresql gin index is bloated

texts:
For btree indices, the pgstattuple extension provides the avg_leaf_density in pgstatindex to assess the 'bloatedness' of an index, i.e. the approximate ratio of the current size and the size it would have after a reindex.

The pgstattuple extension provides a pgstatginindex function, but neither of the returned values seem to be relevant to 'bloatedness'.

Is there a somewhat quick way to detect if a reindex would be useful?


texts: What type of database would you suggest for a large scale delivery company? RDBMS or NoSQL?

texts:
I've been learning the fundamentals of RDBMS and NoSQL systems. To start I thought NoSQL would be the way to go because of how large scale it could become, but understanding the importance of RDBMS ACID properties I'm now thinking this would correct because of regular customer transactions.
Can anyone give me any food for thought? I assume large companies may even use both types for different purposes


texts:
I'm getting the following error as I'm trying to restore from a differential backup:

  System.Data.SqlClient.SqlError: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.

We are using SQL Server 2008 R2 with 2 small databases (A:Production, B:Testing), in full recovery mode.

My daily backups are:

Full nightly backup that runs at 6 PM.
Hourly differential backup during the day.
Log backups every 30 minutes during business hours.

I usually restore my testing database from the latest full backup using Management Studio, but today we discovered that some of the reports are not working correctly, so I'm trying to check what happened exactly yesterday during business hours so I tried the differential backup for the first time.

I got the following error:

  the log or differential backup cannot be restored because no files are ready to roll forward

Then I manually backed up the Testing database, restored it using this same manual full backup (since the one from last night's production is not working), with Replace and NoRecovery options, then I tried restoring the differential from yesterday morning, but I get the following error about paths:

  This backup set cannot be applied because it is on a recovery path that is inconsistent with the database...

Any advice on what am I doing wrong or how can I keep restoring from all the differentials until the end of the day?


texts: Tools for working with xtrabackup files?

texts:
I have database backups in xtrabackup format. I would like to load them into hive without restoring to a mysql instance and use sqoop to then do the transfer.

If there are libraries to work with the format I can probably write my own utility to load the data into hive. I'm also more than happy to receive other suggestions on how to do that.


texts:
I need to purge binary logs from mysql version 4. I have checked the manual found that the syntax is like an advanced versions:

PURGE MASTER LOGS TO mysql-bin.xxx

However, when i use this command, i get the following error:

ERROR 1064 (00000): You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'BINARY LOGS TO 'mysql-bin.xxx'' at line 1

Please avdise.


texts:
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM
    (
        SELECT number
        FROM master..spt_values
        WHERE
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

Per the blog post, the rationale given is

  Numbers tables are truly invaluable. I use them all of the time for string manipulation, simulating window functions, populating test tables with lots of data, eliminating cursor logic, and many other tasks that would be incredibly difficult without them.

But I don't understand what those uses are, exactly -- can you provide some compelling, specific examples of where a "numbers table" saves you a ton of work in SQL Server -- and why we should have them?


texts: Classification of strict two phase locking

texts:
According to the definition of strict two-phase locking:
All exclusive hold locks must be held until the transactions commit.
But if we have transactions that deal with only shared locks, and if the basic definition of two-phase locking is satisfied, can we say that the schedule is strict two-phase?


texts:
Migrating on-prem MySQL 5.5 databases  to AWS RDS MySQL 5.5

Background Information:

I have a MySQL 5.5 dump. I have migrated the same dump to an EC2 instance and it worked. I did restore to EC2 instance to make sure it works at least in EC2.

I tried to migrate our  on-prem MySQL 5.5 databases (from a DB dump) to AWS MySQL RDS  5.5 which does not work (throws some error while migrating)

enter code here error 1153 (08S01) at line 12764: Got a packet bigger than 'max_allowed_packet' bytes aws

By default this parameter is set to 1024-1073741824 range in AWS. I have set the max_allowed_packet to 1073741824 on the mysql server side and yet I see the same above mentioned error. So, I edited the parameter group that I have created with parameter value of 1048576 for max_allowed_packet yet I see the same error.

I tried to pass that particular parameter also like this from the MySQL client but no luck:

mysql  -u user_name -p -P 3306 -h hostname < mysqldump.sql

mysql --max-allowed-packet=1048576 -u user_name -p -P 3306 -h hostname < mysqldump.sql

AWS MySQL 5.5:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.01 sec)

AWS MySQL 5.6:

mysql>  SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

Note that  both AWS RDS instance have the same  size RDS instance (DB instance class: db.m4.xlarge )

To restore AWS RDS MySQL 5.6 I did not change anything. It worked smoothly.

What is causing this issue? Now I am out of idea?
Any help is appreciated Thanks

My parameter group looks like this

AWS RDS parameter groups


texts: Performance test against identical tables (including data) with very different results

texts:
I created 2 identical schemas and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes from their primary keys.

Before adding the non-clustered indexes to the tables, I ran the performance tests to make sure the results would currently be equal. My results were like this:

Queries against Table To Be Indexed:
  Average: 97
  Min/Max: 33 to 338
  Standard Deviation: 48.69
Queries against Table To Not Be Indexed:
  Average: 57
  Min/Max: 32 to 317
  Standard Deviation: 16.43

According to the P-Test, this is P < 0.0001 so the differences are significant.

I cannot think of any reason that these metrics would be so different from each other. Here are the table definitions:

CREATE TABLE Flower
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FlowerName VARCHAR(30) NOT NULL,
    UnitPrice DECIMAL(4, 2) NOT NULL
);

CREATE TABLE Sale
(
    Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    SaleDate DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE LineItem
(
    Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    SaleId INT NOT NULL,
    FlowerId INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_Sale FOREIGN KEY (SaleId) REFERENCES Sale(Id),
    CONSTRAINT FK_Flower FOREIGN KEY (FlowerId) REFERENCES Flower(Id)
);

-- To be indexed:
CREATE TABLE iFlower
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FlowerName VARCHAR(30) NOT NULL,
    UnitPrice DECIMAL(4, 2) NOT NULL
);

CREATE TABLE iSale
(
    Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    SaleDate DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE iLineItem
(
    Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    SaleId INT NOT NULL,
    FlowerId INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_iSale FOREIGN KEY (SaleId) REFERENCES iSale(Id),
    CONSTRAINT FK_iFlower FOREIGN KEY (FlowerId) REFERENCES iFlower(Id)
);

I then run a stored procedure to make up a bunch of data for the not-to-be-indexed tables, then run this to make the data in the to-be-indexed table the same:

-- Replicating data between tables
SET IDENTITY_INSERT dbo.iSale ON;
INSERT INTO iSale (Id, SaleDate)
SELECT Id, SaleDate FROM Sale;
SET IDENTITY_INSERT dbo.iSale OFF;

SET IDENTITY_INSERT dbo.iLineItem ON;
INSERT INTO iLineItem (Id, SaleId, FlowerId, Quantity)
SELECT Id, SaleId, FlowerId, Quantity FROM LineItem
SET IDENTITY_INSERT dbo.iLineItem OFF;

And then finally, I run the jmeter tests with 2000 users, 100 ramp-up seconds, and 10 loops. The thread group has 2 JDBC requests (have tried making separate thread groups for each query too) with the following queries:

-- JDBC Request: Query against non indexed tables:
SELECT
    s.Id AS SaleId,
    li.Quantity AS Quantity,
    f.FlowerName + 's' AS Flowers,
    li.Quantity * f.UnitPrice AS LineItemPrice,
    s.SaleDate AS SaleDate
FROM Sale s
JOIN LineItem li
ON li.SaleId = s.Id
JOIN Flower f
ON f.Id = li.FlowerId
WHERE s.SaleDate BETWEEN '2019-03-01' AND '2019-04-01'
ORDER BY s.SaleDate, s.Id;

-- JDBC Request: Query against indexed tables (not indexed yet)
SELECT
    s.Id AS SaleId,
    li.Quantity AS Quantity,
    f.FlowerName + 's' AS Flowers,
    li.Quantity * f.UnitPrice AS LineItemPrice,
    s.SaleDate AS SaleDate
FROM iSale s
JOIN iLineItem li
ON li.SaleId = s.Id
JOIN iFlower f
ON f.Id = li.FlowerId
WHERE s.SaleDate BETWEEN '2019-03-01' AND '2019-04-01'
ORDER BY s.SaleDate, s.Id;

Is it immediately obvious to anyone why the "non indexed" queries are running consistently faster than the "to be indexed" ones? I even deleted and rebuilt the database from scratch just in case I did add some indexes and forgot about it (even though I checked and there were no non-clustered indexes in the database).

note The "similar question" thing above is telling me to look at this question: Identical tables, identical query, completely different execution times but it is not the same.


texts: Filter results based on data from another table

texts:
I have 3 three tables, products, discounts, and discounted_products:
products stores title, desc, price and etc information about products;
discounts stores discounts' start date, end date, and status;
discounted_products stores product_id (FK), discount_id (FK), discount type (percentage, static), discount amount, and status.

I need to filter and sort products by price ascending order, let's say greater than 100 and less than 150. But before querying the discount amount should be taken into consideration. Only some products have a discount, not all of them. How should I do it effectively?

I guess, first of all, active discounts should be extracted:

SELECT `dicount_id` WHERE CURDATE() > `start_date` AND CURDATE() < `end_date` AND `status` = 2

But after that I have no idea what to do.

Edit: detailed explanation

Let me graph it quickly:

Products table:

id: 1   name: A  price: 200
id: 2   name: B  price: 110
id: 3   name: C  price: 130
id: 4   name: D  price: 180
id: 5   name: E  price: 145

Discounts table:

id: 1   name: DA start_date: 2018-09-15 end_date: 2018-09-30    status: 2
id: 2   name: DB start_date: 2018-09-20 end_date: 2018-10-01    status: 2
id: 3   name: DB start_date: 2018-09-10 end_date: 2018-09-20    status: 2

Discounted_products table:

id: 1   discount_id: 1  product_id: 1 type: 1 (percent) amount: 30 status: 2
id: 2   discount_id: 1  product_id: 2 type: 2 (fix) amount: 45 status: 2
id: 3   discount_id: 2  product_id: 4 type: 2 (fix) amount: 60 status: 2

I wanna query products in 100 - 150 price range.
If there would be no active discounts it will return: from products table: 2, 3 and 5 (both in range 100 - 150)
Since we have two active discounts: from discounts table id : 1 and 2

new price table will look like:

id: 1   name: A  price: 140 => 200 - 200*30%
id: 2   name: B  price: 75  => 110 - 45
id: 3   name: C  price: 130
id: 4   name: D  price: 120 => 180 - 60
id: 5   name: E  price: 145

New result should be: 4, 3, 1, 5


texts: only use extended join-clause if some variable is TRUE

texts:
I am working with Postgis (extension to postgres) where there are several different geometry-types, i.e. 'polygons', 'points' 'linestrings', and multi-variants of these.
I am trying to create a function which should only use extra join-clauses if the geometry-type is a (multi)-polygon.
in all cases, with all geometrytypes, the join should look like this:
--drop function if exists test_return(geometry, double precision);
CREATE OR REPLACE FUNCTION test_return(
      PAR_geom                      geometry
     ,PAR_tolerance                 double precision DEFAULT 0.0000001
) RETURNS setof geometry
AS $$
DECLARE
    VAR_main_id int;
BEGIN

CASE ST_GeometryType(PAR_geom)
WHEN 'ST_LineString'  THEN
    VAR_main_id = 0;
WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
    VAR_main_id = 1;
WHEN 'ST_MultiPolygon' THEN
    VAR_main_id = 2;
ELSE
    raise EXCEPTION 'this function does not work on single-points';
END CASE;

return query
with repeated_pts as (
select (st_dumppoints(PAR_geom)).path, (st_dumppoints(PAR_geom)).geom
where st_npoints(st_snaptogrid(PAR_geom, PAR_tolerance)) <> st_npoints(PAR_geom)
)

select b.geom from
repeated_pts a
inner join
repeated_pts b
on

--- Here is how it should behave for all geometry-types---
a.path[1:VAR_main_id] = b.path[1:VAR_main_id]
and
a.path[array_upper(a.path, 1)]< b.path[array_upper(b.path, 1)]
and
st_dwithin(a.geom, b.geom, PAR_tolerance);
    END;
$$
LANGUAGE plpgsql;

In case its a (multi)-polygon, i want to extend the join-clause with additional join-clauses:
and

(
(
a.path[VAR_main_id] = 1 and
a.path[array_upper(a.path, 1)] > 1 and
b.path[array_upper(b.path, 1)] > 1
)
or
(
a.path[VAR_main_id] > 1 and
b.path[array_upper(b.path, 1)] <= 4
)
)

Is there a smart way to do this?
I know its possible to insert the query into a case-when-then structure like this:
case
when st_geometrytype(PAR_geom) in ('ST_MultiPolygon', 'ST_Polygon')
then --- run query with additional join-clause --

else
-- run query WITHOUT the extra join-clause --
end case;

however, this structure requires I write the first part of the query twice - is there a way to avoid this?


texts:
So normally we have 20-85 Page splits/s but sometimes this spikes up the 700 - 2500 range.

So to figure out what was happening i created a Extended Event with the following:

CREATE EVENT SESSION MonitorPageSplits ON SERVER
ADD EVENT sqlserver.page_split
(
   ACTION (sqlserver.database_id, sqlserver.sql_text)
)
ADD TARGET package0.asynchronous_file_target
(
   SET
       filename = N'***\MonitorPageSplits.etx',
       metadatafile = N'***\MonitorPageSplits.mta'
);
GO

ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start;
GO
--Wait until we notice a page split and run code below!
ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = STOP;
GO

Then to get the data i used the following query

SELECT
   split.value('(/event/action[@name=''database_id'']/value)[1]','int') AS [database_id],
   split.value('(/event/data[@name=''file_id'']/value)[1]','int') AS [file_id],
   split.value('(/event/data[@name=''page_id'']/value)[1]','int') AS [page_id],
   split.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') AS [event_time],
   split.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text]
FROM
   (
       SELECT CAST(event_data AS XML) AS split
       FROM sys.fn_xe_file_target_read_file('***\MonitorPageSplits_0_131317886982910000.etx', '***\MonitorPageSplits_0_131317886982910000.mta', NULL, NULL)
   ) AS t
ORDER BY [event_time]
GO

Now plotting the data in excel diagram gave the following

So then i started to look at the data around 2017-02-17 10:14:39 as the first spike then.
Which resulted in the following data:

database_id file_id page_id event_time              sql_text
7           0       0       2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.603 Unable to retrieve SQL text
7           1       26536   2017-02-17 10:14:39.607 Unable to retrieve SQL text
7           1       37769   2017-02-17 10:14:39.607 Unable to retrieve SQL text
.....

So then i used the following DBCC TRACEON (3604);dbcc page (7, 1, 26536, 1);DBCC TRACEOFF (3604); and that gave me Metadata: ObjectId and with that i could get the table.

But how do i figure out what caused the page split spike?

Also database_id 7 is the main application on the server and file_id 1 is the Data file of the database.

Some more analysis:

3 tables are involved

2 small tables that are involved in one page split each.

The rest of page splits are caused in the table that has the most data and most inserts/updates in the application.

Am i worrying for nothing is it normal to have page splits like this?


texts: Oracle USE_DB_RECOVERY_FILE_DEST filesystem path

texts:
As stated in oracle doc

  The keyword USE_DB_RECOVERY_FILE_DEST to indicate the Fast Recovery Area

I know it's setted during dbca but how can identify the Fast Recovery Area filesystem path on a running Oracle12c instance?


texts: Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

texts:
I have a poorly performing query:

SELECT  user_id, count(item_id) as count
FROM table items
WHERE category = 'magazine'
AND created_at > 1384754400
GROUP BY user_id
ORDER BY count(item_id) desc
LIMIT 100

Whats the optimal indexing strategy in order to optimize this query?

Table Details

500million records with the following structure / cardinalities:

PRIMARY KEY (item_id) - cardinality: 500 M
user_id - cardinality: ~ 25 M
category - cardinality: ~ 2.5 M
created_at - cardinality: ~ 150 M

Indexing:

I have individual indexes on each the user_id, category and created_at fields

I also have the following covering indexes:

(category, user_id) - this is the one the query optimizer defaults to when running explain
(category, created_at)
(category, created_at, user_id) - this is one I attempted to create in order to optimize this query, however, it doesn't seem to be working very well.


texts: Audit MariaDB Which User Account Privileges Are Being Used

texts:
I'm looking to improve the security of my MariaDB installation by better auditing the user accounts and the privileges of each user account.

This database is being provided as a service out to many other users within the enterprise, and in some cases people leave the company or didn't actually need the level of privileges they requested. I'd like to audit the user accounts and see when they last logged in (to make sure users are logging in at least once every 90 days) and auditing the privileges their account has been granted.

Specifically, if possible, I'd like to look which privileges haven't been used within the past 90 days so that I can either alert our service team or (in the future) automatically revoke privileges which are not being used (with the exception of some of the disaster recovery accounts or security teams).

Any guidance on how to proceed with identifying account usage either in the general account access or in the more detailed privilege usage would be greatly appreciated.


texts: Linking tables to extract value based on closest previous date

texts:
I'm going to use an example that was previously posted but modify it to match my problem:

TOCs bu Date:

Location StatDate TOC
WC1  9/1/1900    5600
WC1  10/1/2010   5605
WC2  8/2/1980    4300
WC2  9/5/2005    4280
WC2  10/6/2011   4285

Water Levels by date:

Location  MeasurementDate DepthToWater
WC1         7/26/2006    5.5
WC1         9/30/2008    5
WC1         10/5/2010    6
WC2         7/30/2010    8
WC2         11/3/2015    8.5

Desired result:

Location MeasurementDate  DepthToWater   TOC    Elevation(TOC-DepthToWater)
WC1         7/26/2006    5.5         5600      5594.5
WC1         9/30/2008    5           5600      5595
WC1         3/5/2015     6           5605      5599
WC2         7/30/2010    8           4280      4272
WC2         11/3/2015    8.5         4285      4276.5

I modified the proposed solution that was previously posted, but that solution didn't JOIN the 'location' fields, and I couldn't get it to work anyway.
I would GREATLY appreciate some help with this.


texts: How come spatial objects can't be used in a VALUES statement?

texts:
How come you can do,

SELECT *
FROM (VALUES
  ('foo')  -- or (1), etc.
) AS t(x);

But, not

SELECT *
FROM ( VALUES
  ( geography::Point(0,0,4326) )
) AS t(geog);

I was trying to answer this question, but had to create a temp table instead.


texts: 64 bit Machine 32 bit Excel Using SQl Server Management Studion V17

texts:
I am using the below sql command to import an excel file (created in excel 2016) into SQL Server Management Studio on a 64bit Machine with 32 Bit Office.

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
                Database=C:\File1.xlsx', 'SELECT * FROM [Sheet1$]');

But I am getting the following error:

  Msg 7403, Level 16, State 1, Line 1 The OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" has not been registered.

I tried downloading 2007 Office System Driver: Data Connectivity Components but it didn't help.

Is it better to upgrade to 64 Bit Office? I only want part of the excel document to upload directly into a sql table.  I can do the whole table via import but want to do it with VB or a Macro..


texts: SQL Server xp_cmdshell: account's access changed - how to avoid restarting the service?

texts:
A T-SQL script is running an xp_cmdshell call to stop a local service through Powershell's Stop-Service, say, like this:

exec xp_cmdshell 'powershell Stop-Service -InputObject (get-Service -Include *"MyService"*)'

Now, since the current login is sysadmin, it is being ran under SQL Server's service's account, which is a local account without admin rights. In that case the Stop-Service fails due to lacking access.
Then, if I add the SQL Server's account to Administrators group, the only way I can make this change effective is to restart SQL Server's service. Is there any other way to do this without having to restart the sql server?
Thank you!

PS. I know xp_cmdshell is bad for you, let's please not focus on that.


texts: TSQL: How to DELETE in cascade without altering table / indexes

texts:
I have inserted some data in a table where I should not have. I am trying to delete the data, but it has a 1:M relationship and I need to also delete data that references this one.

I do not want to alter the tables / indexes, I just need this instruction to cascade:

-- this alters the CONSTRAINT -- not an option:
ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;

Also I would like (read - prefer) not to change the IDE GUI settings.


texts: How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup?

texts:
I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database on PROD2 would be promoted to master database.

What I want is to make the slave database (on PROD2) to upstream server and setup another database on PROD1 as downstream server. The idea is to use the upstream server in case of failover and downstream server in case of fallback.

Please advise what configuration changes would be required to achive the above.

Thanks


texts: Comparing 2 functions for any differences in the result set

texts:
Recently, we've done some optimizations to the functions and now the BI Manager has tasked me with a request to compare the result of the 2 functions as below making sure the row counts match, along with the exact values of each field. In the same request he is expecting this to be a while loop that will loop all the YTD ranges dating back 20 years. (not sure if this is really practical in order to test the validity). However, the while loop is not a mandate for the same. Is there a way to compare the result of the 2 functions without hard coding the ranges and automating the same like for e.g declaring a variable and then based on the date range calculating the next date range etc without the user intervention.

Last but not the least a script that would validate there are no differences in the results of both the functions which means that the optimization did work. I am thinking of using except clause but I am not sure if it would work with functions. Also, if that won't work what else would be a possible way of doing it.

I would really appreciate for all the help. It is a big impact for the company if I could complete this task. However, I am not even sure if this is something that should be even handled by a DBA.

Functions
Original Function: report.fnReportDealCore_original(@StartDate, @EndDate, -1)

New Optimized Function: report.fnReportDealCore(@StartDate, @EndDate, -1)

Example of Run Dates dating back 20 years

1/1/1997 – 1/31/2997
2/1/1997 – 2/28/1997
3/1/1997 – 3/31/1997
….
1/1/1998 – 1/31/1998
2/1/1988 – 2/28/1998
…
Through 9/30/2017

Thanks


texts:
I'm trying to create a dev database so I don't create triggers and such on the production database. I use the right click -> Taks -> Copy, followed the wizard and the process starts.

After maybe a minute, it stops and says Error.

The Event Viewer says this:

Package "ShellPackage" failed.

Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [dbo].[VIEWNAME]
AS
SEL..." failed with the following error: "Invalid column name 'C_27'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace:   Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: DOMAIN\administrator
 Source Name: SERVER_SERVER_Transfer Objects Task
 Source ID: {47D1A40A-A6CF-4A5C-897E-80CB9C1D6151}
 Execution ID: {3E979DF7-C5DC-4583-8613-CB9AC9153D56}

What am I doing wrong?

Thank you!


texts:
I have two separated locations connected by a not too reliable VPN. I have a common system that depends on MYSQL that read/write tables.
Will master-master replication keep both locations in sync?
I don't care that tables might not look exactly the same in time on both servers (for example, rows on both masters when VPN fails, might get a different order after both locations write transactions...)

Note: Not doing auto-increment / offset settings


texts: Returning multiple result sets from server-side procedures in Postgres 11

texts:
This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets.

➥ Did this feature indeed appear in the Postgres 11 release?

If so, can you explain how it works briefly? Show some code example?


texts: What role is able to grant role permissions on MSSQL 2016?

texts:
Unfortunately, we'll have to remove db_owner role from not admin users, because people were abusing it.

We'll split roles permissions in 3 groups:

1) readonly (SELECT) with specific write (INSERT,UPDATE,DELETE) on some tables

2) DDL (ALTER) without RW

3) "restricted admin" custom role, able to add other AD groups and users to existing roles

But I can't find what role is able to grant and revoke permissions without being a db_owner. I tried db_accessadmin and db_securityadmin, but when a user with these roles tries to add another user to db_datareader or db_datawriter it fails saying he doesn't have permission.


texts: How to determine if SQL Server Reporting Services database is being used?

texts:
I have a SQL 2005 server that needs to be decommissioned. For regular databases, I have a couple scripts that look at sys.dm_db_index_usage_stats to determine last access. My normal process is to make sure the last access date is back quite a while, then take offline and wait to be sure there are no problems before taking a final backup and then deleting the database.

However, since ReportServer and ReportServerTempDB are associated with SSRS, should they be treated differently? Is there an easier way to tell if SSRS is in use?


texts: How does using separate schemas affect SQL Server 2008 performance?

texts:
I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or stored procedure, and the prefixes mean we have to scan five or six xharacters in before we even see the start of the unique name. I would like to use separate schemas for tables that are just used to drive the UI (menus, roles by person, etc.) and for those that are dimension tables vs. fact tables, etc.

My question is, will there be performance impacts from using multiple schemas (schemae?) was opposed to the good old dbo for everything?


texts: Tables with MD_ prefix were found in my Oracle DB - is there any info about them?

texts:
Recently, during up-gradation of my Oracle DB, I have found many tables whose names are starting with MD_, which are empty.

Is there any info about them?
Can I drop them?


texts: How to find all schemas where a table has records?

texts:
In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for which schemas a table (with a particular name, which will be the same across all the queried schemas) has records/rows?

Is there a view in Oracle which contains all records? For example, if I am looking for columns, I can search ALL_TAB_COLUMNS; if I am searching for tables, I can look in DBA_TABLES or ALL_OBJECTS. Is there an equivalent for records?

This would also be helpful for another query - to find all tables (of a given name) across all schemas, where a particular field has a non-null value in at least one record.


texts: SQL Server 2008 on Hyper-V Virtual Machine with Multiple instances

texts:
Running SQL Server 2008 instance on a Virtual Machine (Hyper-V) on a Windows 2008 R2 OS. Currently has a few production DBs. The whole VM itself is on RAID 5 volume (so are the DB files etc).

We will be adding further development and production DBs onto this VM soon. The production DB's on average are 1.5Gig in size with the largest DB less than 20Gig.

My question is: would having multiple instances make much difference in my situation. i.e.
Data separation (dev, prod)
An instance fails the others still work (not sure if applicable in a shared disk situation)

Would multiple instances be worth the overhead? i.e. more services, memory used by services

Thanks.

PS. Our disk layout is on a single disk and I understand that is not the recommended but will leave as a separate issue as I want to nut down on the multiple instance question first.


texts: Postgres Proper Index for Sorting and Join

texts:
I have a simple schema and query, but am experiencing consistent awful performance with certain parameters.

Schema

CREATE TABLE barcodes (
  id    integer PRIMARY KEY,
  value citext  NOT NULL
);
CREATE INDEX index_barcodes_on_value ON barcodes (value);

CREATE TABLE locations (
  id         integer PRIMARY KEY,
  barcode_id integer NOT NULL REFERENCES barcodes(id)
);
CREATE INDEX index_locations_on_barcode_id ON locations (barcode_id);

Query

EXPLAIN ANALYZE
SELECT *
FROM locations
JOIN barcodes ON locations.barcode_id = barcodes.id
ORDER BY barcodes.value ASC
LIMIT 50;

Analysis

Limit  (cost=0.71..3564.01 rows=50 width=34) (actual time=0.043..683.025 rows=50 loops=1)
  ->  Nested Loop  (cost=0.71..4090955.00 rows=57404 width=34) (actual time=0.043..683.017 rows=50 loops=1)
        ->  Index Scan using index_barcodes_on_value on barcodes  (cost=0.42..26865.99 rows=496422 width=15) (actual time=0.023..218.775 rows=372138 loops=1)
        ->  Index Scan using index_locations_on_barcode_id on locations  (cost=0.29..5.32 rows=287 width=8) (actual time=0.001..0.001 rows=0 loops=372138)
              Index Cond: (barcode_id = barcodes.id)
Planning time: 0.167 ms
Execution time: 683.078 ms

500+ ms for the number of entries in my schema (500,000 barcodes and 60,000 locations) doesn't make sense. Can I do anything to improve the performance?

Note:

Even stranger is the execution time depends on the data. In drafting this question I attempted to include seeded random data, but the seeds seem to be performant:

Seed

INSERT INTO barcodes (id, value) SELECT seed.id, gen_random_uuid() FROM generate_series(1,500000) AS seed(id);
INSERT INTO locations (id, barcode_id) SELECT seed.id, (RANDOM() * 500000)  FROM generate_series(1,60000) AS seed(id);

Analysis

Limit  (cost=0.71..3602.63 rows=50 width=86) (actual time=0.089..1.123 rows=50 loops=1)
  ->  Nested Loop  (cost=0.71..4330662.42 rows=60116 width=86) (actual time=0.088..1.115 rows=50 loops=1)
        ->  Index Scan using index_barcodes_on_value on barcodes  (cost=0.42..44972.42 rows=500000 width=41) (actual time=0.006..0.319 rows=376 loops=1)
        ->  Index Scan using index_locations_on_barcode_id on locations  (cost=0.29..5.56 rows=301 width=8) (actual time=0.002..0.002 rows=0 loops=376)
              Index Cond: (barcode_id = barcodes.id)
Planning time: 0.213 ms
Execution time: 1.152 ms

I'm looking for help diagnosing the slow performance of the query. I can reproduce the problem - I just cannot replicate it with random seed data.

I am running PostgreSQL 9.6.2. Running VACUUM FULL on all the tables in the query didn't do anything.


texts:
I have a 3 Node always availability group:

2 Nodes are In house – Synchronous Mode.
1 Node is remote - Asynchronous Mode.

Every now and then the Remote Node loses connectivity and goes offline from the cluster.  I would like to run a validation test on that node (dr site). Will this affect the other 2 nodes which are in synchronous mode?


texts: Is it possible to get schema ID in another database without using dynamic sql?

texts:
I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic SQL, which is prohibited inside UDFS?

Note:

I can get the database name with DB_NAME
I can get the object name by using OBJECT_NAME because it accepts the db ID as a second parameter
I can get the schema name by using OBJECT_SCHEMA_NAME because it also accespts a db ID as a second parameter
It's easy to get the schema ID using dynamic SQL to select from [db_name_i_want].sys.schemas, but this is not allowed in a  UDF

Update (purpose)

For a possible solution to DB_ID context from farther up call stack, I'm adapting some call stack functions by Gabriel McAdams to work accross multiple databases. His version just pushes the proc ID at each call level onto the CONTEXT_INFO stream. I've modified this to also push the db ID. I could push the schema ID as well, but then things start to get crowded because CONTEXT_INFO is limited to 128 bytes. So, I was hoping to be able to reconstruct the schema ID from the db id and object id in the function the creates a view of the call stack (CallStackView).

Code

-- @db_id, @proc_id are saved in a logging function
DECLARE @db_name nvarchar(128)     = DB_NAME(@db_id)                      -- OK
DECLARE @obj_name nvarchar(128)    = OBJECT_NAME(@proc_id, @db_id)        -- OK
DECLARE @schema_name nvarchar(128) = OBJECT_SCHEMA_NAME(@proc_id, @db_id) -- OK
DECLARE @schema_id int = ? -- What can I do here???


texts: Backing up databases on SQL Server hosted in Windows Azure VM

texts:
One of the clients at my workplace asked me to find out information on what would be the best possible or recommended approach to run maintenance jobs such as backup job for a SQL Server 2016 hosted in Azure.

Should I be running native SQL backups and dumping the backup files in Azure blob storage, or do I need to ask the client to have a file share as a placeholder for the backup or is a locally attached disk better?


texts: I am not able to use the sqlplus after intalling 11g on redhat 6.7

texts:
When I execute:

$ sqlplus / as sysdba
bash: sqlplus: command not found

and the bash profile for oracle user is :

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
#added for oracle
TMP=/tmp; export tmp
ORACLE_HOSTNAME=prashant.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=prod; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=prod;$PATH; export ORACLE_SID
PATH=/usr/sbin: export PATH
PATH=$ORACLE_HOME/bin:PATH; export PATH


texts: How do I force one record to have a true value for a boolean column, and all others a false value?

texts:
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.

Basically, I want to guarantee that this query will always return exactly one row:

SELECT ID, Zip
FROM PostalCodes
WHERE isDefault=True

How would I do that in SQL?


texts: Associating on the ID of an association table

texts:
Sorry about the title, I couldn't find anything better. Suggestions are welcome.

Say we have two tables, Suppliers and Products.
The same product can come from many different suppliers, so we create a third table, let's call it SuppliersAndProducts, with this schema

SuppliersAndProducts

  - id (autoincrement)
  - supplier_id
  - product_id
  - from_date
  - to_date

from_ and to_date are there to account for the fact that a given supplier might well stop selling a given product, but could also begin selling it again at some point in the future.

Now, we want to store the price we pay when we buy a Product from a given Supplier. Prices, of course, change over time, and we want to keep a track of that too.

So we introduce a table, let's call it SuppliersAndPrices, structured like so:

SuppliersAndPrices

  - id (autoincrement)
  - supplier_id (FK to the supplier id)
  - product_id (FK to the product id)
  - price
  - from_date
  - to_date

My question about this last table is more conceptual than anything else: should this table be like I described (meaning that the association in based on both the supplier and product IDs) or instead just reference the id from SuppliersAndProducts with a column that we could call suppliers_and_products_id?

The latter design is more normalized than the former; after all, the association between a Supplier and a Product is already stated in SuppliersAndProducts, so there would be little point in repeating that information. Still, at least to me, for some reason the former feels closer to the real world.

From the point of view of query complexity, to know the price I'm paying today for a given product, with the first design I would have to write (with some pseudo-SQL to remain db-agnostic) something like:

SELECT price
FROM   SuppliersAndPrices
WHERE  supplier_id = X
       AND product_id = Y
       AND today is between from_date and to_date

whereas the second design would entail a join, thus making the query look like:

SELECT price
FROM   SuppliersAndPrices
       INNER JOIN SuppliersAndProducts
               ON SuppliersAndPrices.suppliers_and_products_id = SuppliersAndProducts.id
WHERE  SuppliersAndProducts.supplier_id = X
       AND SuppliersAndProducts.product_id = Y
       AND today is between SuppliersAndPrices.from_date and SuppliersAndPrices.to_date

I'm writing this off the top of my head, sorry about my SQL.

Also, now that I think about it, with the second design I would have to add another condition to the WHERE clause to check the dates in SuppliersAndProducts too, to handle the case in which a Supplier will stop carrying a product and, at some point, begin selling it again. In that case the join condition would return more than one row, and that would be Not A Good Thing™.

So, which one would you choose? Associating on the id of what already is an association in the name of greater normalization but at the price of increasing query complexity? Denormalizing a tiny bit to make querying easier and, arguably, the database structure clearer for future maintainers?

The query for the second design would probably be hidden behind a view, but that's still code that has to be maintained and understood. What I really want to know is which one of the two design is "better", for some definition of "better".


texts: Is replication possible on two different domains?

texts:
I am implementing a database replication between different geograhical locations where each one is on a different domain. After going through all the process, when I start the agent it runs and stops.

When I look at the details it shows:

Error messages:
The process could not connect to Subscriber 'Mydomain\myusername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Source: MSSQLServer, Error number: 18452)


texts: The actual utilization of Ref Cursor is at Database level not at the Database Client level. Is this statement correct?

texts:
From my understanding, the purpose of a ref cursor is to pass a Result Set among procedures/functions(at the DB level) through a "pointer" without passing the actual Result Set which can consume a lot of memory(At DB level).

Say I'm using a java client to talk with my database. I'm calling a stored procedure that returns a ref cursor. At java level I can obtain the result set pointed by the ref cursor as follows.

ResultSet resultSet = (ResultSet) stmt.getObject(1);

Here, the result set is already in the memory inside the stmt object. Even though it is a pointer that was returned, at java level we already have the result set in memory. Therefore, say you only have one simple procedure that returns one result set obtained from a simple SELECT. And you call that procedure from java client and retrieve the result set. In this particular case,

Returning a result set

and

Returning a ref cursor to that result set

both would have the same resource consumption.

Is my understanding correct?

Note: This is a general question. The database could be Oracle/PostgreSQL or any other database that supports SQL Ref Cursor type


texts:
I have a column with description and inside it can contain # value .
what I want is all the words searched from that column with #tag

This is my query

SELECT description FROM  photo where description like '%#aw%';

and this is what I get as output

description
'nice place #awesome'
'my pc #awe'
' nice design #aw'
'my table #awesomedatabase'

and what I want is this output how to modify my query in order to get below output

 description
 #awesome
 #awe
 #aw
 #awesomedatabase


texts:
I want to efficiently sort a table in PostgreSQL based off of two criteria. Here is my table structure:
CREATE TABLE shapes
(
    shape_id character varying,
    shape_pt_lat double precision,
    shape_pt_lon double precision,
    shape_pt_sequence integer
)

And there is an index on shape_id and shape_pt_sequence:
CREATE INDEX shapes_idx ON shapes (shape_id, shape_pt_sequence)

Here is the query I tried at first:
SELECT shape_pt_lat, shape_pt_lon
FROM shapes
ORDER BY shape_id, shape_pt_sequence

However, then I broke it up into two queries, because I thought that would prevent it from being sorted all at once. The first query gets the distinct shapes and then my program will make a second query for each shape_id to sort just the shape records from a single shape_id:
SELECT DISTINCT shape_id FROM shapes

then
SELECT shape_pt_lat, shape_pt_lon
FROM shapes
WHERE shape_id = ?
ORDER BY shape_pt_sequence

It turns out for smaller datasets (shape table records ~ 750k), this does cut the calculation time in half, but for larger datasets (shape table records ~ 4m) the performance was actually slower. I'm guessing maybe it was because for the larger dataset all the extra individual SQL requests may have slowed things down.
Here is the results of EXPLAIN on some queries:
Query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shapes ORDER BY shape_id, shape_pt_sequence
Result:
Index Scan using ielz_tahfnniqhzylvuhipcjjnt_shapes_idx on shapes  (cost=0.42..29796.64 rows=473805 width=34) (actual time=0.039..214.900 rows=473805 loops=1)"
  Buffers: shared hit=31022
Planning Time: 0.361 ms
Execution Time: 259.751 ms

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shapes WHERE shape_id = '117112' ORDER BY shape_pt_sequence
Result:
Sort  (cost=534.92..535.31 rows=156 width=34) (actual time=0.163..0.173 rows=134 loops=1)
  Sort Key: shape_pt_sequence
  Sort Method: quicksort  Memory: 35kB
  Buffers: shared hit=5
  ->  Bitmap Heap Scan on shapes  (cost=5.63..529.23 rows=156 width=34) (actual time=0.062..0.094 rows=134 loops=1)
        Recheck Cond: ((shape_id)::text = '117112'::text)
        Heap Blocks: exact=2
        Buffers: shared hit=5
        ->  Bitmap Index Scan on ielz_tahfnniqhzylvuhipcjjnt_shapes_idx  (cost=0.00..5.59 rows=156 width=0) (actual time=0.050..0.050 rows=134 loops=1)
              Index Cond: ((shape_id)::text = '117112'::text)
              Buffers: shared hit=3
Planning Time: 0.098 ms
Execution Time: 0.216 ms

Anyways, I'm wondering if I'm missing something here and if there is a better way to help instruct PostgreSQL to sort this more efficiently in a single query for example.


texts: MySQL InnoDB table accepts Non-Existent Foreign Key

texts:
Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page.

Working on Windows 7 64-bit.

I've created the tables I needed and specified each foreign key in the "Relation view" (on Structure tab). All the tables are InnoDB and the default engine is set to InnoDB.

Assuming I have the tables A and B, and B has a foreign key to the column ID of the table A.

When inserting a line in table B, if I write 0 (zero) on the foreign key column and submit, it accepts.

In the Browse tab it shows the line with the foreign key column with the value 0 (zero).

When I click on the value (link to the that line on the table A), I go to that table with zero results.

In the variables tab, the "foreign key checks" variable is "ON".

But the foreign keys are not checked or the insert would fail since there is no ID 0 on table A. What have I done wrong or forget?

  Please run SHOW CREATE TABLE yourtable\G for both table A and table
  B and post it in the question. –  RolandoMySQLDBA

Table A

CREATE TABLE `competicaogeral` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Table B

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


texts: Sybase Central: How to set language for UI of Sybase Central

texts:
How to set the language for UI of Sybase Central.. It appears it's taking the language settings from Windows, but having e.g. an English Sybase Central on a German Windows -- how to achieve this?

For database server and other tools it can be set either via SALANG environmental variable or via the tool dblang. On Linux you can just say somethng like

LANG=C scjview

but this seems not to work on Windows.


texts: Calculating percentage over sub query SQL

texts:
I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign.

First I calculated the number of events happening in each campaign:

select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email

Then I grouped the campaigns in the condition whether more than one campaign happened:

select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper

Sample result:

occurences ¦ grouper
132        ¦ 1
360        ¦ 2

Now I want to calculate for each row the percentage of total occurrences. So something like this:

occurences ¦ grouper ¦ percentage
132        ¦ 1       ¦ 132/(132+360)
360        ¦ 2       ¦ 360/(132+360)

I tried this, but it does not work, it does not properly calculate the sum total:

select *, occurences/(select sum(occurences))
from (
         select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper
     ) as occurences_table group by occurences, grouper

Any idea where is my mistake in the last step?


texts: Can't figure out this "trick" to optimize MySQL query

texts:
Here's an excerpt of this article:

SELECT id, name, address, phone
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;

  MySQL is first scanning an index then retrieving rows in the table by
  primary key id. So it’s doing double lookups and so forth.

  The following piece just uses the primary key:

SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;

I don't figure out the difference between those two queries despite the explanation, especially the evoked double lookup...

May someone explain it in more detail?


texts: SQL Server 2012 - Large backup file size

texts:
We have the following setup:

Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak.
From then, a transactional backup is performed every hour. This is appended to Backup1.bak.
Once a day at midnight (except Sundays) a differential backup is performed. This is also appended to Backup1.bak.
The following Sunday at midnight, Backup1 is zipped and moved to external media, a new full backup is created and is placed in Backup2.bak. Backup1.bak is deleted.

The database is running in full recovery mode, and all backups only operate on one database in one instance.

This works well in SQL Server 2008. However, we are now starting to roll out SQL Server 2012 implementations, and the problem we are encountering is that the BAK file is growing exponentially. A typical MDF file size rarely exceeds 15Gb and LDF rarely exceeds 50Gb, but the BAK file is sometimes as big as 900Gb by Saturday. This is naturally causing capacity constraints on client sites with 1Tb HDDs.

Is there a different way of doing transactional and differential backups in 2012 as compared to 2008?


texts:
I have 3 database 1 primary and two standby, The storage of one the standby was abruptly removed without shutting down the database.

Few Days later the storage was returned, I restart the whole database server, and while trying to open my database instance(alter database open) I get the errors below:

ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/drstby/datafile/system.306.918916853'

I tried to do "recover database;" and I get another error:

RMAN-03002: failure of recover command at 12/15/2017 17:11:50
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 62585 and starting SCN of 16625729961

Please what can I do.

Note:
the database in question has the following
FRA
ASM
RMAN BACKUPS.
THANKS


texts:
I'm using MariaDB ODBC driver version 3.0.2 for Windows(on Windows 10) and can't get excel (Excel 2013) to query the database. I'm able to connect to my MariaDB server using HeidiSQL and query just fine, and my PHP application can query just fine. Now I would like to start pulling data for analytics.

I've setup the DSN using the "ODBC Data Source" tool under Administrative Tools in the control panel. I've just left all of the options blank. Below are some screenshots of how I setup the DSN.

When trying to setup the connection in Excel, I go to the data tab and from the "From Other Sources" drop down, I click Microsoft Query.

Then I select my data source (DSN) from the list and click "OK". Then I get two errors. The first error says "String data, right-truncated" and the second error says "Couldn't read this file". Screenshots below.

I'm not sure what is causing this. I've tried using the servers IP address and the servers resolvable name, and I've tried specifying a database. I've also tried specifying a connection character set and selecting the option for using compression. I've also tried enabling dynamic cursors and the option to return matched rows instead of affected rows. Nothing has made any changes.

UPDATE: This is still an issue for me. 6/21/2018


texts: How can I speed-up my query on geo-location processes

texts:
I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query:

SELECT
  *, point(45.1013021, 46.3021011) <@> point(latitude, longitude) :: point AS distance
FROM
  locs
WHERE
  (
    point(45.1013021, 46.3021011) <@> point(latitude, longitude)
  ) < 10 -- radius
ORDER BY
  distance LIMIT 1;

This query successfully gave me what I want, but it is slow. It took 2 to 3 seconds to get one record by given latitude and longitude.

I tried a B-Tree index on the latitude and longitude columns, also tried GIST( point(latitude, longitude)); but still querying is slow.

How can I speed up this query?

Update:

It seems slowness is caused by the ORDER BY but I want to get the shortest distance, so the question remains.


texts: Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

texts:
I changed values for my Mysql parameters

1. innodb_flush_log_at_trx_commit=2(before it was 1)

2. sync_binlog=0(before it was 1)

This has changed some of my parameters on AW RDS drastically ,here are some of them.

  Write Operations(Count/sec)

  Queue Depth

My question is ,What can i interpret from the above results ? Is is good for my database ? Is the performance increased ?


texts: Use federated table to sync only selected columns

texts:
Scenario: I need to sync a table in a remote server with the table in my production server. The problem being that the table structures are not same.

I have looked into the concepts of federated table only to find out that the two tables are exactly same, structure wise, which is not the case here.

So basically, is there any way I can sync only few columns,the matching columns,of those tables?


texts: How to restore from a backup of a very old version of DB2 (version 6.1 backup)?

texts:
I am helping a client with some legacy application that is still running DB2 6.1 on Windows 2000! I am helping them migrate to newer software/hardware infrastructure.

I have a backup of the DB2 6.1 database on Windows 2000.
As a first step I want to restore a new database image from this backup on a
newer version of DB2.
The Latest version of DB2 does not support restoring from a v6.1
backup.

Can I find a relatively new version of DB2 that will restore a v6.1 DB2
backup?

Any insight into options available for my scenario are appreciated.

Thanks,
Kiran


texts: Should I explicitly DENY UPDATE to columns that should not be updated?

texts:
I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE columns that should never be updated.

For example:

create table dbo.something (
    created_by varchar(50) not null,
    created_on datetimeoffset not null
);

These two columns should never be changed once the value has been set. Therefore I would explicitly DENY the UPDATE permission on them.

Recently, during a team meeting a developer raised the point that the logic to ensure the fields never get updated should be contained within the application layer and not the database layer in the event that "they need to update the values for some reason". To me that sounds like typical dev mentality (I know, I used to be one!)

I am the senior architect at my company and I have always worked on the principle of least amount of privileges required to get the app to work. All permissions are audited regularly.

What is the best practice in this scenario?