Cyberiafreak

"Fortunate are those who take the first steps.” ― Paulo Coelho

Few Peformance Queries – Sql 2000

5 Performance Queries – SQL SERVER 2000

1. Use "TRUNCATE TABLE  <tname>” instead of “ DELETE from <tname>”
If you want to delete all rows in a table use q2 for better performance –

q1 :delete from <tname>
q2 :truncate table <tname>

TRUNCATE TABLE – frees up space of tables data,indexes etc without log row deletes.

2. Never use [sp_ ] as prefix for stored proc’s – its reserved prefix. SQL server searches for a stored proc with [sp_ ] prefix in the system procedures first,and after that looks for them in client procs. 

3. There are thousands of examples for count(*),but q2 is faster to achieve the same count operation. here is my query tested in my PC…

    q1:select count(*) from pcl
    q2:select rows from sysindexes where id= OBJECT_ID(‘pcl’) and indid <2

4. Add "SET NOCOUNT ON ” statement in stored proc to reduce network traffic.
 
Tested this queries in my QA…it will be helpful,who are all talking with SQL daily in poor bandwidh. I just added this queries in my Query files already saved.

5. BETWEEN instead IN,
 Example Q1:
  SELECT clt_id
   FROM pcl
    WHERE clt_id BETWEEN 1 AND 10

 Example Q2:

  SELECT clt_id
   FROM pcl
    WHERE clt_id IN (1, 2, 3, 4,5,6,7,8,9,10) 

 Q1 is a best query when you think performance wise

Comment,if any one knows more than this…………………

Mahe~
Src: Read somewhere yesterday in book

Advertisements

April 15, 2005 - Posted by | SQL SERVER - My Own code

1 Comment »

  1. Hi Mahesh, Well and wishes to you. thank u for sending "Recent comment" in my space.

    Like

    Comment by Eppy | April 18, 2005 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: