Optimized Stored Procedure

Question

I am using SQL Server 2008 R2. I have the stored procedure like this :

ALTER PROCEDURE [dbo].[GetZone]
    -- Add the parameters for the stored procedure here
@TheInsee int
AS
BEGIN
declare @Zone int

declare @CountA int
declare @CountB int
declare @CountC int
declare @CountD int
declare @CountN int
declare @CountP int
declare @CountN1 int
declare @CountN2 int
declare @CountN3 int
declare @CountN4 int
declare @CountN5 int
declare @CountN6 int

SELECT @CountA = COUNT(*) FROM LS_CLIENT_INSEE_A WHERE NO_INSEE = @TheInsee
SELECT @CountB = COUNT(*) FROM LS_CLIENT_INSEE_B WHERE NO_INSEE = @TheInsee
SELECT @CountC = COUNT(*) FROM LS_CLIENT_INSEE_C WHERE NO_INSEE = @TheInsee
SELECT @CountD = COUNT(*) FROM LS_CLIENT_INSEE_D WHERE NO_INSEE = @TheInsee
SELECT @CountN = COUNT(*) FROM LS_CLIENT_INSEE_N WHERE NO_INSEE = @TheInsee
SELECT @CountP = COUNT(*) FROM LS_CLIENT_INSEE_P WHERE NO_INSEE = @TheInsee

SELECT @CountN1 = COUNT(*) FROM LS_CLIENT_INSEE_N1 WHERE NO_INSEE = @TheInsee
SELECT @CountN2 = COUNT(*) FROM LS_CLIENT_INSEE_N2 WHERE NO_INSEE = @TheInsee
SELECT @CountN3 = COUNT(*) FROM LS_CLIENT_INSEE_N3 WHERE NO_INSEE = @TheInsee
SELECT @CountN4 = COUNT(*) FROM LS_CLIENT_INSEE_N4 WHERE NO_INSEE = @TheInsee
SELECT @CountN5 = COUNT(*) FROM LS_CLIENT_INSEE_N5 WHERE NO_INSEE = @TheInsee
SELECT @CountN6 = COUNT(*) FROM LS_CLIENT_INSEE_N6 WHERE NO_INSEE = @TheInsee

set @Zone = 
      CASE 
         WHEN @CountA >  0   THEN 1
         WHEN @CountB >  0   THEN 2
         WHEN @CountC >  0   THEN 3   
         WHEN @CountD >  0   THEN 4
         WHEN @CountN >  0   THEN 5
         WHEN @CountP >  0   THEN 6  
         WHEN @CountN1 >  0   THEN 7
         WHEN @CountN2 >  0   THEN 8
         WHEN @CountN3 >  0   THEN 9
         WHEN @CountN4 >  0   THEN 10
         WHEN @CountN5 >  0   THEN 11
         WHEN @CountN6 >  0   THEN 12   
         ELSE 1    
      END

RETURN @Zone  

END

It works fine but when the data of each table is very big, then it hang out because of time out. I wonder if I can simplify this stored procedure.

This stored procedure is very simple: I just give an input @TheInsee and it search in six tables when it found within.

Any help please ?

score 0 · Accepted Answer · answered Jan 08 '13 at 16:07

0

First, since you are only checking if at least one row exists in each table there is no point in counting all the rows. Second, there is no point in querying all tables if you already found rows in a previous table.

I would do something like this:

set @Zone = case
    when exists (select * from dbo.LS_CLIENT_INSEE_A where NO_INSEE = @TheInsee) then 1
    when exists (select * from dbo.LS_CLIENT_INSEE_B where NO_INSEE = @TheInsee) then 2
    -- etc.
    else 1
    end

answered Jan 08 '13 at 16:07

Pondlife

15,992
6
37
51

how can i combine when exist with select count (1) ? – user1958628 Jan 08 '13 at 16:46
Why `select *` when none of the columns are used? `Select 1` would be more efficient as in: `select 1 from dbo.LS_CLIENT_INSEE_A where NO_INSEE = @TheInsee` – Jay Walker Jan 08 '13 at 17:01
1

@JayWalker The efficiency of `SELECT 1` in an `EXISTS`query is a SQL Server urban myth. MSDN [documents](http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4) the use of the asterisk in this situation and see this question for a much more detailed discussion: http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists – Pondlife Jan 08 '13 at 17:08

Optimized Stored Procedure

1 Answers1