Почему время доступа к базе данных меняется?

Question

Для одного из моих приложений я использую SQL Server 2005 и запускаю его на локальном хосте. Когда я обращаюсь к нему через приложение, требуется 1-10 секунд, чтобы начать показывать данные, если я обращаюсь к нему на локальном хосте. Тем не менее, когда я удаляюсь от localhost и обращаюсь к нему с другого компьютера в сети, время доступа составляет около одной секунды.

Почему, и как я могу исправить это, чтобы у меня было одинаковое время доступа на локальном хосте?

Строка подключения:Data Source=THESTUDIO\SQLEXPRESS;Initial Catalog=RentalEase;Integrated Security=True

Запросы с использованием типизированных наборов данных (в основном)

При загрузке формы я обнаружил, что Visual Studio несколько раз запрашивал базу данных с одним и тем же запросом. Хотя из того, что я понимаю, база данных ответит кэшированной версией запроса. Однако я обнаружил, что приложение (из функций привязки данных в winform) выполняет этот запрос:

exec sp_executesql N'UPDATE [tblTenant] SET [TenantStatusID] = @TenantStatusID, [PropertyID] = @PropertyID, [UnitID] = @UnitID, [TenantName] = @TenantName, [Sex] = @Sex, [BirthDate] = @BirthDate, [SSNO] = @SSNO, [CoTenant1] = @CoTenant1, [CoTenant1Sex] = @CoTenant1Sex, [CoTenant1BirthDate] = @CoTenant1BirthDate, [CoTenant1SSNO] = @CoTenant1SSNO, [CoTenant2] = @CoTenant2, [CoTenant2Sex] = @CoTenant2Sex, [CoTenant2BirthDate] = @CoTenant2BirthDate, [CoTenant2SSNO] = @CoTenant2SSNO, [CoTenant3] = @CoTenant3, [CoTenant3Sex] = @CoTenant3Sex, [CoTenant3BirthDate] = @CoTenant3BirthDate, [CoTenant3SSNO] = @CoTenant3SSNO, [CarColor] = @CarColor, [CarModel] = @CarModel, [CarYear] = @CarYear, [CarState] = @CarState, [CarPlateNumber] = @CarPlateNumber, [Memo] = @Memo, [Address1] = @Address1, [Address2] = @Address2, [Address3] = @Address3, [Address4] = @Address4, [Phone] = @Phone, [ReferBy] = @ReferBy, [BeginDate] = @BeginDate, [NoticeGiven] = @NoticeGiven, [LeaseMonth2Month] = @LeaseMonth2Month, [LeaseEnds] = @LeaseEnds, [DepositPaid] = @DepositPaid, [DepositRefundable] = @DepositRefundable, [RefundMemo] = @RefundMemo, [RentDueDay] = @RentDueDay, [Charge1] = @Charge1, [Charge1Amount] = @Charge1Amount, [Charge2] = @Charge2, [Charge2Amount] = @Charge2Amount, [Charge3] = @Charge3, [Charge3Amount] = @Charge3Amount, [Charge4] = @Charge4, [Charge4Amount] = @Charge4Amount WHERE (([ID] = @Original_ID) AND ([TenantStatusID] = @Original_TenantStatusID) AND ((@IsNull_PropertyID = 1 AND [PropertyID] IS NULL) OR ([PropertyID] = @Original_PropertyID)) AND ((@IsNull_UnitID = 1 AND [UnitID] IS NULL) OR ([UnitID] = @Original_UnitID)) AND ((@IsNull_TenantName = 1 AND [TenantName] IS NULL) OR ([TenantName] = @Original_TenantName)) AND ((@IsNull_Sex = 1 AND [Sex] IS NULL) OR ([Sex] = @Original_Sex)) AND ((@IsNull_BirthDate = 1 AND [BirthDate] IS NULL) OR ([BirthDate] = @Original_BirthDate)) AND ((@IsNull_SSNO = 1 AND [SSNO] IS NULL) OR ([SSNO] = @Original_SSNO)) AND ((@IsNull_CoTenant1 = 1 AND [CoTenant1] IS NULL) OR ([CoTenant1] = @Original_CoTenant1)) AND ((@IsNull_CoTenant1Sex = 1 AND [CoTenant1Sex] IS NULL) OR ([CoTenant1Sex] = @Original_CoTenant1Sex)) AND ((@IsNull_CoTenant1BirthDate = 1 AND [CoTenant1BirthDate] IS NULL) OR ([CoTenant1BirthDate] = @Original_CoTenant1BirthDate)) AND ((@IsNull_CoTenant1SSNO = 1 AND [CoTenant1SSNO] IS NULL) OR ([CoTenant1SSNO] = @Original_CoTenant1SSNO)) AND ((@IsNull_CoTenant2 = 1 AND [CoTenant2] IS NULL) OR ([CoTenant2] = @Original_CoTenant2)) AND ((@IsNull_CoTenant2Sex = 1 AND [CoTenant2Sex] IS NULL) OR ([CoTenant2Sex] = @Original_CoTenant2Sex)) AND ((@IsNull_CoTenant2BirthDate = 1 AND [CoTenant2BirthDate] IS NULL) OR ([CoTenant2BirthDate] = @Original_CoTenant2BirthDate)) AND ((@IsNull_CoTenant2SSNO = 1 AND [CoTenant2SSNO] IS NULL) OR ([CoTenant2SSNO] = @Original_CoTenant2SSNO)) AND ((@IsNull_CoTenant3 = 1 AND [CoTenant3] IS NULL) OR ([CoTenant3] = @Original_CoTenant3)) AND ((@IsNull_CoTenant3Sex = 1 AND [CoTenant3Sex] IS NULL) OR ([CoTenant3Sex] = @Original_CoTenant3Sex)) AND ((@IsNull_CoTenant3BirthDate = 1 AND [CoTenant3BirthDate] IS NULL) OR ([CoTenant3BirthDate] = @Original_CoTenant3BirthDate)) AND ((@IsNull_CoTenant3SSNO = 1 AND [CoTenant3SSNO] IS NULL) OR ([CoTenant3SSNO] = @Original_CoTenant3SSNO)) AND ((@IsNull_CarColor = 1 AND [CarColor] IS NULL) OR ([CarColor] = @Original_CarColor)) AND ((@IsNull_CarModel = 1 AND [CarModel] IS NULL) OR ([CarModel] = @Original_CarModel)) AND ((@IsNull_CarYear = 1 AND [CarYear] IS NULL) OR ([CarYear] = @Original_CarYear)) AND ((@IsNull_CarState = 1 AND [CarState] IS NULL) OR ([CarState] = @Original_CarState)) AND ((@IsNull_CarPlateNumber = 1 AND [CarPlateNumber] IS NULL) OR ([CarPlateNumber] = @Original_CarPlateNumber)) AND ((@IsNull_Address1 = 1 AND [Address1] IS NULL) OR ([Address1] = @Original_Address1)) AND ((@IsNull_Address2 = 1 AND [Address2] IS NULL) OR ([Address2] = @Original_Address2)) AND ((@IsNull_Address3 = 1 AND [Address3] IS NULL) OR ([Address3] = @Original_Address3)) AND ((@IsNull_Address4 = 1 AND [Address4] IS NULL) OR ([Address4] = @Original_Address4)) AND ((@IsNull_Phone = 1 AND [Phone] IS NULL) OR ([Phone] = @Original_Phone)) AND ((@IsNull_ReferBy = 1 AND [ReferBy] IS NULL) OR ([ReferBy] = @Original_ReferBy)) AND ((@IsNull_BeginDate = 1 AND [BeginDate] IS NULL) OR ([BeginDate] = @Original_BeginDate)) AND ((@IsNull_NoticeGiven = 1 AND [NoticeGiven] IS NULL) OR ([NoticeGiven] = @Original_NoticeGiven)) AND ((@IsNull_LeaseMonth2Month = 1 AND [LeaseMonth2Month] IS NULL) OR ([LeaseMonth2Month] = @Original_LeaseMonth2Month)) AND ((@IsNull_LeaseEnds = 1 AND [LeaseEnds] IS NULL) OR ([LeaseEnds] = @Original_LeaseEnds)) AND ((@IsNull_DepositPaid = 1 AND [DepositPaid] IS NULL) OR ([DepositPaid] = @Original_DepositPaid)) AND ((@IsNull_DepositRefundable = 1 AND [DepositRefundable] IS NULL) OR ([DepositRefundable] = @Original_DepositRefundable)) AND ((@IsNull_RentDueDay = 1 AND [RentDueDay] IS NULL) OR ([RentDueDay] = @Original_RentDueDay)) AND ((@IsNull_Charge1 = 1 AND [Charge1] IS NULL) OR ([Charge1] = @Original_Charge1)) AND ((@IsNull_Charge1Amount = 1 AND [Charge1Amount] IS NULL) OR ([Charge1Amount] = @Original_Charge1Amount)) AND ((@IsNull_Charge2 = 1 AND [Charge2] IS NULL) OR ([Charge2] = @Original_Charge2)) AND ((@IsNull_Charge2Amount = 1 AND [Charge2Amount] IS NULL) OR ([Charge2Amount] = @Original_Charge2Amount)) AND ((@IsNull_Charge3 = 1 AND [Charge3] IS NULL) OR ([Charge3] = @Original_Charge3)) AND ((@IsNull_Charge3Amount = 1 AND [Charge3Amount] IS NULL) OR ([Charge3Amount] = @Original_Charge3Amount)) AND ((@IsNull_Charge4 = 1 AND [Charge4] IS NULL) OR ([Charge4] = @Original_Charge4)) AND ((@IsNull_Charge4Amount = 1 AND [Charge4Amount] IS NULL) OR ([Charge4Amount] = @Original_Charge4Amount)));
SELECT ID, TenantStatusID, PropertyID, UnitID, TenantName, Sex, BirthDate, SSNO, CoTenant1, CoTenant1Sex, CoTenant1BirthDate, CoTenant1SSNO, CoTenant2, CoTenant2Sex, CoTenant2BirthDate, CoTenant2SSNO, CoTenant3, CoTenant3Sex, CoTenant3BirthDate, CoTenant3SSNO, CarColor, CarModel, CarYear, CarState, CarPlateNumber, Memo, Address1, Address2, Address3, Address4, Phone, ReferBy, BeginDate, NoticeGiven, LeaseMonth2Month, LeaseEnds, DepositPaid, DepositRefundable, RefundMemo, RentDueDay, Charge1, Charge1Amount, Charge2, Charge2Amount, Charge3, Charge3Amount, Charge4, Charge4Amount FROM tblTenant WHERE (ID = @ID)',N'@TenantStatusID int,@PropertyID int,@UnitID int,@TenantName nvarchar(4),@Sex nvarchar(1),@BirthDate datetime,@SSNO nvarchar(9),@CoTenant1 nvarchar(4000),@CoTenant1Sex nvarchar(4000),@CoTenant1BirthDate datetime,@CoTenant1SSNO nvarchar(4000),@CoTenant2 nvarchar(4000),@CoTenant2Sex nvarchar(4000),@CoTenant2BirthDate datetime,@CoTenant2SSNO nvarchar(4000),@CoTenant3 nvarchar(2),@CoTenant3Sex nvarchar(4000),@CoTenant3BirthDate datetime,@CoTenant3SSNO nvarchar(4000),@CarColor nvarchar(4000),@CarModel nvarchar(4000),@CarYear nvarchar(4000),@CarState nvarchar(4000),@CarPlateNumber nvarchar(7),@Memo nvarchar(4000),@Address1 nvarchar(10),@Address2 nvarchar(10),@Address3 nvarchar(10),@Address4 nvarchar(10),@Phone nvarchar(4000),@ReferBy nvarchar(9),@BeginDate datetime,@NoticeGiven datetime,@LeaseMonth2Month bit,@LeaseEnds datetime,@DepositPaid money,@DepositRefundable money,@RefundMemo nvarchar(4000),@RentDueDay nvarchar(4000),@Charge1 int,@Charge1Amount money,@Charge2 int,@Charge2Amount money,@Charge3 int,@Charge3Amount money,@Charge4 int,@Charge4Amount money,@Original_ID int,@Original_TenantStatusID int,@IsNull_PropertyID int,@Original_PropertyID int,@IsNull_UnitID int,@Original_UnitID int,@IsNull_TenantName int,@Original_TenantName nvarchar(4),@IsNull_Sex int,@Original_Sex nvarchar(1),@IsNull_BirthDate int,@Original_BirthDate datetime,@IsNull_SSNO int,@Original_SSNO nvarchar(9),@IsNull_CoTenant1 int,@Original_CoTenant1 nvarchar(4000),@IsNull_CoTenant1Sex int,@Original_CoTenant1Sex nvarchar(4000),@IsNull_CoTenant1BirthDate int,@Original_CoTenant1BirthDate datetime,@IsNull_CoTenant1SSNO int,@Original_CoTenant1SSNO nvarchar(4000),@IsNull_CoTenant2 int,@Original_CoTenant2 nvarchar(4000),@IsNull_CoTenant2Sex int,@Original_CoTenant2Sex nvarchar(4000),@IsNull_CoTenant2BirthDate int,@Original_CoTenant2BirthDate datetime,@IsNull_CoTenant2SSNO int,@Original_CoTenant2SSNO nvarchar(4000),@IsNull_CoTenant3 int,@Original_CoTenant3 nvarchar(2),@IsNull_CoTenant3Sex int,@Original_CoTenant3Sex nvarchar(4000),@IsNull_CoTenant3BirthDate int,@Original_CoTenant3BirthDate datetime,@IsNull_CoTenant3SSNO int,@Original_CoTenant3SSNO nvarchar(4000),@IsNull_CarColor int,@Original_CarColor nvarchar(4000),@IsNull_CarModel int,@Original_CarModel nvarchar(4000),@IsNull_CarYear int,@Original_CarYear nvarchar(4000),@IsNull_CarState int,@Original_CarState nvarchar(4000),@IsNull_CarPlateNumber int,@Original_CarPlateNumber nvarchar(7),@IsNull_Address1 int,@Original_Address1 nvarchar(10),@IsNull_Address2 int,@Original_Address2 nvarchar(10),@IsNull_Address3 int,@Original_Address3 nvarchar(10),@IsNull_Address4 int,@Original_Address4 nvarchar(10),@IsNull_Phone int,@Original_Phone nvarchar(4000),@IsNull_ReferBy int,@Original_ReferBy nvarchar(9),@IsNull_BeginDate int,@Original_BeginDate datetime,@IsNull_NoticeGiven int,@Original_NoticeGiven datetime,@IsNull_LeaseMonth2Month int,@Original_LeaseMonth2Month bit,@IsNull_LeaseEnds int,@Original_LeaseEnds datetime,@IsNull_DepositPaid int,@Original_DepositPaid money,@IsNull_DepositRefundable int,@Original_DepositRefundable money,@IsNull_RentDueDay int,@Original_RentDueDay nvarchar(4000),@IsNull_Charge1 int,@Original_Charge1 int,@IsNull_Charge1Amount int,@Original_Charge1Amount money,@IsNull_Charge2 int,@Original_Charge2 int,@IsNull_Charge2Amount int,@Original_Charge2Amount money,@IsNull_Charge3 int,@Original_Charge3 int,@IsNull_Charge3Amount int,@Original_Charge3Amount money,@IsNull_Charge4 int,@Original_Charge4 int,@IsNull_Charge4Amount int,@Original_Charge4Amount money,@ID int',@TenantStatusID=1,@PropertyID=111,@UnitID=2175,@TenantName=N'Judy',@Sex=N'1',@BirthDate='Apr 29 1980  3:26:06:000PM',@SSNO=N'333333333',@CoTenant1=NULL,@CoTenant1Sex=NULL,@CoTenant1BirthDate=NULL,@CoTenant1SSNO=NULL,@CoTenant2=NULL,@CoTenant2Sex=NULL,@CoTenant2BirthDate=NULL,@CoTenant2SSNO=NULL,@CoTenant3=N'51',@CoTenant3Sex=NULL,@CoTenant3BirthDate=NULL,@CoTenant3SSNO=NULL,@CarColor=NULL,@CarModel=NULL,@CarYear=NULL,@CarState=NULL,@CarPlateNumber=N'001122B',@Memo=NULL,@Address1=N'blah line1',@Address2=N'blah line2',@Address3=N'blah line3',@Address4=N'blah line4',@Phone=NULL,@ReferBy=N'McDonalds',@BeginDate='May  6 2009  4:54:28:000PM',@NoticeGiven='May  6 2009 11:58:45:000AM',@LeaseMonth2Month=1,@LeaseEnds='May 21 2009 10:06:25:000AM',@DepositPaid=50.0000,@DepositRefundable=0.0000,@RefundMemo=NULL,@RentDueDay=NULL,@Charge1=6,@Charge1Amount=50.0000,@Charge2=49,@Charge2Amount=50.0000,@Charge3=45,@Charge3Amount=5.0000,@Charge4=47,@Charge4Amount=7.0000,@Original_ID=10,@Original_TenantStatusID=1,@IsNull_PropertyID=0,@Original_PropertyID=111,@IsNull_UnitID=0,@Original_UnitID=2175,@IsNull_TenantName=0,@Original_TenantName=N'Judy',@IsNull_Sex=0,@Original_Sex=N'1',@IsNull_BirthDate=0,@Original_BirthDate='Apr 29 1980  3:26:06:000PM',@IsNull_SSNO=0,@Original_SSNO=N'333333333',@IsNull_CoTenant1=1,@Original_CoTenant1=NULL,@IsNull_CoTenant1Sex=1,@Original_CoTenant1Sex=NULL,@IsNull_CoTenant1BirthDate=1,@Original_CoTenant1BirthDate=NULL,@IsNull_CoTenant1SSNO=1,@Original_CoTenant1SSNO=NULL,@IsNull_CoTenant2=1,@Original_CoTenant2=NULL,@IsNull_CoTenant2Sex=1,@Original_CoTenant2Sex=NULL,@IsNull_CoTenant2BirthDate=1,@Original_CoTenant2BirthDate=NULL,@IsNull_CoTenant2SSNO=1,@Original_CoTenant2SSNO=NULL,@IsNull_CoTenant3=0,@Original_CoTenant3=N'51',@IsNull_CoTenant3Sex=1,@Original_CoTenant3Sex=NULL,@IsNull_CoTenant3BirthDate=1,@Original_CoTenant3BirthDate=NULL,@IsNull_CoTenant3SSNO=1,@Original_CoTenant3SSNO=NULL,@IsNull_CarColor=1,@Original_CarColor=NULL,@IsNull_CarModel=1,@Original_CarModel=NULL,@IsNull_CarYear=1,@Original_CarYear=NULL,@IsNull_CarState=1,@Original_CarState=NULL,@IsNull_CarPlateNumber=0,@Original_CarPlateNumber=N'001122B',@IsNull_Address1=0,@Original_Address1=N'blah line1',@IsNull_Address2=0,@Original_Address2=N'blah line2',@IsNull_Address3=0,@Original_Address3=N'blah line3',@IsNull_Address4=0,@Original_Address4=N'blah line4',@IsNull_Phone=1,@Original_Phone=NULL,@IsNull_ReferBy=0,@Original_ReferBy=N'McDonalds',@IsNull_BeginDate=0,@Original_BeginDate='May  6 2009  4:54:28:000PM',@IsNull_NoticeGiven=0,@Original_NoticeGiven='May  6 2009 11:58:45:000AM',@IsNull_LeaseMonth2Month=0,@Original_LeaseMonth2Month=1,@IsNull_LeaseEnds=0,@Original_LeaseEnds='May 21 2009 10:06:25:000AM',@IsNull_DepositPaid=0,@Original_DepositPaid=50.0000,@IsNull_DepositRefundable=0,@Original_DepositRefundable=0.0000,@IsNull_RentDueDay=1,@Original_RentDueDay=NULL,@IsNull_Charge1=0,@Original_Charge1=6,@IsNull_Charge1Amount=0,@Original_Charge1Amount=50.0000,@IsNull_Charge2=0,@Original_Charge2=49,@IsNull_Charge2Amount=0,@Original_Charge2Amount=50.0000,@IsNull_Charge3=0,@Original_Charge3=45,@IsNull_Charge3Amount=0,@Original_Charge3Amount=5.0000,@IsNull_Charge4=0,@Original_Charge4=47,@IsNull_Charge4Amount=0,@Original_Charge4Amount=7.0000,@ID=10

На это уходит около полной секунды, чтобы завершить. Что я могу с этим поделать?

Answer 1

Вы можете подключить Sql Profiler к серверу, чтобы увидеть, что именно происходит, и действительно ли операторы Sql идентичны.

Если они есть, и есть разница, возможно, одно из приложений использует другой план выполнения. Очистите кеш с помощью:

DBCC FREEPROCCACHE

И проверьте, если это имеет значение.

Много других предложений в этом посте.

Answer 2

Я предполагаю, что 10 секунд - это количество времени, необходимое для запуска (редко используемой) базы данных на локальном компьютере. На самом деле я не пытался это исправить, но первым делом я бы попытался отключить свойство "Auto close" для этой базы данных.

Более подробное описание свойства "Auto close" см. В этом отрывке из книги "Проектирование баз данных SQL Server 2000 для.NET Enterprise Servers".

Answer 3

Что еще делает ваш локальный компьютер? Как вы спрашиваете? Я был бы удивлен, если задержка в базе данных, если другие загруженные приложения не вызывают обмен.

Answer 4

Возможно, локальный и удаленный компьютеры имеют разные настройки сетевого протокола клиента. Например. локальный компьютер настроен на попытку сначала использовать совместно используемую память, затем net-pipe, а затем tcp, но сервер настроен так, чтобы не прослушивать совместно используемую память (необычно, но возможно). Удаленные машины настроены на непосредственное использование tcp, поэтому они получают джекпот с первой попытки. Просто выстрел в темноте, без дополнительной информации.

Сначала следует проанализировать проблему и определить, где происходит задержка в 10 секунд: при открытии соединения, при выполнении запроса, при возврате результата клиенту и т. Д.

Answer 5

Это может быть связано с конкретными протоколами связи, которые SQL Server пытается использовать на локальном компьютере.

Вам необходимо опубликовать - строку подключения - протоколы, которые включены на вашем сервере (из диспетчера конфигурации SQL Server).