Improving the query standards and performance run time

Question

For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i tune it up further as it runs over hours. I will try to get a repro, but for now, let me know if below can help:-

Query

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM 
       TABLE1,TABLE2,TABLE3

WHERE TABLE2.ID=TABLE1.ID
AND   TABLE2.COLUMN1='Data'
AND   TABLE3.ID=TABLE1.ID
AND   TABLE3.COLUMN2='Value'
AND   TABLE1.COL1=CAST('20190914' as DATE)
AND   DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2

For Table1 there is no CI, we just have Unique NON CI as PK

TABLE 2 and TABLE 3 also does not have any CI, they both have Unique non CI on both ID and datetime column TABLE1.COL1 is a datetime column

Above query is run multiple times in 12 batches a day for different date range in between clause to collect data between 24 hours, like 0-1, 1-2, 2-3 , 3-4 .... 21-22,23-24

Is there a better way to perform above run?

Thanks

Answer 1

Clustered Index is must.Unless we do not have table structure and their description we cannot say which column should be CI.

Provide volume of data in each table and expected volume of output.

DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2 is not SARGAble.

So you can rewrite the query as below.Always check the performance with parameter instead of HARD coded value.

I mean that in real life scenario, we use local variable or Proc's parameter.So test query performance using using same. Check that Parameter Sniffing problem is tolerable or not and how it can be minimized.If Parameter Sniffing is tolerable then ignore it. Option Recompile should be rarely use .

declare @FromDate Datetime=cast(getdate() as date)
declare @FromDatetime=dateadd(hour,1,@FromDate)
declare @ToDatetime=dateadd(hour,2,@FromDate)

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM 
       TABLE1 T1
     inner join  TABLE2 T2 on T2.ID=T1.ID
     inner join  TABLE3 T3 on  T3.ID=T1.ID -- try once T3.ID=T2.ID
WHERE 
   T2.COLUMN1='Data'
AND   T3.COLUMN2='Value'
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime

Alternatively,

It appear that you don't need Table2 and Table3 column in your result set,

So you can rewrite in this manner,

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM dbo.TABLE1 T1   
WHERE 
exists(select 1 from dbo.TABLE2 T2 
     inner join  dbo.TABLE3 T3 on T3.ID=T2.ID
     where T3.ID=T1.ID and T2.COLUMN1='Data' AND   T3.COLUMN2='Value')
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime