0

I work on sql server 2019 i face issue when compare feature values between two tables

Based on RecomendationId .

it take too long time to display result

time that take it 5 minute .

total rows on every table on join compare is 13 milion rows .

query is

 select o.RecomendationId,case when CONVERT(VARCHAR(32), HashBytes('SHA2_256',f1.DiffFeatures),2) =CONVERT(VARCHAR(32), HashBytes('SHA2_256',o.DiffFeatures),2) then 0 else 1 end as DiffValues  
 into ExtractReports.dbo.TechnologyEqualOriRecomended 
 from ExtractReports.dbo.TechnologyOriginalFeaturesEqual o with(nolock)
 inner join ExtractReports.dbo.TechnologyRecomendedFeaturesEqual f1 with(nolock) 
 on f1.RecomendationId=o.RecomendationId 
 group by o.RecomendationId,f1.DiffFeatures,o.DiffFeatures

ddl structure

CREATE TABLE [dbo].[TechnologyOriginalFeaturesEqual](
     [RecomendationId] [int] NOT NULL,
     [DiffFeatures] [varchar](500) NULL
 ) ON [PRIMARY]
    
 GO
    

 CREATE TABLE [dbo].[TechnologyRecomendedFeaturesEqual](
     [RecomendationId] [int] NOT NULL,
     [DiffFeatures] [varchar](500) NULL
 ) ON [PRIMARY]

I also create indexes on two tables but still take too much time reach to 5 minutes

CREATE NONCLUSTERED INDEX [recomednedFeatureValuesCompare_IX] ON [dbo].[TechnologyRecomendedFeaturesEqual]
(
    [RecomendationId] ASC,
    [DiffFeatures] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [originalFeatureValuesCompare_IX] ON [dbo].[TechnologyOriginalFeaturesEqual]
(
    [RecomendationId] ASC,
    [DiffFeatures] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Execution plan as Below :

compare values execution plan

so what i do to make query run faster actual execution plan actual execution plan

7
  • Can you please provide the actual execution plan for when you run the query? (The one you've provided is the estimated execution plan.)
    – J.D.
    Sep 9, 2022 at 17:30
  • 1
    Give this and this a a shot. If one works to make things faster, I'll write a more detailed answer. But like JD said, you should really post the actual plan if you can. Estimated plans suck. Sep 9, 2022 at 17:35
  • i post actual execution plan Sep 9, 2022 at 18:15
  • that looks like the execution plan for building a nonclustered index on a heap. Sep 9, 2022 at 18:23
  • so what i do to enhance performance Sep 9, 2022 at 20:18

1 Answer 1

0

First, I would make RecomendationId the clustered primary key on both tables:

ALTER TABLE [dbo].[TechnologyOriginalFeaturesEqual]
   ADD CONSTRAINT PK_TechnologyOriginalFeaturesEqual_RecomendationId PRIMARY KEY CLUSTERED (RecomendationId);

AND

ALTER TABLE [dbo].[TechnologyRecomendedFeaturesEqual]
   ADD CONSTRAINT PK_TechnologyRecomendedFeaturesEqual_RecomendationId PRIMARY KEY CLUSTERED (RecomendationId);

This may take a while! It may be faster to create new tables and load from these, drop the old ones, rename.

Next, I would do a straight comparison of DiffFeatures, unless you really want a hash match (meaning to values could produce the same value... basically you are replicating something else that does this exactly). The time it takes to compute the hash for each is slower than a straight compare, and you are not omitting the values, so you don't otherwise need them.

Where you could save time if there are only periodic write, but you are doing this query often, is to restructure so that instead of storing DiffFeatures directly, you insert new values to a separate table (common for both tables) and store just the id of that table. This would increase the cost of writing the initial value (and complexity is storing values), but it would make the non-clustered indexes much smaller and faster to load, compare, etc., but you really would need to evaluate your needs holistically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.