May 30, 2012 at 1:00 am
Hello,
I am using below store procedure to fetch the record on live, But it is taking too long time for execution.. 3 to 4 minutes it takes to fetch the records.
ALTER procedure [dbo].[GetCollegeSearchDetails]
(
@courseid numeric(18,0)=null
)
as
select Distinct cast(college_id as int)as college_id,[infra_id],[hostel_available],[hostel_boys],[hostel_girls],[library_available],[library_books],[library_cdvideo],[library_magazine],[library_journals],[bookbank],[internet],[lab],[staffquarters],[canteen],[bank],[atm],[seminarhall],[auditorium],[busservices],[scholarship],[sports_avai],[sports],[teaching_methodology] from college_infrafacilities
-- select Distinct cast(CollegeId as int)as college_id,[Cutoff],[Capround],[Year],[StreamId],[DegreeId],[YearId],[substream_name],[Gender],[UniversityType] from View_Cutoff_substream where Year=(select Max(Year)as Max_Year from View_Cutoff_substream) and Capround='C1' and Gender='M' and UniversityType ='H'
select Distinct cast(College_Id as int)as college_id,[CutoffId],[Cutoff],[Capround],[Year],cast ([StreamId] as int) as SubStreamId,Cast([streamgroup_id] as int) as StreamId,Cast ([DegreeId] as int) as DegreeId,Cast([YearId] as int) as YearId,[substream_name],Cast([EntranceId] as int) as EntranceId,[EntranceName],[Gender],[UniversityType] from [View_College_CutOff]
select Distinct cast(college_id as int)as college_id,Views,View_id,Ratings from College_ViewMaster
select Distinct cast(UniversityId as int)as UniversityId,UniversityName,popular from college_universitymaster
select Distinct cast(college_id as int)as college_id,college_clienttype from college_master where college_clienttype='paid'
select top 20 cast(college_id as int)as college_id,substream_name,cast(substream_id as int)as substream_id,[streamgroup_name],cast([course_id] as int)as [course_id],cast([streamgroup_id] as int)as [streamgroup_id],[college_logo],[college_name],[course_name],[universityid],cast([degree_id] as int)as [degree_id],cast(city_id as int) as city_id,[degree_name],[populer_stream],CityName as city_name FROM View_College_Search_Details where course_id=@courseid
Go
All queries are right, but it is affecting performance part.
I am using a below view in store procedure,
ALTER VIEW [dbo].[View_College_Search_Details]
AS
SELECT TOP (100) PERCENT dbo.college_contactdetails.city_id, dbo.college_citymaster.CityName, dbo.college_citymaster.popular, dbo.college_coursemaster.course_name,
dbo.college_degreemaster.degree_name, dbo.college_streamgroupmaster.populer_stream, dbo.college_streamgroupmaster.streamgroup_name,
dbo.college_master.college_status, dbo.college_master.college_accreditation, dbo.college_master.college_logo, dbo.college_master.college_name,
dbo.college_coursetransition.course_id, dbo.college_coursetransition.degree_id, dbo.college_coursetransition.streamgroup_id,
dbo.college_coursetransition.college_id, dbo.college_coursetransition.universityid, dbo.college_coursetransition.intake, dbo.college_coursetransition.fees,
dbo.college_bankmaster.bank_name, dbo.college_bankdetails.bank_id, dbo.college_substreammaster.substream_name,
dbo.college_coursetransition.substream_id
FROM dbo.college_bankmaster INNER JOIN
dbo.college_bankdetails ON dbo.college_bankmaster.bank_id = dbo.college_bankdetails.bank_id RIGHT OUTER JOIN
dbo.college_streamgroupmaster RIGHT OUTER JOIN
dbo.college_citymaster INNER JOIN
dbo.college_contactdetails ON dbo.college_citymaster.CityId = dbo.college_contactdetails.city_id RIGHT OUTER JOIN
dbo.college_master INNER JOIN
dbo.college_coursetransition ON dbo.college_master.college_id = dbo.college_coursetransition.college_id ON
dbo.college_contactdetails.college_id = dbo.college_coursetransition.college_id LEFT OUTER JOIN
dbo.college_substreammaster ON dbo.college_coursetransition.substream_id = dbo.college_substreammaster.substream_id ON
dbo.college_streamgroupmaster.streamgroup_id = dbo.college_coursetransition.streamgroup_id LEFT OUTER JOIN
dbo.college_degreemaster ON dbo.college_coursetransition.degree_id = dbo.college_degreemaster.degree_id ON
dbo.college_bankdetails.college_id = dbo.college_coursetransition.college_id LEFT OUTER JOIN
dbo.college_coursemaster ON dbo.college_coursetransition.course_id = dbo.college_coursemaster.course_id
GO
Table has more than 38,000 records.
Please help...
Thanks & Regards,
Pallavi
May 30, 2012 at 1:28 am
pallavi.unde (5/30/2012)
select top 20 cast(college_id as int)as college_id,substream_name,cast(substream_id as int)as substream_id,[streamgroup_name],cast([course_id] as int)as [course_id],cast([streamgroup_id] as int)as [streamgroup_id],[college_logo],[college_name],[course_name],[universityid],cast([degree_id] as int)as [degree_id],cast(city_id as int) as city_id,[degree_name],[populer_stream],CityName as city_name FROM View_College_Search_Details where course_id=@courseid
Why all the CASTs to INT? What data types are you using in the base tables? Good performance starts with having a well-designed database.
ALTER VIEW [dbo].[View_College_Search_Details] AS SELECT TOP (100) PERCENT ... FROM ...INNER JOIN ... RIGHT OUTER JOIN ... RIGHT OUTER JOIN ... INNER JOIN ... RIGHT OUTER JOIN ... INNER JOIN ... LEFT OUTER JOIN ... LEFT OUTER JOIN ... LEFT OUTER JOIN...
TOP (100) PERCENT does nothing in a VIEW in modern versions of SQL Server (and even where it had an effect, in SQL Server 2000, there had to be an ORDER BY clause. Views are just stored queries; the result is not stored, it is combined with the query that references the view, and then optimized. If you must define views like this, follow the advice given in the following links to ensure the view is optimizer-friendly:
http://msmvps.com/blogs/robfarley/archive/2008/11/09/join-simplification-in-sql-server.aspx (blog)
http://sqlbits.com/Sessions/Event5/Designing_for_simplification (video)
There appear to be multiple major problems with your database and query design. Correcting the problems properly will likely require a fairly fundamental rethink and redesign - too much for a form question. I hope you find the links useful.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 6:41 am
Actually i need all columns from view and storeprocedure.
one of my friend recommended me to use index... i am not using index in database. is it really feasible solution to increase a performance????
Thanks & Regards,
Pallavi
May 30, 2012 at 6:50 am
You have no indexes? No wonder you have poor performance...
Start with these.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
And I agree the database looks like it may need a bit of a redesign. May I suggest that you consider getting someone (a consultant good at optimisation) to redesign and tune the DB?
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
May 30, 2012 at 7:23 am
I have no idea about indexing actually...
can you please tell how it can implement on database.
step i followed to search :-
1. I opened the table design.
2. right click on primary key id it shown indexes/keys, fulltext index and xml index.
i am not able to get to which key index we need to create and how? also how to check on performance part?
Thanks & Regards,
Pallavi
May 30, 2012 at 7:34 am
pallavi.unde (5/30/2012)
I have no idea about indexing actually...can you please tell how it can implement on database.
Did you read the articles?
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
May 30, 2012 at 7:36 am
Mostly i need to create index on primary key but.... primary key is irself a clustered index.. is there any index which work fast on primary key.
Thanks & Regards,
Pallavi
May 30, 2012 at 7:40 am
A primary key is backed by an index, you wouldn't create a second index on the same column, serves no point and wastes space
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
May 30, 2012 at 7:48 am
Ok i will read the articles if any doubt then again will ask you... please support me for that.
Thanks & Regards,
Pallavi
May 30, 2012 at 8:15 am
GilaMonster (5/30/2012)
May I suggest that you consider getting someone (a consultant good at optimisation) to redesign and tune the DB?
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
May 31, 2012 at 8:02 am
GilaMonster (5/30/2012)
GilaMonster (5/30/2012)
May I suggest that you consider getting someone (a consultant good at optimisation) to redesign and tune the DB?
Agreed. The complexity of this one alone goes way beyond forum assistance, and adding in the total lack of knowledge about indexing and the OP has no hope of success here without professional help. And OP, I meant no disrespect with that - it is a statement that is based on the known information based on 15+ years of database experience. People not trained to fly an airplane can't do that either.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply