Speed of an Insertion

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?

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

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

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.

Code:

---

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?

Quote:

---

...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..

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

Code:

---

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

---

To:

Code:

---

SELECT @ID = @@IDENTITY

---

or

Code:

---

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?