Wait Operation Timed Out by a stored procedure on azure server

Transact-SQL
1

Hi
I am using stored procedure to fetch the data as the stored procedure gives an exception like "Wait operation Timed out " oon azure server but the same stored procedure is working fine on dev server as well on my local machine. What should i do?

here is my stored procedure

CREATE Proc USP_GetUserDetailByID
@id int
As
Begin
Select distinct
uinfo.ID ID,
uinfo.UniqueUserID UniqueUserID,
uinfo.ID UserInfoID,
uinfo.ReferredID ReferredID,
uinfo.UserTypeID UserTypeID,
uinfo.GroomBrideName GroomBrideName,
uinfo.EmailID EmailID,
uinfo.[Password] [Password],
uinfo.ProfileCreatedFor ProfileCreatedFor,
(Select DetailData from MasterDetails where ID = uinfo.ProfileCreatedFor) ProfileCreatedText,
uinfo.DateOfBirth DateOfBirth,
uinfo.Gender Gender,
uinfo.Age Age,
uinfo.Height Height,
(Select DetailData from MasterDetails where ID = uinfo.Height) HeightText,
(Select ID from MasterDetails where ID = uinfo.MaritalStatus) MaritalStatus,
(Select DetailData from MasterDetails where ID = uinfo.MaritalStatus) MaritalStatusText,
uinfo.City City,
(Select ID from MasterDetails where ID = uinfo.[State]) [State],
CASE WHEN uinfo.City is null THEN '' ELSE (SELECT DetailData FROM MasterDetails WHERE ID = uinfo.City) END as CityText,
CASE WHEN uinfo.[State] is null THEN '' ELSE (SELECT DetailData FROM MasterDetails WHERE ID = uinfo.[State]) END as StateText,
uinfo.Country Country,
(Select MasterCategoryID from MasterDetails where ID = uinfo.Country) CountryId,
(Select DetailData from MasterDetails where ID = uinfo.Country) CountryText,
uinfo.Phone Phone,
uinfo.Mobile Mobile,
uinfo.RegisteredMobileNumber RegisteredMobileNumber,
uinfo.About About,
uinfo.[Status] [Status],
rel.Religion Religion,
rel.MotherTongue MotherTongue,
(Select DetailData from MasterDetails where ID = rel.MotherTongue) MotherTongueText,
rel.Section Section,
(Select DetailData from MasterDetails where ID = rel.Section) SectionText,
rel.Division Division,
(Select DetailData from MasterDetails where ID = rel.Division) DivisonText,
edu.EducationLevel EducationalLevel,
(Select DetailData from MasterDetails where ID = edu.EducationLevel) EducationalLevelText,
edu.EducationFeild EducationalField,
(Select DetailData from MasterDetails where ID = edu.EducationFeild) EducationalFeildText,
edu.GraduationDegree GraduationDegree,
(Select DetailData from MasterDetails where ID = edu.GraduationDegree) GraduationDegreeText,
edu.MasterDegree MasterDegree,
(Select DetailData from MasterDetails where ID = edu.MasterDegree) MasterDegreeText,
edu.WorkWith WorkWith,
(Select DetailData from MasterDetails where ID = edu.WorkWith) WOrkingWithText,
edu.WorkingGroup WorkingGroup,
(Select DetailData from MasterDetails where ID = edu.WorkingGroup) WorkingGroupText,
edu.WorkingAs WorkingAs,
(Select DetailData from MasterDetails where ID = edu.WorkingAs) WorkingAsText,
edu.AnnualIncome AnnualIncome,
(Select DetailData from MasterDetails where ID = edu.AnnualIncome) AnnualIncomeText,
edu.InterestedInSettlingAbroad InsertedInSettingAbroad,
hob.Hobbies Hobbies,
(Select DetailData from MasterDetails where ID = hob.Hobbies) HobbiesText,
hob.DressStyle,
(Select DetailData from MasterDetails where ID = hob.DressStyle) DressStyleText,
hob.SportsFitness SportsFitness,
(Select DetailData from MasterDetails where ID = hob.SportsFitness) SportsFitnessText,
hob.FavouriteCuisine FavouriteCuisine,
(Select DetailData from MasterDetails where ID = hob.FavouriteCuisine) FavouriteCuisineText,
hob.FavouriteVacationDestination FavouriteVacationDestination,
hob.FavouriteMusic FavouriteMusic,
(Select DetailData from MasterDetails where ID = hob.FavouriteMusic) FavouriteMusicText,
hob.FavouriteBooks FavouriteBooks,
hob.PreferredMovies PreferredMovies,
(Select DetailData from MasterDetails where ID = hob.PreferredMovies) PreferredMoviesText,
life.Diet Diet,
(Select DetailData from MasterDetails where ID = life.Diet) DietText,
life.Smoke Smoke,
life.Drink Drink,
life.SkinTone Complexion,
(Select DetailData from MasterDetails where ID = life.SkinTone) ComplexionText,
life.BodyType BodyType,
(Select DetailData from MasterDetails where ID = life.BodyType) BodyTypeText,
life.BloodGroup BloodGroup,
life.[Weight] [Weight],
life.ResidentialStatus ResidentialStatus,
life.OwnHouse OwnHouse,
life.OwnCar OwnCar,
life.LanguageKnown LanguageKnown,
fd.FamilyValue FamilyValue,
(Select DetailData from MasterDetails where ID = fd.FamilyValue) FamilyValueText,
fd.FamilyType FamilyType,
(Select DetailData from MasterDetails where ID = fd.FamilyType) FamilyTypeText,
fd.FamilyStatus FamilyStatus,
(Select DetailData from MasterDetails where ID = fd.FamilyStatus) FamilyStatusText,
fd.Father Father,
(Select DetailData from MasterDetails where ID = fd.Father) FatherText,
fd.Mother Mother,
(Select DetailData from MasterDetails where ID = fd.Mother) MotherText,
fd.Brothers Brothers,
fd.MarriedBrothers MarriedBrothers,
fd.Sisters Sisters,
fd.MarriedSisters MarriedSisters,
fd.LivingWithParents LivingWithParents,
rel.ReligiousValues ReligiousValues,
uinfo.HightLightUserAbout HightLightUserAbout,
uinfo.FbUserDetail FbUserDetail

