با سلام
من دو تا جدول دارم به صورت زیر:
CREATE TABLE [dbo].[tblSF22Welding](
[BarcodeId] [bigint] NOT NULL,
[DT] [datetime] NULL,
[S1Leak1] [int] NULL,
[S1Leak2] [int] NULL,
[S1Leak3] [int] NULL,
[S1Leak4] [int] NULL,
[S1Leak5] [int] NULL,
[S1Leak6] [int] NULL,
[S1Leak7] [int] NULL,
[S1Leak8] [int] NULL,
[S1Leak9] [int] NULL,
[S1Leak10] [int] NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSF26Log](
[TestId] [bigint] IDENTITY(1,1) NOT NULL,
[BarcodeId] [bigint] NULL,
[DT] [datetime] NULL,
[Ok1] [bit] NULL,
[Ok2] [bit] NULL,
[Ok3] [bit] NULL,
[Ok4] [bit] NULL,
[Ok5] [bit] NULL,
[Ok6] [bit] NULL,
[Ok7] [bit] NULL,
[Ok8] [bit] NULL,
[Ok9] [bit] NULL,
[Ok10] [bit] NULL,
[OperatorId] [int] NULL,
[QCUserId] [int] NULL,
[QCManOk] [bit] NULL,
)
من نیاز دارم ده بار جدول tblSF22Welding را با tblSF26Log جوین کنم. کوئری زیر را نوشته ام اما اجرای آن حدود 30 ثانیه طول می کشد. جدول tblSF22Welding تا حالا حدود 20000 رکورد دارد و جدول tblSF26Log حدود 60000 و این فقط برای یک ماه است و به زودی رکوردهای این جدول ها به چند میلیون می رسد! آیا راه مناسب تری هم وجود دارد؟
Declare @T table(
DT datetime,
Ok1 bit,
UserId1 int,
Ok2 bit,
UserId2 int,
Ok3 bit,
UserId3 int,
Ok4 bit,
UserId4 int,
Ok5 bit,
UserId5 int,
Ok6 bit,
UserId6 int,
Ok7 bit,
UserId7 int,
Ok8 bit,
UserId8 int,
Ok9 bit,
UserId9 int,
Ok10 bit,
UserId10 int)
INSERT INTO @T
SELECT A.DT,Ok1,CASE WHEN Ok1 IS NOT NULL THEN W1.S1Leak1 ELSE NULL END AS UserId1,
Ok2,CASE WHEN Ok2 IS NOT NULL THEN W2.S1Leak2 ELSE NULL END AS UserId2,
Ok3,CASE WHEN Ok3 IS NOT NULL THEN W3.S1Leak3 ELSE NULL END AS UserId3,
Ok4,CASE WHEN Ok4 IS NOT NULL THEN W4.S1Leak4 ELSE NULL END AS UserId4,
Ok5,CASE WHEN Ok5 IS NOT NULL THEN W5.S1Leak5 ELSE NULL END AS UserId5,
Ok6,CASE WHEN Ok6 IS NOT NULL THEN W6.S1Leak6 ELSE NULL END AS UserId6,
Ok7,CASE WHEN Ok7 IS NOT NULL THEN W7.S1Leak7 ELSE NULL END AS UserId7,
Ok8,CASE WHEN Ok8 IS NOT NULL THEN W8.S1Leak8 ELSE NULL END AS UserId8,
Ok9,CASE WHEN Ok9 IS NOT NULL THEN W9.S1Leak9 ELSE NULL END AS UserId9,
Ok10,CASE WHEN Ok10 IS NOT NULL THEN W10.S1Leak10 ELSE NULL END AS UserId10
FROM tblSF26Log A
INNER JOIN tblSF22Welding W1 ON A.BarcodeId=W1.BarcodeId
INNER JOIN tblSF22Welding W2 ON A.BarcodeId=W2.BarcodeId
INNER JOIN tblSF22Welding W3 ON A.BarcodeId=W3.BarcodeId
INNER JOIN tblSF22Welding W4 ON A.BarcodeId=W4.BarcodeId
INNER JOIN tblSF22Welding W5 ON A.BarcodeId=W5.BarcodeId
INNER JOIN tblSF22Welding W6 ON A.BarcodeId=W6.BarcodeId
INNER JOIN tblSF22Welding W7 ON A.BarcodeId=W7.BarcodeId
INNER JOIN tblSF22Welding W8 ON A.BarcodeId=W8.BarcodeId
INNER JOIN tblSF22Welding W9 ON A.BarcodeId=W9.BarcodeId
INNER JOIN tblSF22Welding W10 ON A.BarcodeId=W10.BarcodeId
WHERE A.MachineId=26 AND (
(Ok1 IS NOT NULL AND W1.S1Leak1 IS NOT NULL)
OR(Ok2 IS NOT NULL AND W2.S1Leak2 IS NOT NULL)
OR(Ok3 IS NOT NULL AND W3.S1Leak3 IS NOT NULL)
OR(Ok4 IS NOT NULL AND W4.S1Leak4 IS NOT NULL)
OR(Ok5 IS NOT NULL AND W5.S1Leak5 IS NOT NULL)
OR(Ok6 IS NOT NULL AND W6.S1Leak6 IS NOT NULL)
OR(Ok7 IS NOT NULL AND W7.S1Leak7 IS NOT NULL)
OR(Ok8 IS NOT NULL AND W8.S1Leak8 IS NOT NULL)
OR(Ok9 IS NOT NULL AND W9.S1Leak9 IS NOT NULL)
OR(Ok10 IS NOT NULL AND W10.S1Leak10 IS NOT NULL))
SELECT Fullname Name,NG,TTL,NG*100.0/TTL Per FROM(
SELECT UserId,SUM(CASE WHEN Ok=0 THEN 1 ELSE 0 END)NG,COUNT(*) TTL FROM(
SELECT OK1 OK,UserId1 UserId FROM @T WHERE UserId1 IS NOT NULL
UNION ALL
SELECT OK2,UserId2 FROM @T WHERE UserId2 IS NOT NULL
UNION ALL
SELECT OK3,UserId3 FROM @T WHERE UserId3 IS NOT NULL
UNION ALL
SELECT OK4,UserId4 FROM @T WHERE UserId4 IS NOT NULL
UNION ALL
SELECT OK5,UserId5 FROM @T WHERE UserId5 IS NOT NULL
UNION ALL
SELECT OK6,UserId6 FROM @T WHERE UserId6 IS NOT NULL
UNION ALL
SELECT OK7,UserId7 FROM @T WHERE UserId7 IS NOT NULL
UNION ALL
SELECT OK8,UserId8 FROM @T WHERE UserId8 IS NOT NULL
UNION ALL
SELECT OK9,UserId9 FROM @T WHERE UserId9 IS NOT NULL
UNION ALL
SELECT OK10,UserId10 FROM @T WHERE UserId10 IS NOT NULL)TT1
GROUP BY UserId
)T1
INNER JOIN tblUsersLogin T2 ON T1.UserId=T2.Id
ORDER BY TTL DESC
