Answer / john

PERFORMANCE TUNING:

Shortly : The performance tuning is reduce the load of the
database.

stored procedure:
--create a sample performance tuning procedure

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
BEGIN

--NOCOUNT is use to reduce the load
--if its off the procedure get (full memory) of execution

SET NOCOUNT ON;

IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO


STEPS:
1.Good code ideas
2.Reduces use more query in same procedure
3.use clear structure of code
4.always done stored procedure is good. because,Transact-Sql
get load of execution

Answer / ravi yanamala

1).Check the indexes,table scans and the statics
2)check whether any stored procedures are recompiles excessively
3)check whether SET NOCOUNT is OFF for any stored procedures or triggers
4)check the query whether written poorly r not.check the database normalization as too much normalization effects performance,check the usage of the cursors and temperory tables.

Answer / parmanand

Agree with John, Just to add this is a way to make our query
fast and to reduce the over load on server.

The RECOMPILE option
SET NOCOUNT ON/OFF
Creating Indexes on tables are few points to fine tune our
query.

what is normalization? Explain different levels of normalization? : Sql server database administration

0 Answers  

---

What is the difference between varchar and varchar(max) datatypes?

0 Answers  

---

What is data source in connection string?

0 Answers  

---

Explain the new features of SQL server 2000?

1 Answers  

---

Why use identity in sql server?

0 Answers  

---

How we create SQL Server 2005 Reporting Services ? Give me Sample

0 Answers   HCL,

---

Hi All, I want to display all duplicate records in the table. My query has to fetch all the records which are duplicate(First Name or Last Name). Also I want the ability to also pull names where there might be a middle initial placed in the end of the first name field, (i.e., "Maria Z. " vs. "Maria") as well. Please guide me to find this. Table: ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 4 Shawn Livermore 5 Prem S 6 Jony Hoffman H 7 Zach Modan I need the query to filter......... ID FirstName LastName 1 Zach H Hoffman 2 Zach Hoffman 3 Troy Hoffman 6 Jony Hoffman H 7 Zach Modan I hope this example will give you clear idea..... Thanks in Advance Prem

2 Answers  

---

Explain the functionalities that views support?

0 Answers  

---

Why we use functions in sql server?

0 Answers  

---

could you please reply for these question: 1.About Use Apply? 2.Avoid cursors - When we have a situation that we can not avoid the use of cursor than what is the alternate solution? is there anything we can use instead of cursor to perform the desired task? which optiomize the peroformance too. 3.What is computed columns? Thanks in advance. Regards, Rupesh

3 Answers  

---

What is Self Join?

4 Answers  

---

What is sub query and its properties?

0 Answers  

---