Make Stored Proc Faster

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@.ExtractDate DateTime,
@.DateFrom DateTime,
@.DateTo DateTime,
@.96hrPlusFlag int,
@.AppointmentsCount int OUTPUT

AS

IF @.96hrPlusFlag = 0

BEGIN
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @.DateFrom AND @.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @.96hrPlusFlag = 1

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime >@.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...You need to LEFT OUTER JOIN tbl_Appointment rather than use NOT IN on it. And in the WHERE clause filter in records where SurgerySlotKey IS NOT NULL.

make more space available for store proc editing

A suggestion for the Microsoft folks (those who have access to them can
forward?):
when editing stored proc from EM, the screen estate is vastly wasted. Some
of us don't care, some of us do care about this. For those who don't care,
they should not have much to object the idea of making more screen space
accessible. So if you folks in the future could reduce the blank space and
convert it to space displaying code, I am one of those who would applaud.
Quentin> when editing stored proc from EM, the screen estate is vastly wasted.
Some
> of us don't care, some of us do care about this.
Use Query Analyzer. Press F8, you can right-click a stored procedure and
hit Edit (or script to new window as create, if you like to drop/create all
your objects).
You can even run only sections of code from this interface (by highlighting
only the relevant portions), test and debug, and - wow - you aren't stuck in
modal mode, so you can actually look at other stored procedures, other
servers, tables, etc. while you are working on a proc.
Enterprise Manager is not really meant for development, but more for
Enterprise Management, hence the name.
A|||Thanks Aaron.
I'm thinking making more space available for EM stored proc editing does not
hurt anyone and some benefit from it.
Quentin
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OQdu1L4VDHA.1784@.TK2MSFTNGP10.phx.gbl...
> > when editing stored proc from EM, the screen estate is vastly wasted.
> Some
> > of us don't care, some of us do care about this.
> Use Query Analyzer. Press F8, you can right-click a stored procedure and
> hit Edit (or script to new window as create, if you like to drop/create
all
> your objects).
> You can even run only sections of code from this interface (by
highlighting
> only the relevant portions), test and debug, and - wow - you aren't stuck
in
> modal mode, so you can actually look at other stored procedures, other
> servers, tables, etc. while you are working on a proc.
> Enterprise Manager is not really meant for development, but more for
> Enterprise Management, hence the name.
> A
>|||> I'm thinking making more space available for EM stored proc editing does
not
> hurt anyone and some benefit from it.
I understand, however I'm suggesting a workaround, because you're not going
to see any changes to the client tools in SQL Server 7.0/2000.

Make Filter = False

I have a stored proc to return the main report data. I have another dataset1 to return the distinct values for my parameter. I filter the main data based on the parameter selected by user. I wanted to add 'ALL' option to the parameter drop down. I have added an UNION to the dataset1 to include this option. I now want to change my filter expresion from '=Fields!FRole.Value = Parameters!PRole.Value' to include ALL option and basically ignore the filter. Is it possibleMulti-valued parameters are not supported RS 2000. Here's a related post
with a solution that might work for you:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ONnNRtsBEHA.3064%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3D%2522in%2Bclause%2522%2Bgroup:microsoft.public.sqlserver.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DONnNRtsBEHA.3064%2540tk2msftngp13.phx.gbl%26rnum%3D2
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"vrodkar" <vrodkar@.discussions.microsoft.com> wrote in message
news:A6CBA50D-2C8F-4A97-B925-CE5DE0378347@.microsoft.com...
> I have a stored proc to return the main report data. I have another
dataset1 to return the distinct values for my parameter. I filter the main
data based on the parameter selected by user. I wanted to add 'ALL' option
to the parameter drop down. I have added an UNION to the dataset1 to include
this option. I now want to change my filter expresion from
'=Fields!FRole.Value = Parameters!PRole.Value' to include ALL
option and basically ignore the filter. Is it possible|||I'm curious on this as well as I am also trying to
implement this on a report. Has anyone founnd a
workaround?
>--Original Message--
>I have a stored proc to return the main report data. I
have another dataset1 to return the distinct values for
my parameter. I filter the main data based on the
parameter selected by user. I wanted to add 'ALL' option
to the parameter drop down. I have added an UNION to the
dataset1 to include this option. I now want to change my
filter expresion from '=Fields!FRole.Value =Parameters!PRole.Value' to include ALL option and
basically ignore the filter. Is it possible
>.
>|||Yes I use an "(All)" option in most of my reports.
It's easier if you're using queries instead of stored procedures.
In your parameter list have an item labelled "(All)" give it a Value of "%".
In your main data query have criteria or where clause using the 'LIKE' operator against the parameter, so in SQL;
SELECT * FROM tblData WHERE Country LIKE @.Country
% is the SQL wildcard character, but must be used with the like operator.
Regards
Chris McGuigan
"BiggieSize" wrote:
> I'm curious on this as well as I am also trying to
> implement this on a report. Has anyone founnd a
> workaround?
> >--Original Message--
> >I have a stored proc to return the main report data. I
> have another dataset1 to return the distinct values for
> my parameter. I filter the main data based on the
> parameter selected by user. I wanted to add 'ALL' option
> to the parameter drop down. I have added an UNION to the
> dataset1 to include this option. I now want to change my
> filter expresion from '=Fields!FRole.Value => Parameters!PRole.Value' to include ALL option and
> basically ignore the filter. Is it possible
> >.
> >
>