Microsoft Will Support Python In SQL Server 2017 (infoworld.com)

There was a surprise in the latest Community Technology Preview release of SQL Server 2017. An anonymous reader quotes InfoWorld: Python can now be used within SQL Server to perform analytics, run machine learning models, or handle most any kind of data-powered work. This integration isn't limited to enterprise editions of SQL Server 2017, either -- it'll also be available in the free-to-use Express edition... Microsoft has also made it possible to embed Python code directly in SQL Server databases by including the code as a T-SQL stored procedure. This allows Python code to be deployed in production along with the data it'll be processing. These behaviors, and the RevoScalePy package, are essentially Python versions of features Microsoft built for SQL Server back when it integrated the R language into the database...

An existing Python installation isn't required. During the setup process, SQL Server 2017 can pull down and install its own edition of CPython 3.5, the stock Python interpreter available from the Python.org website. Users can install their own Python packages as well or use Cython to generate C code from Python modules for additional speed.
Except it's not yet available for Linux users, according to the article. "Microsoft has previously announced SQL Server would be available for Linux, but right now, only the Windows version of SQL Server 2017 supports Python."

Comments

Always catching up to PostgreSQL!

[Anonymous Coward]

PostgreSQL has had Python support for years. It also has R support.

But to be fair to SQL Server, it is a very good RDBMS. Both SQL Server and PostgreSQL make MySQL look really, really, really bad. Even SQLite is making MySQL look pathetic these days!

Re:Always catching up to PostgreSQL!

[Hognoxious]

Is it webscale, or does it use joins?

Re:Always catching up to PostgreSQL!

[tomhath]

Is it webscale, or does it use joins?

Yes to both.

Re:Always catching up to PostgreSQL!

[Anonymous Coward]

How so? My company (a big one) is moving away from Oracle and SQLServer to Postgres.

What's the problem?

Re:Always catching up to PostgreSQL!

[bhcompy]

They're all still catching up to SQLAnywhere

Re: Always catching up to PostgreSQL!

[cyber-vandal]

I think it has sharding

Meh.

[Anonymous Coward]

PostgreSQL can do (server-side) python. Also tcl, perl, lua, javascript (V8), shell, R. Perhaps I've forgotten some.

Re: Meh.

[PJ6]

You think a db can serve just one app, apparently. My current db serves hundreds, no way they can all be installed there. Another serves the corporate dwh. Installing apps on that one will make me do horrible things to you. Don't make me, bro!

Where I come from, having many applications accessing the same database directly is a disaster for maintainability. Not that it's good for everything, but 3-tier is about more than just security.

The orgs I work for have rather higher security requirements than garden variety stuff so I doubt I'd ever be able to put the service layer on the same box as a database, but for smaller, purely internal applications I totally would.

Anyway my point is, aside from some very specific things (like doing nested set for hierarchical data) why use a 3GL to put non-relational logic in SQL statements at all, when you're already supposed to have a layer on top of that where all your business logic and units tests are, in a language already suited to it?

I know. Unit tests, what are those.

a little late to the party

[ooloorie]

You've been able to use Python for a while in Postgres, MySQL, SQLite, and even DB2.

I can't quite figure out why anybody would want to use Microsoft SQL Server.

Re:a little late to the party

[DogDude]

I can't quite figure out why anybody would want to use Microsoft SQL Server.

Easy to use. Works well. Tons of features. Free to a point. After that, inexpensive.

Re:a little late to the party

[tomhath]

As opposed to Postgresql - which is easy to use, works well, has tons of features, and is free in every sense of the word.

Although I will concede that if you're already locked into being a MS shop and use .net for your applications then SqlServer might make sense.

Re:a little late to the party

[roninmagus]

There are many, many reasons to bash Microsoft. MS SQL Server is not one of them.

Re:a little late to the party

[DogDude]

No .Net applications, but the cost is negligible. The time it would take to learn and migrate to a new system would quickly eat up any cost savings. https://www.microsoftstore.com...

Re:a little late to the party

[Anonymous Coward]

Being able to use SQL Server Management Studio makes up for any cost savings that PostgreSQL might deliver.

SSMS makes it trivial to manage and develop for SQL Server, and it makes the admins and devs extremely efficient.

There aren't any development tools for PostgreSQL that come close to being as capable and efficient as SSMS is.

pgAdmin is really half arsed compared to SSMS, in my experience.

pgAdmin III was a shitty wxWidgets-based app. Even something as simple as copy and pasting was all buggered the last time I had to use it, about a year ago.

pgAdmin 4 is even worse, basically being a web app that they've wrapped and bundled with a browser. Its UI is inconsistent, it's slow, and it I ran into all kinds of bugs when I last had to use it. I wanted to report the bugs, but their shitty bug tracking system forces you to sign up and log in before you can even see a list of bugs! No thanks, I don't have time to waste with idiocy like that.

There are some third party tools that support PostgreSQL, but they're usually quite costly, and none of them are as good as SSMS is, either.

It does no good to save a relatively small amount of money by using PostgreSQL, only to have much-costlier admins and devs slowed down thanks to PostgreSQL's terrible management and development tooling.

Re:a little late to the party

[djbckr]

...but the cost is negligible.

3 grand for 10 seats? That's negligible? PostgreSQL is $0 for unlimited seats.

Re:a little late to the party

[Anonymous Coward]

Could we use the command line? Of course. Would it be sensible for us to use such a limited interface? Absolutely not!

Maybe you work with small, 6-table MySQL DBs where your most complex query is "select * from wp_posts where id = 5;".

The rest of us are working in the real world.

We're working with multi-terabyte or even multi-petabyte DBs with tens of thousands of tables. We're writing complex queries that are thousands of lines long.

We want proper syntax highlighting and autocompletion when editing SQL queries because those things make us a lot more efficient.

We want to be able to incrementally work on our complex queries without having to retype them into a command line client, and without having to drop them into a file and run it through the command line client again and again, and without having to open several different terminal windows/tabs just to do this.

We want to be able to view the schemata of five or six tables at once while we're writing our queries.

What you're proposing is that we only use manual hammers to build a house, while what we're actually trying to do is build an entire district of skyscrapers using modern tools.

Maybe efficiency doesn't matter to a hobbyist like you. But efficiency matters a great deal to those of us professionals who don't have time to waste with shitty tooling. We need the best, even if that means paying a little bit of money to get it. Our time isn't free, so we don't use shitty tools just to save a couple of dollars, especially when our lost time costs way more than that.

SSMS gives us the tooling we need. PostgreSQL or MySQL's shitty command line clients don't.

Re:a little late to the party

[jma05]

Postgres, since a long time, like SQL Server now, allows *embedding* Python in the database run stored procedures.

The MySQL and DB2 links you provided are not the same. They are simply about calling stored procedures from Python clients. Pretty much every relational database and programing language can do the latter.

Embedding within the database, on the hand, is a more exotic and a very useful feature.

Re:a little late to the party

[tomhath]

The time it would take to learn and migrate to a new system would quickly eat up any cost savings

Yes, that's what I said: if you're already locked in to MS and have already paid for the licenses you may as well stay locked in. I developed on SQLServer for several years and liked it. But if I was starting a new project today I'd definitely go with Postgresql.

Re:a little late to the party

[laffer1]

Our solution to this problem was to get a Jetbrains license for their suite. We already were buying Intellij (Java/web) and Resharper for .NET projects and this gave us data grip. While data grip is a relatively new product, it does all the basics with a much better UI, syntax highlighting and supports all of the databases we use including PostgreSQL, SQL Server, MySQL and Oracle. We now have one tool for all the databases we use instead of multiple. In our case, we have one enterprise app that uses SQL Server, we're phasing out Oracle and it's only used for our intranet at this point, we have one mysql database for a wordpress site and that's going away. All new projects are in PostgreSQL. Major cost savings.

