Hists for the Queries

Hi Gurus,

I am migrating from Oracle to Sql Server.I would like to know the best query hints
available in the sql server.To improve the peroformance of a query which is taking
about 10 minutes.Not All some best ones only please.


Thanks,



raj

1. Design the database properly in the first place.
2. Use INNER JOIN whenever possible.
3. Don't use OR or IN if it can be avoided.
4. Never use cursors or loops when it can be set based. This is a thought process.
5. Never give more information then you need to.

Post the query. We'll see what we can do with it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Use proper indexes.

[Derrick, I know that indexes are part of database design, but I think it is worth to stress the significance of using them.]

--

Marek 'chopeen' Grzenkowicz

Rediscover the web
http://www.mozilla.org/firefox/

I read somewhere that you have to tell oracle what indices to use cause otherwise it will just do table scans (no flame intended, I think krajdba thinks he always must give directions to the database engine).

SQL server has a query optimizer that it runs before execution of the query which chooses the best strategy to execute it. If there are the right indices defined and a table scan is more work it will use them.

You can still use query hints if you think SQL server makes the wrong decissions but this happens rarely.

Do you know about the graphical execution plan? If not give BOL (books online, the manual that comes with SQLS) a visit.

Short hint list for the beginning: http://www.codeproject.com/cs/database/sqldodont.asp?df=100&forumid=13472&exp=0&select=894369

I can't stop wondering what we are talking about here -- query hints (join hints, query hints, and table hints) or general hints concerning working with SQL Server? [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br /><br />Marek 'chopeen' Grzenkowicz<br /><br /><i>Rediscover the web<br /<a target="_blank" href=http://www.mozilla.org/firefox/>http://www.mozilla.org/firefox/</a></i>

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I can't stop wondering what we are talking about here -- query hints (join hints, query hints, and table hints) or general hints concerning working with SQL Server?<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Uh, you are right, I lost the meaning of original question reading responses especially Derrick's [<img src='/community/emoticons/emotion-11.gif' alt='8)' />] Anyway, I think for someone coming from Oracle environment everything mentioned may be helpful.

To make the situation even more complicated, check the subject of this discussion.

--

Marek 'chopeen' Grzenkowicz

Rediscover the web
http://www.mozilla.org/firefox/

Hi Guys,

(Reading the responses after a long time)

You all have made a mess of my Original question.I guess you Guys should go out and relax or
watch a movie.

I consider Satya's response as a Good one.

Anyways.

Thanks for All

Raj

raj

lol <img src='/community/emoticons/emotion-1.gif' alt='' /> Is it true than that you have to use query hints in Oracle if you want a certain index to be used?

What I read in a book (inside sql server, bit outdated) is that Oracle doesn't use indices at all unless you said so. There is no part in the query optimization that does investigate wath index is most appropriate.
I don't know what version of Oracle it referes to but the book is from 2001.

No it is not the case, at least on Oracle 8 (I think) and 9 (for sure).

i vaguely recall that prior to 8i or there abouts, Oracle defaults to rule based optimization, hence perhaps the greater need for index hints,
i also recall that there were issues with the early versions of cost based opt (CBO), but these were mostly resolved by 8i

i would not think that hints are as necessary using CBO,
the more current versions of Oracle should be CBO,

Joe - you are right. CBO is now a default option (especially in 10g) and RBO is "depreciated technology" on its way out

Simas