Speed of an Insertion

[panayotisk]

I am using SQL server 2000.
I am using a stored procedure to make a creation of a specific object.
This involves at most three INSERT statements and two SELECTS in a transaction.
The problem is that every time the stored procedure is executed for the 1st time on the server it takes a lot of time to complete. The next insertions are almost instantaneous - I guess because the execution plan had been stored.
Is there a way to load the execution plan before the first object creation? Or should I search for a way to speed up my creation stored procedure?

[olivthill]

There is a very clear and informative article about tuning SQL server's stored procedure at http://www.sql-server-performance.co...procedures.asp.

[panayotisk]

Nice article. Well, I guess I will try to measure the performance and find where exactly there is the problem.
Thanx.

[panayotisk]

Ok I found the bottleneck:
my creation stored procedure calls another one which takes around 80% of the execution time.
This is the actual procedure.

CREATE PROC prc_dic_CreatePhrase
@Phrase 	TPhrase,
@LangAbbr 	TLangAbbr,
@id TID OUTPUT
AS
	SET NOCOUNT ON

	-- Fix the phrase quotes (make them double for the query)
	SET @Phrase = Replace(@Phrase, '''', '''''')

	-- Attempt to Insert the new phrase (the scope ID should be retrieved in the same batch)
	DECLARE @Insert_stmt TStmt	
	SET @Insert_stmt =	'SET @ScopeID = -1
				INSERT INTO DIC_' + @LangAbbr + ' ([phrase]) VALUES (N''' + @Phrase + ''')
				IF (@@ERROR = 0)
				BEGIN
				 	SELECT @ScopeID = SCOPE_IDENTITY() FROM DIC_' + @LangAbbr +'
				END'

	EXECUTE sp_executesql @Insert_stmt, N'@ScopeID int OUTPUT', @ScopeID = @id OUTPUT

	SET NOCOUNT OFF

TStmt is nvarchar(4000), TPhrase is nvarchar(250), TLangAbbr is char(2), TID is int. This takes around 5.5 seconds on clean cache, and around 1 sec when it has been executed before, to execute on my PC where the database table already has more than 2.000.000 records. The table has the following fields: an ID (indexed) and the phrase (PK, clustered). I need to keep these indexes. Is there a way to speed up the INSERTion?

[erickwidya]

...to execute on my PC where the database table already has more than 2.000.000 records.

try BULK INSERT instead and remove the index while inserting then re-index again..i'm not sure how but i read somewhere to use that kind of method

and using Unicode datatype tooks 2 time more processing then using non-Unicode..i read it somewhere too

if u figure it out, plz post the answer ok..

thx..

[panayotisk]

Actually it was not the INSERT!!
It was the FROM Clause in the SELECT SCOPE_IDENTITY() statement. When I changed the following:

SELECT @ScopeID = SCOPE_IDENTITY() FROM DIC_' + @LangAbbr +'

To:

SELECT @ID = @@IDENTITY

or

SELECT @ScopeID = SCOPE_IDENTITY()

The execution time changed dramatically (on a clean cache).

Anyone has a clue why omitting the FROM <table> changed performance dramatically?