|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-09-06 : 09:22:28
|
| Dave writes "Hi Guys. I have a stored procedure that selects a single record from a series of tables, and returns it to an ADO recordset.Running the stored proc on our dev server through Query Analyser, it executes and returns the reords in less than 2seconds. However, having moved the Procedure to another server, it is taking approx 41/2 minutes to execute the same procedure.There are a large amount of Joins to the same table, a lookup table to identify types. My immediate thought is that since this table is being accessed 26 times in the same select statement, the record locking is occuring at the table level on the one server, and at record level on the development server. therefore, my question is....How to I check to see what level of locking is specified on the server, and how do I change that?I've added the Procedure in Below in case it is of help!Thanks in Advance for your Help!Dave LongWeb Developer08004homes.comCREATE PROCEDURE pGetPropertyData (@PropertyID nvarchar(64)) ASset nocount onSELECT DISTINCT--property nodep1.PropertyId AS property_id, p1.DataSourceID as DataSourceID,--property|pricing nodel1.LookUpValue AS pricing_offer_status, l2.LookUpValue AS pricing_sale_or_rent, p1.Price AS pricing_local_price, '' AS pricing_language, p1.currency AS pricing_local_currency, '' AS pricing_euro_price,--property|details nodel3.LookUpValue AS details_property_type, l4.LookUpValue AS details_tenure, l5.LookUpValue AS details_construction_status,--property|details|availability nodel6.LookUpValue AS details_availability_rent_due_every, p1.RentalStart AS details_availability_start_date, p1.RentalEnd AS details_availability_end_date,--property|details nodep1.UnitsRemaining AS details_units_remaining, '' AS details_property_agent_site_location, l7.LookUpValue AS details_era, l8.LookUpValue AS details_furnished,--property|details|rooms nodep1.NoOfBathrooms AS details_rooms_bathrooms, p1.NoOfBedrooms AS details_rooms_bedrooms, p1.NoOfReceptions AS details_rooms_receptions,--property|details nodep1.NoOfFloors AS details_number_of_floors, p1.PropertySize AS details_property_area_size, p1.SchoolDetails AS details_nearest_school,--property|features node--property|features|garden nodep1.GardenSize AS features_garden_size, l9.LookUpValue AS features_garden_facing,--property|features|extras nodel10.LookUpValue AS features_extras_air_con, l11.LookUpValue AS features_extras_alarm, l12.LookUpValue AS features_extras_balcony, l13.LookUpValue AS features_extras_central_heating, l14.LookUpValue AS features_extras_controlled_access, l15.LookUpValue AS features_extras_fireplace, l16.LookUpValue AS features_extras_fitted_kitchen, l17.LookUpValue AS [features_extras_off-street_parking], l18.LookUpValue AS features_extras_own_entrance, l19.LookUpValue AS features_extras_phone, l20.LookUpValue AS features_extras_roof_terrace,--property|features|windows nodel21.LookUpValue AS features_windows_double_glazing, l22.LookUpValue AS features_windows_original,--property|features|flooring nodel23.LookUpValue AS features_flooring_stripped_wood, l24.LookUpValue AS features_flooring_carpets,--property|agent nodea1.AgentName AS agent_name, a1.AgentCode AS agent_code,--property|agent|address nodea1.Street AS agent_address_street, '' AS agent_address_locality, a1.Town AS agent_address_town, a1.County AS agent_address_county, a1.Country AS agent_address_country, a1.Postcode AS agent_address_postcode, a1.GridRefEast AS agent_address_easting, a1.GridRefNorth AS agent_address_northing,--property|agent|contact_details nodeac.ContactName AS agent_contact_name, ac.PhoneDay AS agent_contact_phone, ac.Fax AS agent_contact_fax, ac.EmailAddress AS agent_contact_email,--property|agent nodea1.LogoLocation AS agent_logo, a1.URL AS agent_web_site,--property|location |
|
Stored Procedure Timeout