|
Здравствуйте. Система: MS SQL Server 2000 и Visual FoxPro 8.0. Проблема следующая – запрос оформленный в ХП работает очень медленно (где-то 25 секунд)
CREATE PROCEDURE CurListHousePassport @IDPersAcc_Shadow INT, @CurDateWork_Shadow DATETIME, @UserID_Shadow INT AS
/*ХП возвращает список записей для паспортного стола за текущую дату @CurDateWork_Shadow*/
SET DATEFORMAT DMY
SELECT PersAcc.NumberPA, RTRIM(Roomer.Surname) + ' ' + RTRIM(Roomer.Name) + ' ' + RTRIM(Roomer.Patronymic) AS FIO,
House.CodeHouse, RTRIM(House.AddrStreet) + ' д.' + RTRIM(House.AddrHouse) + ' к.' + RTRIM(House.AddrFrame) +
' кв.' + RTRIM(DetailsPersAcc.NumberFlat) AS Address, PersAcc.NumberJEU, House.Oblast,
House.Region, House.Settlement, House.AddrStreet, House.AddrHouse, House.AddrFrame,
DetailsPersAcc.NumberFlat, DetailsPersAcc.AmountRoom, DetailsPersAcc.AmountSeparateRoom,
DetailsPersAcc.AmountRoomers, DetailsPersAcc.AmountPrivileger, DetailsPersAcc.AmountTenants,
DetailsPersAcc.Floor, DetailsPersAcc.TotalSpace, DetailsPersAcc.FloorSpace, DetailsPersAcc.PaySpace,
DetailsPersAcc.KitchenSpace, DetailsPersAcc.HeatSpace, DetailsPersAcc.Phone,
DetailsPersAcc.NumberOrder, DetailsPersAcc.DateOrder, DetailsPersAcc.DetailsOrder,
DetailsPersAcc.Balcony, DetailsPersAcc.TypeRoom, House.AmountFloor, House.Material,
DetailsPersAcc.IDHouse, Roomer.Surname, Roomer.Name, Roomer.Patronymic, Roomer.Sex, Roomer.Alliance,
Roomer.Nationality, Roomer.Birthday, Roomer.Master, Roomer.NumberPassport, Roomer.SeriesPassport,
Roomer.OrganizationPassport, Roomer.DateIssuePassport, Roomer.DateValidPassport, Roomer.Conscript,
Roomer.OblastBirthday, Roomer.RegionBirthday, Roomer.CityBirthday, Roomer.Citizenship,
Roomer.NumberPassportID, Roomer.WorkPlace, Roomer.Children, RoomerArrival.DateArrival,
RoomerArrival.OblastArrival, RoomerArrival.RegionArrival, RoomerArrival.CityArrival,
RoomerArrival.AddrStreetArrival, RoomerArrival.AddrHouseArrival, RoomerArrival.AddrFrameArrival,
RoomerArrival.AddrFlatArrival, PersAcc.ID AS IDPA, PersAcc.IDPersAcc, Roomer.ID AS IDR,
RoomerArrival.ID AS IDRA, House.ID AS IDH
FROM PersAcc
INNER JOIN Roomer
ON PersAcc.IDPersAcc = Roomer.IDPersAcc
INNER JOIN RoomerArrival
ON PersAcc.IDPersAcc = RoomerArrival.IDPersAcc
INNER JOIN DetailsPersAcc
ON PersAcc.IDPersAcc = DetailsPersAcc.IDPersAcc
INNER JOIN House
ON DetailsPersAcc.IDHouse = House.IDHouse
WHERE (@CurDateWork_Shadow BETWEEN PersAcc.DateStart AND PersAcc.DateEnd)
AND (@CurDateWork_Shadow <> PersAcc.DateEnd)
AND (@CurDateWork_Shadow BETWEEN Roomer.DateStart AND Roomer.DateEnd)
AND (@CurDateWork_Shadow <> Roomer.DateEnd) AND (Roomer.Master = 1)
AND (@CurDateWork_Shadow BETWEEN RoomerArrival.DateStart AND RoomerArrival.DateEnd)
AND (@CurDateWork_Shadow <> RoomerArrival.DateEnd) AND (RoomerArrival.Master = 1)
AND (@CurDateWork_Shadow BETWEEN DetailsPersAcc.DateStart AND DetailsPersAcc.DateEnd)
AND (@CurDateWork_Shadow <> DetailsPersAcc.DateEnd)
AND (@CurDateWork_Shadow BETWEEN House.DateStart AND House.DateEnd)
AND (@CurDateWork_Shadow <> House.DateEnd)
GO
В результате выполнения запроса получается 4916 записей (таблицы PersAcc, DetailsPersAcc, Roomer, RoomerArrival, RoomerDeparture имеют тоже по 4916 записей, только таблица House имеет 50 записей). Я использую в форме (form1) в DataEnvironment специальный CursorAdapter1 с алиасом – housepassport в который, по свойству AutoOpen получаю данные следующим способом:
stringSQL = "EXEC CurListHousePassport ?CurrentDateWorking, ?UserID"
sqlResult = SQLEXEC(connectHandle2, stringSQL, "housepassport")
IF sqlResult < 0
= MESSAGEBOX('Error – CurListHousePassport', 16, ‘Error, Query SQL')
ENDIF
А на форме form1 есть Grid1, к которому я в RecordSource прописываю housepassport (т.е. полученные данные)
Так вот я при нажатии кнопки ”добавить” на форме form1 вызывается другая форма (form2), в которой вызывается ХП AddPersAcc и происходит опять обновление курсора housepassport:
THISFORM.LockScreen = .T.
THISFORM.Grid1.RecordSource = ""
stringSQL = "EXEC CurListHousePassport?CurrentDateWorking, ?UserID"
sqlResult = SQLEXEC(connectHandle2, stringSQL, "housepassport")
IF sqlResult < 0
= MESSAGEBOX('Error - CurListHousePassport', 16, 'Error SQL')
ELSE
SELECT housepassport
LOCATE FOR housepassport.IDPA = uniqIDPersAcc
ENDIF
THISFORM.Grid1.RecordSource = "housepassport"
stringSQL = "EXEC CurListHumanPassport?CurrentDateWorking, ?UserID"
sqlResult = SQLEXEC(connectHandle2, stringSQL, "humanpassport")
IF sqlResult < 0
= MESSAGEBOX('Error - CurListHumanPassport', 16, 'Error SQL')
ENDIF
SELECT housepassport
THISFORM.Refresh()
THISFORM.LockScreen = .F.
Есть ли какие-либо варианты решения этой проблемы, т.е. ускорить выполнение запроса?
|