Re:a little late to the party

[WaffleMonster]

Embedding within the database, on the hand, is a more exotic and a very useful feature.

For licensing and vendor lock-in.

Re:a little late to the party

[ooloorie]

I didn't bash Microsoft SQL Server. But good relational databases are a dime a dozen; I can't even imagine any features Microsoft SQL Server could have to make me want to go through the trouble of switching and take the risk inherent in using a Microsoft-proprietary product.

Re:a little late to the party

[Kjella]

Easy to use. Works well. Tons of features. Free to a point. After that, inexpensive.

After that, not so inexpensive... $14k/core for the Enterprise edition at "no level" list price is pretty harsh. You could build a pretty sweet database server for the licensing money. That said, for an organization that doesn't have any OSS culture and thus doesn't understand anything that doesn't answer an RFQ it's okay. If we somehow managed to get approval for a PostgreSQL server with no vendor backing us up I fear that some sales droid would convince some higher-ups somewhere to go Oracle, DB2, SAP/SAS or Teradata.

Re:a little late to the party

[jma05]

> For licensing and vendor lock-in.

How exactly? That makes no sense.
They embedded an open source language. Previously, the extensions were via CLR.
If you mean it is non-standard SQL, nearly every major database today supports non-standard extensions - both proprietary and open source.

Re:a little late to the party

[countach74]

Postgres has always supported transactions, as far as I'm aware. And in fact, what you can do with transactions is pretty incredible and beyond what most databases offer. For instance, you can actually put most database migrations in a transaction. Something not go as expected? Roll back and your schema's all as it was. I'm wondering if perhaps you are thinking of MySQL?

Re:a little late to the party

[Anonymous Coward]

Could we use the command line? Of course. Would it be sensible for us to use such a limited interface? Absolutely not!

Maybe you work with small, 6-table MySQL DBs where your most complex query is "select * from wp_posts where id = 5;".

Yeah, great deduction Sherlock. If someone is advocating the command line, you immediately think they're using WordPress since those technologies go hand in hand with one another. About par for the course for someone who relies on SSMS.

We want proper syntax highlighting and autocompletion when editing SQL queries because those things make us a lot more efficient.

We want to be able to incrementally work on our complex queries without having to retype them into a command line client, and without having to drop them into a file and run it through the command line client again and again, and without having to open several different terminal windows/tabs just to do this.

Autocomplete, hmm. Do you even know what readline is? You know you can edit your queries in vim, too, right? Why would you need to open several terminal windows? You're lost! You give the impression that if you were in a bash shell, you would be completely out of your depth.

We want to be able to view the schemata of five or six tables at once while we're writing our queries.

What you're proposing is that we only use manual hammers to build a house, while what we're actually trying to do is build an entire district of skyscrapers using modern tools.

Maybe efficiency doesn't matter to a hobbyist like you. But efficiency matters a great deal to those of us professionals who don't have time to waste with shitty tooling. We need the best, even if that means paying a little bit of money to get it. Our time isn't free, so we don't use shitty tools just to save a couple of dollars, especially when our lost time costs way more than that.

SSMS gives us the tooling we need. PostgreSQL or MySQL's shitty command line clients don't.

You sound like a real Microsoft MVP. You know, there are some of us out there that cut our teeth on Unix and Linux and the command line. We read the man pages and even the source code. You wouldn't know anything about that.

Re:a little late to the party

[WaffleMonster]

How exactly? That makes no sense.

The interface is not standardized. Same code running in a real application tier has no such dependencies on database specific language bindings to operate.

Re: a little late to the party

[cyber-vandal]

I've used both and I'm far more productive in SSMS than I ever would be in vim because unlike vim SSMS is designed for SQL management and makes life way easier. The whole point of computers is to make things easier and trying to manage multiple databases and queries using bash and vim would be painful beyond belief. You can work in such a wildly inefficient manner if your pathetically fragile ego requires that to make you feel superior but the sensible people will use tools (including the command line) that make them more efficient and run rings around you.

Re:a little late to the party

[DogDude]

If you've got 10 people interacting with the database at a time, then yes, $3K is negligible.

Re:a little late to the party

[roskakori]

I can't quite figure out why anybody would want to use Microsoft SQL Server.

Corporate policy. It does make sense if large parts of your IT infrastructure already use MS SQL server, especially applications from external vendors. Adding another database increases costs and complexity for maintenance and support.

And it's inexpensive enough for the little money saved by using the free PostgreSQL not to make any noticeable difference. SQLite is very nice for certain applications but does not scale well (by design). MySQL still lacks support for many SQL features present even in SQLite and is bearable only through an ORM. I hear the finally thinking about adding support for common table expressions.

Re:a little late to the party

[Attila Dimedici]

Because lots of commercial database applications(ERPs, CRMs, MRPs, etc) are built on top of it. I have never worked for a company which builds those types of applications, so I do not know why they chose MS SQL (I have some educated guesses, but I do not know).

Re:a little late to the party

[jma05]

There is a good reason to have that code at the database tier and not in the "real application tier" aka client client tier. And this reason is partly the same reason as why we do stored procedures. We want the data to be resolved and reduced before it leaves the database tier, not sorted out in the client tier. This also allows for centralizing business logic rather than have it spread out among various client implementations. And the non-standardness is no different than doing non-standard stuff that we already do in stored procedures.

Re:a little late to the party

[Xest]

Probably because it has deep integration with windows networks and security that most businesses run on, coupled with the fact it's a proven reliable, fast, and highly scalable RDBMS. MySQL for example just isn't reliable, last time I ran it it would corrupt the data store on disk and you had to run a fix tool provided with MySQL to get the server to even start and load your database again.

Beyond that though it has great surrounding services for ETL, analysis, and reporting, coupled with clean and easy integration into the .NET ecosystem (which, as the other article posted recently shows is one of the most prominent languages for financial/enterprise use during the working day). There's also high quality 1st party support available with defined SLAs.

I think the mistake you're making is that you're assuming that because it's not right for you, it's not right for anyone. But you're not everyone, some companies have the cash to blow on software that's proven, and integrates fantastically with their environment. If your budget is zero or near enough then fine, of course MS SQL server isn't for you, but not everyone is doing basic zero budget stuff. There are big businesses out there that need something enterprise grade, and that typically means Oracle, MS SQL.

Re:a little late to the party

[ooloorie]

I think the mistake you're making is that you're assuming that because it's not right for you, it's not right for anyone.

I just asked a question.

Probably because it has deep integration with windows networks and security that most businesses run on

That's lock-in, not a technical advantage, as are most of the other things you list. Those are compelling reasons if you have already invested heavily in Microsoft. But they are not a good reason to, say, pick up Microsoft SQL Server and install it on Linux.

There are big businesses out there that need something enterprise grade, and that typically means Oracle, MS SQL.

Well, and there are several enterprise-grade relational databases that don't come from Microsoft and don't come with Microsoft's strings attached: Oracle, DB2, and Spanner for example.

Re:a little late to the party

[Xest]

"That's lock-in, not a technical advantage, as are most of the other things you list."

Call it what you want, there's real practical benefit in being able to have centralised security configuration. Knowing that when you lock out a user account on the domain, that they also can no longer log into every database server and so on has massive practical benefit.

"Well, and there are several enterprise-grade relational databases that don't come from Microsoft and don't come with Microsoft's strings attached: Oracle, DB2, and Spanner for example."

I already mentioned Oracle, and sure, DB2, though it's a small player. Spanner is neither a true RDBMS, nor used widely in the enterprise.

I get it, you hate Microsoft, that's fine. But don't pretend MS SQL server isn't widely used, and it's widely used for good reason - it's a good product.

Besides, even your argument about vendor lock-in makes no sense. SQL server for Linux is open source, the whole point being that it's easy to migrate to.

