TheSniper105 Ответов: 2

Упрощение времени выполнения подзапроса SQL

у меня есть следующий запрос, который занимает более 4 минут для просмотра есть ли другой способ, чтобы переписать этот запрос не в том, что таблица содержит billsDetails 100к записей и Billtable содержится 10к записей


Структура таблицы BillDetails

BillDetailsID bigint not null " PK"
productID bigint null,
billID bigint null
BillType tinyint not null

BillsOperations стол
Биллид бигинт не нулевой " ПК"
Атрибутом parentId значение типа bigint нуль "само отношение"
IsPermitted бит not null
BillType tinyint not null

SELECT * FROM (
SELECT 
b.BillID, b.Code,b.BillType,  dbo.Fn_BillNameByType(b.BillType) AS BillTypeName ,
(SELECT COUNT(BillDetailID) FROM dbo.BillsDetailsOperations WHERE BillID= b.BillID)   AS OriginalCount,


(
SELECT COUNT(DISTINCT ProductID) FROM dbo.BillsDetailsOperations
WHERE BillID IN (
SELECT BillID FROM dbo.BillsOperations WHERE ParentID = b.BillID AND BillType IN (9,10)))   AS PermittedCount

FROM dbo.BillsOperations  AS b
WHERE b.IsPermitted = 1 and b.BillType NOT IN (5,8,9,10)) AS detailsTbl
WHERE (detailsTbl.OriginalCount < detailsTbl.PermittedCount) OR (detailsTbl.OriginalCount > detailsTbl.PermittedCount)


Что я уже пробовал:

я пытался создать группу, но не работал так, как я хочу

itsmypassion

Пожалуйста, укажите структуру таблицы.

TheSniper105

я сделал

FranzBe

Вы уже проверили опцию "Включить фактический план выполнения" и посмотрели на результаты?

TheSniper105

включение этой опции занимает много времени

Jörgen Andersson

Просто любопытно.
Вы приняли мое решение, значит, оно сработало.
Насколько быстрее он стал?

TheSniper105

он выполняется в 00: 00: 31 быстрее, чем рассматриваемый запрос, и я нашел другое решение я принимаю ваш ответ, потому что он дает мне ключ, чтобы сделать запрос быстрее (join вместо sub query) я нашел решение, которое выполняется в 00: 00:00: D

Jörgen Andersson

Рад помочь.

2 Ответов

Рейтинг:
9

Jörgen Andersson

Прежде всего и самое главное, вам нужен индекс на BillDetails 

CREATE NONCLUSTERED INDEX BillDetails_BillID_IX ON BillDetails (BillID) INCLUDE (productID)
И, возможно, также один на ParentID.

Во-вторых, по возможности избегайте коррелированных подзапросов. Вместо этого используйте соединения.
Попробовать это:
WITH j AS (
    SELECT  bo.ParentID
           ,bdp.productID
    FROM    dbo.BillsDetailsOperations bdp
    JOIN    dbo.BillsOperations bo ON bo.BillID = bdp.BillID
    WHERE   bo.BillType IN (9,10)
    )
,detailsTbl as (
    SELECT  b.BillID
           ,b.Code
           ,b.BillType
           ,dbo.Fn_BillNameByType(b.BillType) AS BillTypeName
           ,COUNT(bd.BillDetailID) AS OriginalCount
           ,COUNT(DISTINCT ProductID) AS PermittedCount
    FROM    dbo.BillsOperations  AS b
    LEFT JOIN   dbo.BillsDetailsOperations bd ON bd.BillID = b.BillID
    LEFT JOIN   j ON j.ParentID = b.BillID
    WHERE   b.IsPermitted = 1
        AND b.BillType NOT IN (5,8,9,10)
    GROUP BY b.BillID
           ,b.Code
           ,b.BillType
    )
SELECT  *
FROM    detailsTbl
WHERE   detailsTbl.OriginalCount != detailsTbl.PermittedCount

Рейтинг:
2

kosmas kafataridis

Привет

Прежде всего, в вашем случае я бы сделал BillDetails.Внешний Ключ BillId.

и если я правильно вас понял, то это мое решение.

CREATE TABLE #BillsOperations (	BillID  [bigint], ParentID  [bigint] null,IsPermitted  [bigint] NULL,BillType  [bigint] NULL);
CREATE TABLE #BillDetails (	BillDetailsID   [bigint], productID   [bigint],billID   [bigint] NULL,BillType [bigint] NULL);
CREATE TABLE #BillType (ID   [bigint], descr nvarchar(100));
insert into #BillType (ID, descr) values (9,'INVOICE');
insert into #BillType (ID, descr) values (1,'CREDITNOTE');

insert into #BillsOperations (BillID, ParentID,IsPermitted,BillType) values (1,null,1,9);
insert into #BillsOperations (BillID, ParentID,IsPermitted,BillType) values (2,null,1,9);
insert into #BillsOperations (BillID, ParentID,IsPermitted,BillType) values (3,null,1,1);

insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (1,1,1,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (2,2,1,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (3,3,1,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (4,4,1,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (5,1,2,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (6,1,2,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (7,2,2,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (8,3,2,9);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (9,1,3,1);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (10,2,3,1);
insert into #BillDetails (BillDetailsID, productID,billID,BillType) values (11,1,3,1);

select * from (select 
a.BillID, a.BillType,  c.descr AS BillTypeName , count(*) AS OriginalCount,
case when a.BillType IN (9,10) then COUNT(DISTINCT b.ProductID) else 0 end AS PermittedCount

from #BillsOperations a inner join #BillDetails b on a.BillID=b.billID inner join #BillType c on a.BillType=c.ID
group by a.BillID, a.BillType,  c.descr) x
--WHERE (OriginalCount < PermittedCount) OR (OriginalCount > PermittedCount) 


drop table #BillsOperations;
drop table #BillDetails;
drop table #BillType;


Хорошего дня

TheSniper105

не тот же запрос, что я хочу