Issue in Processing Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Issue in Processing Time

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-27 : 15:13:47

I executed 2 stored procedures in 2 different databases

Sp-1 in Database A

Sp-1 inserts records into a emp table
1. 980 records are there for processing and out of 980 only 700 meets the criteria for insertion and it took 5 mints to insert since the table from which the selection is done has 9782719 records

Sp-2 in Database B

Sp-2 inserts records into a emp table
1. 120 records are there for processing and out of 120 only 76 meets the criteria for insertion and it took 2 mints to insert since the table from which the selection is done has 9782719 records

Why there is this difference in the processing time so much.....especially 76 records took 2 mints where was 700 records took 5-6 mints

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-27 : 15:18:01

Please post the DDL for the tables including indexes and constraints.

Tara Kizer
aka tduggan

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-27 : 17:23:33

Below shown is the query used in both the Sps only difference is 2 different databases and they process different records based on the emp_number in each database whether emp_status='Y'

INSERT INTO tbl_emp_comments
(
ref_id,emp_number,comment_type,
comment_date,comment_system,
comments,created_by,create_date,
updated_by,update_date
)

SELECT
ld.ref_id,ddni.emp_number,ddni.comment_type,
ddni.comment_date,'1' as comment_system,
CASE
WHEN comment_type IN('KTH') THEN ISNULL(ddni.long_comments_line1,'')+ ISNULL(ddni.long_comments_line2,'') + ISNULL(ddni.long_comments_line3,'')
ELSE
ISNULL(ddni.comments_line1,'') + ISNULL(ddni.comments_line2,'') + ISNULL(ddni.comments_line3,'') + ISNULL(ddni.comments_line4,'') + ISNULL(ddni.comments_line5,'')
END as comments,
ddni.created_by,ddni.create_date,
ddni.updated_by,ddni.update_date
FROM
tbl_history_info ddni
INNER JOIN tbl_emp_detail ld ON
ddni.emp_number=ld.emp_number AND
ld.emp_status='Y' AND
ddni.processed='N' AND
ddni.emp_indicator IN ('A','C') AND
ISNULL(ddni.comments_line1,'') NOT LIKE 'EMP-SYS%' AND
ISNULL(ddni.long_comments_line1,'') NOT LIKE 'EMP-SYS%' AND
ddni.comment_date > (
SELECT MAX(comment_date) FROM tbl_emp_comments
WHERE ref_id=ld.ref_id AND (LEN(created_by)=3)
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-27 : 17:28:44

Posting the query is helpful, but we need what was mentioned in my first post. Also, the below should be in a WHERE clause and not in the JOIN condition:


ld.emp_status='Y' AND
ddni.processed='N' AND
ddni.emp_indicator IN ('A','C') AND
ISNULL(ddni.comments_line1,'') NOT LIKE 'EMP-SYS%' AND
ISNULL(ddni.long_comments_line1,'') NOT LIKE 'EMP-SYS%' AND
ddni.comment_date > (
SELECT MAX(comment_date) FROM tbl_emp_comments
WHERE ref_id=ld.ref_id AND (LEN(created_by)=3)
)

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources