question

Robert Morgan avatar image
Robert Morgan asked

SQL Server very slow creating stored procedure

The following stored procedure took longer than 10 minutes to create (not run)? when I exec this sp it takes just as long to return 300 records?

Any reason why this should be?

USE [XXXXX]
GO
/****** Object:  StoredProcedure [dbo].[Update_Event]    Script Date: 16/06/2010  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <XXXXXX>
-- Create date: <15/06/2010>
-- Description: <loads  Event table in XXXXXX database>
-- =============================================
CREATE PROCEDURE [dbo].[Update_Event]   
    AS
BEGIN   
    SET NOCOUNT ON; 
    insert into dbo.[Event] 
      ( OID,ClientID,CaseID,SystemEventCode,Revoked,
        EventEndDate,EventEndTime,EventStartDate,EventStartTime,
        ServiceType,ServiceSubType,WorkUnitProvider,TravelTime,EventVenueCode)

    SELECT 
       oid, auditClientId, getCaseIdString, mySystemEventCode, isRevoked,
       eventEndDate, eventEndTime, eventStartDate, eventStartTime,
       myServiceType, myServiceSubtype, myWorkUnitProvider, eventTravelTime, myEventVenueCode  
FROM       JCCTEST...[Event]

END

Bob

t-sqlstored-procedures
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Kev Riley avatar image
Kev Riley answered

Is there any reason to specify the server in your from clause?

Is this really inserting data from another (linked) server?

If so, how big are the fields? What is the network between the two servers like? 300 records, 14 fields - if they where all varchar(max), could take a while.......

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

If that's a linked server then the compile and execution time is dependent on hopping between machines. 10 minutes seems highly excessive, but not knowing your system, network and configurations, it's possible. Also, you need to take into account how you've configured DTC since this will be a factor in the query. Since you have no filtering information (a WHERE clause) you're also going to be getting a table scan on the other server, not to mention having to move all the data across the network. That means contention on the other server will seriously impact your query. You might be better off looking into a mechanism that is optimized for batch data movement, say Integration Services or bulk copy through SQL Command.

4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image Daniel Ross commented ·
where i worked previously, they had a self taught SQL developer and just about every single stored procedure had multiple cross server joins...they even had updates on cross server joins. It was a nightmare, there was blocking left right and centre, even updating a stored procedure would create blocks! I replaced the stored procedures with SSIS packages and things were really improving...until I and 75% of the workforce were made redundant, damn you GFC! The moral of the story is SSIS will be your friend.
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Would the table scan(s) take place when the procedure is created so that the plans are produced ready for first execution or do the plans get created at time of 1st execution?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
The execution plan would be created the first time it was executed and the scans would just take place every time it was executed. But, a query through a linked server like this goes to the linked server, connects and validates the structures of the tables referenced when the stored procedure is created.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Great story! Thanks
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

What's the timing on simply running the SELECT statement through Sql Server Management Studio?

SELECT
oid, auditClientId, getCaseIdString, mySystemEventCode, isRevoked,
eventEndDate, eventEndTime, eventStartDate, eventStartTime,
myServiceType, myServiceSubtype, myWorkUnitProvider, eventTravelTime, myEventVenueCode
FROM
JCCTEST...[Event]

What is JCCTEST? Is it another SQL Server, or another DBMS?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.