Stored procedure creation suddenly drops to 1/36 speed!

[ptj]

I am creating a large number of stored procedures from vb code which are done dynamically because they need to be amended depending on the server names/IPs of the servers in each installation. The stored procedures copy data between a local server and a linked server.

When I have an empty database my vb code creates the stored procedures at a rate of 6 a second, but if the database has data in it, the rate drops to about 1 stored procedure every 6 seconds. 36 times slower!!!

Has anyone got any ideas why this might be happening and what on earth I can do about it! Sometimes the linked server refers back to the same machine, but other times it is a different sql server entirely.

Thanks in anticipation

Phil

 

[LogicalmanUS]

Phil,

From what you say, the SProcs are generally the same, except for the source and destination servers/databases/IP addresses.
If this is the case, have you considered using parameters to pass the servers/databses/IP Addresses? Then the SProcs would be compiled already and not need to be compiled each time, thus, they run faster.

Logicalman

 

[cheerio]

Stored Procedures usually optimise the query plan when you save the procedure.

If you create them with an empty database little time is going to be spent on optimisation. However, when you populate with data you can expect the procedures to be inefficient because the plan will not be optimal.

 

[constellation]

Hi - I am a colleague of PTJ and it is actually me that is doing the programming for this.

Looking at the two responses given so far I have tested the resulting SPs as created with an empty -vs- full database and despite the extra time required to create them on the full database they do not seem to run any quicker.

So I turn to LogicalmanUS's suggestion, which seems fine to me except that I cannot get it to work. I cannot get it to accept the parameter value in the body of the query.

Example:

CREATE PROCEDURE SP_TEST_1
@MyServerIP AS VARCHAR(20)
AS
INSERT INTO tblTest (PriKey, Details)
SELECT PriKey, Details
FROM @MyServerIP.DB_OFFICE.dbo.tblTest S
WHERE NOT S.PriKey IN (SELECT PriKey FROM tblTest)

This fails on line 6 with a complaint about the . after @MyServerIP. I have tried putting @MyServerIP in single quotes, double quotes, everything I can think of and always there is something wrong in the syntax.

LogicalmanUS or anyone else: can you give me an example of how to make this work please?

Thanking you in advance.

 

[JayKusch]

Need to create this Dynamically and then execute it. Replace your INSERT statement with this w/in your SP

declare @SQLCommand VarChar(500)

SET @SQLCommand =

'INSERT INTO tblTest (PriKey, Details)
SELECT PriKey, Details
FROM ' + @MyServerIP + '.DB_OFFICE.dbo.tblTest S
WHERE NOT S.PriKey IN (SELECT PriKey FROM tblTest)'



Thanks

J. Kusch

 

[JayKusch]

Sorry also need to add ...

EXEC (@SQLCommand)

Thanks

J. Kusch

 

[LogicalmanUS]

Constellation,

When using a passed param in a SQL statement, not being used solely for a variable in a where statement, you need to build the string to execute first.

This has two advantages, firstly you can create a much more precisie SQL statement to execute, and secondly, you can print the statement out first in QA and then test it from QA before running it in the SProc.

Therefore, as the parameter you passed is an integral part of the SQL statement, use the following syntax:


CREATE PROCEDURE usp_TEST_1
@MyServerIP AS VARCHAR(20) = ''
AS
DECLARE @mySQL varchar(1000)
SET @mySQL = ''
SET @mySQL = @mySQL + 'INSERT INTO tblTest (PriKey, Details) '
SET @mySQL = @mySQL + 'SELECT PriKey, Details FROM '
SET @mySQL = @mySQL + @MyServerIP
SET @mySQL = @mySQL + '.DB_OFFICE.dbo.tblTest S '
SET @mySQL = @mySQL + 'WHERE NOT S.PriKey IN '
SET @mySQL = @mySQL + '(SELECT PriKey FROM tblTest)'

PRINT @mySQL
EXEC (@mySQL)


You can REM out the Print statement when actually running it, or REM out the EXEC statement to check what the string looks like prior to running it.

NOTE: I have changed the prefix of the SProc to be usp (there are plenty of FAQs as to why this should be done) and also made it lower case as all prefixes usually are (Helps see the proper name easier)


Hope this helps,

Logicalman

 

[constellation]

Thank you both, that seems to work now.