Re: MS SQL Server vs Oracle, MySQL or MongoDB

  • From: Jeremiah Peschka <jeremiah@xxxxxxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Sep 2014 10:48:35 -0700

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@xxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx>
> 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@xxxxxxxxx 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?
> >
> //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.
>
>
>
>
>
>
>

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2014