from UsersInfo uinfo 
left join EducationAndOccupation edu on uinfo.ID = edu.UserID 
left join ReligionAndEthinicity rel on uinfo.ID = rel.UserID
left join HobbiesAndInterests hob on uinfo.ID = hob.UserID
left join LifestyleAndAttributes life on uinfo.ID = life.UserID
left join FamilyDetails fd on uinfo.ID = fd.UserID
where uinfo.ID = @id
order by uinfo.ID desc

End

2

all of these calls to MasterDetails will kill you!
is your dev machine's data a restore from your product aws system?

what the world is this?? cleartext password???
uinfo.[Password] [Password]

Is this a learning database or is this real?

3

This is the real data Base and what should i do to overcome this problem?
I just need to get the data from MasterDetail Table So now what can i do to solve this?

4

answer these questions please?
You said "On azure server but the same stored procedure is working fine on dev server"

  1. Does your dev server have the same data restored to it from azure server
  2. Does your local machine have the same data restored to it from azure server
5

No
on Dev server and local machine there are something 100 records but on azure server i've something 450 records . So both are different.

6

honestly your database design, if it is indeed real life, does not lend itself to good performance in fact it does hw opposite. and on top of that you are doing so many inline queries to the same table it is manifesting itself as a timeout.
short of a total rewrite I am not sure how you can improve this query.
it is a very unusual design to me. i could be very wrong.
for example you have a table named religionandethnicity assuming ones religion is directly associated with their ethnicity. what if they have no religion? does it mean they have no race? or will you have for example value for ethnicity column but religion column will be null or blank.
just not very well designed database to me.

1 Like
7

@yosiasz You are absolutely right but have you any idea about avoiding the inline query and a part from this my problem has been solved as i have placed all the inline queries into the last of the stored procedure and now its working fine.

8

other than totally redoing your database/table design you could try this
notice what I did with FamilyDetails. try the same for all of the other tables where you are getting the details for

select blahblah, fd.FamilyValueText
from UsersInfo uinfo 
left join EducationAndOccupation edu on uinfo.ID = edu.UserID 
left join ReligionAndEthinicity rel on uinfo.ID = rel.UserID
left join HobbiesAndInterests hob on uinfo.ID = hob.UserID
left join LifestyleAndAttributes life on uinfo.ID = life.UserID
left join (Select DetailData , fd.UserID, FamilyValueText
                  from dbo.MasterDetails md
                    join dbo.FamilyDetails fd on md.ID = fd.FamilyValue) ,
                 ) fd on uinfo.ID = fd.UserID
where uinfo.ID = @id