27
Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case:
Summary
The numbers are different because:
The select reads the LOB in packet-sized chunks
The variable assignment test does not read the LOB at all
The "...
answered May 8 '16 at 14:07
18
Use pg_read_file('location_of file')::bytea.
For example,
create table test(id int, image bytea);
insert into test values (1, pg_read_file('/home/xyz')::bytea);
Manual
15
I think the right answer here depends a lot on your application, and how important those documents are.
For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the ...
15
While I disagree that BLOBs should just be in another table -- they should not be in the database at all. Store a pointer to where the file lives on disk, and then just get that from the database...
The primary issue they cause (for me) is with indexing. Using XML with query plans, because everyone's got'em, let's make a table:
SELECT TOP 1000
ID = ...
13
Don't do it.
There really isn't an upside to having files stored in the database.
Doesn't it already feel weird and fishy when you think to yourself:
Should I store files in a database or a filesystem?
Even better, say it out loud.
On to the facts:
Using the database
"PROS"... but not quite:
"Atomicity" which is correct but it's a double edged ...
13
This is the solution I came up with:
Enable xp_cmdshell with
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
If needed create a directory with xp_cmdshell to get the needed permissions.
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
Use BCP with queryout
EXEC master..xp_cmdshell 'BCP "...
13
It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST.
USE tempdb;
DROP TABLE IF EXISTS dbo.RobertBurns;
CREATE TABLE dbo.RobertBurns
(
Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Scotch VARCHAR(50),
HaggisAddress VARBINARY(MAX)
);
DECLARE @...
11
You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.
Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).
Call expdp as usual, adding:
REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION
Short example (schema: mat):
create or replace
package remap as
function null_lob(...
11
In the absence of any answers I've explored the issue further myself.
It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much.
I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla ...
11
Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors).
One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, ...
11
Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).
Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...
11
How large are these images, and how many do you expect to have? While I mostly agree with @sp_BlitzErik, I think there are some scenarios where it is ok to do this, and so it would help to have a clearer picture of what is actually being requested here.
Some options to consider that alleviate most of the negative aspects pointed out by Erik are:
FILESTREAM ...
10
I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:
Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData [4]:
Enter length of ...
10
You can run that, no problem:
VACUUM FULL ANALYZE pg_largeobject;
Might even remove some dead rows. Details:
VACUUM returning disk space to operating system
But it's probably not going to solve your actual problem.
When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of binary ...
answered Aug 29 '15 at 23:36
Erwin Brandstetter
110k12 gold badges237 silver badges370 bronze badges
10
am I correct in thinking that LOB_DATA pages can cause slow scans not
only because of their size, but also because SQL Server can't scan the
clustered index effectively
Yes, reading LOB data not stored in-row leads to random IO instead of sequential IO. The disk performance metric to use here to understand why it is fast or slow is Random Read IOPS.
...
10
I would use the "Generate Scripts" option in SQL Server Management Studio.
In Object Explorer - highlight the database hosting the source
table.
Right-click menu - Tasks | Generate Scripts
Select specific table object - click Next page
Click Advanced button
Under General Options
- Change 'Type of data to script' to "Data Only"
Save to file - change ...
9
I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in the main heap tables so as to move them out of the way of operations that may count large numbers of records. Other dbs may do something similar.
The major ...
8
...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.
This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types.
It definitely appears that the LOB data must be loaded into memory on the server ...
answered May 3 '16 at 10:56
7
Do not store files in a database.
Everyone, without exception, that can run any RDBMS on the market already has a database specifically for storing files, and the RDBMS itself is using it! That database is the filesystem. Now let's talk about some of the potential drawbacks of storing files in the database, as well as some specific mitigating factors for ...
7
Bill Karwin has addressed this before in his answer to Row size error with MySQL
I also addressed this in the past : MySQL: Row size too large (> 8126)
Based on his post, and the fact that you still have several TEXT and VARCHAR fields, you should set the following values higher in my.cnf:
[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 2G
...
7
That is not how you load a BLOB field. BFILENAME returns a BFILE locator, and BFILE data is not stored in the database, but in files outside of but accessible to the database.
The above command would not even succeed if BLOBCOL is really of BLOB type, you would get the below error:
ORA-00932: inconsistent datatypes: expected BLOB got FILE
Another mistake ...
7
If the data must be stored in SQL Server for whatever reason I can think of a few benefits to storing it in a separate table. Some are more convincing than others.
Putting the data in a separate table means you can store it in a separate database. This can have advantages for scheduled maintenance. For example, you can run DBCC CHECKDB only on the database ...
7
Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled.
The reference points to an Answer written early in 2010, a few months before DYNAMIC and COMPRESSED were introduced in the "InnoDB plugin".
So, I claim that that other Q&A is out of date! That is the 754 Upvotes are no longer valid....
6
My vote would be for neither. Store the data in a system like Amazon S3 or Microsft's CDN and store that URL in the database.
This way you get reliability of having the data always accessible without having monster sized databases to deal with.
6
BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB
As for TINY/MEDIUM/LONG; how big is your data?
TINYBLOB : max 255 bytes
BLOB : max 65,535 bytes
MEDIUMBLOB : max 16,777,215 bytes
LONGBLOB : max 4,294,967,295 bytes
In some small minority of cases TINYBLOB may suffice, ...
6
Memory related settings
You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file.
Actually, there's a litany of good info in this DBA....
6
There's more to life than performance, like data integrity. If you only care about performance you should be writing all your code in Assembly.
There is a higher risk of screwing things up by using the file system:
If you keep files in the file system, you now have two things to backup instead of one.
Other stuff: dealing with orphaned files. Dealing ...
6
From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres.
bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB value). Which means that this is kept in the memory on the JVM while reading it. This is the same as with Oracle's BLOB type. From a JDBC point of view those two ...
answered Jan 11 '16 at 12:21
a_horse_with_no_name
50.5k10 gold badges99 silver badges132 bronze badges
5
Just do
SELECT ItemId
FROM MyTable
WHERE Value = 0xAAFF
You are converting the varchar representation to varbinary(max) which is not correct.
SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.
5
Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small.
I have discussed TOAST before in the DBA StackExchange
May 01, 2012 : what is bigger than a longblob?
Mar 21, 2012 : Are many NULL columns harmful in mysql ...
Only top voted, non community-wiki answers of a minimum length are eligible
