Хранимая процедура "вращается" при выполнении из Visual Studio 2012

ProgramBox

Хранимая процедура «вращается» при выполнении из Visual Studio 2012

Post author:admin
Запись опубликована:24 декабря, 2022
Post category:Вопросы по программированию

#sql-server #visual-studio #stored-procedures

#sql-сервер #visual-studio #хранимые процедуры

Вопрос:

Я написал 3 хранимые процедуры, и все они работают нормально при выполнении в SQL Server. Однако, когда я выполняю их в Visual Studio 2012, они просто «вращаются», Создавая блокировки и т. Д. Есть идеи?

Вот один из них. Другой вариант — выбирать значения из встроенного представления на основе того же SQL и не вставлять в #temp

     @StartDate      As DateTime,
    @EndDate        As DateTime,
    @EmployeeCode   As VarChar(50) = NULL 
AS
BEGIN

SET NOCOUNT ON;


select * into #temp
from 
(SELECT e.Code AS EMPLOYEE_ID,
   e.FirstName AS FIRST_NAME,
   e.LastName AS LAST_NAME,
   ec1.TransactionDate AS DATE_OF_SHIFT,
   ou.Code AS DEPT_UNIT_CODE,
   CASE
       WHEN ec1.Classification = '1' THEN
           MIN(ec1.TransactionDate)
   END AS CLOCK_IN,
   CASE
       WHEN ec2.Classification = '2' THEN
           MAX(ec2.TransactionDate)
   END AS CLOCK_OUT,
   pc.code,
   epb.hourvalue 
FROM APIHealthcare_History_Live.dbo.Employee e (nolock)
JOIN APIHealthcare_Live.dbo.EmployeeClocking ec1 (nolock)
    ON ec1.EmployeeID = e.ID
JOIN APIHealthcare_Live.dbo.EmployeeClocking ec2 (nolock)
    ON ec1.OutClockingGuid = ec2.Guid
JOIN APIHealthcare_Live.dbo.OrganizationUnit ou (nolock)
    ON ec1.OrganizationUnitID = ou.ID
LEFT JOIN APIHealthcare_Live.dbo.EmployeePremiumBucket epb (nolock)
    ON ec1.Guid = epb.TransactionGuid
JOIN APIHealthcare_Live.dbo.PayCode pc (nolock)
    ON epb.PayCodeID = pc.ID
JOIN APIHealthcare_Live.dbo.PayGroupInstance pgi (nolock)
    ON pgi.ID = epb.PayGroupInstanceID
WHERE 

ec1.transactiondate between @StartDate and @EndDate
and epb.PaymentClassification = '1'
AND e.code = @EmployeeCode   

    AND e.ArchiveTaskID =
(
    SELECT MAX(e2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
    WHERE e2.ID = e.ID
)
GROUP BY e.Code,
     e.FirstName,
     e.LastName,
     ec1.TransactionDate,
     ou.Code,
     ec1.Classification,
     ec2.Classification,
     pc.code,
     epb.hourvalue

UNION
SELECT e.Code AS EMPLOYEE_ID,
   e.FirstName AS FIRST_NAME,
   e.LastName AS LAST_NAME,
   ec1.TransactionDate AS DATE_OF_SHIFT,
   ou.Code AS DEPT_UNIT_CODE,
   CASE
       WHEN ec1.Classification = '1' THEN
           MIN(ec1.TransactionDate)
   END AS CLOCK_IN,
   CASE
       WHEN ec2.Classification = '2' THEN
           MAX(ec2.TransactionDate)
   END AS CLOCK_OUT,
   pc.code,
   epb.hourvalue 
FROM APIHealthcare_History_Live.dbo.Employee e (nolock)
JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec1 (nolock)
    ON ec1.EmployeeID = e.ID
JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec2 (nolock)
    ON ec1.OutClockingGuid = ec2.Guid
JOIN APIHealthcare_History_Live.dbo.OrganizationUnit ou (nolock)
    ON ec1.OrganizationUnitID = ou.ID
LEFT JOIN APIHealthcare_History_Live.dbo.EmployeePremiumBucket epb (nolock)
    ON ec1.Guid = epb.TransactionGuid
JOIN APIHealthcare_History_Live.dbo.PayCode pc (nolock)
    ON epb.PayCodeID = pc.ID
JOIN APIHealthcare_History_Live.dbo.PayGroupInstance pgi (nolock)
    ON pgi.ID = epb.PayGroupInstanceID
WHERE 
ec1.transactiondate between @StartDate and @EndDate
and epb.PaymentClassification = '1'
AND e.code = @EmployeeCode     
AND e.ArchiveTaskID =
(
    SELECT MAX(e2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
    WHERE e2.ID = e.ID
)
AND ou.ArchiveTaskID =
(
    SELECT MAX(ou2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.OrganizationUnit ou2 (NOLOCK)
    WHERE ou2.ID = ou.ID
)
AND pc.ArchiveTaskID =
(
    SELECT MAX(pc2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.PayCode pc2 (NOLOCK)
    WHERE pc2.ID = pc.ID
)
                             
GROUP BY e.Code,
     e.FirstName,
     e.LastName,
     ec1.TransactionDate,
     ou.Code,
     ec1.Classification,
     ec2.Classification,
     pc.code,
     epb.hourvalue) as X

select EMPLOYEE_ID, 
FIRST_NAME, 
LAST_NAME,
DATE_OF_SHIFT, 
DEPT_UNIT_CODE,
CLOCK_IN, 
CLOCK_OUT, 
code, 
sum(hourvalue) as Hours
from #temp
group by EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
DATE_OF_SHIFT,
DEPT_UNIT_CODE,
CLOCK_IN,
CLOCK_OUT,
CODE
Order by CLOCK_OUT
END

Вот другой.

     @StartDate      As DateTime,
    @EndDate        As DateTime,
    @EmployeeCode   As VarChar(50) = NULL 
AS
BEGIN

SET NOCOUNT ON;


select EMPLOYEE_ID, 
FIRST_NAME, 
LAST_NAME,
DATE_OF_SHIFT, 
DEPT_UNIT_CODE,
CLOCK_IN, 
CLOCK_OUT, 
code, 
sum(hourvalue) as Hours
from 
(
SELECT e.Code AS EMPLOYEE_ID,
   e.FirstName AS FIRST_NAME,
   e.LastName AS LAST_NAME,
   ec1.TransactionDate AS DATE_OF_SHIFT,
   ou.Code AS DEPT_UNIT_CODE,
   CASE
       WHEN ec1.Classification = '1' THEN
           MIN(ec1.TransactionDate)
   END AS CLOCK_IN,
   CASE
       WHEN ec2.Classification = '2' THEN
           MAX(ec2.TransactionDate)
   END AS CLOCK_OUT,
   pc.code,
   epb.hourvalue 
FROM APIHealthcare_History_Live.dbo.Employee e
JOIN APIHealthcare_Live.dbo.EmployeeClocking ec1
    ON ec1.EmployeeID = e.ID
JOIN APIHealthcare_Live.dbo.EmployeeClocking ec2
    ON ec1.OutClockingGuid = ec2.Guid
JOIN APIHealthcare_Live.dbo.OrganizationUnit ou
    ON ec1.OrganizationUnitID = ou.ID
LEFT JOIN APIHealthcare_Live.dbo.EmployeePremiumBucket epb
    ON ec1.Guid = epb.TransactionGuid
JOIN APIHealthcare_Live.dbo.PayCode pc
    ON epb.PayCodeID = pc.ID
JOIN APIHealthcare_Live.dbo.PayGroupInstance pgi
    ON pgi.ID = epb.PayGroupInstanceID
WHERE ec1.transactiondate between @StartDate and @EndDate 
and epb.PaymentClassification = '1'
AND e.code = @EmployeeCode
AND e.ArchiveTaskID =
(
    SELECT MAX(e2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
    WHERE e2.ID = e.ID
)
GROUP BY e.Code,
     e.FirstName,
     e.LastName,
     ec1.TransactionDate,
     ou.Code,
     ec1.Classification,
     ec2.Classification,
     pc.code,
     epb.hourvalue

UNION
SELECT e.Code AS EMPLOYEE_ID,
   e.FirstName AS FIRST_NAME,
   e.LastName AS LAST_NAME,
   ec1.TransactionDate AS DATE_OF_SHIFT,
   ou.Code AS DEPT_UNIT_CODE,
   CASE
       WHEN ec1.Classification = '1' THEN
           MIN(ec1.TransactionDate)
   END AS CLOCK_IN,
   CASE
       WHEN ec2.Classification = '2' THEN
           MAX(ec2.TransactionDate)
   END AS CLOCK_OUT,
   pc.code,
   epb.hourvalue 
FROM APIHealthcare_History_Live.dbo.Employee e
JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec1
    ON ec1.EmployeeID = e.ID
JOIN APIHealthcare_History_Live.dbo.EmployeeClocking ec2
    ON ec1.OutClockingGuid = ec2.Guid
JOIN APIHealthcare_History_Live.dbo.OrganizationUnit ou
    ON ec1.OrganizationUnitID = ou.ID
LEFT JOIN APIHealthcare_History_Live.dbo.EmployeePremiumBucket epb
    ON ec1.Guid = epb.TransactionGuid
JOIN APIHealthcare_History_Live.dbo.PayCode pc
    ON epb.PayCodeID = pc.ID
JOIN APIHealthcare_History_Live.dbo.PayGroupInstance pgi
    ON pgi.ID = epb.PayGroupInstanceID
WHERE ec1.transactiondate between @StartDate and @EndDate
and epb.PaymentClassification = '1'
AND e.code = @EmployeeCode
AND e.ArchiveTaskID =
(
    SELECT MAX(e2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.Employee e2 (NOLOCK)
    WHERE e2.ID = e.ID
)
AND ou.ArchiveTaskID =
(
    SELECT MAX(ou2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.OrganizationUnit ou2 (NOLOCK)
    WHERE ou2.ID = ou.ID
)
AND pc.ArchiveTaskID =
(
    SELECT MAX(pc2.ArchiveTaskID)
    FROM APIHealthcare_History_Live.dbo.PayCode pc2 (NOLOCK)
    WHERE pc2.ID = pc.ID
)
GROUP BY e.Code,
     e.FirstName,
     e.LastName,
     ec1.TransactionDate,
     ou.Code,
     ec1.Classification,
     ec2.Classification,
     pc.code,
     epb.hourvalue) as X
     
group by
EMPLOYEE_ID, 
FIRST_NAME, 
LAST_NAME,
DATE_OF_SHIFT, 
DEPT_UNIT_CODE,
CLOCK_IN, 
CLOCK_OUT, 
code 
order by date_of_shift, code 
END

1. Существуют «параметры, влияющие на планирование», которые изменяют способ создания планов SQL Query Planner для выполнения запросов, и это может привести к серьезным различиям в производительности между разными клиентами, выполняющими один и тот же код SQL. Сравните настройки SSMS и Visual Studio для ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL и QUOTED_IDENTIFIER. Прочитайте превосходную статью Эрланда Соммарскога » Медленно в приложении, быстро в SSMS?» Понимание загадок производительности , чтобы понять, почему это происходит.

2. Вызывает ли очень простая хранимая процедура SELECT 1 ту же проблему? Я предлагаю вам постепенно добавлять код, пока не найдете часть, вызывающую проблему. Или используйте sp_whoisactive , чтобы определить, какой тип ожидания вызывает проблему.

Ответ №1:

Это разные среды выполнения. И да, уже рекомендуется прочитать статью Соммарскога. Но я думаю, что основной причиной, по которой эти процедуры будут медленными (и не последовательными), является UNION оператор. Вы должны использовать UNION ALL . Во-вторых, избавьтесь от этих (nolock) подсказок. Они вам не помогут. Если вам это действительно нужно, вы должны использовать

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

в начале вашей процедуры. Он делает то же самое.