Performance refactoring: Trying to avoid the table scan I have a proc containing a query joining a few tables and I am having some performance issues.

The main table (which is a huge table) has a PK and a few NC indexes.

CREATE TABLE [dbo].[TableA]
(
    [TableAID] [bigint] NOT NULL,
    [UserID] [int] NOT NULL,
    [IP1] [tinyint] NOT NULL,
    [IP2] [tinyint] NOT NULL,
    [IP3] [tinyint] NOT NULL,
    [IP4] [tinyint] NOT NULL
    CONSTRAINT [PK_TableA]
        PRIMARY KEY CLUSTERED  ([TableAID] ASC)
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [idx_1] ON [dbo].[TableA]
(
    [UserID] ASC
)

CREATE NONCLUSTERED INDEX [idx_2] ON [dbo].[TableA]
(
    [IP1] ASC,
    [IP2] ASC,
    [IP3] ASC,
    [IP4] ASC
)

This is the query with the poor perf:

SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4
    FROM [dbo].[TableA] a WITH (NOLOCK)
    JOIN [dbo].[TableB] b WITH (NOLOCK) ON b.UserID = a.UserID
    JOIN [dbo].[Tablec] c WITH (NOLOCK) ON b.CountryID = c.CountryID
    JOIN (
        SELECT
            IP1, IP2, IP3, IP4
            from
            @IPs
            ) as ip ON
                ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND
                ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND
                ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND
                ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))

The definition of @IPs table:

DECLARE @IPs TABLE (
    IP1 int,
    IP2 int,
    IP3 int,
    IP4 int
    )

INSERT INTO @IPs(IP1,IP2,IP3,IP4)
SELECT      T.v.value('(IP1/node())[1]', 'int'),
            T.v.value('(IP2/node())[1]', 'int'),
            T.v.value('(IP3/node())[1]', 'int'),
            T.v.value('(IP4/node())[1]', 'int')
FROM @IPAddresses.nodes('//IPAddresses/IPAddress') T(v)

@IPAddresses is xml. Just found out that xml could send more IPs, so that means more than one row in the IPs table.

The issue is the number of reads on TableA. Even if I have a NC index for the IP columns, that join condition is forcing a table scan...

How can I improve the performance? How can I refactor this table/query?

I am still thinking if there is a simpler and better way to rewrite this code:

SELECT
        IP1, IP2, IP3, IP4
        from
        @IPs
        ) as ip ON
            ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND
            ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND
            ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND
            ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))

...in case when we have more IPs.

There are a few things that make this challenging. If you aren't careful the NULL checks can prevent index seeks. Also, when the columns are NULL you obviously can't search against them. So if IP1 is NULL then the four column index idx_2 won't be very useful. It doesn't really seem to be possible to define one index that will be selective for any combination of NULL variables. Also, SQL Server cannot continue an index seek after it seeks on an inequality predicate:

  In the same way, if we have an index on two columns, we can only use
  the index to satisfy a predicate on the second column if we have an
  equality predicate on the first column.

That means that tricks that use the bounds of the TINYINT data type aren't likely to be effective, such as the following:

a.IP1 >= NULLIF(ip.IP1, 0) AND a.IP1 <= NULLIF(ip.IP1, 255)

In addition to that, the strategy that I'm using seems to work much better with the new cardinality estimator introduced in SQL Server 2014 and your question is tagged with SQL Server 2008.

I strongly recommend splitting up the table variable into rows and processing one row at a time. This is mostly to deal with the NULL values and because your comments hinted that most of the time you only get one row. As long as performance is good enough for one row and you don't have too many rows it should be fine. If that isn't acceptable perhaps you can check if any of the rows in the temp table (don't use a table variable) are NULL and branch your code in that case.

With all of that said, it appears to be possible to get very good performance without a clustered index scan as long as at most two of the IP address pieces aren't NULL. When three of them are NULL you're getting back most of the table and at that point it probably makes sense to do a clustered index scan.

Below is the data that I mocked up to test various solutions. I generated 100 million IP addresses by randomly picking an integer between 0 and 255 for each piece. I know that IP address distribution in real life isn't quite that random but didn't have a way to generate better data.

CREATE TABLE [dbo].[TableA](
    [TableAID] [bigint] NOT NULL,
    [UserID] [int] NOT NULL,
    [IP1] [tinyint] NOT NULL,
    [IP2] [tinyint] NOT NULL,
    [IP3] [tinyint] NOT NULL,
    [IP4] [tinyint] NOT NULL
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
( [TableAID] ASC )
);

-- insert 100 million random IP addresses
INSERT INTO [dbo].[TableA] WITH (TABLOCK)
SELECT TOP (100000000)
      ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10000
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
    , ABS(BINARY_CHECKSUM(NEWID()) % 256)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3;

CREATE TABLE [dbo].[TableB] (
    [UserID] [int] NOT NULL,
    FILLER VARCHAR(100),
    PRIMARY KEY (UserId)
);

-- insert 10k users
INSERT INTO [dbo].[TableB] WITH (TABLOCK)
SELECT TOP (10000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Notice how I didn't create either of the nonclustered indexes that you already have. Instead I'll create one index for each IP piece:

CREATE NONCLUSTERED INDEX [idx_IP1] ON [dbo].[TableA] ([IP1] ASC);
CREATE NONCLUSTERED INDEX [idx_IP2] ON [dbo].[TableA] ([IP2] ASC);
CREATE NONCLUSTERED INDEX [idx_IP3] ON [dbo].[TableA] ([IP3] ASC);
CREATE NONCLUSTERED INDEX [idx_IP4] ON [dbo].[TableA] ([IP4] ASC);

The space required for the indexes is not insignificant. Here is the comparison:

╔═══════════╦═════════════╗
║ IndexName ║ IndexSizeKB ║
╠═══════════╬═════════════╣
║ idx_1     ║     1786488 ║
║ idx_2     ║     1786480 ║
║ idx_IP1   ║     1487616 ║
║ idx_IP2   ║     1487616 ║
║ idx_IP3   ║     1487632 ║
║ idx_IP4   ║     1487608 ║
║ PK_TableA ║     2482056 ║
╚═══════════╩═════════════╝

If necessary you can weigh the pros and cons of using row or page compression to reduce the size of the indexes. However, if you don't know which IP pieces will be NULL and you need to avoid a clustered index scan then I don't see a better alternative to the four indexes. The strategy that I'm going to employ is called an index join. The nonclustered index includes the clustered key TableAID which makes it possible to join the indexes together. Each index should have a selectivity of around 0.4% and it should be relatively cheap to find those rows with a nonclustered index seek. Joining together all of the indexes should greatly reduce the result set and at that point you can do clustered index seeks against the table to get the other column values that you need such as UserID.

Here's the query:

DECLARE @ip1 TINYINT = ?;
DECLARE @ip2 TINYINT = ?;
DECLARE @ip3 TINYINT = ?;
DECLARE @ip4 TINYINT = ?;

SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4
FROM [dbo].[TableA] a
JOIN [dbo].[TableB] b ON b.UserID = a.UserID
WHERE
((@ip1 is NULL) OR (@ip1=a.IP1)) AND
((@ip2 is NULL) OR (@ip2=a.IP2)) AND
((@ip3 is NULL) OR (@ip3=a.IP3)) AND
((@ip4 is NULL) OR (@ip4=a.IP4))
OPTION (RECOMPILE, QUERYTRACEON 9481);

With the RECOMPILE hint I'm taking advantage of the parameter embedding optimization. This optimization is only available with a certain service pack (SP4?) so make sure that you're patched up. The query optimizer is able to split up the single table access on TableA into an index join if it sees fit. Note that the estimated plan is very likely to be misleading here. You want to pay attention to the actual plan.

The QUERYTRACEON 9481 hint should not be included in your version of the query. I'm using it to force SQL Server to use the legacy CE which is only necessary because I'm testing against SQL Server 2016.

Let's run a few tests. With the following parameters:

DECLARE @ip1 TINYINT = 1;
DECLARE @ip2 TINYINT = 102;
DECLARE @ip3 TINYINT = 234;
DECLARE @ip4 TINYINT = 172;

I get merge join and loop joins.

With the following parameters:

DECLARE @ip1 TINYINT = NULL;
DECLARE @ip2 TINYINT = 102;
DECLARE @ip3 TINYINT = 234;
DECLARE @ip4 TINYINT = 172;

I get a very similar plan, except that the index on IP1 is not used in the query. The query still finishes in around 125 ms.

With the following parameters:

DECLARE @ip1 TINYINT = 88;
DECLARE @ip2 TINYINT = NULL;
DECLARE @ip3 TINYINT = NULL;
DECLARE @ip4 TINYINT = NULL;

The query finishes in around 5 seconds. I get a hash join along with a clustered index scan. This isn't necessarily a bad thing but can be avoided with some effort (see the following paragraph):

In case there is a need to effectively force index joins (perhaps SQL Server 2008 is missing an optimization that I'm taking advantage of), it can be done, but it is much more complicated.


MYSQL Stock Database Optimization I am downloading a bulk of stock data:

Ticker symbol, company name, industry sector,

Open, high, low, close

I was wondering performance-wise if separating the ticker, company name, industry sector from the price data would be optimal, as the tickersymbol and companyname just repeat over and over again.

Or would inner joining the ticker+price every time be slower?

So:

single table (ticker/price), or
two tables (ticker)(price)?

I would always create two tables and I would also split the prices tables into many tables as they tend to get really big.

I would do something like this:

Tickers/Companies table:

CREATE TABLE `tickers` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ticker` VARCHAR(10) NOT NULL DEFAULT '0',
    `name` VARCHAR(80) NOT NULL DEFAULT '0',
    `sector` VARCHAR(80) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ticker` (`ticker`)
)
ENGINE=MyISAM;

Depending on amount of data you expect and the way you query for data, I would then split prices data into tables based on datetime. Let's assume you get lots of data for each ticker per day and that you would mostly query this daily data. I would then form tables likes this:
price_YEAR_MONTH_DAY

Example for two tables:

CREATE TABLE `price_2014_03_23` (
    `ticker_id` INT(10) UNSIGNED NOT NULL,
    `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `open` DOUBLE NOT NULL,
    `high` DOUBLE NOT NULL,
    `low` DOUBLE NOT NULL,
    `close` DOUBLE NOT NULL,
    PRIMARY KEY (`ticker_id`, `datetime`)
)
ENGINE=MyISAM;

CREATE TABLE `price_2014_03_24` LIKE `price_2014_03_23`

So a single day select would look like this:

select p.* from price_2014_03_23 as p
left join
  tickers as t on t.id = p.ticker_id
where
  t.id = 1 and
  p.datetime = '2014-03-23 09:00:00'

If you'd want data for more days, you can do it like this:

select
  p.*
from
  (select * from `price_2014_03_23` WHERE `ticker_id` = 1 union all
   select * from `price_2014_03_24` WHERE `ticker_id` = 1) as p

If you do not have that many data in prices tables, you can simply use only one and query that one.

I usualy split data tables based on queries I perform on it. So, I want to minimize the UNION ALL and make sure all indexes are in place.
If you can provide some more insights on data being gethered (amount also) and the information you want to get from it, I can provide more specific solutions.


Please Review my Schema Anything obvious that could be improved upon? :)  I am positive there are no direct errors.  More what I am looking for is errors in normalization and areas that I could simplify data storage. Please see my previous question for the context and motivation.

In addition to the rather odd (and possibly over-restrictive modeling of the phone number components), the "data" concerns me.  It basically looks like the EAV pattern restricted to DECIMAL data type, which can have it's place, however there appears to be NO OTHER data modeled at all.

When you finally get round to querying these things, it's a lot easier and involves less pivoting if you can just do things like SELECT revenue - expenses AS profit or whatever it is these different types of data are that are attached to each user each week.


Experiencing CLR error on SQL Azure DB Out of nowhere we started seeing this error and it seems to occur fairly frequently when making calls to the database or any other database in our Elastic Pool. The DTUs aren't being maxed out and the resource dmvs don't seem bad.

  Failed to enter Common Language Runtime (CLR) with HRESULT
  0x80131022. This may be due to low resource conditions. (db)

Here's what I got from the system resource governor pool

Resource Pool Name           cache_memory (MB)   used_memory (MB)
internal                     104.773437          1577.125000
default                      37.609375           38.796875
SloSecSharedPool             2.914062            8.156250
InMemBackupRestorePool       26.210937           101.437500
InMemDmvCollectorPool        186.195312          203.406250
InMemMetricsDownloaderPool   2.234375            2.250000
InMemDTAPool                 0.000000            0.000000
SloHkPool                    0.000000            0.031250
InMemQueryStorePool          22.453125           35.304687
InMemWIAutoTuningPool        3.312500            4.062500
InMemXdbLoginPool            3.976562            6.250000
PVSCleanerPool               0.000000            0.000000
InMemTdeScanPool             0.000000            0.000000
SloSharedPool1               1108.890625         1234.312500

From the sys.dm_os_performance_counters this is what it looks like for the past 3 hours.

cpu%    data_io%   log_write%   memory_usage%   max_worker%   sessions%
22.37   73.51      16.54        41.56           5.50          0.43

This doesn't seem like a common error for SQL Azure as I cannot find anything that pertains to this happening past SQL Server 2008. Any help would be appreciated.

Update:

We got a response from the Microsoft support rep and it looks like our memory usage was completely capped out.

After moving the elastic pool up a tier the error has gone away. We have ~56GB of memory instead of the ~28GB we had previously and the error ceased. This would have probably also moved us to a different server on Azure which may have fixed the problem for now. The site has been running at around 78% on memory usage with the buffer cache hit ratio @ 8588 and PLE of 87332. Now it's operating at usage under 2% across the board for CPU, Workers, DataIO, etc. during normal load which seems like a huge waste.

We could not definitively identify what was causing the errors but we assume it was the complete memory usage as the site is running fine for now...

While Azure SQL Database (not the new Managed Instance) does not support custom SQLCLR Assemblies (i.e. the "CLR enabled" server-level configuration option), CLR is still used internally for the following features:

CLR datatypes:

Geometry
Geography
Hierarchyid

Built-in functions:

introduced in SQL Server 2012:

FORMAT
PARSE
TRY_PARSE

introduced in SQL Server 2016:

AT TIME ZONE
COMPRESS
DECOMPRESS
sys.time_zone_info

maybe others

SSIS (Fuzzy Lookup / sp_FuzzyLookupTableMaintenanceInvoke, etc)
Change Data Capture (CDC)
Replication
Master Data Services
Policy Based Managemen (PBM; originally named "Dynamic Management Framework (DMF)" )
External Tables (including External Data Sources and possibly External File Formats): This functionality is new as of SQL Server 2016, and this functionality using CLR internally has been mentioned in other answers here by Joe and Henrik. Both Joe and Henrik indicate being told by Microsoft that External Tables rely upon CLR, and while I have not been able to confirm this directly (by seeing the system App Domain get created when using any of this functionality), I was at least able to confirm that when "Lightweight Pooling" mode is enabled, all 3 of these components fail with the following error:

  Msg 7432, Level 16, State 0, Line XXXXX
  Heterogeneous queries and use of OLEDB providers are not supported in fiber mode.

maybe others

Of course, this does not point to what would be hogging memory. But it should help identify the affected area(s).


Does this database table violate normalization rules? I’m working with Access 2010.

I’m developing a database for a public art program – we paint large scale murals on walls.
The database tracks blank walls in the city as potential sites for murals.  It includes information about the building itself and the surrounding property such as the lot the wall faces.

My main question is about the WallsMaster table.  As you can see there are about 30 fields… and about 10-15 more that I’m thinking of adding resulting in 45 fields, maybe even more.

As far as database performance is concerned:

is it better to separate these out into multiple tables or keep them all in one?
Should I break up WallsMaster and have another table that cover my general categories within this table…. maybe called Damage and one called Obstruction and one called FacesLot etc… and then set up FK relationships between them and WallsMaster?

I am  thinking about normalization rules… just not sure if these qualify as groups of related/repeating data as far as 1NF is concerned.  My understanding is that is more about not having a table with something like AuthorName, Book1, Book2, Book3, etc.

Here’s a rough schema of my database:

Table: WallsMaster
WallID (PK)
StreetAddress
City
State
Zip
BldgName
Occupied_or_Vacant
Faces_Direction (NSEW)
Residential_or_Commercial
Historical_Property (Yes/No)
Visible_to_traffic (Yes/No)
Faces_Parking (Yes/No)
Faces_Fenced_Lot (Yes/No)
Faces_Abandoned_Lot (Yes/No)
Faces_Garden (Yes/No)
Faces_ParkPlayground (Yes/No)
Faces_street (Yes/No)
Wall_Surface (Lookup: Brick, Stucco, etc)
Damage_Water (Yes/No)
Damage_Crumbling (Yes/No)
Damage_Graffiti (Yes/No)
Damage_Other (Yes/No)
Obstruction_Trees (Yes/No)
Obstruction_Powerlines (Yes/No)
Obstruction_Other (Yes/No)
Number_Stories
Height
Width
Image (Link)
GoogleStreetView (Link)
Notes (memo field)

Table: WallContacts
ContactID (PK)
WallID (FK)  many:many.  i.e., one wall can have many contacts (owner, manager, tenant) and one contact can be affiliated with many properties (i.e., one owner owns several walls)
FirstName
LastName
Address
City
State
Zip
ContactType (Lookup: Owner, Manager, Neighbor, etc.)
Phone
Email

Table: WallInteraction  (Catalogs each time our staff talks to someone affiliated with that wall or conducts an inspection of the property, etc)
InteractionID(PK)
WallID (FK)
StaffName
Date
InteractionStatus
Notes
(this may expand to include more fields as we work with this more)

Thank you!!

WallsMaster doesn't break any of the rules, if each of those fields is an independent property of the wall. I would suggest it's probably not worth redesigning...

This is assuming that the properties listed are the fixed set of properties. If there are going to be extra things added, then it would be worth splitting it out into a table which links properties and walls.


Login to local db with ip I install sqlserver and login with SSMS with windows auth. and server = DESKTOP-XXXXX\MSSQLSERVER01
When I tried to connect with 127.0.0.1 ot 127.0.0.1:1433 I can't
I put into TCP/IP
 but still I can't login with 127.0.0.1\MSSQLSERVER01
why is that?

If you installed a SQL server named instance, by defaut, it have to be contacted on a random port not ont the port 1433.
Please use 127.0.0.1\MSSQLSERVER01 or change the tcp/ip configuration and set a fixed port.
Verify that sql server agent is running. This is how a named instance is translated to the right dynamic or static port.


Aggregate query with min Having a product_stock table with following structure:

product_id
warehouse_id
stock
price

Which has bunch of records (same product can be in different warehouses):

1, 1, 0, 500
1, 2, 5, 505
1, 3, 7, 508
2, 1, 0, 400
2, 2, 0, 404

Now, for every product_id, I want to select cheapest one in stock and if product is not in stock anymore, select price and warehouse as NULL - basically, the result should be:

1, 2, 5, 505
2, NULL, 0, NULL

Here's a sqlfiddle.

[Update]: Almost nailed it (now need to figure out how to select appropriate warehouse):

select product_id, min(price) from (
    select product_id,
        CASE WHEN stock = 0 then NULL else warehouse_id end,
        CASE WHEN stock = 0 then NULL else price end from stock
) AS f group by product_id;

Returning (still need to figure out the warehouse_id):

product_id  min
1   505
2   (null)

[Update 2]: I was able to get the warehouse_id, but this query kills the row without price:

SELECT stock.product_id, stock.warehouse_id, stock.price FROM (
    SELECT product_id, min(price) as price FROM (
        SELECT product_id,
          CASE WHEN stock = 0 then NULL else warehouse_id end,
          CASE WHEN stock = 0 then NULL else price end
        FROM stock
    ) AS f GROUP by product_id
) AS ff JOIN
stock on stock.product_id=ff.product_id and stock.price = ff.price;

Result:

product_id  warehouse_id    price
1   2   505

This can be much simpler, yet, with DISTINCT ON:

SELECT DISTINCT ON (product_id)
       product_id
     , CASE WHEN stock = 0 THEN NULL ELSE warehouse_id END AS warehouse_id
     , stock
     , CASE WHEN stock = 0 THEN NULL ELSE price END AS price
FROM   product_stock
ORDER  BY product_id, (stock = 0), price;

Assuming stock to be NOT NULL.

SQL Fiddle.

About DISTINCT ON:

Select first row in each GROUP BY group?

Postgres has a proper boolean type and one can ORDER BY any boolean expression. FALSE sorts before TRUE sorts before NULL. So rows with (stock = 0) sort behind rows with any other value for stock - except NULL, which would sort last.


evaluate execution plan with different statements I have 2 different sql statements similar to this:
SELECT * FROM complicated_view v WHERE v.day = DATE '2020-01-25';
SELECT * FROM complicated_view v WHERE v.day = DATE '2021-01-25';

while both of them could be executed with the same execution plan, they don't. One is slow, the other one is fast. Now I'm trying to figure out why. What I'm currently looking for, is a way to evaluate an SQL statement identified by its sql_id with different execution plans, so I can see which step the optimizer estimates to be pretty costly. Something like this:
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash2');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash2');

Unfortunately, there is no function "evaluate_plan" in dbms_xplan, it's a functionality I'm missing and hope to get a hint on how to get this information.
That way I could identify the step that the optimizer considers to be too costly and work on that.
Is there any functionality in oracle that can give me this information?
oh, and here is the advanced question: Can I do this and in addition also add the optimizer_feature enable, something like this:
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '19.1.0');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '12.2.0.1');

we're getting very different runtime behaviour due to this setting.
I'm not looking for help on how to make my view better. I'm not asking WHY the statement is slow. I'm looking for help on HOW to work on problems like this. I'm looking for a way to evaluate an execution plan for a statement in a scenario where it COULD be executed with the execution plan, but has never been, since another execution plan was always used.

If you can run the query with the different plans (i.e. if it does not run forever) you can use this approach to see which step ran longer than the optimizer expected:
SELECT /*+gather_plan_statistics*/ *
FROM complicated_view v
WHERE v.day = DATE '2020-01-25';

select *
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

If you search for gather_plan_statistics you will see a number of articles that explain how to use it. I look for steps in the plan that take the most time and whose actual rows are very different from the optimizer's estimated row count.


How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql? I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query.

Simply i can use IF-ELSE statement and write the select combinations in it.

Is there any way in sql to avoid this select combinations(all combinations with 4 parameters)?

Something like

WHERE
   (@p1 IS NULL OR SomeCol1 = @p1)
   AND
   (@p2 IS NULL OR SomeCol2 = @p2)
   AND
   (@p3 IS NULL OR SomeCol3 = @p3)
   AND
   (@p4 IS NULL OR SomeCol4 = @p4)

This will probably run slower then using separate SELECTs separated by IF. However, you do have 16 permutation. So I'd probably combined both techniques:

IF (common combination of NULL parameters #1)
 SELECT ...
ELSE IF (common combination of NULL parameters #2)
 SELECT ...
ELSE
 SELECT using WHERE above


Dynamic conversion of long data to wide with multiple columns I'm trying to convert long data:

ID|SchoolID|Section|RepScore|SportsScore|PartyScore
1 |20      |1      |23.2    |70.2       |42.3
2 |20      |6      |65.2    |75.8       |52.3
3 |20      |7      |77.2    |72.2       |66.3
4 |21      |10     |13.2    |40.2       |72.3
5 |21      |11     |25.2    |55.8       |72.3
6 |21      |12     |37.2    |62.2       |76.3

to wide data (I am using only the first three rows to keep it short here):

SchoolID|RpScr1|RpScr6|RpScr7|SprtScr1|SprtScr6|SprtScr7|Prty1|Prty6|Prty7
20      |23.2  |65.2  |77.2  |70.2    |75.8    |72.2    |42.3 |52.3 |66.3

I have tried this:

DECLARE @cols VARCHAR(1000)
DECLARE @cols2 VARCHAR(1000)
DECLARE @cols3 VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols2 = STUFF(( SELECT distinct  '], [b' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SELECT  @cols3 = STUFF(( SELECT distinct  '], [c' + cast([Section] as varchar)
                        FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SET @sqlquery = 'SELECT * FROM
      (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore
       FROM [dbo].[SchoolData] z) base
       PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS finalpivot
       PIVOT (Max(SportsScore) FOR [Section] IN (' + @cols + ')) AS finalpivot2
       PIVOT (Max(PartyScore) FOR [Section] IN (' + @cols + ')) AS finalpivot3'

EXECUTE ( @sqlquery )

  This is the error I get:   Msg 8114, Level 16, State 1, Line 7 Error
  converting data type nvarchar to int. Msg 473, Level 16, State 1, Line
  7 The incorrect value "a9" is supplied in the PIVOT operator. Msg 207,
  Level 16, State 1, Line 8 Invalid column name 'Section'.

When I try it with just one like this:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
    SELECT  @cols = STUFF(( SELECT distinct  '], [a' + cast([Section] as varchar)
                            FROM [dbo].[SchoolData] FOR XML PATH('') ), 1, 2, '') + ']'
SET @sqlquery = 'SELECT * FROM
          (SELECT z.Section,z.RepScore,z.SportsScore,z.PartyScore
           FROM [dbo].[SchoolData] z) base
           PIVOT (Max(RepScore) FOR [Section] IN (' + @cols + ')) AS   finalpivot'

It works for just one column and gives column names like this [1],[6],[7]. But it does not work for multiple columns like I need. Any ideas?

are you looking for this,

CREATE TABLE #T(ID INT,SchoolID INT,Section INT,RepScore DECIMAL(5,2)
,SportsScore DECIMAL(5,2),PartyScore DECIMAL(5,2))
INSERT INTO #T VALUES
(1,20,1 ,23.2,70.2,42.3)
,(2,20,6 ,65.2,75.8,52.3)
,(3,20,7 ,77.2,72.2,66.3)
,(4,21,10,13.2,40.2,72.3)
,(5,21,11,25.2,55.8,72.3)
,(6,21,12,37.2,62.2,76.3)
--SELECT * FROM #T

DECLARE @colRpName VARCHAR(1000)
DECLARE @colRpVal VARCHAR(1000)
DECLARE @colSportVal VARCHAR(1000)
DECLARE @colPartyVal VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT
@colRpName = STUFF(( SELECT distinct  '], [' + cast([Section] as varchar)
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ']'
,  @colRpVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[a ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colSportVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[b ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colPartyVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[c ' + cast([Section] as varchar)+']'
                        FROM #T t1 FOR XML PATH('') ), 1, 2, '') + ''
from #T t

--SELECT @colRpName,@colRpVal

SET @sqlquery = '
SELECT fp.SchoolID,'+@colRpVal+'
,'+@colSportVal+'
,'+@colPartyVal+'
FROM
(SELECT SchoolID,[section],RepScore

FROM #T z) base
PIVOT (sum(RepScore) FOR [section] IN ('+@colRpName+')) AS   fp
CROSS APPLY
(
SELECT fp1.SchoolID,'+@colSportVal+'
FROM
(SELECT SchoolID,[section],SportsScore

FROM #T z) base1
PIVOT (sum(SportsScore) FOR [section] IN ('+@colRpName+')) AS   fp1
WHERE fp1.SchoolID=fp.SchoolID
)ca
CROSS APPLY
(
SELECT fp2.SchoolID,'+@colPartyVal+'
FROM
(SELECT SchoolID,[section],PartyScore

FROM #T z) base2
PIVOT (sum(PartyScore) FOR [section] IN ('+@colRpName+')) AS   fp2
WHERE fp2.SchoolID=fp.SchoolID
)ca1
  '

--print @sqlquery
  EXEC(@sqlquery)-- please use sp_executesql

DROP TABLE #T


Details about existing roles and users in azure sql database I would like to get details of all users existing in a sql server DB and the respective roles they are part of and the permissions granted/denied for the a role/user.

what is the best way to get this details?

If you are talking about Azure SQLDB or Managed instance you can use this script:

SELECT role.name AS RoleName,
       member.name AS MemberName
    FROM sys.database_role_members  rolemember
JOIN sys.database_principals AS role
     ON rolemember.role_principal_id = role.principal_id
JOIN sys.database_principals AS member
     ON rolemember.member_principal_id = member.principal_id
     order by 1

I hope this helps.


Postgres cost of large volume of inserts in many tables in a single transaction What is the additional cost incurred by inserting large volume or rows (millions), in many tables in a single transaction ?

Can something be done (tuning parameters) so that the cost of inserting in large volume in a single transaction approaches the cost of doing it in autocommit ?

You have it backwards - it is generally better to do many rows in a single transaction than one at a time with autocommit. The reasons are a) disk I/O and b) network round trips between client and server. You will need to run benchmarks to find the ideal batch size for your data and your hardware - try 100, 1000, 10000 sized transactions and see. At some point it will peak and past that the transactions will be "too big" as you bump into some other limit.


SQL Server query takes too long to execute. What could the problem be? There are two tables

Table 1 :

Year     char(4)
MonthKey char(2)
ComplaintCount int

Table 2 :

FileCode  varchar(50)
StartDate varchar(10)
EndDate   varchar(10)

I wrote a query and the query takes a long long time to execute. Of course there is no result and its still executing and i think it is in a loop maybe, which is not expected!

The query is:

SELECT dbo.GetLastDayOfMonth(c.Year,c.Monthkey) AS DateKey,
       ComplaintCount,
       Count(FileCode)
FROM Table1 C
INNER JOIN Table2 F
    ON dbo.GetLastDayOfMonth(c.Year,c.Monthkey) = dbo.GetLastDayOfMonth(LEFT(f.FileFormationDate,4),SUBSTRING(f.FileFormationDate,6,2))
GROUP BY dbo.GetLastDayOfMonth(c.Year,c.Monthkey), c.ComplaintCount

The function GetLastDayOfMonth() returns the last day of month according to the Persian Calendar. There are 3,000,000 records in Table 1 and 3 records in Table 2. Even running the JOIN by itself (without the GROUP BY) is still executing and until now i can see 1,000,000 records in the Results pane.

What could the problem be and how can I fix it?

This is the code for the function:

CREATE FUNCTION [dbo].[GetLastDayOfMonth] (
    @year CHAR(4)
    ,@Month CHAR(2)
    )
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @result VARCHAR(10)

    SET @result = (
            SELECT max(datekey)
            FROM StageArea.Common.DimTime
            WHERE [Year] = @year
                AND MonthKey = @Month
                AND LastDayOfMonthKey = 1
            )

    RETURN @result
END

Thank you for your effort and suggestions. Here is the solution which took only 6 minutes to execute:

WITH T1
AS (
    SELECT FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2)) AS Datekey
    FROM [StageArea].[Sales].[viwLossCalculation] f
    GROUP BY FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2))
    )
    ,T2
AS (
    SELECT dbo.GetLastDayOfMonth(c.Year, c.Monthkey) AS datekey2
        ,ComplaintCount AS complaintcount
    FROM [StageArea].[Preservation].[viwComplaint] c
    )
SELECT C.DateKey
    ,f.complaintcount
    ,COUNT(DISTINCT c.FileCode) AS FileCodeCount
FROM T1 C
INNER JOIN T2 F ON C.DateKey = F.DateKey2
GROUP BY C.DateKey
    ,f.ComplaintCount;


Postgres query slow, shows long running active queries, statement_timeout does not work I have a Postgres database that's accessed through a node.js api using a mobile react native app. Don't thing the code stack specifics matter, but essentially there are queries in the psql db that stick around as status = 'active' for days that are slowing the whole database way down. Using the following to check:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Manually killing the active queries with

SELECT pg_cancel_backend(__pid__)

fixes the issue and gets the database back to normal speed. But, of course we want to make sure this doesn't necessarily happen intermittently and slows down our app for all users.

Another thing to mention is that I've attempted to put a time limit on the active queries with statement_timeout but I'm not sure I'm doing this correctly. Testing it with...

SET statement_timeout = 1;
SELECT * from "table_name";

does not work. That is... the SELECT statement above takes 400 milliseconds and doesn't terminate. Seems that I don't have this procedure right. The statement does work on

SET statement_timeout = 10000;
SELECT pg_sleep(15);

with it canceling the second statement. Does that mean that statement_timeout only works on idle processes and not execution time?

The specifics of the query (including just anything out of the ordinary, everything else is normal 'where' statements)

It's a select statement joining three tables together: two of them are normal, one is a zipcodes table with 40k+ rows indexed on the zipcode column.
There is a CASE statement in the SELECT piece (before where), like such

(CASE
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 5
                        THEN 0
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 10
                        THEN 1
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 20
                        THEN 2
                        ELSE (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))))
                    END   )
                    AS distance

Yes, this definitely seems like it would take some time, however it does not for most users. And still, why would this query show as 'active' for days. For example, if it couldn't find the zipcode in the zipcodes table it would either not return anything or error out.

Any and all help is greatly appreciated! Even if you see anything out of place please feel free to comment! Forgive me for any simple things I missed :-)!

SET statement_timeout = 1;
SELECT * from "table_name";

  does not work. That is... the SELECT statement above takes 400
  milliseconds and doesn't terminate. Seems that I don't have this
  procedure right.

I don't know what else might be going on in your system, but this definitely does work for me as long as "table_name" is large enough to take more than 1 millisecond.  Are you issuing this from 'psql'?  Or if from something else, then what is that?  If you can't trust that your commands are actually getting sent to the database, or that any errors are reliably getting sent back, that is going to make it hard to accomplish anything else.


How do you select "array"-type columns via the DBLINK function? Using PostgreSQL, I can easily do a select statement to return a column that is an array of the datatype point. i.e. colA is the datatype point[]

select colA from tbl

Returns a column that contains an array of points:

+-----------------------------+
|            colA             |
+-----------------------------+
| (180.0,90.0),(-180.0,-90.0) |
| (64.0,38.0),(-26.0,-40.0)   |
| etc                         |
+-----------------------------+

I'm certain the column is of type point[], because select colA[0] returns as expected.

I'm trying to do this query from another server using DBLINK(). This is the query:

select
colA
from dblink(
  'my_conn',
  'select
   colA
   from public.tbl'
) as s (
  colA point[]
)

This query fails with this error message:

  SQL Error [22P02]: ERROR: malformed array literal: "(180,90),(-180,-90)"
    Detail: Array value must start with "{" or dimension information.

I tested that I am able to select the datatype point using DBLINK. I.e. this query succeeds:

select
colA
from dblink(
  'my_conn',
  'select
   colA[0]
   from public.tbl'
) as s (
  colA point
)

So, my question is How do you select an array via the DBLINK function?.

The solution I found was via casting:

Specify the column in DBLINK SQL as TEXT
Cast to text in the DBLINK SQL statement
Cast from TEXT to POINT[] outside of DBLINK

PostgreSQL: What is the best way to cast from from text to point[]?


How to reclaim space taken by an index that partially built and was terminated by a power outage I'm running postgres (postgis) 9.4.2 on a mac (10.10.4).

I've got a couple big tables (several TBs).

During an index build on one of them that takes about a week, I watched the available HD space drop as you'd expect to nearly the point at which the index would be finished  when a power outage lasted longer than the battery unit and the system went down. I had buffers off, and fillfactor=100 during the build since it's a static datasource. On reboot, the available space left on the drive is exactly where it was at nearly the end of the index build. Vacuum analyze doesn't free the space.

I tried dropping the table and re-ingesting, and that didn't drop the space. Now I'm at a place where I don't have enough space to build the index.

Are the files generated during the index build stuck in some limbo where they can't be removed by the system because of the way the machine went down during the power outage?

When I look at the table sizes + indexes in the db (which is the only data on that drive) they add up to about 6TB.  The drive is 8TB, and there is less than 500GB left on the drive, so it seems there are about 1.5TB lost somewhere which is about the size that index would have been.

Any ideas?

Normally we'd expect that when postgres was restarted, the crash recovery process would have removed files related to a rollback'ed index from the data directory.

Let's assume that it didn't work, or at least that it has to be checked manually.

The list of files that should be in the datadir can be established with a query like this:

select pg_relation_filenode(oid)
   from pg_class
  where relkind in ('i','r','t','S','m')
    and reltablespace=0
  order by 1;

reltablespace=0 is for the default tablespace. If the problematic index was created in a non-default tablespace, this 0 must be replaced by its OID in pg_tablespace.

i,r,t,S,m in relkind correspond respectively to indexes, tables, toast space, sequences, materialized views. All these objects have their data in files whose names match pg_relation_filenode(oid).

On disk, the data files are below $PGDATA/base/oid/ where oid is the oid of the database obtained by select oid,datname from pg_database.
If we're not talking about the default tablespace, base is replaced by PG_version_somelabel instead.

List and sort the files matching relfilenodes in that directory:

ls | grep -E '^[0-9]+$' | sort -n > /tmp/list-of-relations.txt

(that actually keeps only the first segment for relations that are larger than 1Gb. If there are lingering segments not attached to anything they should be considered separately)

and diff that file with the result of the query above.

If there are lingering data files that do not correspond to any object that the db knows about, they should appear in that diff.


Can I recover a TDE certificate by restoring the MASTER database? (We are, fortunately, not currently in this situation, just planning ahead to see what our options would be if it ever occurred.)

For a database encrypted with Transparent Date Encryption (TDE), a copy of the database backup is unrecoverable unless you have a backup of the certificate used to encrypt it.

What if you don't have that? Are there any additional options?

In the event of a total server failure, would restoring a backup of the MASTER database on new hardware also restore the certificate?

Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted.

This is the TDE encryption hierarchy:

Service master key (protected by Windows; tied to the service account credentials, and a machine key)
Database master key (in this case, the one for the master database)
Certificate
TDE encryption key

The first three items are stored in the master database, and can all be backed up. The fourth is stored (encrypted by the certificate from #3) in the header of the encrypted database.

So in a failure scenario, you would have to restore enough of the encryption hierarchy to allow you to read the TDE key. SQL Server creates the service master key at installation; thus while restoring the master database to a different instance will also restore items 2 and 3, the necessary key(s) to decrypt them will not be present. Result: unreadable data.

The two best options are to either restore the certificate (#3) from a backup (a good option if master cannot be restored for whatever reason), or restore your master database and its master key (#2) from a backup. Restoring the master key may be a better option if you have a lot of certificates/keys protected by this key, and need to make them all accessible at once. This comes with the same precautions normally associated with restoring the master database (collations, logins, database names and file paths, etc.)

Generally, I'd only recommend restoring master in a recovery scenario. For a migration/scale-out scenario (such as using Availability Groups/mirroring with a TDE-encrypted database), it's better to backup/restore the certificate (#3) so that it's encrypted using the master keys unique to each instance it's moving to. You will need to include the private key with the certificate backup.

In any case, you're going to have to make key/certificate backups, so guard them well, and store them in redundant, secure locations. Simply having a backup of master will not get you out of a TDE disaster; you're going to need a backup of at least one key or certificate.


What is the best approach to managing daily excel flat file imports into SQL Server with varying column counts per file? I need to load excel spreadsheets into SQL Server on a daily basis. It's simple enough to build an SSIS package but, the problem that I am facing is that the client is allowed to name the column headers as they like, the columns can be in whatever order, and the column count can vary.

This is the best I can get the owner to agree to.

Each client's excel file will have one column that is the same no matter what. This column will be the Tax ID Number.
Each client must use the same format on their excel spreadsheet each time they submit their file.

This is what the final product must look like.

I need to be able to load automate loading all the excel data and headers into a SQL Server database without losing any data with each client having varying column counts.
the asp.net mvc application must be able to query a tax id that is entered by the customer. The query must return the headers from the excel spreadsheet and all the corresponding data associated with that record.

I tried to get better consistency but, this is the best I can get. I have normally worked with data that had predefined parameters so, I would appreciate any help with suggestions to help me move this forward.

You're dealing with unstructured data here I'm afraid and honestly, I feel your pain. I don't think you can really do what you want in SSIS - partially because it will expect your Excel format to be fixed. Even if you get it fixed, with SSIS and Excel - you'll have problems with how it guesses the metadata types for columns based on a sample of the data in the sheet - with the Excel provider, you only have 2 choices for text data for example - NVARCHAR(255) and NVARCHAR(MAX). It can get ugly pretty fast if you try to enforce data-typing at a data level even with fixed columns but varying lengths.

As such, I think you'll need to embrace the unstructured nature of the data and store it as such - storing it as an XML/JSON data types and converting the data from the spreadsheet and letting the MVC application parse the XML. The only other way I can suggest is using something like a key-value store. There's huge drawbacks to this from a relational perspective though. This is commonly known as EAV (Entity-Attribute-Value)

You might also want to consider non-SQL Server alternatives that can take unstructured data and search on it as well.


Add variable value in single quoted string. Expand variable within single quotes I have tried different variations of the below commands but non work.
I simply want to run the following command using a variable within the location string:

CREATE TABLESPACE data OWNER appuser
  LOCATION '/var/lib/pgsql95/:variable/pg_tblspc/data';

What has been tried (variations):

EXECUTE CREATE TABLESPACE data OWNER appuser
  LOCATION ''/var/lib/pgsql95/' || base || '/pg_tblspc/data'';';

and

PREPARE tablespace (text) AS
  CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/$1/pg_tblspc';

EXECUTE tablespace('base');

and

\set myvariable 'base'
CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/':myvariable'/pg_tblspc';

Try this:

-- This will concatenate the values:
\set myvariable '/var/lib/pgsql95/' :myvariable '/pg_tblspc'

-- This will expand the variable single-quoted:
CREATE TABLESPACE aspire_data OWNER aspireapp LOCATION :'myvariable'


What do cursors do internally? I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR...

The client saw this SP ran for more than a day so, my manager assigned this to me.

Upon searching, I used table variable and a while loop to iterate each record.

To my surprise, the difference between the two was huge.

Someone told me that while loop is essentially, a CURSOR.

But when I used table variable with a WHILE LOOP, it ran very very very fast.

Now, what do cursors do internally?

Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose.

There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other resources are needed, could cause parts of the query to be rerun, and so forth. Also your inner loop may be doing things that affect the behaviour of the cursor by updating base tables it reads from in a way that makes the database engine need to do more work.

If we could see the two bits of code you are talking about we could be more specific.

By running the initial query into a table variable and looping through that you are essentially emulating a STATIC FAST_FORWARD READ_ONLY cursor, those options remove quite a chunk of the complexity that the engine might otherwise have to worry about. Your main SELECT is definitely run once and in one go, and your loop is not competing with other activity on the base data while it interacts with the copy in the variable.

Of course you might also find that something within your two loops (the one using the cursor and the one without) has significantly different performance metrics if they are not written exactly the same.


Procedure to replace a BDR server I have set up a 2-node PG 9.4 system with BDR. Replication works great.

Now, let's suppose that one of the two nodes needs to be replaced. In fact, let's be more specific. Let's say that the node on which I ran the command to create the group : SELECT bdr.bdr_group_create() has to be replaced.

What is the procedure to do this?

Thanks

We use to do the following:

1- create a new node

2- Copy data from the node that you want to keep

sudo -u postgres \
    /usr/lib/postgresql/9.4/bin/pg_basebackup \
    -h $HOST_TO_CLONE -p 5432 -D $DATADIR

3- Join the cluster:

sudo -u postgres  /usr/lib/postgresql/9.4/bin/bdr_init_copy \
    -d "host=$HOST_TO_CLONE dbname=$DB_NAME port=5432" \
    '--local-dbname="host=$LOCAL_IP dbname=$DB_NAME port=5432" \
    -n $LOCAL_IP -D $DATADIR

After bdr_init_copy finish, you can delete the init node with the following command:

SELECT bdr.bdr_part_by_node_names(ARRAY(SELECT(bdr.bdr_get_local_node_name()));

This works ok in our installation.

Regards


Unable to restore a PostgreSQL database from a .backup file I am using Ubuntu 18.04 and PostgreSQL. For the GUI, i am using pgadmin4 (browser version). I created a new database and when trying to restore, the following error message comes up:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"> <title>400 Bad Request</title>
Bad Request

The CSRF token is missing.

I Tried using the terminal way using this tutorial but it does'nt seem to work.

Here is the screenshot of the problem:

An alternartive is using pg_restore command


How to use value return by postgresSQL function in another query I am calling PostgreSQL function that returns me the text, How can I store this value in local variable and use the value/variable in another query. ?

It's not very clear what exactly you mean by a local variable - there is no such thing in SQL.  In this case, just use the function in the place where you would put your variable (like a_horse_with_no_name suggested in his comment), like

SELECT important_thing FROM fancy_table WHERE it_thing_happened > now();

now() is a function, and illustrates the case nicely.

If you are about PL/pgSQL variables, you can do

[...]
my_timestamp := now();  -- not very meaningful IRL
[...]

Note that you can do this only inside a PL/pgSQL function or a DO block.

Last, if you mean a psql (that is, client-side) variable, you can use the \gset command:

test=# SELECT now() AS ts
test-# \gset i
test=# \echo :ts
2017-05-31 10:34:48.148068+02

Note that \gset is available from psql version 9.3 only.


create a stored procedure if it doesnt exist using a ddl in db2 My requirements are,

I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it.

What I tried is,

IF EXISTS (select procname into Migration_procname from sysibm.sysprocedures where procname like 'GIAM_PRIVILEGE_MIGRATION') THEN
    DROP PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION;
ELSE
CREATE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL
BEGIN

......
......
update/select statements
......

END
@

which didn't work.
so I even tried with creating another procedure which is doing same task still no success.

The error message was

  DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IF EXISTS (select procname" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601

Any pointers will be helpful.

NOTE: I am using DB2/LUW 9.5

IBM Added The OR REPLACE option in version 7.1

So your code should work like this :

CREATE OR REPLACE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL
BEGIN

......
......
update/select statements
......

END


MySQL Query missing from the slow query log I've been looking at queries creating many temporary tables in the performance_schema.events_statements_summary_by_digest.

FROM performance_schema.events_statements_summary_by_digest
where SUM_CREATED_TMP_TABLES > 0 and digest is not NULL
order by SUM_CREATED_TMP_TABLES desc limit 1\G

I have a clear worst offender:

                 COUNT_STAR: 76703
             SUM_TIMER_WAIT: 96029343223000
             MIN_TIMER_WAIT: 631886000
             AVG_TIMER_WAIT: 1251963000
             MAX_TIMER_WAIT: 19597013000
              SUM_LOCK_TIME: 14475751000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1125890
          SUM_ROWS_EXAMINED: 29941322
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 76742
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 76754
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-09-25 00:08:30
                  LAST_SEEN: 2019-09-25 11:22:49

If my understanding is correct.  The above suggests the query has been executed 76,703 times since 2019-09-25 00:08:30 and created 76,754 temporary tables.  Implicitly.

What I don't understand is that if I set the long_query_time to 0 the query does not show up in the slow query log.
I thought if the long_query_time was set to 0 all queries are written to the log.
Thanks in advance.

Thanks to Vinicius@Percona.  He explained the reason the query does not appear in the slow query log is due to the fact that existing connections are not impacted by the long_query_time being changed.  Only new connections.


SQL Server Transaction Replication on different versions of Windows Currently we use SQL Server 2014 Transaction Replication in Windows Server 2012 (both Publisher and Subscriber are on different servers with the same operating system).

We need to add a new subscriber, and my question is this: do we need to have the same operating system for the newly added subscriber, i.e. Windows Server 2012, or can I use the Windows 7 operating system.

Note : Both Publisher and Subscriber are in two different domains.

You can use SQL Server replication on different Windows versions.  What is more important is the version of SQL Server.

https://blogs.msdn.microsoft.com/repltalk/2016/08/12/suppported-sql-server-versions-in-replication-topology/


Mariadb default logs location & --log-error I am using mariadb and I to investigate some issue I wanted to check the logs. To my surprise, log file is not generated for mariadb.

I suspect this cannot be the case so I am doubting my search skills.

MariaDB [(none)]> show variables like 'log_error'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

I have added the entry in my.cnf still above field is coming to be empty.

[root@cslcodev11-oem ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@cslcodev11-oem ~]#

So, I want to know do we have any default location where these logs are getting generated, if the path cannot be read from config files.

Update:

After some investigation, I found this information,

Reference: https://mariadb.com/kb/en/mariadb/error-log/

Activating the Error Log

The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable).

On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).

On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.

So now I understand that due to some reason the configuration log_error in my.cnf is not considered.  So as per the suggestion, on Unix system, if --log-error is not used, logs are redirected to stdout.

BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

Going off the documentation, the error log should be in the default location of /var/lib/mysql and it should be named the default hostname.err, but it's just not there.

In the directory /var/lib/mysql I do have a tc.log, iblogfile0, iblogfile1, aria_log.00000001, and aria_log_control files. But I cannot read any of them, they are encrypted. Anyone know what these are?

  BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

After running the following command,

cat /etc/systemd/system/mysqld.service | grep "mysql"

we can see that the line that systemd uses to start the process is ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION. The options passed to MariaDB are stored in the $MYSQLD_OPTS variable.

If we also run cat /etc/systemd/system/mysqld.service | grep "MYSQLD_OPTS" we get the following.

MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
Use the [Service] section and Environment="MYSQLD_OPTS=...".
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

The file MY_SPECIAL.conf does not exist on my machine, as find / -name "*MY_SPECIAL*" reveals.

I do have entries for mysqld in journalctl. So I'm thinking that it uses the the systemd system logs by default for us systemd users.

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

  systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use journalctl -u mariadb.

This answers the question because --log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.


Do SQL Server disabled databases cause any usage concerns? I have a SaaS e-commerce app. When clients stop using the service I disable their SQL Server Database and User Account rather than delete them.

Do these disabled databases cause any concerns related to stress on the server or SQL Server limitations on databases? For example is the any type of overhead just leaving them there? I have our backup tasks setup to not make backups for disabled databases.

I use SQL Server 2008 Web Edition.

I don't think you'll see much overhead except for system processes that enumerate databases for certain tasks. E.g. trace the server and expand the databases node in Management Studio - I bet you see a LOT more activity on the system when there are 50 active databases and 500 disabled databases compared to just 50 active databases.

Once a client has left, I would typically make a final backup of the database, mark the database as offline, wait 30 days in case they make any post-requests for data or reports, then drop the database once the backup is off-site (or whatever your "really backed up" status means).


The publication 'My_publication' doesn't exist? We are trying to test the success of Transactional replications.
by Googling , i found that i need to execute in our publisher this stored procedure :

sp_publication_validation

However , When i execute :

exec sys.sp_publication_validation
@publication=ReplicationFaouzi,
@rowcount_only=0,
@full_or_fast=0,
@shutdown_agent=0

I get this error :

  Msg 20026, Level 16, State 1, Procedure sp_MSpublication_validation, Line 57
      The publication 'ReplicationFaouzi' doesn't exist ?

When i run this sql :

SELECT publication
FROM MSpublications
ORDER BY publication

I get in Result :

ReplicationFaouzi

what is wrong ?

Update

By checking the code of this stored procedure i found that it calls un-found stored procedure  sys.sp_MSreplcheck_publish and sys.sp_MSrepl_getpublisherinfo :

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
    return (1)
end

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo  @publisher      = @publisher,
                                                @publisher_type = @publisher_type OUTPUT,
                                                @rpcheader      = @cmd OUTPUT

sys.sp_MSreplcheck_publish exist under a database named model ,and i can't find sys.sp_MSrepl_getpublisherinfo!!

Update 2

My Team re-created the  replication database and  now  this sql command:

 SELECT [name] FROM sys.databases WHERE is_published = 1;

dosen't return any value , but this :

 SELECT [name] FROM sys.databases WHERE is_merge_published = 1;

returns 'Guichet'

Now, when  i run :

USE [Guichet];
GO
EXEC sp_helppublication;

I get nothing  !

You need to run that procedure in the context of the publication database (the database that is the source of the data you wish to replicate).  You can do that with a USE statement:

USE [YourDatabaseName];
GO

EXEC sys.sp_publication_validation
    @publication=ReplicationFaouzi,
    @rowcount_only=0,
    @full_or_fast=0,
    @shutdown_agent=0

If you're unsure of which database is the publication database, you can run this query to find which databases are configured as publishers:

SELECT [name] FROM sys.databases WHERE is_published = 1;

Once you have the database name from that query, you can run this to see the name of the actual publications that should be passed to sp_publication_validation.  In your case, the publisher is named Guichet, so you'd run:

USE [Guichet];
GO
EXEC sp_helppublication;

This will give you the value that needs to be passed to the @publication parameter.

Note that all of the above is written in the context of transaction replication (which is what the original question was about).  Different steps are required for different types of replication.

There is a great "information gathering" script on the Microsoft Docs website that walks through getting info on distributors and publishers when using different types of replication:

Distributor and Publisher Information Script


MySQL Trigger 2 is not working I need some help identifying what I did wrong.

I created two tables to track history from my main table. When I update a state from 0 to 1, the first trigger copies the data into history table 1.

But when I change the state from 1 to 2, the second trigger is not triggering, thus not copying the data into history table 2.

CREATE TABLE stateOneHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

CREATE TABLE stateTwoHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

Below are the triggers I created.

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE
ON syslog
FOR EACH ROW

BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
        THEN
            INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ;

DELIMITER //

CREATE TRIGGER stateTwoHistoryTableTrigger
AFTER UPDATE
ON syslog
FOR EACH ROW

BEGIN
    IF (old.syslogState=1 & new.syslogState=2)
        THEN
            INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ;

Are you sure  stateTwoHistoryTableTrigger exists? I'm asking since at least some versions of MySQL don't support multiple triggers with the same action time and event for the same table. You can check if the trigger exists like:

select trigger_name
from information_schema.triggers
where EVENT_OBJECT_TABLE = 'syslog';

If that is the problem you can add both actions in one trigger like:

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
    THEN
        INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

ELSEIF (old.syslogState=1 & new.syslogState=2)
    THEN
        INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

    END IF;
END; //

DELIMITER ;

May I ask why you want two history tables? If you keep all history in one table you can derive the two history tables from there:

CREATE TABLE stateHistoryTable (
    historyDate DATETIME DEFAULT NULL,
    eventID int(11) NOT NULL,
    eventTIME decimal(15,0) NOT NULL,
    newestEventTime decimal(20,0) DEFAULT NULL,
    deviceIP varchar(20) NOT NULL,
    syslogMessage varchar(1000) NOT NULL,
    syslogState decimal(2,0) NOT NULL,
    eventCount decimal(5,0) NOT NULL
) engine=innodb;

CREATE TRIGGER stateHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState + 1 = new.syslogState)
    THEN INSERT ...
END IF;

CREATE VIEW stateOneHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 1;

CREATE VIEW stateTwoHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 2;


Personal and Global messaging database design I am trying to implement a database design system that will be sending messages to a person(thus 1 to 1) or many persons (1 to many).

    table --users
              |u_id | Name |
              | 01  | Aku  |
              | 02  | Sika |
              | 03  | Admin|

    table --messages
              |m_id | sender_id | Body   | Time |
              | 100 |  01       | hello  | 16:04|
              | 200 |  02       | Hi     | 16:08|
              | 300 |  03       | hi Guys| 19:12|

    table --recipient
              |r_id| m_id | recipient_id|
              | 50 | 100  |  02         |
              | 51 | 200  |  01         |
              | 52 | 300  |  01         |
              | 53 | 300  |  02         |

    table --message_status
              |m_id | recipient_id | status | deleted |
              |100  |   02         | read   |  no     |
              |200  |   01         | unread |  no     |
              |300  |   01         | read   |  yes    |
              |300  |   02         | unread |  no     |

From my schema above, a user can send a message to one person and he can also send a message to many persons.Once a user sends a message to a person, we will have all the recipients userid in the recipient table as well as in the message_status table . So assuming a user is sending one message to 1 million users at a time, it means, there will be a 1 million rows in both the recipient and message_status table.

Please can there be a way to handle this by reducing the many insertion of rows when a user sends a message to many users or that is the right thing to do.

NOTE

Please the system should allow every recipient to delete a recieved message if he/she so wishes, however their delete should not affect other recipients. So if user with u_id = 02 deletes her recieved message, user with u_id=01 should still view his message.

Thanks for helping.

You could combine recipient and message_status.  That would halve the number of inserts.


Fallback to known existing records when a record is missing Related to this question, I want to return module contents for various regions/languages. I built my site in English so I know I have all of those pieces, but since I'm getting translations from an outside agency I might be missing pieces. We want those pieces to fallback to English when in doubt.

Here are my tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...

modules

id | module
--   ------
1    header
2    main
3    footer

contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             2           This is my main
2    1             3           This is my footer
3    2             1           Este es mi encabezado
4    2             3           Este es mi pie de página

See here, I am missing the 'main' module (module_id=2) for 'es-ES' (language_id=2)

Here's how I'm pulling my data:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'es-ES' OR l.tag IS NULL

which returns

module | content
------   -------
header   Este es mi encabezado
main     null
footer   Este es mi pie de página

I want that null value for 'main' to fallback to the English version ("This is my main")
Is there a simple way to do this that I'm missing?

You need something similar to

SELECT m.module,
       COALESCE(c_esp.content, c_eng.content) content
FROM `modules` m
LEFT JOIN ( contents c_esp,
            languages l_esp ) ON (     m.id = c_esp.module_id
                                   AND c_esp.language_id = l_esp.id
                                   AND l_esp.tag = 'es-ES' )
LEFT JOIN ( contents c_eng,
            languages l_eng ) ON (     m.id = c_eng.module_id
                                   AND c_eng.language_id = l_eng.id
                                   AND l_eng.tag = 'es-ES' )


MySQL limits on huge number return zero rows I have table about 1,000,000 rows. this query work well:

SELECT * FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)
LIMIT 2000,10

But this query return zero rows:

SELECT * FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)
LIMIT 800000,10

What's my problem? Any configure or etc?

Table have 1,000,000 records but It look like Table doesn't have 800000+ records where

`articles`.`hash` NOT
IN (
'1z8y'
)

But table have 2000+ records where

`articles`.`hash` NOT
IN (
'1z8y'
)

LIMIT 800000,10 will return 10 records after 800000th records but it looks like you didn't have 800000 records which satisfy your where clause condition.

You can find no. of records which satisfy your where clause using this query

SELECT Count(*) FROM `articles`
WHERE `articles`.`hash` NOT
IN (
'1z8y'
)


In Oracle 12c, how can we implement pagination on large table using rowid? I am new to the database. I would like to implement pagination on large tables using rowid but what I found that rowid can be alphanumeric digits and not necessarily they are in any order so I wonder if we can use them to paginate the tables. The table from which I would like to get the records in pagination is very large table having more than 2 billion records and if we execute the query based on offset and length then that comes up with an error Ora1652 unable to extend temp segment in tablespace temp. So is there way to get the results in pagination using rowid.

Any help would be appreciated thank you in advance.

Edited:

Let's suppose I have a Table say LARGE_ROWS having ID and RANDOM two columns whereas ID is the primary key column.

SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    large_rows
                ORDER BY
                    large_rows.id
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    large_rows S
WHERE
    RN > 900
    AND P.RD = S.ROWID;

You need to try following by yourself multiple times to see if you are getting any improvement in performance with the correct result.

I tried different approaches and Following is giving good performance in my case.

DROP TABLE STUDENT;

CREATE TABLE student (RNO NUMBER, NAME VARCHAR2(1000));

-- I have just added 2,000,000 records
INSERT INTO STUDENT
SELECT LEVEL, 'STUDENT - ' || LEVEL
FROM DUAL CONNECT BY LEVEL <= 2000000;

-- selecting the 10th page of length 100 each
SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    STUDENT
                ORDER BY
                    RNO
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    STUDENT S
WHERE
    RN > 900
    AND P.RD = S.ROWID;

A Rowid is made by OOOOOOFFFBBBBBBRRR
O is object id，F is file id，B is block id，R is row number.
Starting from 11g some new mechanism is introduced. If the next rowid is in the same block with the current row, then the current block can be reused. Hence, "Consistent gets" increase.

I hope you will get direction in solving your issue.

Cheers!!


Can you provide rights to a snapshot without providing rights to the database? Is it possible to give a user or group rights to a snapshot without giving them rights to the database if the database and snapshot both reside on the same instance?

From Limitations and Requirements of Database Snapshots on TechNet:

  A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.

Update:

As Martin Smith suggested in a comment, one way is to grant permissions on database, take a snapshot, and then remove permissions. The login could be temporarily disabled for the period that rights were granted.


Daily Index Blocking There is ONE index rebuild which everyday is blocked causing the job to fail.  It is the same index and same stored procedure which blocks.  We used an automated stored procedure to rebuild or reorg each index based on fragmentation.  How can I fix this issue?  Any ideas are greatly appreciated.

We face this daily at work too, DBCC or Re-Index run into lock timeouts/blocks

What we can do (there's no one solution)

Check the blocking query (we have job to save lock/deadlock every
minute), investigate root cause, and fix if possible
Change timing of Re-Index job if possible
For SQL Job, set Retry
Check Ola's dbo.CommandLog table (used by
IndexOptimize) to see the error -> from there we can copy command
manually to re-run, or use procedure to re-run the "failed commands"
only


SELECT count vs DELETE JOIN. Why/how are they different? Can someone explain in what way these queries could be different and the results are not the same?

> begin;
Query OK, 0 rows affected (0.01 sec)

>select count(*) from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
+----------+
| count(*) |
+----------+
|    23666 |
+----------+
1 row in set (0.20 sec)

>delete b.* from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
Query OK, 23649 rows affected (0.74 sec)

Can someone please explain why the delete shows a different number of rows than the count? I am using the same join. Are they not equivalent?

The reason the count differs is due to multiple rows being present in tableA that have a foreign key reference to the same row in tableB.

When using an inner join the SELECT produces a separate row for each of the records in tableA.  The DELETE statement is only affects tableB.  As mentioned in ypercube's comment, if you were to do a distinct count, the numbers would match.


Mysql Join to get multiple row value from I am new to my Mysql and need help regarding join
I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I want to get value of all rows. I only know how to get single row value but failed to get multiple row value

this is the code I am using to get single row value from from foreign key:

SELECT e.employee_id, e.first_name, e.last_name,
e.address_id, e.phone_id, e.dob, e.maritial_id,
e.sex_id, e.photo, s.sex_label
FROM employee AS e JOIN sex AS s ON e.sex_id = s.id

SHOW CREATE TABLE phone

CREATE TABLE `phone` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mobile` varchar(15) DEFAULT NULL,
 `mobile_one` varchar(15) DEFAULT NULL,
 `phone` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

SHOW CREATE TABLE address:

CREATE TABLE `address` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address_one` varchar(50) DEFAULT NULL,
 `address_two` varchar(50) DEFAULT NULL,
 `state` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;


How can I stop a single Progress OpenEdge server process and not the whole database? Is there a way to stop a single _mprosrv process started by the database broker? All I can find from documentation and google searches are ways to disconnect a single user connection or shut down the entire database.

For context, the problem is, that we are running an OpenEdge 10.2 database, that is mostly used by remote 4GL clients and one singular client using SQL over ODBC to run data export queries once a week. After rebooting the entire server in our current configuration, the database started up and broker spawned _mprosrv server processes up to the maximum number of servers allowed for the broker. Therefore it can't start up a _sqlsrv2 process any more, because all the slots are occupied. Even during low usage periods where no remote 4GL clients are connected, the ODBC connection does not succeed, since the idle servers are not shut down to allow new servers to be spawned.

Hence the question - can I shut down a single 4GL server process to trigger a new server creation for SQL connections without taking the entire database and application offline?

After googling around some more with different queries, I found my answer from the Progress Knowledge Base after all.

First of all, it turns out that indeed, the remote server processes by default stay active until database shutdown happens. So if all the slots are occupied by 4GL servers, they will not open up a slot for a SQL server by themselves.

However, the PROMON utility has commands to terminate specific server processes gracefully to free up server slots. The sequence of commands is as follows from the PROMON main menu:

R&D for Advanced options
4 for Administrative functions
7 for Server options
7 for Terminate a server

From the server list find the server ID that needs to be terminated and type it in. After issuing the command, using the R option to refresh the screen the terminated server ID should show up as inactive.

After terminating a server, a new SQL server should be spawned if an ODBC connection to the system is initiated.

On Windows you might need to use a local user account, that is in the Administrators group. Domain users belonging to Administrators group are not allowed to terminate servers.

Sources:

Background information how remote servers are handled
Instructions on how to terminate a single server process
Information on how domain administrator accounts are handled on Windows


Emulate a TSQL sequence via a stored procedure I have a requirement to create a stored procedure which emulates a TSQL sequence.  That is it always gives an increasing distinct integer value on every call.  In addition, if an integer is passed in it should return that value if there has never been a result greater or the next highest integer available.  It goes without saying there can be multiple clients calling this SP at the same time.

Given a table MetaInfo with columns MetaKey varchar(max) and MeatValueLong bigInt.  It is expected the row with the MetaKey of 'Internal-ID-Last' will contain the last highest value assigned.  I created the following stored procedure:

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    UPDATE MetaInfo WITH (ROWLOCK)
      SET MetaValueLong = CASE
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong+1
                            ELSE @inID+1
                          END
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION

END

My question is simply, does this stored procedure work as expected (all callers will be assigned a unique result)?

I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...


Has Replication Stalled? What Happened:

I reinitialized a subscription 16 hours ago. I had about 1.2M records to transfer, so I was expecting it to take a while. The Replication Monitor was saying "N transactions with N commands were delivered" for hours, so I figured everything was OK.

The Issue:

This morning when I check the Replication Monitor, I see this:

Oh no!, I exclaim, thinking I need to reinitialize replication with a newer snapshot to avoid pulling 1.2M rows. But I look at the other Tab on the Replication Monitor, and I see this:

Question:

I'm wondering why Transactions are being delivered, despite Replication Monitor complaining about the snapshot.

It's hard to say exactly but it looks like something went wrong during the reinitialization process.  Transactions are flowing from Publisher to Distributor but the Subscriber is requiring a new snapshot.  I'd recommend generating a new snapshot and then it will be reapplied by the Distribution Agent.  At that point you should be back in sync.


on deleting a row, the update logic is run also I am using a after insert, update delete trigger.

When I insert a record in my application called as clarity, it inserts a row in the test table with the value as 'Insert'

When I update the record in my application, it insert a row in my test table with the value as 'Update'

When I delete the record in my application, it adds two rows in my test table with the value as 'Update' and 'Delete' in that order.

Why would deleting a row call the update logic of the trigger.

My code is as below

USE [claritydb]
GO
/****** Object:  Trigger [niku].[TRIG_STATUSRPT]    Script Date: 11/11/2019 11:04:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @STATUSID INT,
            @PROJECTID INT,
            @SCHEDULE_STATUS INT,
            @SCOPE_STATUS INT,
            @EFT_STATUS INT,
            @OVERALL_STATUS INT,
            @ACTION_TYPE VARCHAR(50),
            @MAXSTATUSIDVAR INT,
            @DelCount int,
            @InsCount int,

   SELECT @InsCount = Count(*) FROM INSERTED
   SELECT @DelCount = Count(*) FROM DELETED

---- Get data from inserted/ updated
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM inserted
 ---- Get data from deleted
SELECT @STATUSID= ID,
         @PROJECTID= ODF_PARENT_ID,
            @SCHEDULE_STATUS= COP_SCHEDULE_STATUS,
            @SCOPE_STATUS= COP_SCOPE_STATUS,
            @EFT_STATUS= COP_COST_EFT_STATUS
           FROM deleted
SELECT TOP 1 @MAXSTATUSIDVAR= ID FROM ODF_CA_COP_PRJ_STATUSRPT op where op.ODF_PARENT_ID = @PROJECTID  ORDER BY ID DESC

   If @InsCount > 0 and @DelCount = 0
   Begin
      -- At least 1 row inserted. Your Insert Trigger logic here
        INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Insert',GETDATE(),@MAXSTATUSIDVAR)

   End
   Else If @DelCount > 0 and @InsCount = 0
   Begin
      -- at least 1 row deleted. Your Delete Trigger logic here
       INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Delete',GETDATE(),@MAXSTATUSIDVAR)
   End
   Else If @DelCount > 0 and @InsCount > 0
   Begin
      -- old row deleted, new row inserted; both indicates an update.
      -- your update logic here.
              INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
              Values( @STATUSID, @PROJECTID, @SCHEDULE_STATUS, @SCOPE_STATUS,@EFT_STATUS, 'Update',GETDATE(),@MAXSTATUSIDVAR)
   End
END

Instead of diagnosing your update/delete logic issue, I think it's better to just re-write your trigger.  The biggest problem with your trigger is that it's not set based, so that any attempt to modify multiple records at a time will fail, or more accurately give you odd results.  I suspect that this is at least part of the problem you are experiencing with it.

Please take a look at the one below.  It should work for you, but I apologize if I got some column names incorrect.  The CTE_Details at the top fuses the INSERTED and DELETED tables together and will properly return the Action taken based on whether it's an insert, update or delete.  It then inserts that into your reporting table.

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID
            , I.ODF_PARENT_ID
            , I.COP_SCHEDULE_STATUS
            , I.COP_SCOPE_STATUS
            , I.COP_COST_EFT_STATUS
            , ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELTED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID
            , D.ODF_PARENT_ID
            , D.COP_SCHEDULE_STATUS
            , D.COP_SCOPE_STATUS
            , D.COP_COST_EFT_STATUS
            , ActionTaken = 'Delete'
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
    SELECT T.ID AS STATUSID
        , T.ODF_PARENT_ID AS PROJECTID
        , T.COP_SCHEDULE_STATUS AS SCHEDULE_STATUS
        , T.COP_SCOPE_STATUS AS SCOPE_STATUS
        , T.COP_COST_EFT_STATUS AS EFT_STATUS
        , T.ActionTaken
        , GETDATE() AS CREATED_DATE
        , OP.ID AS MAXSTATUSIDVAR
    FROM CTE_Details AS T
        OUTER APPLY (   SELECT TOP (1) M.ID
                        FROM ODF_CA_COP_PRJ_STATUSRPT AS M
                        WHERE M.ODF_PARENT_ID = T.ODF_PARENT-ID
                        ) AS OP

END

** EDIT ** - I fixed a bug in my example code for the Insert action path, it wouldn't simulate the error you are seeing.

I have an example here below that shows it working correctly.  I think you have something else going on.  If you run the example code below you should see four entries.  2 inserts, 1 update and 1 delete.

DROP TABLE IF EXISTS dbo.TestMe;
DROP TABLE IF EXISTS dbo.TestMeReport;

CREATE TABLE dbo.TestMe
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestValue VARCHAR(100) NULL
    )

CREATE TABLE dbo.TestMeReport
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestMeID INT NOT NULL
    , TestValue VARCHAR(100) NULL
    , ActionTaken VARCHAR(20) NULL
    )

GO

CREATE TRIGGER trgTestMe ON dbo.TestMe
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID, I.TestValue, ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELETED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID, D.TestValue, 'Delete' AS ActionTaken
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO dbo.TestMeReport
    (TestMeID, TestValue, ActionTaken)
    SELECT T.ID, T.TestValue, T.ActionTaken
    FROM CTE_Details AS T

END

GO

TRUNCATE TABLE dbo.TestMe
TRUNCATE TABLE dbo.TestMeReport

INSERT INTO dbo.TestMe (TestValue)
VALUES ('Test1')
    , ('Test2')

UPDATE dbo.TestMe
SET TestValue = 'Test3' WHERE TestValue = 'Test1'

DELETE FROM dbo.TestMe WHERE TestValue = 'Test2'

SELECT * FROM dbo.TestMeReport


Getting repetition of same values multiple times while combining two queries I wrote two queries in MySQL Workbench and combined them into a single query:

Select *
from
  (
    (select division.name,
            date(meter_data.reading_date),
            sum(meter_data.meter_delta) 'Gas Consumption (m3)'
     from meter, meter_data, division
     where meter.meter_id = meter_data.meter_id
       and meter.division_id = division.division_id
       and Date(meter_data.reading_date) between '2018-04-01' and '2018-05-03'
     group by date(meter_data.reading_date)
    ) as table1,

    (Select Date(receipt.receipt_date),
            sum(case receipt_item.item_name
                    when 'Gas' then receipt_item.item_amount else 0
                end) 'Gas Purchase'
     from receipt_item, receipt
     where receipt.receipt_id = receipt_item.receipt_id
       and Date(receipt.receipt_date) between '2018-04-01' and '2018-05-03'
     group by Date(receipt.receipt_date)
    ) as table2
  );

When I execute the queries separately, each query returns the exactly 33 rows, but combined it returns 1089 rows. Why?

Maybe You need

Select *
from
  (
    (select division.name,
            date(meter_data.reading_date) AS reading_date,
            sum(meter_data.meter_delta) 'Gas Consumption (m3)'
     from meter, meter_data, division
     where meter.meter_id = meter_data.meter_id
       and meter.division_id = division.division_id
       and Date(meter_data.reading_date) between '2018-04-01' and '2018-05-03'
     group by date(meter_data.reading_date)
    ) as table1,

    (Select Date(receipt.receipt_date) AS receipt_date,
            sum(case receipt_item.item_name
                    when 'Gas' then receipt_item.item_amount else 0
                end) 'Gas Purchase'
     from receipt_item, receipt
     where receipt.receipt_id = receipt_item.receipt_id
       and Date(receipt.receipt_date) between '2018-04-01' and '2018-05-03'
     group by Date(receipt.receipt_date)
    ) as table2
  )
WHERE table1.reading_date = table2.receipt_date;

?


Postgresql Slow performance query after table partitioning I have a query which was executing in 30 secs before one of its table parititioned and after partitioning the query is ever running.

Table that is recently partitioned is ysm_yield_session_master.
This new table holds 4 to 5 partitions and each partition has up to ~90 million rows.

yr_yield_result and ywd_yield_website_details are other partitioned tables (each partition is very large, millions of rows) which were partitioned long ago.

SELECT
     ovp_ota_name AS ota_name,
     COALESCE(t.pending_count, 0) AS pending_count
FROM ovp_ota_vendor_profile
LEFT JOIN
(
     SELECT
           ovp_ovp_key as ota_key,
           COUNT(*) AS pending_count
     FROM ysm_yield_session_master
     LEFT JOIN ywd_yield_website_details ON ysm_ysm_key=ywd_ysm_key
     LEFT JOIN yr_yield_result ON yr_ywd_key=ywd_ywd_key
     LEFT JOIN wm_abc ON ywd_wm_key=wm_wm_key
     LEFT JOIN wmo_abc ON wmo_wm_group=wm_group
     LEFT JOIN ovp_abc ON ovp_ovp_key = wmo_ovp_key
     WHERE (   ysm_yield_date = 'now'::text::date
            OR ysm_yield_date = 'yesterday'::text::date)
       AND (GREATEST(now(), ysm_yield_time) - LEAST(now(), ysm_yield_time))
          < '06:00:00'::interval
       AND (   ywd_yield_date = 'now'::text::date
            OR ywd_yield_date = 'yesterday'::text::date)
       AND (   yr_yield_date IS NULL
            OR yr_yield_date = 'now'::text::date
            OR yr_yield_date = 'yesterday'::text::date)
       AND yr_yr_key IS NULL
       AND ovp_ovp_key IS NOT NULL
     GROUP BY ovp_ovp_key) t ON ovp_ovp_key = ota_key
ORDER BY ovp_ota_name;

I still have the original table, and I see the query runs much faster when I replace the partitioned table with the old table. I compared the execution plan of the query before and after partitioning and they are very different and the one with partition is expensive.

I ensured the stats are updated after partitioning. Not sure what else I could do.

Any thoughts..?

FYI,
The total number of rows for all partitions is approx 360 million records.
The total number of rows of standalone table is approx 2 billion records (however it is much faster on this table)

Execution plan on old standalone table

Execution plan on new partitioned table

In the new plan, you can see seq. scan on the partitioned table and it's very expensive - cost about 18mln when the total cost of the old query is only 6mln in total.

I saw the same effect when I didn't use correct conditions in where or join. What is the partition key? Can your query use only one of the partition_sub_table or not. Do you have indices on sub-tables?