November 19, 2021 at 12:01 pm
I work on SQL server 2012 i need to use group by instead of distinct
so how to do that please
query working without any problem and give me result i need but I need to use group by instead
of distinct on last statement executed in exec @SQL
and if there are advice about indexes can help me to enhance performance is preferable
IF OBJECT_ID('[dbo].[Codes]') IS NOT NULL
DROP TABLE [dbo].[Codes]
IF OBJECT_ID('[dbo].[gen]') IS NOT NULL
DROP TABLE [dbo].[gen]
IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL
DROP TABLE [dbo].[PartAttributes]
IF OBJECT_ID('[dbo].[Allfeatures]') IS NOT NULL
DROP TABLE [dbo].[Allfeatures]
IF OBJECT_ID('dbo.AllData') IS NOT NULL
DROP TABLE dbo.AllData
IF OBJECT_ID('dbo.Condition') IS NOT NULL
DROP TABLE [dbo].Condition
IF OBJECT_ID('dbo.core_datadefinition_Detailes') IS NOT NULL
DROP TABLE core_datadefinition_Detailes
CREATE TABLE [dbo].[Codes](
[ZPLID] [int] NULL,
[Code] [varchar](20) NULL,
[Proceed] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'32111502', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100000', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100050', 1)
CREATE TABLE core_datadefinition_Detailes(
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ColumnName] [nvarchar](500) NOT NULL,
[ColumnNumber] [int] NOT NULL,
CONSTRAINT [PK_Core_DataDefinition_Details] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
insert into core_datadefinition_Detailes([ColumnNumber],[ColumnName])
values
(202503,'Product Shape Type'),
(1501170111,'Type'),
(1501170046,'Maximum Peak Pulse Current'),
(202504,'Package Family')
CREATE TABLE [dbo].[gen](
[TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
[CodeTypeID] [int] NULL,
[RevisionID] [bigint] NULL,
[Code] [varchar](20) NULL,
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[ZfeatureType] [nvarchar](200) NULL,
[EStrat] [nvarchar](2500) NULL,
[EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[gen] ON
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7565, 849774, 307683692, N'8541100050', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7566, 849774, 307683692, N'8541100050', 4239, 202504, N'Package', N'Not In(''Die'',''Wafer'',''N/A'')', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7567, 849774, 307683692, N'8541100050', 4239, 1501170111, NULL, N'=''Zener''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7577, 1019997, 313023938, N'8541100000', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (9497, 1809942, 329888149, N'32111502', 4239, 1501170111, NULL, N'=''Zener''', N'')
SET IDENTITY_INSERT [dbo].[gen] OFF
CREATE TABLE [dbo].[PartAttributes](
[PartID] [int] NOT NULL,
[ZfeatureKey] [bigint] NULL,
--[FeatureName] [nvarchar](200) NOT NULL,
[AcceptedValuesOption_Value] [float] NULL,
[FeatureValue] [nvarchar](500) NOT NULL
) ON [PRIMARY]
GO
--INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 202503, NULL)
--INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 1501170111, NULL)
--INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 1501170046, 3)
--INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 202504, NULL)
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202503, NULL, N'Discrete')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170111, NULL, N'Zener')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170046, 3, N'3A')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202504, NULL, N'SOT')
CREATE TABLE [dbo].[Allfeatures](
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[FeatType] [int] NULL,
[AcceptedValueID] [int] NULL,
[IsNumericValues] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, NULL, 0, 0, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202503, NULL, 33, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202504, NULL, 34, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170046, 2044, 814, 1)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170111, 2043, 155, 0)
CREATE TABLE dbo.AllData
(
PartID INT,
Code VARCHAR(20),
CodeTypeID INT,
RevisionID BIGINT,
ZPLID INT,
ConCount INT,
FeatureName nvarchar(500),
FeatureValue nvarchar(500)
)
UPDATE Codes
SET
Proceed=0
DECLARE @Code VARCHAR(20)
DECLARE @ZPID INT
DECLARE @Sql nvarchar(max)
DECLARE @Con nvarchar(max)
DECLARE @ConStr nvarchar(max)
WHILE (Select Count(*) From Codes with(nolock) where Proceed =0 ) > 0
BEGIN
---select * from gen
SELECT Top 1 @ZPID=ZPLID, @Code=Code From Codes with(nolock) where Proceed=0
SELECT * INTO Condition FROM Gen G with(nolock) WHERE g.Code=@Code AND G.ZPLID=@ZPID AND G.ZfeatureKey IS NOT NULL
SET @Con= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')
FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And FeatureValue ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName
FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr )
DECLARE @separator CHAR(1) = '$';
SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
stuff(( SELECT ''$'' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()]
FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from Condition C
inner join dbo.core_datadefinition_Detailes d with(nolock) on C.ZfeatureKey=d.columnnumber
INNER JOIN PartAttributes P on P.partid=PM.partid)CP
where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
ORDER BY CP.ZfeatureKey
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
, 1, 1, '''') as FeatureName,
stuff(( SELECT ''$'' + CAST( CP2.FeatureValue AS VARCHAR(300)) AS [text()]
FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2
INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey)CP2
where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code
ORDER BY CP2.ZfeatureKey
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
, 1, 1, '''') as FeatureValue
FROM
PartAttributes PM
INNER JOINCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',
'Where (1=1 and ',@Con , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition))
EXEC (@SQL)
DROP TABLE Condition
UPDATE Codes Set Proceed = 1 Where @ZPID=ZPLID AND Code=@Code
END
--select * from alldata
November 19, 2021 at 7:56 pm
If you are getting the results you need, is there a reason to go with DISTINCT instead of GROUP BY?
If you absolutely need to remove the DISTINCT and replace it with GROUP BY, grouping by all of the columns should do essentially the same thing as DISTINCT, but it likely will not change performance of the query.
You can verify this by doing something like:
WITH cte AS (SELECT 1 AS DATAs
UNION ALL
SELECT 1
UNION ALL
SELECT 2
)
SELECT DISTINCT DATAs
FROM cte;
WITH cte AS (SELECT 1 AS DATAs
UNION ALL
SELECT 1
UNION ALL
SELECT 2
)
SELECT DATAs
FROM cte
GROUP BY DATAs;
If you run that, you will notice you get the same result set in both queries - 1 and 2. Your duplicate 1 is removed. Remove the DISTINCT or GROUP BY and you get 2 1's followed by a 2.
Now as for indexes, that will take a bit more analysis. But in general, you want your indexes to cover the query so you can reduce scans and key lookups. Anything that is used to filter the data should be in the Index, and any column that is required in the result set should be in an INCLUDE on the index. This will help the one query you are writing; it will not help ALL queries though and may HURT other queries. Plus, adding indexes uses more disk, so if you have a 1 GB table and you end up including all columns in the index, that index is going to be 1 GB in size, so you will have 2 GB on disk for that 1 table. Plus, if you add more indexes, it will affect your data change performance (INSERT, UPDATE, and DELETE), and in the case of a duplicate index, it can hurt SELECT performance too.
I would recommend looking at the execution plan to determine where indexes are required and look at table sizes and where table scans are happening.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply