0

I am trying to understand the below query being run from our developers on read only database which just runs forever.

Table here is partitioned table on partition column trickey per day.

Query is something like below

Select 
   a,
   b,
   c,
   d,
   trickey 
from 
   table 
where 
   (1=1) 
   and (1=1) and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913

Will above query not eligible for partition key elimination OR how can i improve or tune up the above query?

Also what is the benefit of (1=1 )used in where clause and does that help or degrade performance?

@Justin- Modified query

Select 
       a,
       b,
       c,
       d,
       trickey 
    from 
       table 
    where 
       (1=1) 
       and (1=1) ---and convert(varchar(10),trickey,112) BETWEEN 20190913 and 20190913 replace this one with modified version

and trickey>=cast('2019-09-13' as date)
and trickey< cast('2019-09-14' as date)
3

You should be able to get the estimated query plan in SSMS without running the query.

If you do, you'll see that SQL Server is not going to be able to use partition elimination because you have a function on the partitioning column. Just like using a function on an indexed column prevents SQL Server from using the index, using a function on a partitioning column prevents SQL Server from doing partition elimination. SQL Server has no idea how to prove which partition function( column ) data might be in so it has to scan all of them.

Assuming trickey is a datetime column, you likely just want to compare it against dates directly rather than using a function

and trickey >= cast( '2019-09-13' as date ) 
and trickey <  cast( '2019-09-14' as date )
  • 5
    Your answer is likely correct, but estimated plans don't show partition elimination. See here vs. here. – Erik Darling Sep 18 at 21:49
  • @ErikDarling, My bad. I did not intended to do so. Later when i try to edit the option went away. I should delete it probably and check again. – BeginnerDBA Sep 19 at 15:38
  • @justin - i modified the code as mentioned for where clause and query returned 0 rows with execution plan showing as constant scan. So it seems changing that does not wo – BeginnerDBA Sep 19 at 15:39
  • @BeginnerDBA - Is trickey a datetime column? And are you sure that it has data where the value is between those two dates – Justin Cave Sep 19 at 15:44
  • @JustinCave: Yes thats correct, trickey is a datetime column [PK] and yes data is there – BeginnerDBA Sep 19 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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