Returning the unutilised range from existing Integer values

[mukund]

Hi,
I have a PO Table that has some integer values. HubId is an officelocationid, different id is assigned to different offices
e.g. 1 for US
2 for UK
The procedure below is written, to return the missing range of PO Numbers

the sample output is as follows
FromNo ToNo
------- ------
1 101
103 105
109 110
112 113
115 115
118 118
124 9999

This means that the above range of PONo are not utilised.
The maximum no. is 9999

Therefore the user needs to know that the above nos can be utilised.

I have written the procedure and I am getting the desired results, but I want to optimize it as it is taking a heck lot of time.

Any suggestions/help is welcome.

/* Proc Starts */
Create Procedure sp_GetPORange
@HubId int
AS
SET NOCOUNT ON
CREATE TABLE #Tmp_POFinalRange(FromNo int, ToNo int)
CREATE TABLE #Tmp_POAllRange (NO int)
CREATE TABLE #Tmp_PORange_no (NO int)
INSERT INTO #Tmp_PORange_no
select PoNo from Pohead Where PONo is not null and POId like ltrim(rtrim(str(@HubId))) + '%' order by PONo asc
Declare @no int, @FromNo int, @ToNo int, @cnt int, @PrevNo int
Select @FromNo = 0
Select @ToNo = 0
Select @no = 1
While @no < 10000
Begin
INSERT INTO #Tmp_POAllRange values (@No)
Select @No = @No + 1
End
Delete from #Tmp_POAllRange Where No IN (Select No From #Tmp_PORange_no)
Declare nogen_cusrsor INSENSITIVE CURSOR FOR
Select no from #Tmp_POAllRange
Open nogen_cusrsor
FETCH NEXT FROM nogen_cusrsor INTO @NO
Select @FromNo = @No
WHILE (@@FETCH_STATUS = 0)
Begin
If @No - 1 <> @PrevNo
Begin
Select @ToNo = @PrevNo
INSERT INTO #Tmp_POFinalRange VALUES(@FromNo, @ToNo)
Select @FromNo = @No
End
Select @PrevNo = @No
FETCH NEXT FROM nogen_cusrsor INTO @NO
End
INSERT INTO #Tmp_POFinalRange VALUES(@FromNo, @No)
DROP TABLE #Tmp_POAllRange
DROP TABLE #Tmp_PORange_no
Close nogen_cusrsor
DeAllocate nogen_cusrsor
SET NOCOUNT OFF
Select * from #Tmp_POFinalRange Order By FromNo
Drop Table #Tmp_POFinalRange

/* Proc Ends */

 

[kim1]

Hi,

Try to avoid the most possible Temporary table and Cursor because it is resource time consuming for SQL Server.

Here are my optimizations for you:

1. Put a clustered index on your PoNo field in Pohead table
2. Try to replace some temporary tables for derived table (must faster)
3. Create an Identity column in #Tmp_POAllRange table instead of an integer column and you add yourself an incremental integer each time there is a row added, let SQL do it for you, it is much faster.
4. For assignment, use SET instead of SELECT (SET @no = @no + 1)
5. Do your concatenation before putting in your WHERE clause, because you prohibit SQL Server to use the index search:
select PoNo from Pohead Where PONo is not null and POId like ltrim(rtrim(str(@HubId))) + '%' order by PONo asc

I hope that can help and lead you for optimizing your query.

Need more help, let me know.


Kim

 

[mukund]

Hi Kim,
I used derived tables and the perofrmance has improved, but not up to the mark as yet. I just was wondering whether there is any alternative to the looping that finds out the from to range, because that seems to be consuming most of the time.
Again I tested this procedure in two environments. In developement environment it consumes more time than in Demo server environment, meaning this peroformance seems to be server specific. It depends on the config. of the SQL Server.

Thanks for the help, but still this proc needs more optimization.

Mukund.