Re: slow stored procedure

(remylawrence@xxxxxxxxx) writes:
The following procedure took over 24 hours to run. I've tried several
things to make it faster but since I am a beginner I figured I would
just post it as I originally had it and see how an expert might make
it run faster. I can't manipulate the database design. Sorry if I'm
leaving out a lot of information. Here it is:

Being an expert is one thing. But to optimize a stored procedure only
from seeing a code does not take an expert - it takes a person with
skills in clairvoyance.

That is, I would need to know the table and index defintions, have
some information about the data distribution. It would help to have
the current plans. And of course, it would help to know which query/ies
that is taking most of the time. I would also like to know how large
portion of the table much a condition like WHERE EVT_UPDATED < @CutOFfDate
would hit.

So for now, I will offer only observation:

SELECT * from dbo.R5ACTSCHEDULES --level 3
WHERE (CAST(ACS_EVENT AS NVARCHAR) +
CAST(ACS_ACTIVITY AS NVARCHAR)) IN
(
SELECT (CAST(ACT_EVENT AS NVARCHAR) +
CAST(ACT_ACT AS NVARCHAR))
FROM dbo.R5ACTIVITIES
WHERE ACT_EVENT IN
(
SELECT EVT_CODE
FROM dbo.R5EVENTS
WHERE EVT_UPDATED < @CutOffDate AND
(EVT_STATUS = 'GLCL' OR
EVT_STATUS = 'C' OR EVT_STATUS = 'CANC')
)
)

I don't know why you use string concatenation here, but my guess is
that you don't know about EXISTS:

SELECT *
FROM dbo.R5ACTSCHEDULES as
WHERE EXISTS (SELECT *
FROM dbo.R5ACTIVITIES a
WHERE a.ACT_EVENT = as.ACS_EVENT
AND a.ACT_ACTIVITY = as.ACS_ACTIVITY
AND EXISTS
(SELECT *
FROM dbo.R5EVENTS e
WHERE a.ACT_EVETNT = e.EVT_CODE
AND EVT_UPDATED < @CutOffDate
AND EVT_STATUS IN ('GLCL', 'C', 'CANC')))

How much this would help I don't know, though.

Since the conditions reapperars in the subqueries, it could also be
an idea to capture the inserted rows with the OUTPUT clause (SQL 2005
only) and then use this in the remaining queries. But that depends
on where the bottleneck is.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: IRowsetFastLoad and nvarchar (1) fields
    ... definition in my DDL - I wonder if nvarchar really does ... That means characters. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.data.oledb)
  • Re: Datatype-convertion in TSQL
    ... You need to use one of the format codes, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Datatype-convertion in TSQL
    ... Can't find a code for datetime-conversion from nvarchar format DD/MM/YYYY ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Encoding For HashBytes
    ... Does anyone know how varchar is ... create a hash that matches what SQL server does. ... means codes in the range 0 to 255, and for nvarchar a UTF-16 encoding. ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: JDBC - Cant get unicode with ResultSet.getString()
    ... The column is a nvarchar and I use ResultSet.getStringto retrieve it. ... >> The data is correct in Sql Server - I viewed it there. ... >> And it is the jdbc getString() that returns it wrong, ...
    (microsoft.public.sqlserver.odbc)