temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave I executed some alter statements on my master server, which have been successfully executed on master (now there is no temp table on master) and all alter also have been replicated on slave server successfully. But still I am getting temp table on slave (#sql-7a87_230c32.ibd and #sql-7a87_230c32.frm).
Now I need help to know below points:

When alter has been successfully done then why this temp table still exist and not deleted.

Now how can I delete this temp table and what will be its impact.

May I read this temp file or know by any command that for which table this temp table was created.

Note: If I delete it from its directory path then mysql will throw error message related with it and also I am not aware what will be its impact.
I will be very thankful for any quick help.

2nd Update

1.. When alter has been successfully done then why this temp table still exist and not deleted.

After reading #sql-7a87_230c32.frm whith the help of your post, now it is clear that it was my first alter table (I altered 6 tables one by one).
Below are some facts which will help to know exact reason-

sql-7a87_230c32.frm file creation time is "2014-06-09 01:49:00" and its .ibd file #sql-7a87_230c32.frm time is "2014-06-09 03:34:00" and its size is 3.4 GB while its main file size is 6.8 GB

After checking server logs it came to know that slave was being shutdown at "2014-06-09 03:33:05" (I have to check if it was done by system admin team even they were aware about this db activity or itself) and restarted when alter was executing on slave.

This table creation time on slave is showing as "2014-06-09 04:43:32".

After analyzing above facts it seems first time alter statement on this table was executing near about "2014-06-09 01:49:00" but without copying completely (as temp .ibd table size is less than its original size) it was rolled back when server was gone shutdown near about at "2014-06-09 03:33:05" but system did not remove these temp tables. Now again when slave gone up it was started replication again same point and now altered table successfully, which was completed by "2014-06-09 04:43:32".
But as per my understanding in this case temp tables should be remove along with rolled back but in this case it was not done. Please help me to clear it.

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.
Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.
Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.
Don't worry about the temp table name being used again. Look at the name of the temp table
#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in 1610 or 240 which 1,099,511,627,776. I think you will be OK in this.
Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.
However, you could probably rename the .frm and use a utility to read the table structure.
Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?
EPILOGUE
Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.
Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow
UPDATE 2014-06-10 08:36 EDT
It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).
Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.


Missing rows under read committed isolation It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one:

The Read Committed Isolation Level

It states (again, it's reasonably well known), that a statement running under read committed isolation:
Can encounter the same row multiple times;
Can miss some rows completely;

My question is about the 'missing rows' part. Examples that talk about missing rows usually demonstrate the problem using queries like:
select count(*) from table.
My question is can the rows be missed in a 'regular' select query? Meaning, can a query like
select * from table
or even
select * from table where id = @id
also miss rows that are committed before the start of that query?
This question applies to Read committed with locking (not RCSI) only, as RCSI doesn't allow these types of anomalies.

A scenario where rows committed before the start of the SELECT query can be missed is when an index key value is updated. Consider this query:
SELECT * FROM dbo.YourTable;

The execution plan will likely perform an ordered clustered scan to return all columns. If a key value is updated and committed during the scan and the new value is less than the ordered scan point at the time of the update, the row will not be returned.
With this query:
SELECT * FROM dbo.YourTable WHERE id = @id;

The row will not be missed if id is the primary key and a singleton seek is used. But in the case of a seek/scan of non-unique index where the key value is updated and committed during the scan, the row may or may not be returned depending on the scan point that the time of the update.


SQL Server Timeout exception for multiple Users using SQL Stress We have a stored procedure which takes around 3-4 secs to execute as it has many joins and unions. But if we try to execute that stored procedure using SQL Stress with 100 threads, it gives many time out exceptions. The message is..

  Timeout expired. The timeout period elapsed prior to obtaining a
  connection from the pool. This may have occurred because all pooled
  connections were in use and max pool size was reached
  .

This is on a production server.

If we execute the same thing using SQL Stress in our local environment it does not give any exception.

It might be an SQL Server Configuration issue. It shows 5031 MB used in the task manager max and does not go beyond that, however the System memory is 64 GB.

What steps should I take to investigate further?

The timeout is with the connection pool, which is a collection of connections maintained by the client.  All of the connections in the pool are in use, and it is timing out getting a connection object from the connection pool, not timing out trying to connect to the SQL Server.  For example, you may have 30 connections in the connection pool, and a 5 second timeout for obtaining a connection from the pool.  A connection pool timeout will occur if all 30 connections in the pool are busy and none are freed up within 5 seconds of a request for a connection pool object.

So you can increase the number of connections in the pool, increase the connection pool timeout, or work to see if the stored procedure execution time can be improved.  It is likely that all of the connections are busy due to blocking, and if that's the case, increasing the connections in the pool and the timeout won't increase the number of times the stored procedure can execute per minute and you'll need to find out how to make the stored procedure execute faster.  So a good first step would be to look at the execution plans and missing indexes for all of the queries in the stored procedure.

Perhaps obvious, but since you state it works better on the "local" server, what are the configuration differences between the two servers?  If read committed snapshot is enabled on the local server but not the other, that one config change could have a huge impact.


SQL Server Data File/Log File poor performance I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery process.
This server is a Dell PowerEdge R720 running Windows Server 2008 R2 and SQL Server 2012 Standard (11.0.2100.60) (x64).
-Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz, 2500 Mhz, 6 Core(s), 12 Logical Processor(s)
-16GB RAM
-DELL PERC S110 SCSI Disk Device running as a software RAID 5.
The disk is partitioned into two local drives: C and D. The OS files are all on C. SQL Server is installed on D, and the database files (.mdf and .ldf) are also on D. In addition, we're writing our nightly backups to the D drive! :-)
The database mdf file is around 6GB. Backups are taking 30 minutes.
A quick look at the wait types revealed that the WRITELOG wait type was the most prevalent - consuming 69%:

Suspecting transaction log/disk performance issues, I enabled various PerfMon counters and established baseline averages over a couple of days. The PerfMon counters include, but are not limited to:
\PhysicalDisk(0 C: D:)\Avg. Disk sec/Write - Avg = 0.073
\SQLServer:Databases\Log Flush Wait Time - Avg = 71.403
\SQLServer:Databases\Log Flushes/sec - Avg = 1.910
\SQLServer:Databases\Log Bytes Flushed/sec - Avg = 1724.604

The transaction log was originally configured with an initial size of 100MB and set to grow at 10%. When I first logged onto the machine, the transaction log had grown to 5GB across 256 VLFs suggesting some fragmentation.
Paul Randall's query against sys.dm_io_virtual_file_stats reveals some serious write latency with both the data and log files - although contrary to the WRITELOG wait type, the data file write latency is quite a bit more:
Data File: 1369
Log File: 66

Based on the article by Kimberly Tripp, I "rebuilt" the transaction log and assigned it a size of 2GB (somewhat arbitrarily), yielding 24VLFs.
This seemed to decrease the WRITELOG wait type percentage some (now at ~61%) as well as the \SQLServer:Databases\Log Flush Wait Time (now at ~48ms).
Bus this still seems high.
I realize that the data and log files (and backups) should all be on a different drives for parallel reads/writes and maximum throughput. Best practices aside, we have other production systems configured the same way as this one, but that are running better, e.g. Avg Log Flush Wait Time = < 3 ms.
What else could I look at to figure out why this server in particular is slower with regards to disk read/write response time and backups?
Could index fragmentation/fill factor settings cause this poor of performance?
What about disk fragmentation?
If I need to tell the client we need another disk, then so be it - I just want to be certain before spending money.
Any other ideas???

Points:

RAID 5 is a poorly performing configuration for database files especially for writes, and
Partitioning is even worse.

Yes, it works and is reliable, but it's definitely a low-performance disk configuration that I would only use myself for development or the most light-weight of apps.  The standard configuration for the physical host of a SQL Server are separate physical volumes for the system and data disks using RAID 1+0.

I will note @Spörri's recommendation also: first, check to see if the RAID array is degraded because of a failed disk.


Table with clustered index implicitly sorting by unique nonclustered index I have a table that captures the host platform that a user is running on. The table's definition is straightforward:

IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[ActivityPlatform] (
        [ActivityPlatformId] [tinyint] IDENTITY(1,1) NOT NULL
        ,[ActivityPlatformName] [varchar](32) NOT NULL
        ,CONSTRAINT [PK_ActivityPlatform] PRIMARY KEY CLUSTERED ([ActivityPlatformId] ASC)
        ,CONSTRAINT [UQ_ActivityPlatform_ActivityPlatformName] UNIQUE NONCLUSTERED ([ActivityPlatformName] ASC)
    ) ON [Auth];
END;
GO

The data it stores is enumerated based on a JavaScript method that uses information from their browser (I don't know much more than that, but could find out if needed):

When I perform a basic SELECT without an explicit ORDER BY, however, the Execution Plan shows that it is using the UNIQUE NONCLUSTERED index in order to sort instead of the CLUSTERED index.

SELECT * FROM [Auth].[ActivityPlatform]

When explicitly specifying the ORDER BY, it correctly sorts by ActivityPlatformId.

SELECT * FROM [Auth].[ActivityPlatform] ORDER BY [ActivityPlatformId]

DBCC SHOWCONTIG('[Auth].[ActivityPlatform]') WITH ALL_LEVELS, TABLERESULTS shows no table fragmentation.

What am I missing that could cause for this? I thought so long that the table was created on a clustered index, it should automatically sort by it implicitly without need to specify ORDER BY. What is SQL Server's preference in choosing the UQ? Is there something I need to specify in the table's creation?

No, sorting is not implicit and should not be relied upon. In fact, in the first tooltip, you can see that it is explicitly stated that Ordered = False. This means SQL Server didn't do anything at all to implement any sorting. What you observe is just what it happened to do, not what it tried to do.

If you want to be able to predict a reliable sort order, type out the ORDER BY. Period. What you might observe when you don't add ORDER BY might be interesting, but it cannot be relied upon to behave consistently. In fact in this post, see #3, I show how a query's output can change simply by someone else adding an index.

T-SQL Tuesday #56 : Assumptions

  What is SQL Server's preference in choosing the UQ?

The UNIQUE NONCLUSTERED index contains the clustering key, so it is covering for the query. In this case, your table only has two columns, so the clustered and nonclustered indexes contain the same data (just sorted differently). They're both the same size, so the optimizer could choose either. That it chooses the nonclustered index is an implementation detail.

I call this a "coin flip."


SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist SQL Server 2005 sp3 Standard on 64-bit Windows Server 2008

inconsistently receiving an error that seems to indicate corruption

select *
from some_table
where text_field like ‘%the fox did not go into the hen-house %’

sometimes, but not always, this and similar queries will generate the following error

Msg 7105, Level 22, State 6, Line 1
The Database ID 11, Page (1:990063), slot 4 for LOB data type
node does not exist. This is usually caused by transactions
that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

the severity level of 22 causes the connection to be broken

the data has long ago been committed

the table has only two indexes, neither of which list the text field

the database is in isolation level read committed snapshot

the database is being mirrored

the error is being generated by simple select queries, not by update queries

the error showed up for the first time about a month ago, one occurrence; second occurrence was twelve days ago; third and fourth occurrences were yesterday; today there have been two new occurrences

the queries are being run without hints, no use of NOLOCK

I have repeatedly run dbcc checkdb and dbcc checktable, and so far in a half dozen tries, no errors noted by either command; these commands take much longer to run than expected .. on similarly sized databases, dbcc checkdb will run in under a minute, but for this one, it takes almost ten minutes

the error comes and goes, meaning sometimes I run the query and it returns quickly with all expected information, and other times running the query returns the error

the network/server engineers were asked to review the associated SAN disks, and report that there are no issues

what could be the root cause of this issue? could it be hardware? if so, how can I guide the engineers? if not hardware, what else could it be?

Read what the bloke (Paul Randal) who wrote some of the associated SQL Server code says

And logged on MS Connect

And have you ran DBCC CHECKDB (KB 2015760)?


How to perform FullText search on numeric column in MySQL to meet user requirements Having below table:

CREATE TABLE `event` (
      `eventId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `eventTime` bigint(20) NOT NULL COMMENT 'ex: 1431201865000 (epoch in milliseconds)',
      `plateNumber` varchar(10) NOT NULL COMMENT 'ex: 5849',
      `plateCodeId` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'ex: 1 = A, 2 = D, 3 = Q, 4 = NY',
      PRIMARY KEY (`eventId`),
      KEY `eventTime` (`eventTime`),
      FULLTEXT INDEX `platenumberft` (`plateNumber`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

The User want to search plateNumber in form '%334%', knowing that's only supported by fulltext index, I wonder if its possible to avoid the usage of fulltext index since it create 6 tables and even I didn't find benchmarks highlight the impact of fulltext index on large table I am worried about the performance since the table above will hold over 1 billion records in which insertion rate is 150 records per second 24/7.

MySQL Docs recommends never to use string "varchar" data type to store numeric data, and since the platenumber always numeric, it should be created as:

`plateNumber` INT(10) UNSIGNED NOT NULL

Is it possible to query numeric field in search form '%334%' with speed equal to the fulltext index? and How?

Use Case: the user received information that the plate number contain digits '334' in the middle, so the first and last digits are unknown.

When searching for a numeric string in the middle, FULLTEXT is the wrong approach.  LIKE is the only approach, and it requires a full scan of the plates.  Live with it.

But... How often do you need to search for the middle of a string?  Not often.  If you have the left part of a string, LIKE '234%' works quite efficiently; it is a "range".  But then you need a regular INDEX, not FULLTEXT.

FULLTEXT is aimed at words, not substrings, not full strings.  Don't use it for licence plates.

Don't use INT for a plate number.  It is not a "number" on which you perform arithmetic; that is what the MySQL Doc is talking about.  The plate number is merely a string that often contains only digits, so use a VARCHAR with the minimal collation.  In the 'west', plates are numbers and capital letters.  So, COLLATION ascii_bin is optimal.  However, that means fold to upper case both the plate numbers as they are stored.  Also fold any query strings.  At the same time, remove any punctuation (dash, heart, star, etc) that is not actually important to the comparison.

I cannot address the collation specifics for Bangladesh, but I suspect that utf8_bin would suffice.


If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)? I'm asking this out of curiosity, being inspired by this question.

We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).

We can add a VARCHAR(MAX) column, as INCLUDEd, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?

Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:

USE tempdb;

DROP TABLE IF EXISTS #LOB_FOR_ME;

CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX)
);

CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);

Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 1, REPLICATE('Z', 8000)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

There are no LOB pages in the index:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2540 ║         2540 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2540 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

But if I add rows with values that take 8001 bytes:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

Now I have 1 LOB page in the index for every row that I just inserted:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2556 ║         5080 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2556 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
║           0 ║ NONCLUSTERED INDEX ║ LOB_DATA             ║       2540 ║         2540 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 1;

Results upon executing:

  Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
  0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I instead query the rows with 8001 bytes:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 2;

Now I see lob reads:

  Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
  lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.


UPDATE statement with and without SELECT keyword I have been doing some testing on removing the SELECT keyword from this update:

Update  #tmpDriverTime
Set     WorkDay2 = (Select IsNull(Sum(PyblHrs),0)
From    spr_DriverTimeEntry
Where   spr_DriverTimeEntry.DrvrID = #tmpDriverTime.EmployeeNo
And     Convert(Varchar(12),dtwrkd,112) = Convert(Varchar(12),@StartDate_1,112))

From the testing I have done it looks like the same data is returned whether or not I have the SELECT keyword. I ran the code by our DBA to get his approval of removing the SELECT statement and he said I can not as removing the SELECT statement would cause the update to work differently but he can not articulate how it works differently. I have done some searching on the new and found a few MSDN pages where they run the UPDATE FROM command with out a SELECT keyword. So now I am confused, my testing and other examples I found on MSDN say I can remove the SELECT keyword but my DBA says no. Does the removal of the SELECT keyword affect how the UPDATE FROM works and if so how does it affect it?

Personally, I would re-write this in the following way, to clearly separate the source and the target, instead of using this subquery assignment technique. I am making assumptions on where some of these columns are coming from, of course, because you didn't use aliases, so the references were ambiguous.

DECLARE @sd DATETIME;
SET @sd = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate_1), 0);

;WITH hrs(DrvrID, ph) AS
(
  SELECT EmployeeNo, SUM(PyblHrs)
    FROM #tmpDriverTime
    GROUP BY EmployeeNo
)
UPDATE e
  -- "WorkDay2" represents a sum of payable hours? Quaint.
  SET WorkDay2 = COALESCE(hrs.ph, 0)
  FROM hrs
  INNER JOIN dbo.spr_DriverTimeEntry AS e -- schema prefix always, please
  ON hrs.DrvrID = e.DrvrID
  -- please don't convert both sides to a string
  -- use a range, then you can still use an index:
  WHERE e.dtwrkd >= @sd
    AND e.dtwrkd <  DATEADD(DAY, 1, @sd);


Subtracting sets within a grouped column, should I be pivoting? I'm not the best at explaining, but I have a table that has the format

CREATE TABLE foo
AS
  SELECT type,date::date,tp,price
  FROM ( VALUES
    ( 'A', '2010-10-01', 1, 0.05 ),
    ( 'A', '2010-10-01', 2, 1.04 ),
    ( 'B', '2010-10-01', 1, 0.53 ),
    ( 'B', '2010-10-01', 2, 1.04 ),
    ( 'C', '2010-10-01', 1, 0.05 ),
    ( 'C', '2010-10-01', 2, 1.02 ),
    ( 'D', '2010-10-01', 1, 0.05 ),
    ( 'D', '2010-10-01', 2, 1.08 )
  ) AS t(type,date,tp,price);

And what I want to do is subtract different types where the date and tp are the same. So that would be A-B, A-C, A-D, B-A, B-C, B-D, C-A, C-B, C-D, D-A, D-B, D-C.

To me this seems like I would want a wide format table, with the columns date, tp, A, B, C, D and then do a column-wise subtraction based on possible combinations. If the subtraction is less than 0, then the value is 0

The desired output looks something like this:

combo | date      | tp | price
---+------------+----+-------
A_B  | 2010-10-01 | 1  | 0
A_B  | 2010-10-01 | 2  | 0
A_C  | 2010-10-01 | 1  | 0
A_C  | 2010-10-01 | 2  | 0.02
A_D  | 2010-10-01 | 1  | 0
A_D  | 2010-10-01 | 2  | 0
and so on for all the combinations

Should I be looking at using crosstab ? Or is there a simpler/more elegant solution? My current solution is a view that uses CTE(s) to create all the possible dates and type combos, and then I have a function that goes through every typeA and typeB, date, tp combo. It is very slow.

The initial table in question is 2857658 rows

Simple self-join

What you want is something like this...

SELECT
  ARRAY[f1.type,f2.type] AS type,
  date,
  tp,
  greatest(f1.price-f2.price, '0.00') AS price
FROM foo AS f1
INNER JOIN foo AS f2
  USING (date, tp)
WHERE f1.type <> f2.type
ORDER BY f1.type, f2.type, tp;

I deviated a bit from what you desired. Generally, you don't want string concatenation like that. It's less useful. You're better off using an array. If you do want string concatenation just use f1.type || '_' || f2.type.

 type  |    date    | tp | price
-------+------------+----+-------
 {A,B} | 2010-10-01 |  1 |  0.00
 {A,B} | 2010-10-01 |  2 |  0.00
 {A,C} | 2010-10-01 |  1 |  0.00
 {A,C} | 2010-10-01 |  2 |  0.02
 {A,D} | 2010-10-01 |  1 |  0.00
 {A,D} | 2010-10-01 |  2 |  0.00
 ...
 (24 rows)

Crosstab

Cross-tab does something totally different. It only ever changes the display.

SELECT *
FROM crosstab($$
  SELECT
    ARRAY[f1.type,f2.type] AS type,
    date,
    tp,
    greatest(f1.price-f2.price, '0.00') AS price
  FROM foo AS f1
  INNER JOIN foo AS f2
    USING (date, tp)
  WHERE f1.type <> f2.type
  ORDER BY f1.type, f2.type, tp;
$$, $$VALUES (1),(2)$$
) AS t(type text[],"date" date,tp1 numeric,tp2 numeric);

Produces...

 type  |    date    | tp1  | tp2
-------+------------+------+------
 {A,B} | 2010-10-01 | 0.00 | 0.00
 {A,C} | 2010-10-01 | 0.00 | 0.02
 {A,D} | 2010-10-01 | 0.00 | 0.00
 {B,A} | 2010-10-01 | 0.48 | 0.00
 {B,C} | 2010-10-01 | 0.48 | 0.02
 {B,D} | 2010-10-01 | 0.48 | 0.00
 {C,A} | 2010-10-01 | 0.00 | 0.00
 {C,B} | 2010-10-01 | 0.00 | 0.00
 {C,D} | 2010-10-01 | 0.00 | 0.00
 {D,A} | 2010-10-01 | 0.00 | 0.04
 {D,B} | 2010-10-01 | 0.00 | 0.04
 {D,C} | 2010-10-01 | 0.00 | 0.06
(12 rows)

12 rows rather than 24. We pivoted and put tp1, and tp2 together. Pick your poison -- what kind of display do you want? I always prefer not using cross tab unless someone requires it.

Performance questions

  My current solution is a view that uses CTE(s) to create all the possible dates and type combos, and then I have a function that goes through every typeA and typeB, date, tp combo. It is very slow.

We would need to actually see the views, and to an output of EXPLAIN ANALYZE to know why it runs slow. Try the query I suggested at the top and see if it speeds things up.


How to display a number with fixed number of digits before the decimal? I am looking for result 0123.56 and the following query should have worked.

SQL> select to_number(123.56,'0999.99') from dual;
select to_number(123.56,'0999.99') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number

SQL>

But it doesn't. What am I doing wrong here?

It looks like you are trying to convert a number to a number - try to_char instead:

select to_char(123.56,'0999.99') from dual;

TO_CHAR(123.56,'0999.99')
-------------------------
 0123.56

Note that in contrast, select to_number('0123.56','0999.99') from dual; will succeed because the format specification matches, but will not return the text 0123.56 as you want - the '0999.99' in this case is an input format, not an output format - the number 123.56 will be returned.


Learning to draw entity-relationship diagram for a scenario involving Offices, Employees and Projects I am trying to learn entity-relationship diagram, following question is the objective.

  Information consulting firm has number of offices with employees. The employees can move offices depend on the project, but location is always recorded. This consulting firm manage number of projects and have vast base of clients. The company's success is depend on assigning relative employee with relative skill and location. The time an employee spends on project is recorded each day. This include time for travel and break time etc. To reduce
  time for travel they try to allocate employees from the nearest office possible.

  Draw Entity Relationship diagram and entity listing with attributes.

I created entity-relationship diagram by using Visio. Please comment on my attempt.

Office

Address
telephone
office manager

Employees

name
address
job
hourly rate
start_date
location etc

Projects

Name
Manager
Start Date
End Date
Estimated Cost
Quote
Actual Cost

Client
Name
address
telephone
e-mail
contact

Assignment

name
Employee name
Date Assigned
Hours spent

Here is my attempt in this image:

Good. The essentials are there. A first rough list of entities is to take the nouns of the story - office, employee, project, clients, assignment, skill. Each table has a unique, sequential, meaningless key. Columns are given descriptive names. Here are the suggestions by entity.

office> The manager is an employee and should have a separate foreign key relationship with manager_employee_id. There is a many-to-many with employee. Avoid M-to-M relationships. In this case, an office has many employees. But an employee has one home office. I suggest naming fields uniquely with a common suffix describing the domain, office_address, instead of address.

employee> The job_id should be a foreign key to job instead of title. The office_id should be a foreign key to office instead of location. The column names should be hire_date instead of the start_date to avoid confusion with the project start_date. Use employee_address instead of address based on the domain suffix rule.

project> The project manager is a separate foreign key to employee pm_employee_id. What is the business difference between estimated_cost and quoted_cost? Make sure there is a foreign key field to match every relationship line. Client_id should be the foreign key. Office_id should be a foreign key.

assignment> Good, this is an intersecting table between project and employee. I suggest renaming the date to assigned_date to keep the suffix consistent. Notice that the hours_worked are in this table with the assigned_date. The assigned_date happens once. But the hours worked happen daily.

daily_assignment> Maybe there should be a separate child table called daily_assignment with its own primary key, a foreign key to assignment, and the worked_date and hours_worked as fields. This tracks each item of data uniquely at its own level.

client>
I suggest renaming the address to client_address.

There are ways to make it more complicated. But I think this model works well. It should give you flexibility to make business changes, yet still be simple enough to code.


How to reverse-engineer SQL statements issued by a legacy application I am re-writing the question below to provide more details.

We have a legacy application and are now developing it using a new technology. The database is SQL Server 2014. We know 90% of the tables that get updated when an operation is performed on the UI. However, we are not sure if we have covered all the tables.

The customer wants us to run a scan of the database on the old system when an operation is performed on the front end and then do the same with the new application. HE would like to make sure that we are touching the same tables in the old and new application.

So, I would like to understand if there is some query I can run that will give the list of tables that were updated when I perform an operation on the front end. All the tables have last update date and user who performed the update.

You can setup a test database and trace the application activity using the SQL Server profiler. By tracing the event "Security Audit\Audit Schema Object Access Event" you can effectively trace all the SQL the client is sending to the server.  But that will not necessarily help you in discover the application logic.


How to efficiently store impressions in db? For my flask app I need to track impressions, clicks on images. The current implementation that I have is impressions table where I store impression ip, useragent, geo location and all the relationships that I would like to have. So every impression has it's own row. Mainly I'm interested in count of impressions by hour, day, date range, month.
I tested this implementation by inserting 2.5M rows in my impression table. The problem with this implementation is that count query are slow when I try to get impressions count for whole month.(5 seconds per query) Index doesn't help much here. I tried different query's and MySQL db optimization and results are not satisfactory.
In my opinion I will have to change the way I handle impression tracking. I was thinking to create a table that holds impressions count for every hour. When ever new impression happens I would just increment counter for that hour.
But before I go and rewrite bunch of code and make mistake again.
I'm asking here how other web app handle tracking impressions and get results so fast? Or what is the correct way to store impressions?
Hopefully some can guide me to fix this problem.
Best regards

Summary tables.  Separate table for counts.
Are you keeping information about each impression?  Or just a counter?  It makes a big difference.  But we can solve it either way.
If you go with a table with just daily counter:
CREATE TABLE FooImpressions (
    foo_id  INT UNSIGNED NOT NULL,  -- same as main table, but not AUTO_INCREMENT
    date DATE NOT NULL,
    counter INT UNSIGNED NOT NULL,
    PRIMARY KEY(foo_id, date)
) ENGINE=InnoDB;

And
INSERT INTO FooImpressions (foo_id, date, counter)
    VALUES (?, ?, 1)
    ON DUPLICATE KEY UPDATE
        counter = VALUES(counter) + 1;

This automatically starts a new row for each Foo each day (as needed).
Then, to get last weeks counts:
SELECT foo_id, SUM(counter) AS impressions
    FROM FooImpressions
    WHERE date >= CURDATE() - INTERVAL 7 DAY
      AND date  < CURDATE()
    GROUP BY foo_id;

Of course, the date range can be changed to handle any consecutive date range.
There are variants on this for more complex situations and for situations that need more performance.  But this is a simple "summary table" together with its maintenance and its usage.
Yes, one variation would have DATETIME and truncate that to the hour.  Don't have both an hourly summary table and a daily one until you have demonstrated that the hourly one is not fast enough.
If you need up-to-the-minute counts, some Summary techniques do not provide such (because the do nightly or hourly batch updates for speed); the use of IODKU is always up-to-the-minute.
Even if you have another table with more info on the impressions, have this Summary table for speed of reporting.
More discussion:  http://localhost/rjweb/mysql/doc.php/summarytables


Troubleshooting high memory usage on MariaDB 10.5.4 I am experiencing very high memory usage on MariaDB server. I am not sure if it is due to misconfiguration or a bug.
Server:                 MariaDB
Server version:         10.5.4-MariaDB MariaDB Server

We are running MariaDB 10.5.4
Our server is configured to use 140G for bugger and 9G for log.
innodb_buffer_pool_size=140G
innodb_log_file_size=9G
max_heap_table_size            = 8G
tmp_table_size                 = 8G

However, our server is currently using 225G without any load on server, and frequently spikes over 240G during queries.
All tables are InnoDB. Memory buffer usage according to information schema is the following:
MariaDB [db]> SELECT  ENGINE,         ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",         ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",         ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",                                                                                COUNT(*) "Num Tables"     FROM  INFORMATION_SCHEMA.TABLES     WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")     GROUP BY  ENGINE;
+--------+-----------+----------+-----------+------------+
| ENGINE | Data MB   | Index MB | Total MB  | Num Tables |
+--------+-----------+----------+-----------+------------+
| NULL   |      NULL |     NULL |      NULL |         20 |
| Aria   |       0.8 |      0.3 |       1.1 |         24 |
| CSV    |       0.0 |      0.0 |       0.0 |          2 |
| InnoDB | 1952761.3 | 284144.6 | 2236905.9 |       2517 |
| MEMORY |       0.0 |      0.0 |       0.0 |          3 |
+--------+-----------+----------+-----------+------------+

Total large memory allocated 151397597184
Dictionary memory allocated 899109080
Buffer pool size   9019360
Free buffers       1024
Database pages     9018336
Old database pages 3329014
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 47473690, not young 10534657191
0.00 youngs/s, 0.00 non-youngs/s
Pages read 192376965, created 62530810, written 146872114
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9018336, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Server memory usage starts out normally, but greatly increases during couple days until it eventually consumes all of server's resources and restarts.
We began experiencing this issue shortly after upgrading from an ancient version of MariaDB. (do not recall which one, but it was very dated).
How can I troubleshoot this issue further, and what can I do to ensure that memory usage does not go above 160G?

POSSIBLE MEMORY LEAK ISSUES
There have been three releases since version 10.5.4

03 Nov 2020 : MariaDB 10.5.7
07 Oct 2020 : MariaDB 10.5.6
10 Aug 2020 : MariaDB 10.5.5

There have been some memory issues fixed since version 10.5.4
Memory Issues Fixed in 10.5.5
Revision #d1bb7f9143

2020-06-24 12:51:55 +0300
Fixed memory leak in item_sum.cc::report_cut_value_error()

Revision #572e53d8cc

2020-06-19 15:24:16 +0530
MDEV-22931:  mtr_t::mtr_t() allocates some memory

Memory Issues Fixed in 10.5.6
None (just branch merges from previous versions)
Memory Issues Fixed in 10.5.7
Revision #0d581562d8

2020-10-22 17:09:18 +0300
Fix memory leak on Alter_drop allocation

Revision #d98ccbe1e1

2020-08-21 11:54:16 +0300
MDEV-23526 InnoDB leaks memory for some static objects

WHAT CAN YOU DO NEXT
You might need to update to MariaDB 10.5.7 on a Staging Server, load data into it.Once you do that, you can do some benchmarking.

Perhaps you can mysqlbinlog dump everything from the binaries logs in the 10.5.4 server and import the SQL generated into the Staging Server running 10.5.7.
You could also import the slow log from 10.5.4 server into 10.5.7 Staging Server.

See if memory bloats up or not.
If memory does not bloat, then you should upgrade the prod server to 10.5.7.


Ola Hallengren index maintenance - long periods of time between commands? I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the next command starting. Sometimes this gap is over an hour.

Does anyone else observe this on their systems? Is there something I can do to shorten the (I'm guessing) discovery time between items to maintain? Below is the parameter set I'm running them with.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@LogToTable = 'Y',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y' " -b

So when I run this:

SELECT DATEDIFF(MINUTE, cl.StartTime, cl.EndTime)
, *
FROM master.dbo.CommandLog AS cl
WHERE cl.StartTime > '2014-12-13'
ORDER BY cl.ID

I see this:

Digging a bit on this, using a common query to retrieve index fragmentation took just about an hour even to return 160 rows on a 1.5TB database:

SELECT  s.name AS schemaname ,
        t.name AS tablename ,
        t.object_id ,
        i.name AS indexname ,
        i.index_id ,
        x.page_count ,
        x.avg_fragmentation_in_percent ,
        x.avg_page_space_used_in_percent ,
        i.type_desc
FROM    sys.dm_db_index_physical_stats(DB_ID(
                                ), NULL, NULL, NULL, 'LIMITED') x
        INNER JOIN sys.tables t ON x.object_id = t.object_id
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i ON x.object_id = i.object_id
                                AND x.index_id = i.index_id
WHERE   x.index_id > 0
        AND alloc_unit_type_desc = 'IN_ROW_DATA'
        AND x.page_count > 1000;

Which leads me to believe that when this part of the index maintenance routine runs, it's taking a very long time at the beginning of a new database to collect initial information and build commands.

SET @CurrentCommand12 = @CurrentCommand12 +
'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent),
@ParamPageCount = SUM(page_count)
FROM sys.dm_db_index_physical_stats(
@ParamDatabaseID, @ParamObjectID, @ParamIndexID,     @ParamPartitionNumber, ''LIMITED'')
WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND     index_level = 0'

EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int,
@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int,
@ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT',
@ParamDatabaseID= @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID =
@CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber,
@ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount =
@CurrentPageCount OUTPUT


SQL Server Merge Replication, Agent Batch Size Is there anyway to increase the merge agent batch size after the publication is created?

I know DownloadGenerationsPerBatch flag when creating a new Publication, however what I'm looking for is a way to modify this flag in a running publication

The prefered method would be to alter the merge agent profile.

See this link for a detailed explaination on how to do that:
http://msdn.microsoft.com/en-us/library/ms152515.aspx

The setting will take effect the next time the merge agent runs.


Optimize Percona MySQL InnoDB 3.2TB database (Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project)

I have a database server that is crazy big in size and is running on crazy small server, ie. 32GB RAM, 24 cores. During the weekend we are going to add 18GB of RAM to the machine to make it itsy bitsy more capable (server load during the day jumps up to average 80!!) until we will sort out performance issues with the application using this database.

I suspect that the load is caused by DB is swapping because of lack of free RAM (about 5GB always in SWAP).

This database has multiple small-ish tables and one huge table which is receiving GPS and IO data at rate around 350,000 records / h and this table is partitioned by a partition/month.

Mysqltuner suggests running optimize table on all tables but I have had read that doing so on InnoDB tables is useless and also it would take long hours.

Here is a snip from the script output:

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
  query_cache_limit (> 16M, or use smaller result sets)
  join_buffer_size (> 2.0M, or always use indexes with joins)
  tmp_table_size (> 16M)
  max_heap_table_size (> 16M)
  table_cache (> 4096)
  innodb_buffer_pool_size (>= 1726G)

Should I blindly trust it or is there another way to do this?

I know the first thing is to reconfigure the DB after the upgrade, what else should I be aware of and what should I try? Downtime should be as always as short as possible so I really don't want to run a query which would run for 5h straight.

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run.
Swapping should be a no-go for MySQL. Make sure to tune your innodb_buffer_pool_size to maximize memory usage without swapping. Please make sure you understand that there may be other things using memory: filesystem cache, needed for logging and other operations, and per-client used memory, like the join buffer and sort buffers. All together should be less than the available physical memory, as MySQL/InnoDB does its own way of buffering, and if it uses virtual memory that in reality it is on disk, you are going to have a bad time.
It is difficult to provide foolproof "general advice" without a full review of your server. But in my experience, 90% of the problems are with the queries and the database design, not with the server configuration.
However, here you have a good introduction to MySQL/Innodb configuration optimization by a good human (Peter Zaitsev). Hope it helps.


Setting up a many-to-many relationship based off of two other many-to-many relationships I have 3 main top level entities:

Project (PK: Id)
Owner (PK: Id)
Product (PK: Id)

Here are the rules:

A Project can have many Owners. An Owner can have many Projects.
A Project can have many Products. A Product can have many Projects.

This is the part that is confusing me:

A ProjectOwner can have many ProjectProducts and a ProjectProduct can have many ProjectOwners.

Here is how I currently have it setup (w/ two additional tables besides the ones above):

ProjectOwner (PK: ProjectId, PK: OwnerId)
ProjectProductOwner (PK: ProjectId, PK: ProductId, PK: OwnerId)

I am trying to figure out if I am following best practice with this because something seems strange. For some reason I feel like I should have this broken out into 3 tables (ProjectOwner, ProjectProduct, ProjectOwnerProduct).

Just to give you an idea of how things will be saved in the application it will be as follows:

User creates  a Project. User creates Owners and during this process they add them to the project. User adds multiple Products to the Project and associates them with Project Owners from the previous step.

It looks like you're almost where you want to be. I typically design those linking tables a bit differently, as well, because I don't like compound PKs and that may not be a "best practice" as much as a personal preference:

Primary Tables:

Create Table Projects (
    ProjectID int not null identity(1,1) primary key,
    ProjectName nvarchar(2000) not null,
    StartDate datetime2,
    FinishDate datetime2
    -- Other fields here
    )

Create Table People (
    PersonID  int not null identity(1,1) primary key,
    PersonName nvarchar(2000) not null,
    CreateDate Datetime2
    --other fields here
    )

Create Table Products (
    ProductID  int not null identity(1,1) primary key,
    ProductName nvarchar(2000) not null,
    CreateDate datetime2
    --other fields here
    )

Linking Tables:

Create Table ProjectManagers (
    ProjectManagerID   int not null identity(1,1) primary key,
    ProjectID int not null,
    PersonID int not null, --this is the manager
    -- other fields here
    Constraint FK_ProjectManager_Project Foreign Key (ProjectID) References Projects (ProjectID),
    Constraint FK_ProjectManager_Owner Foreign Key (PersonID) References People (PersonID)
)

Create Table ProjectProducts (
    ProjectProductID int not null identity(1,1) primary key,
    ProjectID int not null,
    ProductID int not null,
    -- other fields here
    Constraint FK_PRojectProduct_Project Foreign Key (ProjectID) References Projects (ProjectID),
    Constraint FK_PRojectProduct_Product Foreign Key (ProductID) References Products (ProductID)
)

Create Table ProductOwners (
    ProductOwnerID int not null identity(1,1) primary key,
    ProductID int not null,
    PersonID int not null,  --the owner
    -- other fields...
    Constraint FK_ProductOwner_Owner Foreign Key (PersonID) References People (PersonID)
    )

And now let's put some data in...

Insert into projects (ProjectName, StartDate) values ('Project 1', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 2', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 3', SYSDATETIME());

insert into People (PersonName, CreateDate) values ('Alan Alda', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Ben Bova', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Claire Collins', SYSDATETIME());

Insert into Products (ProductName, CreateDate) values ('1 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('2 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('3 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('4 product', SYSDATETIME());

And now let's link these together.

Insert Into ProjectManagers (ProjectID, PersonID) values (1, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (1, 2);
Insert Into ProjectManagers (ProjectID, PersonID) values (2, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (3, 3);

Insert into projectproducts (projectid, productid) values (1, 1)
Insert into projectproducts (projectid, productid) values (2, 1)
Insert into projectproducts (projectid, productid) values (3, 1)
Insert into projectproducts (projectid, productid) values (2, 2)
Insert into projectproducts (projectid, productid) values (3, 3)
Insert into projectproducts (projectid, productid) values (1, 4)
Insert into projectproducts (projectid, productid) values (2, 4)
Insert into projectproducts (projectid, productid) values (3, 4)

Insert Into ProductOwners (ProductID, PersonID) values (1, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 3)
Insert Into ProductOwners (ProductID, PersonID) values (3, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 2)

So now you can link a product up to a project and from there down to the owners or vice versa.

select proj.ProjectName, peMgr.PersonName [Proj Manager], prod.ProductName, peOwn.PersonName [Product Owner]
from projects proj
left join ProjectManagers pm on pm.ProjectID = proj.ProjectID
left join ProjectProducts pp on pp.ProjectID = proj.ProjectID
left join people peMgr on peMgr.personID = pm.personID
left join Products prod on  prod.ProductID = pp.ProductID
left join ProductOwners po on po.ProductID = prod.ProductID
left join people peOwn on peOwn.PersonID = po.PersonID
order by 1, 2, 3

This gives us :

ProjectName |  Proj Manager |  Product      |   Product Owner
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda

Similar queries can reverse that to show the owners and their products or other combinations. Some XML work can be used to turn this into comma-separated lists instead of more rows...


triggers - using the inserted / deleted tables in Dynamic SQL Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

Without more insight into the intended goal of this request, it certainly seems as though even with this immediate issue solved, the working code might not provide anything truly useful. Some concerns are:

Depending on how many tables this Trigger will be placed on and/or how frequent the DML operations are, this could cause minor performance issues in the database(s) where this Trigger is creating the tables because creating tables requires a Schema-Lock (I believe) and doing that too frequently might complicate some other operations.
If this Trigger will be placed on more than one Table, how will you distinguish the operations between the different Tables (unless giving them their own prefix for the dynamically created tables)?
Do you have an UpdatedDate or some date field in the table? If not, there is no sense of chronology without looking at the table creation date.
How do you plan to clean up all of these various tables? Maybe it would be best to create a Schema just to hold these tables?
Do you plan on indicating anywhere the DML operation that took place?
If you want to track the "before" and "after" values on an UPDATE, then you need to capture both inserted and deleted tables. But if they have GUID-based names, then you won't be able to correlate between the "inserted" and "deleted" copy tables for a particular UPDATE operation. You would have to re-use the same GUID value and denote the "insert" or "delete" in the table name prefix. If you weren't dynamically creating the table, then you could include a column specifying the DML action, dump both inserted and deleted tables into the already existing table, and just use a GUID or INT from a Sequence to correlate between 2 rows of the same UPDATE operation.
Depending on what version and edition of SQL Server you are using, you might want to look into Change Tracking and Change Data Capture.

However, all of that being said, the issue of interacting with the inserted and deleted tables via Dynamic SQL is an interesting problem. Unfortunately, it can't be done in T-SQL. So now it's also a challenge :-). Fortunately, this can actually be done. How so? With a little help from our friend, Mr SQLCLR.

Now, there doesn't seem to be a lot of situations that really demand, or even benefit from, SQLCLR Triggers. They seem to be the least useful things you can create with SQLCLR. However, here we have a scenario that they are a wonderful fit for. SQL submitted from SQLCLR code is Dynamic SQL. And SQLCLR Triggers have access to the inserted and deleted tables, so it would seem that SQLCLR Triggers can access the inserted and deleted tables in Dynamic SQL. Below is the code that does exactly that to accomplish this request (please note that the DB connection is using the in-process "Context Connection", so the Assembly can be marked with PERMISSION_SET = SAFE; no need for an Asymmetric Key or to set the database to TRUSTWORTHY ON):

Test table for the Trigger to be created on (if using Visual Studio / SSDT, the table definition has to be included in the project):

CREATE TABLE TableThatHasTriggers
(
   TableThatHasTriggersID INT IDENTITY(1, 1) NOT NULL
                          CONSTRAINT [PK_TableThatHasTriggers] PRIMARY KEY,
   InsertTime DATETIME NOT NULL
              CONSTRAINT [DF_TableThatHasTriggers_InsertTime] DEFAULT (GETDATE()),
   SomeValue NVARCHAR(50) COLLATE Latin1_General_100_CI_AS NULL
);

The SQLCLR C# code:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class Triggers
{
    [SqlTrigger(Target = "TableThatHasTriggers", Event = "FOR INSERT, UPDATE")]
    public static void tr_TableThatHasTriggers_audit()
    {
        string _AuditSQL = @"
                SELECT ins.*
                INTO   dbo.[TMPIns_" + Guid.NewGuid().ToString().Replace("-", "") + @"]
                FROM   INSERTED ins;
";

        SqlConnection _Connection = new SqlConnection("Context Connection = true");
        SqlCommand _Command = _Connection.CreateCommand();
        _Command.CommandText = _AuditSQL;

        // SqlContext.Pipe.Send(_AuditSQL); // display query for debugging purposes ONLY

        try
        {
            _Connection.Open();
            _Command.ExecuteNonQuery();
        }
        finally
        {
            _Command.Dispose();
            _Connection.Dispose();
        }
    }
}

T-SQL wrapper object to place the SQLCLR Trigger onto the Table:

CREATE TRIGGER [dbo].[tr_TableThatHasTriggers_SQLCLRaudit]
  ON [dbo].[TableThatHasTriggers]
  AFTER INSERT, UPDATE
  AS EXTERNAL NAME
             [InsertedTableViaDynamicSQL].[Triggers].[tr_TableThatHasTriggers_SQLCLRaudit];


Examples of SQL transaction procedures for sales tracking or a financial database I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:

salesman creates new product shipped onto floor (itempk, car make, year, price).
salesman changes price.
salesman creates sale entry for product sold (salespk, itemforeignkey, price sold, salesman).
salesman cancels item for removed product.
salesman cancels sale for cancelled sale.

The examples I have found online are too generic, like "this is a transaction", I would like something resembling what I am trying to do to understand it.

Anybody have some good similar or related sql examples I can look at to design these? Do people use transactions for sales databases? Or if you have done this kind of sql transaction before could you make an outline for how these could be made?

My (closed as not a real question) thread so far on stack overflow: Need example SQL transaction procedures for sales tracking or financial database

Latest update, user will send new inputs/ changes /and cancellations from a c# application.
Application data:

Products On Display (this is the parent node which has 3 child nodes)
Sales(child node of Products On Display)
Product Custom Features(child node of Products On Display)
Product Price / current status (child node of Products On Display)

C# App will package that data into XML format and then execute some SQL stored procedures with transactions holding together the xml to table conversions into the SQL Tables designed with the same parent/child node structure using something like what is described by the answers to this related question on Stack Overflow:
https://stackoverflow.com/q/2756773/613799

I wish there was a book on designing multi-user sales databases, and the stored procedure transactions that will be used by the related user apps from scratch  app->xml->database. Please let me know if you know of a good one, or a chapter of a book.

Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.

Any examples would be very basic as every database schema would be different.  This is where having a database or data architect comes in handy for a project.


reduce network traffic for MySQL queries As part of a hobby-project I have a number of servers on my network that measure various parameters (both system statistics and domotica) and store the results (mostly as 5-minute averages) in various tables in a MySQL database running on one of the servers.

Using bash each server periodically queries the MySQL server to get a subset of the stored data in a local file. Then, I use python and gnuplot to turn the raw data in some nice trend graphs.

I currently make hourly, daily and weekly graphs. I already have a lot of data and I would like to make a couple of yearly graphs, however, I'm seeing that queries for a week's worth of data already take some time (query + network + local diskwrites) so I'm hesitant to proceed.

I was wondering if you guys have experience with this type of problem and know of / have experience with standard solutions that I have not found.

Are there magic queries, compression techniques or local storage solutions that I might want to investigate? The goal being to reduce the network load and reduce the MySQL server workload by preventing it to have to server the same data over and over again.

A few considerations:

Ensure your tables have indexes which serve the queries well. The best designs have an index on any column which appears in a where clause.

You can also have multiple columns in an index. See composite indexes.

If your accessing the same data multiple times you can look into caching, with something like memcached.

If you regularly summarise the data and access that same summarised data regularly you could put stored procedures in your database to update a summary table every time you enter new data into your fact table.

By no means the best possible answer but definitely the first things you should consider on a growing dataset.

I'll add some referencing links later but I'm on mobile right now.


myisampack running really slow for large DBs I was going through compressing a bunch of archival myisam tables using myisampack

It was making decent progress until it got to the most recent tables.  The overall database is a set of archives for a single production table.  Each archive version represents 1 quarter of a year going back several years.

Nothing has drastically changed in the data being stored or the average row length.  The individual archives have been growing with time as there simply are more on more rows each quarter over the previous one.

Up until the most recent 4 quarters the rate of compressing/rebuilding the index (which is only a single int PK) was around 14-16k rows/seconds.

The last ones were crawling along only doing about 1k rows/second.  Even right from the start; it wasn't like it started of as fast as the others and then suddenly slowed down after getting so far into it.

There wasn't any spike in general system load or i/o contention from other processes when it started slowing down.

Has anyone experienced this?  Is there something myisampack where it's overall rate of progress it should be expected to tank when compressing a table over a certain size?

Edit:  Since these are myisam tables I should point out they are all in the same datadir and all stored on the same physical array.

If you have any TEXT/BLOB fields in your MyISAM tables, you may just have bloated fields.

You verify this, go back to your older MyISAM tables and perform this:

SELECT LENGTH(TextOrBLOBField) FROM some_table;

Run this same query against more recent tables and see if the slowness is just O(n) (order n) running time based on the amount of recorded data.


SQL - Is it possible to have a Trigger statement with a WHERE clause that is an equality measure I'm trying to add a counter to one of my tables. So far I have these tables

Heroes {hero_id, hero_name, villain_id, team_id}
Villains {villain_id, villain_name, team_id}
Teams {team_id, team_name, no_of_members}

I want to create a trigger that updates the member count whenever a person is added to a team. So far i have something like this but it keeps giving me Trigger compilation errors. I have seen other instances where people have used WHERE clauses in triggers but they arent similar to my problem. If anyone could tell me why this isnt compiling or what I'm doing wrong I'd be very grateful.

CREATE TRIGGER team_no_inc
    BEFORE INSERT ON Heroes
    FOR EACH ROW
BEGIN
    UPDATE Teams SET no_of_members = no_of_members + 1
    FROM Heroes, Teams WHERE Teams.team_id = Heroes.team_id;
END;
.
RUN;

In Oracle triggers, you can access the modified table using :NEW (for inserted and after an update data) and :OLD (for deleted and before the update).

Also there is noUPDATE .. FROM syntax. Your trigger can be written:

CREATE TRIGGER team_no_inc
    BEFORE INSERT ON Heroes
    FOR EACH ROW
BEGIN
    UPDATE Teams SET no_of_members = no_of_members + 1
    WHERE Teams.team_id = :NEW.team_id;
END;

Tested at dbfiddle.uk.


Does a read replica help if the problem are heavy queries? The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather some heavy queries joining a few tables. These queries are executed just every few minutes, and execution seldom overlap, but they sometimes make the DB choke and as the system has more data, it's a growing problem. The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

I am relatively new to the team and today I asked if a read replica had been considered. I know little about DBs, so that felt like natural to me, but a senior engineer told me that it would barely help, because the read replica would have the same problems as the master: it would still need to do the same writes as the master, and the heavy queries would be equally heavy to it and the chances of it timing out, the same. The only gain would be the amount of them each of the instances has to serve.

Put like that, sounds very reasonable. My questions are:

Is a read replica really not of any help in this situation?
What are some alternatives?

PS: in case it matters, it's PostgreSQL.

If you had a lot of concurrent heavy queries, the replica could help spread the workload.  Or if the heavy query was interfering with other work the master database needs to do.  But if you don't run multiple queries at the same time and the problem is only that they take too long, there is no reason to think a replica would help that situation.

  make the DB choke

What does that mean? Databases don't get food lodged in their trachea. Are you getting error messages(other than apparently query time-outs)?

  The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

There is a lot more to performance optimization than that.  What about indexes?  Are you using a version of PostgreSQL that supports parallelism?  See https://dba.stackexchange.com/tags/postgresql-performance/info for more info on how to optimize (or at least, how to ask optimization questions).

What are alternatives?  Well, if the only problem is that the queries time out, then change your time outs.  Tune your queries, as above.  Upgrade the database version so it can use parallelism.


best way to add Entity id & entity types in database we are doing shopping site with mysql with eav model to store values.

in below image , 2 types of entity types are there under products_information table : 1) Mobile 2) T-shirts

I wanted to know in which table i have to store those entity types & entity ids

Is i need to create new table or adding above 2 columns in existing table ?

Make "Entity" an entry in attributes_list and treat it as any other attribute.

Also, do the same for "Category" (whatever your categories might be) and drop Category_details table


What does this error mean in IBM Netezza Administrator? - ERROR: Permission denied on "_VT_DISK_PARTITION" What does this error mean when opening IBM Netezza Administrator?

ERROR: Permission denied on "_VT_DISK_PARTITION"

I asked someone in my department, and I guess the tool always throws this error when you do not have administrator permissions.  That Netezza view (_VT_DISK_PARTITION) is not accessible with non-admin security.  It's necessary to show some of the features in the default view/tab ("System") of the tool.

When clicking on the "Database" view/tab, and navigate to some database and table, it might take a bit to load the data.  But that view/tab should not throw any errors to make the tool usable.


PostGIS geocoding from stdaddr? PostGIS provides many different methods of address standardization

Some newer methods return stdaddr,

standardize_address (which requires explicit lex, gaz, and rules)

Others return norm_addy

pagc_normalize_address (which provides default pagc_lex, pagc_gaz, and pagc_rules) to standardize_address
normalize_address (which is the original)

However, geocode() only takes norm_addy, how do I cast a stdaddr to norm_addy. I intend to cache my stdaddr on a table and I'd like to have a quick and fast way to finish the geocode.

From this example, and from the code shown in \df+ pagc_normalize_address I was able to write this CAST.

CREATE OR REPLACE FUNCTION stdaddr2normaddy(addr stdaddr) RETURNS norm_addy AS $body$
  SELECT (
    (addr).house_num,  --address
    null,              --predirabbrev
    (addr).name,       --streetname
    (addr).suftype,    --streettypeabbrev
    null,              --postdirabbrev
    (addr).unit,       --internal
    (addr).city,       --location
    (addr).state,      --stateabbrev
    (addr).postcode,   --zip
    true               --parsed
  )::norm_addy;
$body$ LANGUAGE sql IMMUTABLE;

CREATE CAST (stdaddr AS norm_addy)
  WITH FUNCTION stdaddr2normaddy(stdaddr)
  AS IMPLICIT;

Now you can test it out...

SELECT x.* FROM pagc_normalize_address(
  '3311 My Street Dr., Kingwood Texas, 77345'
) AS x;

SELECT (g.x).*
FROM (
  SELECT x::norm_addy
  FROM standardize_address(
    'tiger.pagc_lex',
    'tiger.pagc_gaz',
    'tiger.pagc_rules',
    '3311 My Street Dr.',
    'Kingwood Texas, 77345'
  ) AS x
) AS g;


Tempdb autogrowth settings Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files?

I ask because I would like to let SQL Server use the round-robin algorithm to spread the workload out across each of the files. I understand that if the files are not of equal size, then SQL Server will use the file with the most space, thus increasing the chances on causing latch contention. Is my understanding here correct?

Cheers,

You could run this against each file, replacing each n with the size and growth amounts:

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

ALTER DATABASE tempdb
MODIFY FILE
(
  NAME       = tempdev2,
  SIZE       = nMB,
  FILEGROWTH = nMB
);

...

Now to ensure that they all grow at the same time (instead of only one of them growing, e.g. in response to a large spill or other operation), then you can enable trace flag 1117, but keep in mind that this flag applies to all databases, so if tempdb isn't your only database with more than one data file, you'll want to test this behavior. More info:

http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/sql-server-2008-trace-flag-t-1117.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Tempdb-configuration-survey-results.aspx


Why can't I SPOOL an EXPLAIN PLAN? Why can't I spool the explain plan to a txt file?

SPOOL EXPLAIN_PLAN_1.txt

set heading off
set echo off
set pages 999
set long 90000

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));

SPOOL OFF;

Error that I see

SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Oracle Version: 11g

SP2-0768 Illegal SPOOL command

  Cause: An invalid option was used in the SPOOL command.
  Action: Check the syntax of the SPOOL command for the correct options.

  SP2-0769 Usage: SPOOL { <file> | OFF | OUT }

  where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
  Cause: Incorrect syntax for the SPOOL command was entered.
  Action: Check the syntax of the SPOOL command for the correct usage.

Exact same command that you have shown in the question is working fine for me as shown below.
OS: Oracle Linux 6
Oracle Version: 11.2.0.4.0

[oracle@testsrv Desktop]$ sqlplus / as sysdba

SQL> SPOOL EXPLAIN_PLAN_1.txt
SQL> set heading off
SQL> set echo off
SQL> set pages 999
SQL> set long 90000
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical'));  2

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL> SPOOL OFF;

[oracle@testsrv Desktop]$ cat EXPLAIN_PLAN_1.txt
SQL>
SQL> explain plan for select * from hr.employees;

Explained.

SQL> select plan_table_output from
  2  table(dbms_xplan.display('plan_table',null,'typical'));

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL> SPOOL OFF;
[oracle@testsrv Desktop]$


How do I map SSIS 2012 Package Variables to Parameters? I have created multiple packages that use variables to assign Connection Manager Expressions such as Connection String. However in the new SQL 2012 Project Deployment Model I don't seem to be able to configure these variables.

Would I therefore need to create these Parameters for the Environment and map them to specific Package Variables? Is this even possible and how is this done?

This link describes configuring the Parameters, but not associating to package Variables.

Further to this, it seems I could set a Varaiable Expression to be a Project or Package Parameter, however this seems a bit convoluted. Perhaps I should stop using Variables altogether?

Update: I've now cut-over to using Project-level and Package-level Parameters (instead of Package Variables) when I need to modify per-environment settings such as file paths and connection strings. Perhaps this was the intent from MS: any best practice advice is of course, still welcome.

I am bit too late to answer this question but I would like to make a point about Parameter role in package deployment model

It says'

  In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters.

  When you deploy a package that contains parameters using the package
  deployment model and then execute the package, the parameters are not
  called during execution. If the package contains package parameters
  and expressions within the package use the parameters, the resulting
  values are applied at runtime. If the package contains project
  parameters, the package execution may fail.

Here is a beautiful answer about setting up variables for different environment configurations.


Very slow request with small difference I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert.

I have three requests. First and second are fast (0.2s), and the third is very slow (7s) when the WHERE clause is the combination of the first ones.

// Fetch all items in a universe (it's a category in marketing terms)

 SELECT item.codeItem, item.designation
 FROM Item item
 WHERE item.universe = 10
 ORDER BY designation  LIMIT 0,25

// search articles from a supplier (an article may have many prices with different suppliers)
//Price table is used as an associative table between Article and Supplier

SELECT DISTINCT item.codeItem, item.designation
FROM Item item, Price price, Supplier supplier
WHERE  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

// search Article with a supplier in a universe

SELECT DISTINCT item.codeItem, item.designation
FROM Item a, Price t, Supplier supplier
WHERE item.universe = 10 AND  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

First question is : Why is the last one so slow ? I would have thought that adding AND filters could make the request even faster.

Second question is : what would you try first to improve the query. Is a nested SELECT a good solution ?

It would be interesting to know if there are indexes on your tables (and if there are, their structure as well).  There is the slight possibility that these would help to understand the reason behind the third query being much slower than the second one.

Well, this does not answer this mystery, but apart from the above, you should omit the Supplier table from your queries.  Since you don't select any data from it, the only thing it does is multiplying the number of the rows selected (SELECT any_column FROM table1 t1, table2 t2 without further conditions on which t1 row to be joined to which t2 row produces the Cartesian product of the two tables - it can be written as table1 t1 CROSS JOIN table2 t2 in other RDBMSes, however in SQLite CROSS JOIN only fixes the order in which tables are processed).  You apparently use SELECT DISTINCT to get rid of the multiplicated results (@ypercube, thanks for the suggestion).

It would be interesting to see whether the speed difference is still there after these modifications.  If the answer is 'yes', than again I would try to swap the two parts of the WHERE clause and check the difference again.  (Well, SQLite seems to be quite smart avoiding plan gotchas like this, but you never can tell with bees.)


When does a MySQL session end? I have a stored procedure as described below.

DROP PROCEDURE `GetVoteID`//
CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT votes_id TEXT)
BEGIN
    SET SESSION group_concat_max_len = 100000;
    SET @eliminated_candidates := (
        SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))
        FROM vote_final
        WHERE votes = minimum_votes
        AND round_id = current_round
    );
    SELECT GROUP_CONCAT(CONVERT(vote_id, CHAR(11))) INTO votes_id
    FROM votes_copy
    WHERE FIND_IN_SET(vote_candidate_1, @eliminated_candidates) != 0;
END

Since I'm using a group_concat that produces quite long strings in both the set-statement and the select-statement I need to change the sessions group_concat_max_len to 100000 chars. But this is where I run into problems. Using this method I've figured out that SET SESSION group_concat_max_len = 100000; really never changes the limit. When the length of @votes_id reaches 1,024 it's content is truncated - even though I've changed the limit to 100,000.

CALL GetVoteID(501,3,@votes_id);
SELECT sum(char_length(@votes_id));

This leaves me with two questions.

When does this session actually end? At the end of the stored procedure or at the end of each statement within the procedure?
What am I doing wrong with the group_concat_max_len?

To answer your questions:

Session ends when session (with mysql) ends and not when stored procedure ends! So the setting of group concat you did appears correct.

You may want to refer about statement caching and local variables scopes in SP.

I simulated your scenario and it worked well for me!

MySQL Version: 5.6.24-72.2-log

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS session_test $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE session_test (INOUT str TEXT)
    -> BEGIN
    -> show session variables like 'group_concat_max_len';
    -> set session group_concat_max_len = 10000;
    -> show session variables like 'group_concat_max_len';
    -> select group_concat(ts1) into str from t1;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call session_test(@myvar);
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.01 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select length(@myvar);
+----------------+
| length(@myvar) |
+----------------+
|          10000 |
+----------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1260 | Row 501 was cut by GROUP_CONCAT() |
+---------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> show session variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 10000 |
+----------------------+-------+
1 row in set (0.00 sec)

You see the last warning cause my group concat length went beyond 10K but that's OK!

You're not doing anything wrong with the variable as such it appears fine to me!

Are you sure you have group concat length of 10k? Please try to exec the select statement from procedure.


How can I set up SQL Server 2008 users without Windows/Domain accounts? I'm trying to do this through the SQL Server Management Studio GUI.

The user accounts will be accessed by applications directly, so I don't want to create Windows user accounts.  I'd rather use something like the sa account.

Is there a way of creating users directly in SQL Server?

The first part is to create a login for the new user, if you can't login to the server you can't perform queries on a database.

In MSSMS Expand the Security tab, right click on Login and select New User. Click the SQL Server authentication radio button and fill in the password for the new user. You will probably want to uncheck the box for "Enforce password expiration" and "User must change password at next login" if you are using the login from an application.

Click the User Mapping page in the left panel and map the new login to a database and give them the desired roles, click OK and you're done.

With all of that said there are two things you should be aware of.

Questions like this are better asked on https://serverfault.com/ or apparently http://dba.stackexchange.com :-)
It is still better to create windows / domain accounts and either run your application pool under that context or use impersonation.


mongodump 3.4.1 error reading collection: Closed explicitly [BACKUP COMMAND]: mongodump -u username -p password -d database --port port -o output path.

[ISSUE]: error writing data for collection to disk: error reading collection: Closed explicitly.

The storage requirements of my MongoDB database are constantly increasing. So far the database is using 20 GB of disk space and I am encountering errors when I want to dump the database. It always fails and the above issue is displayed in the terminal shell when I use the above mongodump command. I have to reissue the mongodump command again and again until the backup of the database finishes.

So my questions are:

Is there a good alternative database backup tool/command for MongoDB?
Is there a stable and fast backup command for large-scale MongoDB databases?
Am I using the correct procedure?

A quote from the horse's mouth (ie. https://docs.mongodb.com/manual/core/backups/#back-up-with-mongodump):

  mongodump and mongorestore are simple and efficient tools for backing up
  and restoring small MongoDB deployments, but are not ideal
  for capturing backups of larger systems.


SQL Server 2008 and constant expressions If I have the following scenario, would Replace be evaluated a single time?

DECLARE @text nvarchar(32) = 'This is a test field'

SELECT textField FROM Table
WHERE Id = @idICareAbout AND textField = REPLACE(@text, ' ', '')

I would hope that it would, but I tend to be nervous about it and do this instead:

DECLARE @text nvarchar(32)
Select @text = REPLACE('This is a test field', ' ', '')

SELECT textField FROM Table
WHERE Id = @idICareAbout AND textField = @text

Of course replace here is just an example, and I'm sure this second variant is probably clearer and safer, but occasionally when I'm over optimizing, I think "What if I didn't have to do the replace at all, because the first part of the WHERE expression evaluates to false". Normally this would be such a micro-optimization it's silly to even think about but there have been a few times where the scenario is such that it may make a not miniscule difference. (Many more where clause parameters and maybe a bunch of HASHBYTES of parameters passed in.)

So I'm just curious how SQL server handles it.

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant Functions to get a grasp what is 'constant' in SQL to start with.

See http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/ for an example when SQL Server does not do boolean short-circuit.


How to make MYSQL smart enough to understand when it should take into account stop word and when it shouldn't? Ok, here is the problem. I have a table

Text
text nature ....
text2 nature ....
a lot of nature ... rows here..
....
go with nature
with nature her go it

Now, i select:

select * From MyFullText where MATCH(text) AGAINST ('+go +with +nature' IN BOOLEAN MODE)

As a human being, we will bring out only 2 rows:

Text
go with nature
with nature her go it

However, MySql is not human & it shows all rows that have the word "nature" since "go" & "with" are stop words so it doesn't take into account these words.

What if we have 1000 rows containing the word "nature"? if that is the case then user will waste a lot of time for searching.

In this case like '%go with nature%' does a much better job than fulltext.

So, can you find out an elegant solution that meets these requirements:

-Use "Fulltext ability for all non-stop words"

-If there are "stop words" in the search term then it should go back to use LIKE as in like '%go with nature%'

-Generally, it will be very smart to bring the best result depending on the search term.

I state you have two possibilities with MySQL:

Deactivate stop word list

You can deactivate the stop word list completely.

  To override the default stopword list, set the ft_stopword_file system
  variable. [...] The variable value should be the path name of the file
  containing the stopword list, or the empty string to disable stopword
  filtering.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html

(You might want to change ft_min_word_len too, since its default value is 4 and "go" is only 2 characters long.)

This would allow you to use the fulltext index even in case of presence of stop words, which offers by far more possibilities than any search with LIKE and is a lot faster.

Control the query

Your application can scan the search words before the query to actually change the query depending on "do the search words contain a stop word". If yes, query with LIKE, otherwise query with AGAINST. This way still leaves you with a very slow LIKE based query in the stop word case!

I do not know of further possibilities with MySQL as its capabilities concerning fulltext searches are rather limited.

Non-MySQL-Options (Take a look around)

There is other software around, which eases problems of search&find, for example the modified MySQL code Sphinx. Sphinx obviously has a different approach than MySQL since its default stop word list is empty. If there even is the possibility to add another software to your infrastructure, you might take a look at its possibilities or search for other software which might fulfill your needs (e.g. Apache Lucene).


Image fades on the SSRS report We have a SSRS report built using Report Builder 3. There is a coloured logo on top of this report which is displayed correctly when see the report on screen, print it or export it to Word. However when the report is exported to pdf, the image gets faded and seems transparent. The image is embeded in the report and the image file type is .jpg. W

Why is it happening? How can I prevent it?

As Jon kindly suggested, I changed the format of the image to .png and the issue is resolved on the spot.


How can I group (by) two joined tables How can I group (by) two joined tables (in an Oracle database) like this:

cant:
id      CANT_VAL    COD_VAL
18059   18.56       R12
18060   11          R10
18061   15          R11

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL
18059   18.56       R12
18059   1134        R10
18059   1234        R3
18060   11          R10
18061   15          R11
18061   1111        R5

Thanks!

That is not a GROUP BY, but UNION:

select * from cant
union
select * from col
order by id, cant_val;


Correct way to write this view? Total sales/purchases/profits by day I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables:

transaction:
idtransaction (pk)
user_iduser (fk)
item_iditem (fk)
total_price (bigint)
time(datetime)
transaction_type (enum: 'buy', 'sell')

profit:
idprofit (pk)
transaction_idtransaction_bought (fk)
transaction_idtransaction_sold (fk)
quantity_profit (int)
profit_unit (bigint)

calendar:
days (date)

Calendar table is a simple table created from a stored procedure. It consists on every date between 2000-2050. Profits are already calculated from the application side, the profits table simply stores the results.
 Note that different users MAY participate in the same 'profit', (e.x user 200 buys an item and user 201 sells it. Again this is handled by the application side). In this case you'd increment the value of sales and profit to user 201 and the value of purchases to user 200.

I'm trying to create a view that outputs this (a snapshot of sums for each day for each user)

date      userid  total_bought  total_sold  total_profit  profit_margin
01-01-2016   200   150000       2000000      50000         0.20
02-01-2016   200   512          14515        456           0.05
03-01-2016   200   150000       1000000      5000          0.14
04-01-2016   200   512          12515        4556          0.07

and so on (do the same for every user and date, so in total the number of rows should be users x dates)

However my implementation is outputting wrong data.

create view v_history_details as

select calendar.days as hday,
users.iduser as user_id,
sum(t1.price_total) as total_buy
sum(profit.profit_unit*profit.quantity_profit) as total_profit,
(sum(total_profit)/sum(total_buy)) * 100 AS margin

from
calendar

JOIN transaction t1 ON date(t1.time) = calendar.days
JOIN transaction t2 ON date(t2.time) = calendar.days

JOIN users on t1.iduser = users.iduser

where t1.transaction_type = 'Buy'
and t2.transaction_type = 'Sell'

GROUP BY calendar.days

order by calendar.days ASC

The issue comes from joining with the transaction table twice. I need a way to split the purchases and sales data but at the same time I also need them in the same view. I'm kind of new to all this

I'd really appreciate some guidance on how to structure this view here.

I ended up doing it this way:

SELECT temp1.hday,
temp1.user_id,
temp1.total_buy,
temp1.total_sell,
coalesce(temp2.total_profit,0) as total_profit,
coalesce(temp3.margin,0) as margin
from

(
select
calendar.days as hday,
users.iduser as user_id,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Buy' THEN transaction.price_total END),0) as total_buy,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Sell' THEN transaction.price_total END),0) as total_sell
-- (total_profit/total_buy) * 100 AS margin
from
calendar
JOIN transaction ON date(transaction.time) = calendar.days
JOIN users on transaction.user_iduser = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp1

left JOIN

(
select calendar.days as hday,
users.iduser as user_id,
coalesce(sum(profit.profit_unit*profit.quantity_profit),0) as total_profit
from calendar
JOIN profit on date(profit.timestamp_sell) = calendar.days
JOIN users on  profit.users_idusers_OUT = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp2

ON temp1.hday = temp2.hday

left join

(
select (sum(profit.profit_unit*profit.quantity_profit)/sum(t1.price_unit*profit.quantity_profit))*100 as margin, calendar.days as hday
                from profit
                join transaction t1 on profit.transaction_idbuy_buy = t1.idbuy
                join transaction t2 on profit.transaction_idbuy_sell = t2.idbuy
                join users on t2.user_iduser = users.iduser
                join calendar on calendar.days = date(profit.timestamp_sell)

                GROUP BY calendar.days, users.iduser

) temp3

on temp2.hday = temp3.hday

I had to create 2 temporary tables. The first gathers information for purchases and sales with conditional aggregates while the second one contains the profit information. profit margin comes from merging again with a 3rd table.

There's probably easier ways of doing this, though