SQL Server Performance Forum – Threads Archive

Problem in Fetching Records

I am facing a problem where I have to fetch more than 500K records and I have to improve the performance of my stored procedure having joins over 4 temporary tables. I cannot use the Indexes on the normal tables due to frequent insertion and updations. To avoid this problem, I fetched all teh records and stored them into 4 temp tables and then applied indexes on them. The result was negligible as there was not any difference in the speed. How can I improve the performance the speed of fetching the data. Below is a part of the stored procedure. ……………………………………………………………… CREATE INDEX ixTMP ON #tblDetail (Responseid) CREATE TABLE #tblResponse (
[ResponseID] [bigint] primary key clustered,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[IPAddress] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SendID] [bigint] NOT NULL ,
[ContactID] [bigint] NOT NULL ,
[SurveyID] [bigint] NULL ,
[PageNO] [int] NULL ,
[IsDeleted] [bit] NULL ,
[InActive] [bit] NULL
)
CREATE TABLE #tblEmailListDet
(
[EmailID] [bigint],
[EmailAddress] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData1] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData2] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData3] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData4] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData5] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomData6] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [bit] NULL ,
[IsDeleted] [bit] NULL ,
[LoginID] [bigint] NULL
) CREATE TABLE #tblResponseHiddenFields
(
[RHFields] int ,
[ResponseID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[HField1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HField2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HField3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
insert into #tblResponse
select * from tblresponse where [email protected] AND IsDeleted=0 insert into #tblEmailListDet
select * from tblEmailListDet insert into #tblResponseHiddenFields
select * from tblResponseHiddenFields where [email protected] –CREATE INDEX ixTMP2 ON #tblResponse (Responseid)
CREATE clustered INDEX ixTMP3 ON #tblEmailListDet (EmailID)
CREATE clustered INDEX ixTMP4 ON #tblResponseHiddenFields (Responseid)
CREATE INDEX ixTMP4 ON #tblresponse (ContactID) // @SelFields are the options which are concatenated dynamically as per user requirements. set @strQuery=N’select tDetail.* ‘ + @SelFields + ‘ from #tblDetail tDetail join
#tblresponse TR on tr.Responseid=tDetail.Responseid
left join #tblEmaillistDet td on tr.ContactID=td.EmailID
left join #tblResponseHiddenFields h2 on tr.responseid=h2.responseid ‘
–where TR.surveyID=’ + convert(varchar(10),@SurveyID) +’ AND TR.IsDeleted=0′
exec(@strQuery)
drop table #tblDetail
drop table #tblResponse
drop table #tblEmailListDet
drop table #tblResponseHiddenFields ………………………………………………………… Thanks in Advance.

try to create indexes after creation of the tables and see SURYA

What is the volume on this table?
How many rows will be inserted and what is the time gap? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

1. This kind of reporting should be done on separate database. You need to periodiacaly replacate data from production to reporting database and report from there.
2. Why is SurveyID bigInt?
3. I hope you have an index starting with surveyId on tblResponse.
4. Temp tables doesn’t help because you have first to potentially select even more data from original tables to populate temporary ones, so inserts and updates affects these selects the same way (maybe even more) they affect selecting and joining original tables. Better make sure you have proper index on each table.
5. If it is acceptable to occasionally have/miss a row from incomplete transaction you can use noLock hints:
set @strQuery=N’select tDetail.* ‘ + @SelFields + ‘ from tblDetail tDetail (nolock) join
tblresponse TR (nolock) on tr.Responseid=tDetail.Responseid
left join tblEmaillistDet td (nolock) on tr.ContactID=td.EmailID
left join tblResponseHiddenFields h2 (nolock) on tr.responseid=h2.responseid
where tr.SurveyId = ‘ + cast(@SurveyId as varchar(10) + ‘ and tr.IsDeleted = 0’
–where TR.surveyID=’ + convert(varchar(10),@SurveyID) +’ AND TR.IsDeleted=0′
exec(@strQuery)

]]>