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.com/stored_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?