The reality is most companies would rather pay for something solid and reliable like MS SQL server that integrates well into the rest of their ecosystem, than have something free but shit like MySQL. As I said before, you may have your own reasons not to want MS SQL, or for just hating Microsoft, fine, but don't expect everyone else to agree with you when some of us actually have a wide range of RDBMS experience and aren't just pulling nonsensical theories about a particular product out of our arses as you clearly are.

Re:a little late to the party

[ooloorie]

I get it, you hate Microsoft, that's fine. But don't pretend MS SQL server isn't widely used, and it's widely used for good reason - it's a good product.

Geez, man, get a hold of yourself. I didn't "pretend" anything, nor do I "hate Microsoft". For example, I think the Linux community should have adopted C# and Mono widely and have argued extensively for that here.

SQL server for Linux is open source,

Really? Where is the source? Where is the open source developer community? As far as I can tell, there are just binary package installations.

the whole point being that it's easy to migrate to. ... The reality is most companies would rather pay for something solid and reliable like MS SQL server

Where are the benchmarks that are showing that MS SQL Server is actually more reliable, faster, etc. than other servers?

As far as I can tell, MS SQL Server on Linux only exists as binary packages from an unofficial repository, with no consistent distribution support, almost no user community, few language drivers, and little tool support, and those limitations carry over into any kind of cloud usage of MS SQL Server. In terms of functionality, it seems to be behind FOSS databases in terms of available plug-ins, data types, and distributed features.

So, the only substantive argument so far I have heard you make for MS SQL Server is that it comes from Microsoft and interoperates well with Microsoft products, something so totally obvious that I thought it should have been clear that I wasn't asking about that.

Again, I have an open mind here. If MS SQL Server is indeed a high performance, open source relational database server with useful and advanced features, by all means, I'd love to know about it and see some convincing links, starting with a link to the source code repository and some comparative benchmarks. Right now, all I hear from you is name calling and superstitions.

Re:a little late to the party

[DogDude]

if you're already locked in to MS and have already paid for the licenses you may as well stay locked in.

What this "lock in" you're talking about? SQL Server can use ANSI standard SQL, and can export schema and data in simple flat text files.

Re:a little late to the party

[Xest]

You're making it pretty clear by the fact you can't even answer these questions for yourself that you have no idea what the fuck you're talking about.

Even if I do do your research for you wants the point where you're clearly a zealot? The fundamental fact you're making assertions about a peace of software you're demonstrably highlighting you have no idea alone means that any discussion with you is a losing proposition.

If you genuinely had an open mind you wouldn't be calling something you have never used, and have no knowledge of a pointless proposition. The fact you are means any suggestion you're capable of holding an open mind on the topic is already well and truly null and void - an open mind requires you to not jump to conclusions, yet that's the entire premise of your thread here - your conclusions based on zero actual experience of the subject at hand.

If I've learnt anything about Slashdot it's that I have way better ways to spend my time than trying to convince a zealot of the facts, so here's a better idea - fire up Google and go and find the answers to your own question if you care. If you're not going to bother to do that then you merely prove my point - you're not here with an open mind, you're here to spread your zealotry.

Re:a little late to the party

[ooloorie]

You're making it pretty clear by the fact you can't even answer these questions for yourself that you have no idea what the fuck you're talking about.

Absolutely correct. Hence my statement I can't quite figure out why anybody would want to use Microsoft SQL Server.

so here's a better idea - fire up Google and go and find the answers to your own question if you care

I did, before posting even. That's how I included the link I included. I can't find any open source repository or release; it's not in the Ubuntu package manager; there are few language bindings for it in Ubuntu repositories; I couldn't find any benchmarks comparing it with other database systems running on Linux either.

Even if I do do your research for you

So you actually know nothing about MS SQL Server yourself, you just like it because... what?

you're not here with an open mind, you're here to spread your zealotry

Yes, my horrible "zealotry" of asking for someone to actually explain their use of MS SQL Server based on technical reasons.

Re:a little late to the party

[Xest]

"So you actually know nothing about MS SQL Server yourself, you just like it because... what?"

This is precisely the point I'm making - you say you want to learn, but you're not listening. I pointed out that I've worked with many other RDBMS in the past. Oracle is unnecessarily convoluted and proprietary just for the sake of trying to sell specialist training, though it is powerful and performant. MySQL is a joke - the very fact it even has (or had) to be bundled with a tool to fix broken datafiles is in itself farcicle, and as I said, in a production environment I saw it collapse for exactly this reason roughly every 3 - 6 months. I've never used DB2, but I've also never heard of anywhere using it in recent years either and frankly even their 3 case studies on their product site are companies I've never heard of. Maybe it's great, who knows, but it's just not a prominent option anymore, and with reduced prominent comes reduced training availability, reduced available skill sets on the market, reduced peer support when things go wrong.

So again, I like MS SQL server because it's stable unlike MySQL, it's easy to use, diagnose, maintain, and debug unlike Oracle, it offers centralised security configuration, as I've explained already, it integrates excellently not just with .NET but is widely and well supported in just about every language's DB framework out there.

On performance it can easily hold it's own against Oracle and PostgreSQL, I'll admit I have seen faster throughput on MySQL, but let's be clear, that's because MySQL cuts corners, and hence why it suffers from data integrity issues. If I wanted to sacrifice any of the ACID principles then I'd use a data storage system that intentionally does that and is designed for that from the outset, rather than because of failure of implementation - e.g. I'd look at a NoSQL solution if that fit my needs.

We use it for massive, massive databases, probably one of the highest use case scenarios you'll see without getting into Google index or Facebook use scale applications and here it does well too, it scales well, and it's easy to scale. We use it in a scenario where we're bound by contract in terms of response times, where any loss of reliability for even a short period can have literally millions of pounds of impact. Microsoft's support has been great - even on their existing closed source version they listen and make product changes based on our feedback, and that of other customers.

Others here have made the point that there's plenty of reasons to hate Microsoft, but SQL server isn't one of them, and that's absolutely true. Microsoft do a lot wrong, but products like Visual Studio, languages like C#, and products like SQL server are prime examples of things they absolutely do right.

I would suggest, rather than assuming you know it all and making sweeping comments about products you do not understand that you turn your thinking around and instead consider that something might be okay until you have reason to think otherwise. Because once again, by jumping to the conclusion that something is bad just because Microsoft, then hating people and childishly making them your foe because they explained why you're wrong, you're making a fool of yourself.

I could jump to conclusion too, I could assume that because you have a relatively high UID that you're probably relatively young and utterly naive, thinking you know better than those of us who have some extensive experience in the industry. I'm not going to do that though because I might be wrong - I'm going to give you the benefit of the doubt that you're just stressed, and venting your anger here or some such by trolling regardless of your circumstances, but ultimately it doesn't change the fact that you're wrong, and would do well to listen to all those explaining why if you ever want to get anywhere in this industry. A successful career has no room for zealotry and willful ignorance, you simply evaluate the options and take the best one for your use case, as many companies have found, hence the sales figures, that's quite often MS SQL server.

Re:a little late to the party

[ooloorie]

I would suggest, rather than assuming you know it all and making sweeping comments about products you do not understand

I didn't make "sweeping comments", I asked a question.

you say you want to learn, but you're not listening

I am listening, but you haven't told me anything I didn't already know: MS SQL Server seems to be a reasonably good and stable product on Windows. It doesn't seem to be open source (despite your claims) and only seems to have Linux pre-releases. Saying that it can "easily hold it's own against Oracle and PostgreSQL" means that it isn't substantially better than either even on Linux. So, still no compelling reason to use it, and two very compelling reasons not to use it: incomplete Linux support and lack of source code.

then hating people and childishly making them your foe

I don't "hate you", the "foe" tag is simply my "has nothing substantive to say" marker. It means I'll preferentially skip your postings in the future. In the dim past, one could automatically downrank people like you. I wish Slashdot had killfiles.

Re:a little late to the party

[Xest]

But there we are again - changing the terms of the discussion, your problem with it now is merely that you're complaining it isn't open source (hint: it's in preview still), and that it has incomplete Linux support.

Yet here is your original post where you apparently didn't make a sweeping comment and where you claim you merely asked a question:

"You've been able to use Python for a while in Postgres [postgresql.org], MySQL [mysqltutorial.org], SQLite [python.org], and even DB2 [ibm.com].

I can't quite figure out why anybody would want to use Microsoft SQL Server."

Want to retract that now? It's pretty clear you're backpedalling - I don't care if you want to ignore me, just don't pretend you're here to learn when you're clearly not interested in that, and just end up backpedalling when you get called out. People like me are more than happy to share our experience with such products, but don't waste our time if you're not willing to listen and are actually just busy being a zealot whilst pretending otherwise.

Re:a little late to the party

[ooloorie]

I can't quite figure out why anybody would want to use Microsoft SQL Server.

Want to retract that now?

Absolutely I want to retract it! I now understand that there is still a vocal population if Microsoft zealots out there to whom "integrates with Windows domains" and "has a Windows GUI" are actually desirable features that trump "has predictable long-term pricing and availability" and "doesn't lock you into one vendor".

venting your anger here or some such by trolling [...] A successful career has no room for zealotry and willful ignorance

Good grief, look who's talking.

Re:a little late to the party

[Xest]

Well, thank you for admitting you were wrong at least, I guess.

Shame you've still decided to bask in wilful ignorance though by refusing to listen to a word anyone else with actual experience of multiple products is telling you, and still amazed you think lock-in is even possible in a product that supports standard SQL and doesn't force you into any extensions (which just about all SQL RDBMS have btw) but hey, you don't know much so I shouldn't be too surprised that you're again mouthing off about something you have no clue about in the name of an unfounded anti-MS rant though I guess.

It's a trap!

[kbg]

It's a trap!

Microsoft got one thing right...

[__aaclcg7560]

They're using Python 3 and not Python 2. I switched my codebase to Python 3 and uninstalled Python 2 a few years ago from my Windows PCs.

Re:SQL Server wins at query performance vs. postgr

[Anonymous Coward]

one of the main reasons of this is Postgresql's lack of parallelism at the query level

Nonsense. Shilling is bad enough, but at least get your facts straight.

Just because you can doesn't mean you should

[WaffleMonster]

If python integration is anything like .NET or java language bindings:

1. You won't see any performance benefit vs. shared memory

2. You will get hit with all of the downsides of ignoring separation dogma for temporary expediency.

Re:Just because you can doesn't mean you should

[tomhath]

You will get hit with all of the downsides of ignoring separation dogma

Write your business logic in the language which is most appropriate and run it where it's most convenient. The "separation dogma" was a fad that has passed.

Re:Just because you can doesn't mean you should

[WaffleMonster]

Write your business logic in the language which is most appropriate and run it where it's most convenient. The "separation dogma" was a fad that has passed.

RO0OFL nice. Almost thought you were being serious.

Re:Just because you can doesn't mean you should

[tomhath]

You sound like one of those Java/Hibernate guys: "Why would I write a 20 line function in the database when I can write a few hundred lines of boilerplate, configuration, and interface code that works almost as well?" Do you still use Struts?

Re:Just because you can doesn't mean you should

[MightyMartian]

So let's get this straight. You have databases that optimize SQL queries because of the narrow scope of that language, and somehow you think throwing in random code blocks in some other language where optimization for the data set is just about impossible and you don't think you're going to get serious performance hits?

There are certainly times when stored procedures may be necessary, but I've made it my policy to avoid them wherever possible. Using convenience as an argument for overriding a pretty sound programming philosophy sounds like a recipe for shit performance.

Re:Just because you can doesn't mean you should

[joshuao3]

I always thought that having one long list of stored procedures in a database was kinda silly. It lends itself to coming up with creative naming conventions to keep things organized. I wish that MS would implement namespaces within the stored procedures so that I could organize them in some manner.

Re:Just because you can doesn't mean you should

[tomhath]

somehow you think throwing in random code blocks in some other language where optimization for the data set is just about impossible and you don't think you're going to get serious performance hits?

You're missing the point. Data access uses sql no matter what language your business logic is written in and no matter where the application code runs.

The advantage of having that logic reside inside the DB server versus in a container or separate app server is that you save all the network traffic moving data back and forth; the app code is the same in either place and is just as modular either way. All that changes is where it's deployed.

Re:Just because you can doesn't mean you should

[WaffleMonster]

You sound like one of those Java/Hibernate guys: "Why would I write a 20 line function in the database when I can write a few hundred lines of boilerplate, configuration, and interface code that works almost as well?" Do you still use Struts?

I don't drink Java/struts/hibernate. I have written more extended procedures than I care to admit over the years and today find myself completely unable to retroactively justify any of those decisions.

I understand the appeal of these things I know why people want to use them. I also know from experience in non-trivial environments these tendencies have cost prohibitive consequences.

On the low end you can in get away with virtually every bad practice you want.. it doesn't matter. Sloppiness does not scale and by scale I do not mean the amount of data I mean the complexity of the overall system.

Re:Just because you can doesn't mean you should

[WaffleMonster]

The advantage of having that logic reside inside the DB server versus in a container or separate app server is that you save all the network traffic moving data back and forth

You accomplish this by running your code on the same computer in a separate process using standardized data access APIs.

the app code is the same in either place and is just as modular either way. All that changes is where it's deployed.

I have a bad feeling about this. Someone points out the obvious when application becomes the database you lose the ability for application to leverage multiple databases or connect to different databases or have the ability to isolate application from the database due to application resource constraints.. and your response.. no no it's still the same application it can just as easily connect to those databases and do all the things a separate application can do.

The reality is you don't get to have it both ways. That kind of flexibility only comes by paying the no-shortcuts tax at which point there is zero reason to even think about embedding into the database in the first place.

Re:Just because you can doesn't mean you should

[tomhath]

you lose the ability for application to leverage multiple databases or connect to different databases

Not at all. Foreign tables or db linking is every bit as efficient as your alternatives.

or have the ability to isolate application from the database due to application resource constraints

Possibly, there is no one size fits all. But these days the availability of cores and memory weakens that argument too.

Re:Just because you can doesn't mean you should

[BigZee]

That is an inelegant way of doing it to be honest. Application s/w should be running on different hosts. The database server (or cluster) should be running the database and nothing else. However, to get the benefits of running database code in the right place, being able to run Python inside the database would be the right way to achieve this. Oracle has included a JVM inside the database for many years. It gets the database related code into the right place but you still need to keep the rest of the application (web server, app server, non DB related application code) at arms length (another server). Ultimately being able to use Python is no different to being able to run PL/SQL, JAVA, TransactSQL inside the database.

Re:SQL Server wins at query performance vs. postgr

[laffer1]

Even if this was true, which I doubt as I've seen SQL Server choke on bad locking from poorly written apps, the cost savings from not buying sql server licenses means you get bigger servers or instance types (if aws) versus going with Microsoft. You can throw hardware at the problem.

Re: Because...

[cyber-vandal]

No, not wanting to waste vast sums of money for no business benefit syndrome.

Meh.

[PJ6]

Just put your web service on the same box as the database.

pymssql

[motorsabbath]

Meh. Pymssql works pretty well. Am I missing something?

Yeah right

[tailgunner_050]

Problem I'm having with microsoft these days is that they do things that make you go 'yay that's really good' then you wonder for how long. Its just another carrot on a string to attract foolish developers over only to be forced one day later to have to adapt to some microsoft propriatery way of doing it to sap money out of you.