RE: MS SQL Server vs Oracle, MySQL or MongoDB

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Sep 2014 17:29:42 +0000

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<mailto: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<mailto: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