complex Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex Stored Procedure

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-04-15 : 07:03:17

Dear All,

I have a complex stored proc, which I inherited from the previous developer, and I wish to fine tune it a bit, because its taking 1.36 mins to retreive 5383 rows, which I think is quite excessive.

The stored proc is as follows:-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
@projectid int
AS
create table #ebdindex (
[indexid] [int] IDENTITY (1, 1) NOT NULL ,
[stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[trans] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[status] [int]
)
create table #residinpages (
[indexid] [int],
[stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[pageid] [int]
)
create table #residinpages2 (
[indexid] [int],
[pageid] [int]
)
insert into #ebdindex
Select stringid, trans, status from edbs e where
e.lcid='en' and e.projectid=@projectid
insert into #residinpages
select distinct e.indexid, e.stringid, CASE
WHEN s.pageid IS NULL then 0
ELSE s.pageid
END as pageid
from #ebdindex e,
stringidsinpages s, pages p
where e.stringid *=s.stringid
order by e.indexid
declare @indexId nchar(128)
declare @pageId nchar(128)
declare @RowNum int
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
set @RowNum = 0
WHILE @RowNum < (select max(indexid) from #ebdindex)
BEGIN
set @RowNum = @RowNum + 1
insert into #residinpages2 values( @indexId, @pageid)
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
where indexId > @indexId
END
select e.indexid,e.stringid, e.trans, r.pageid, e.status from #residinpages2 r, #ebdindex e
where e.indexid=r.indexid
drop table #residinpages2
drop table #residinpages
drop table #ebdindex

Is there another way to write this stored proc, to make it return the results faster? I am new in SQL and this is quite a complex one for me.

Your help is much appreciated

Thanks

Regards

Johann

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-15 : 07:31:15

I think you need a SQL Server Developer if all your queries are along these lines.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:00:11

This maybe?

SELECT		ROW_NUMBER() OVER (ORDER BY e.StringID) AS IndexID,
		e.StringID,
		e.Trans,
		e.Status,
		COALESCE(s.PageID, 0) AS PageID
FROM		eDBs AS e
LEFT JOIN	(
			SELECT		StringID,
					MAX(PageID) AS PageID
			FROM		StringIDsInPages
			GROUP BY	StringID
		) AS s ON s.StringID = e.StringID
WHERE		e.LcID = 'en'
		AND e.ProjectID = @ProjectID

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-04-15 : 08:38:05

Hi Peso

I got the following error :-

Msg 4104, Level 16, State 1, Procedure sp_getpageidsforedb, Line 8
The multi-part identifier "p.PageID" could not be bound.
Msg 4104, Level 16, State 1, Procedure sp_getpageidsforedb, Line 8
The multi-part identifier "p.PageID" could not be bound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 08:43:07

See edited response above.

E 12°55'05.25"
N 56°04'39.16"

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-04-15 : 08:50:29

Hi peso

looks really good

Thanks

J

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-04-15 : 08:51:59

Hi peso

Will it be possible to explain to me what you did, because there are some things I cannot understand

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 09:15:47

I used row_number() function to get a "rownumber" on the fly, where you did insert into temptable.

And instead if looping all records to get any single matching record from another table,
I use a derived table to get max PageID (equivalent to any matching) for each stringid.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources