Storing a Binary File in a Database

Question

Storing a Binary File in a Database

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

mburbea Right there with Babe Points: 781 More actions · Answer 1

I'd recommend a filestream over this approach. Filestream is natively supported, and offers asynchronize I/O, which means inserting your blobs will be faster, as will retrieval. Filestream is ideal for storing files of 1mb or larger though, and anything less isn't worth the overhead. I use it to great to store certain dataset raw files, and the performance gain of using filestream was about ~4x the performance of trying to do it without it.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

Jeff Moden (5/28/2015)
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Extremely personal and grossly incorrect opinion noted. 😉

@jeff, "better" is relative and there is clearly a preference, but "grossly incorrect" also reads as an opinion given its subjectivity. Clearly you disagree with the "better" assertion but the quoted text seems taken out context.

@Solomon, rereading your post, when taken in context I am thinking "more robust in terms of security options" might have been more the intent of the comment but Jeff won't let anyone off the hook that easily. Canbyou clarify, "better" how?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

Orlando Colamatteo (5/28/2015)
Jeff Moden (5/28/2015)
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Extremely personal and grossly incorrect opinion noted. 😉
@jeff, "better" is relative and there is clearly a preference, but "grossly incorrect" also reads as an opinion given its subjectivity. Clearly you disagree with the "better" assertion but the quoted text seems taken out context.
@Solomon, rereading your post, when taken in context I am thinking "more robust in terms of security options" might have been more the intent of the comment but Jeff won't let anyone off the hook that easily. Can you clarify, "better" how?

Orlando, your respectful tone and level-headed attempt at fairness is making it hard for me to simply ignore Jeff, but I will forgive you, this time 😛

By "better" I mean: in every conceivable way such that they should probably get rid of those other features altogether, and it would probably be best to just implement all of the built-in functions via SQLCLR.

_{Now be vewy, vewy qwiet, and let's see if he takes the bait.}

But seriously, I meant at the very least more robust security handling, because that was the context of that conversation (as you pointed out). But even beyond that, there are several advantages it has over those other features, most (or maybe all?) of which I enumerated and explained in Stairway to SQLCLR Level 1: What is SQLCLR?[/url]. However, I guess I was being a little unfair to xp_cmdshell since there are times when it is the better choice. So that should move to the "sometimes" category.

I hope that adds some clarity. Take care, Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Orlando Colamatteo (5/28/2015)
Jeff Moden (5/28/2015)
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Extremely personal and grossly incorrect opinion noted. 😉
@jeff, "better" is relative and there is clearly a preference, but "grossly incorrect" also reads as an opinion given its subjectivity. Clearly you disagree with the "better" assertion but the quoted text seems taken out context.
@Solomon, rereading your post, when taken in context I am thinking "more robust in terms of security options" might have been more the intent of the comment but Jeff won't let anyone off the hook that easily. Canbyou clarify, "better" how?

Solomon should actually know better than to say things like he's said above because he took part in a rather lengthy thread that Solomon concerning performance where SQLCLR was put up against some SQL Server functionality and got beaten quite badly. Of course, there are other places where SQLCLR will beat SQL Server equally as badly. I'm not sour on SQLCLR by any means but I do think that people go a bit overboard with it simply because they don't know how to do certain things in T-SQL especially if the use of SQLCLR also comes at the price of performance. I'm also really tired of people that suggest that SQLCLR is "far better than alternatives" whether they be xp_CmdShell, extended stored procedures, etc, etc, because it's not a panacea. "It Depends". It's like the people that claim that the only way to do certain things is through SSIS and then they call a dozen stored procedures because SSIS can't do what needs to be done.

Nothing in this business is a panacea and when people like Solomon say something is "far better", they'd better be ready to prove it. He actually can't though. That thread I was talking about is proof of that and he really should know better than to make such broad and unsupportable claims.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Solomon Rutzky (5/28/2015)
_{Now be vewy, vewy qwiet, and let's see if he takes the bait.}
I hope that adds some clarity. Take care, Solomon..

More than you know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 6

Jeff Moden (5/28/2015)
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Solomon should actually know better than to say things like he's said above because he took part in a rather lengthy thread that Solomon concerning performance where SQLCLR was put up against some SQL Server functionality and got beaten quite badly. Of course, there are other places where SQLCLR will beat SQL Server equally as badly. I'm not sour on SQLCLR by any means but I do think that people go a bit overboard with it simply because they don't know how to do certain things in T-SQL especially if the use of SQLCLR also comes at the price of performance. I'm also really tired of people that suggest that SQLCLR is "far better than alternatives" whether they be xp_CmdShell, extended stored procedures, etc, etc, because it's not a panacea. "It Depends". It's like the people that claim that the only way to do certain things is through SSIS and then they call a dozen stored procedures because SSIS can't do what needs to be done.
Nothing in this business is a panacea and when people like Solomon say something is "far better", they'd better be ready to prove it. He actually can't though. That thread I was talking about is proof of that and he really should know better than to make such broad and unsupportable claims.

Jeff, I think you are reading something into what I said that just isn't there. I neither said, nor implied, that SQLCLR was pancetta (it's not even kosher).

I've been involved in several threads related to SQLCLR vs "other stuff" and I have made it quite clear that, as much as I advocate SQLCLR, I even more so advocate using the right tool for the job. And I wasn't merely "involved in a thread" related to SQLCLR vs T-SQL performance, I published a study on the topic, CLR Performance Testing[/url], which a) shows T-SQL more often than not being A LOT faster than SQLCLR (inline, not UDFs), and b) concludes with "keep in mind that CLR is not always the best choice". And be sure to check out comment #3.

And yes, I did not go into explicit detail here regarding all of the qualifications of my "far better than" statement. But I did say "sometimes" better than OPENROWSET / OPENQUERY, and stated in my reply to Orlando that I had mis-stated xp_cmdshell and that it should also be in the "sometimes" category (I will go back and fix my original comment about that). Regarding the ways in which SQLCLR is better than some of those alternatives, I documented most of it here: Stairway to SQLCLR Level 1: What is SQLCLR?[/url]. And, I don't see a problem with saying that SQLCLR is "far better" than Extended Stored Procedures since, if nothing else, they have been deprecated since SQL Server 2005.

So, I agree that nothing (or almost nothing) in this business is Pangea (that is just silly and nonsensical). And again, I do not believe that I said or implied that SQLCLR was a magical, solves-all-problems, way to go. In fact, I agree with exploring the use of File Stream and File Table as they might be far better depending on the situation (though I still don't like the "OPENROWSET(BULK" method for this).

So hopefully that helps clear things up.

Take care,

Solomon..

P.S. As for that "let's see if he takes the bait" thing, it was really late and I was being silly.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

I was just taking exception to the "Far better than" statement. Coming from such an intelligent and well known person, people might actually believe that rather than "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)