April 15, 2008 at 5:05 am
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
April 15, 2008 at 5:32 am
[font="Verdana"]Johann, look at the below SProc, I tried to update.
ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
@projectid int
AS
Set Nocount On
Begin
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]
)
Go
insert into #ebdindex
Select stringid, trans, status from edbs e where
e.lcid='en' and e.projectid=@projectid
Go
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
Go
declare @indexId nchar(128)
declare @pageId nchar(128)
declare @RowNum int
declare @maxid int
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
set @RowNum = 0
select @maxid = max(indexid) from #ebdindex
WHILE @RowNum < @maxid
BEGIN
set @RowNum = @RowNum + 1
insert into #residinpages2 values( @indexId, @pageid)
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
where indexId > @indexId
END
GO
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
GO
End
Always make a habit to add SET NOCOUNT ON statement, so that the SProc will not return any message.
Confirm on this.
Mahesh
[/font]
MH-09-AM-8694
April 15, 2008 at 6:29 am
Mahesh Bote (4/15/2008)
ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
@projectid int
AS
Set Nocount On
Begin
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]
)
Go
Those "Go" statements will break the procedure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2008 at 6:40 am
Hi Malesh
Got the following errors:-
Msg 102, Level 15, State 1, Procedure sp_getpageidsforedb, Line 21
Incorrect syntax near ')'.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@projectid".
Msg 208, Level 16, State 0, Line 2
Invalid object name '#ebdindex'.
Msg 208, Level 16, State 0, Line 7
Invalid object name '#residinpages'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#residinpages2'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'End'.
April 15, 2008 at 6:45 am
Johann Montfort (4/15/2008)
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
What about something like this. It's not cimplete because I don't have your structures, but the query you have is doing a lot of work that it doesn't need to. Instead of inserting into temp tables and then joining the temp tables to other tables to insert into other temp tables, just write a select statement that gets the data you need. No guarantees this will be perfect the first time, but it's going to be a lot more performant and close to what you need.
SELECT ROW_NUMBER() OVER ( ORDER BY e.stringid ) AS indexid
,e.stringid
,e.trans
,e.status
,CASE WHEN s.pageid IS NULL THEN 0
ELSE s.pageid
END AS pageid
FROM edbs e
JOIN stringidsinpages s
ON s.stringid = e.stringid
JOIN pages p
ON p.id = s.id--? something? this is missing in your query and you're not using data from this table in the query
WHERE e.lcid = 'en'
AND e.projectid = @projectid
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2008 at 7:09 am
Hi I solved this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
@projectid int
AS
SELECTROW_NUMBER() OVER (ORDER BY e.StringID) AS IndexID,
e.StringID,
e.Trans,
e.Status,
COALESCE(s.PageID, 0) AS PageID
FROMeDBs AS e
LEFT JOIN(
SELECTStringID,
MAX(PageID) AS PageID
FROMStringIDsInPages
GROUP BYStringID
) AS s ON s.StringID = e.StringID
WHEREe.LcID = 'en'
AND e.ProjectID = @ProjectID
April 15, 2008 at 7:29 am
You did?
Then I didn't have to explain the algorithm here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100990
N 56°04'39.16"
E 12°55'05.25"
April 15, 2008 at 7:34 am
Nice. Much better, and I'm sure it performs a lot better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply