Performance Query - 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

General SQL Server Forums

New to SQL Server Administration

Performance Query

Author

Topic

dlotus
Starting Member

2 Posts

Posted - 2010-08-04 : 23:36:40

Hi, These days I am working on Sql Server 2000.

I am working on some of scripts. I would like to know about that how I can improve following sql to have fast performance.

INSERT INTO request_att (id,reqest_id,name,value)
SELECT newid(), r.request_id, 'performance','High'
FROM request r
WHERE r.cond1 is not null
AND
r.obsolete_flag =0

This is related to insert some values in a table from other table. There is already index(non-clustered) on request_att.request_id, name and Request.request_id table.
This query will be execute on a PRODUCTION table which consists of more than 10 lakhs records. This will be one time load script.

Solution or suggestion will be appreciate.

Thanks,
dk

dk

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-05 : 08:06:15

how many indexes do you have against this table? how do you have indexes defined?
how do you have statistics on indexes set?

try to set fill factor on indexes as well as page_fill.

dlotus
Starting Member

2 Posts

Posted - 2010-08-05 : 12:10:56

Thanks 4 reply!
Two indexes created on request_att (target)table, one is on primary id and 2nd is composite key and I created as follows:
CREATE NONCLUSTERED INDEX XPK_Req_Name_Vstr_id
ON request_attribute(request_id,name, value_str)
do I need to modify it?

I am CRM devloper and generally not work on SQL, so I will look into fill factor option you mentioned. But I just give this one time sql to feed request_att table. So, looking for SQL query which more efficient than my one.

dk

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-05 : 12:25:11

try to set pad_index and fillfactor.
you can drop index and recreate it - as long as this change won't have any immidate impact on production server.


CREATE NONCLUSTERED INDEX
[index_name] on [table_name](
	[column_name] ASC)
	WITH 
	(PAD_INDEX  = OFF
	,STATISTICS_NORECOMPUTE  = OFF
	,SORT_IN_TEMPDB = OFF
	,IGNORE_DUP_KEY = OFF
	,ONLINE = OFF
	,ALLOW_ROW_LOCKS  = ON
	,ALLOW_PAGE_LOCKS  = ON
	,FILLFACTOR = 80) 
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources