Re: MS SQL Server vs Oracle, MySQL or MongoDB

From: Jeremiah Peschka <jeremiah_at_brentozar.com>
Date: Tue, 2 Sep 2014 10:48:35 -0700
Message-ID: <CAAP1c+kEVwTP9=W74aLU7XFgH4C5-th6=R9gPaT3HeuRzarjqw_at_mail.gmail.com>

One downside of the forced parameterization setting is that it will prevent filtered indexes from being used. It's a pretty big hammer, but when a vendor won't change their code...

In general - SQL Server does a great job at 95-99% of general purpose cases. It's not going to give you performance out to the edge cases like you can with Oracle, but for most applications, it's a good enough database engine that solves many database problems really well.

---
Jeremiah Peschka - Managing Director, Brent Ozar Unlimited
MCSE: Data Platform, MVP
Cloudera Certified Developer for Apache Hadoop


On Tue, Sep 2, 2014 at 10:29 AM, Powell, Mark <mark.powell2_at_hp.com> wrote:



>

>

> SQL Server wants you to use bind variables and in fact will automatically

> convert simple SQL with constants into identical SQL statements.  There is

> a database level option similar in effect to the cursor_sharing database

> parameter:

>

>

>

> First point reference

>

>

> http://social.technet.microsoft.com/Forums/sqlserver/en-US/00d9f804-afe0-4b64-9773-40374e5b38c4/bind-variables-in-sql-server?forum=sqldatabaseengine

>

> Second point reference

>

> See Parameterization (2008 R2 version)

>

> http://msdn.microsoft.com/en-us/library/bb522682.aspx

>

>

>

>

>

>

>

> *From:* oracle-l-bounce_at_freelists.org [mailto:

> oracle-l-bounce_at_freelists.org] *On Behalf Of *Hans Forbrich

> *Sent:* Monday, September 01, 2014 2:08 PM

> *To:* Jeremiah Peschka

> *Cc:* Yong Huang; ORACLE-L

>

> *Subject:* Re: MS SQL Server vs Oracle, MySQL or MongoDB

>

>

>

> Thanks for that update.  I admit my SQL Server information is a decade

> old.

>

> The SQL Server expert who advised me back then insisted that SQL Server

> does not need BIND Variables or equivalent - and the best way to handle

> things is to build the SQL statement each time by concatenating statement

> segments - because the engine re-parses each and every time.

>

> /Hans

>

> On 01/09/2014 7:20 AM, Jeremiah Peschka wrote:

>

>  As an aside, SQL Server uses a similar parsing concept to Oracle.

> Queries are compiled as infrequently as possible and cached until something

> triggers a plan recompile or until the plan is forced out of memory. The

> SQL Server optimizer does a good enough job, and employs some relatively

> good algorithms, to make sure it doesn't spend too long compiling a single

> statement. The overarching idea is that it's better to run  a lot of "good

> enough" plans than a few perfect plans. It's becoming rarer, with newer

> optimizer versions, to see compilation time outs, but they do still happen.

>

> ---

> sent from a tiny portion of the hive mind...

> in this case, a phone

>

> On Aug 30, 2014 6:59 PM, "Hans Forbrich" <fuzzy.graybeard_at_gmail.com>

> wrote:

>

> "but irrelevant here."

>

> You disassociate the transaction engine, and therefore the way the RDBMS

> is designed to behave, from te individual statement.  In my opinion, that

> is a fundamental flaw in the discussion.

>

> One basic assumption in Oracle is that a sequence of statements will be

> performed many times (transactional) and therefore it is important to parse

> ONCE and reuse that repeatedly at a minimal cost for each successive time.

>

> Therefore, your comparison of SQL Server ('good enough parse' each time)

> vs Oracle (optimal parse as infrequently as possible) is irrelevant if the

> developer actually understands that.  If they do not, and they incur a new

> parse for each occurrence, then it is a sign that the developer has not

> bothered to learn the engine, and is deliberately or inadvertently

> sabotaging the way the engine can perform.  And THAT is based on the

> transactional engine.

>

> Another assumption, in reporting/BI, is that the cost (time) of parsing is

> irrelevant when looking at the overall operational duration.   Therefore,

> spending additional effort during the parsing, to get an optimal plan for

> the technology involved (different plans for Exadata vs home grown) is

> desirable.

>

> /Hans

>

> On 30/08/2014 1:22 PM, Yong Huang wrote:

>

>  Hans,

>

>

>

> Any pointer would be appreciated. Note that I'm talking about parsing

> overhead in Oracle vs. SQL Server. I don't see the connection to

> transactions or resource management in general, which would be an

> interesting topic but irrelevant here.

>

>

>

> Yong Huang

>

>

>

> --------- original message ---------

>

>

>

> On 29/08/2014 9:21 AM, Yong Huang (Redacted sender yong321_at_yahoo.com for

> DMARC) wrote:

> > Reading Jonathan's blogs on SQL Server reminds me of Laimutis's old

> > question: Why is SQL statement parsing a big issue to Oracle but not to

> > SQL Server?

> >

> http://www.freelists.org/post/oracle-l/Any-reason-not-to-have-logic-in-the-db,17

> >

> > That's a great question and I'd love to hear some comments.

> That question has been around for several decades and has created some

> very amusing and heated flame wars, especially in CDOS.

>

> But it does tie to 'what is a transaction' and how the different engines

> handle transactions as well as 'what is resource management' and how do

> the different engines manage resources.

>

>

>

>

>

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 02 2014 - 19:48:35 CEST