Performance related queries

Question

Post reply

Performance related queries

karthik M

One Orange Chip

Points: 29139
More actions
November 7, 2007 at 1:18 am

#122194

Hi Experts,
1. Tell me the difference between partioned view and partioned table ?
2. Will a partioned table affect performance ?
3. If cursor usage is mandatory ,which cursor type is best to use ?
4. If cursor usage is mandatory ,which cursor type ( i.e server side or cursor side )is best to use ?
5. Which protocol is best for performance ? why ?
6. How to assign space for tempdb ? is there any calculation ?
7. what is the first step to be followed in tuning a query ? why ?
8. How to clean up the Database on certain interval ?
9. What is the real time usage of Model database ? where exactly it was applying by the programmer ?
10. Will trigger or constraints improve performance or not ? if yes which will get priority ?
karthik

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Ok there... it's time for YOU to hit the books Mr. Senior Software Engineer with an MCA... look in Books Online...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

This smells of homework, or interview questions. Is that the case?

Most of tthese are easily answered with Books online or a quick google search

karthikeyan (11/7/2007)
Hi Experts,
1. Tell me the difference between partioned view and partioned table ?

http://msdn2.microsoft.com/en-us/library/aa933141(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/ms345146.aspx

2. Will a partioned table affect performance ?

Maybe, it depends what you're doing with it.

3. If cursor usage is mandatory ,which cursor type is best to use ?

It depends. Why is cursor usage mandatory?

4. If cursor usage is mandatory ,which cursor type ( i.e server side or cursor side )is best to use ?

It depends. Why is cursor usage mandatory?

5. Which protocol is best for performance ? why ?

http://msdn2.microsoft.com/en-us/library/ms187892.aspx

6. How to assign space for tempdb ? is there any calculation ?

http://msdn2.microsoft.com/en-us/library/ms175527.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

7. what is the first step to be followed in tuning a query ? why ?

Depends on the query and why its slow. Generally it's to see why the query's slow.

8. How to clean up the Database on certain interval ?

Define clean up.

9. What is the real time usage of Model database ? where exactly it was applying by the programmer ?

http://msdn2.microsoft.com/en-us/library/ms186388.aspx

10. Will trigger or constraints improve performance or not ? if yes which will get priority ?

In general, no and maybe, in that order.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Thanks Gila and Jeff:)

Basically why i am asking some known and some available in books online is, They will explain with some good example, I agreed , but If i ask to your people , i will get some good real time answers and example. Because your people experience will speak best on the above questions than books online.

As i am in growing stage your well experienced answers will help me to put very strong basement and your examples will lead me to reach the top of the mountain very easily.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You'll probably get better and more accurate advice if you read up on the concepts in Books Online and on the MSDN pages, then post here for specifics that aren't covered or that you're still unsure of.

Asking for stuff that's easily available in the manual makes it look like you're been lazy, and will get you answers like "Read Books Online"

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Yes I agreed your point ,As you said i have the habit to read articles, documents , NewLetters and books online. But i feel your ideas, explanations and examples will be very useful for me.

karthik