December 11, 2008 at 5:36 am
We have a .Net Apllication which does the function of retrieving records from the database and displaying it to the user.The database is in SQL Server 2005.The query I need to tune has 5 tables.
The records in these tables range from 5 lakhs to 40 lakhs.The primary key of the table is uniqueidentifier column..The clustered index is also put on the sam ecolumn.Regular insertion of data takes place in this database.The number of row inserted will be around 40 lakhs.
The query I am tuning fetches records in 2 mins to 4 mins.I am supposed to tune it and make the query fetch it within a minute.
Steps i have taken to improve the performance:-
1)Put nonclustered indexes on the columns used in joins,on columns used in where clause,
2)Put effective indexes such that the index seek operation is only carried out.
3)Separated the heavily accessed tables and indexes of that table to a separate filegroup.
4)Modified teh database design.Added a new column.Put a identity key on it.Shifted the clustered index which was on the uniqueidentifier column to the newly added column.Retained the uniqueidentifier column as the primary key of the table but with non clustered index
All the above stated methods did not help me to much extent
Please find below the query
select csp.SpeakerRowGUID
, css.SessionRowGUID
, cut.FilePath
, cut.FileName
, cr.SDNCatNo
, cr.WaveFileRoot
, cut.Transcription
, cut.UtteranceRowGUID
, saf.LeadSilence
, saf.TrailSilence
,cpc.promptcatgname
from css join b csp on css.SpeakerRowGUID = csp.SpeakerRowGUID
join cr on cr.SDNCatNo = css.SDNCatNo
join sal
on sal.SDNCatNo=cr.SDNCatNo and sal.SDNCatNo=css.SDNCAtNo
join cut on css.SessionRowGUID = cut.SessionRowGUID AND css.SDNCatNo = cut.NSDNCatNo and sal.SDNCatNo=cut.NSDNCatNo
join cp on cp.PromptRowGuid = cut.PromptRowGuid
join cpc on cpc.PromptCatgRowGuid = cp.PromptCatgRowGuid
join #promptname pn on pn.promptname=cpc.promptcatgname
join saf on cut.UtteranceRowGUID = saf.UtteranceRowGUID
where cr.Platform = 'Server' and sal.Languagename = 'FRench'
and csp.DataUsagepurpose = 'Training'
order by csp.speakerrowguid,css.sessionrowguid
csp table contains 60000 records
css contains around 50000 records
cr contains around 1 lakh records
cut around 40 lakhs(Will increase)
cp arnd 5 lakhs
cpc arnd 2000 records
saf around 40 lakhs
#promptname is a table containing at the max 100 records
Please help me to tune the query
December 11, 2008 at 5:54 am
Can you post the table schemas, and the index definitions please?
How many rows is a lakh?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply