RE: MS SQL Server vs Oracle, MySQL or MongoDB

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