14
Views
3
Comments
Solved
How to remove Condition from where clause for the fields where values are NULL?
Question

Pasting SHORT SQL(masked) Background, Audit Table contains more than 20 Million Records. For the application "@ApplicationNumber,@From, @To and @UserId" parameters are optional. By Default only one of the 4 parameter is mandatory.  In case user is selecting only 1 parameter, rest other parameters are being used in SQL with DEFAULT values example @NULLDATE for Date field and '' for Text Field. The SQL runtime is taking more than 8 mins.

Current Query has been written as following. 

Because of Condition Like "({Audit}.[DateTime] >= @From OR @From = @Nulldate)" . where values are NULL the condition is changing to "NULL = NULL". This is a TRUE statement for all the records. Because of this SQL is forced to run with FULL Table Scan. 


How to remove Condition from where clause for the fields where values are NULL?

SELECT  {Audit}.[Id],
        {Audit}.[DateTime],
        
        
FROM      {Audit}
LEFT JOIN {User}         ON {Audit}.[UserId] = {User}.[Id]
LEFT JOIN {Screen}       ON {Audit}.[ScreenId] = {Screen}.[Id]

WHERE  @IsVisibleSearch = 1
    AND ({Audit}.[ApplicationNumber] =  @ApplicationNumber OR @ApplicationNumber = ' ')
    AND ({Audit}.[DateTime] >= @From OR @From = @Nulldate)
    AND ({Audit}.[DateTime] <= @To OR @To = @Nulldate)
    AND ({Audit}.[UserId] = @UserId OR @UserId = 0)  
ORDER BY @OrderBy,
        {Audit}.[Id] DESC
    
    

Rank: #619
Solution

Hi,

You can actually inject the where clause as a parameter, enabling the 'Expand Inline' Query Parameter Option.

As an example:

Then passing the result of this expression into the FromClause inside your SQL. Don't forget the Expand Inline.


This is an example for Date values. For Text ones you must take into account SQL injection and you have to protect user inserted inputs with the EncodeSql function.


Now, regarding your full table scan, I'm not sure this is the problem. Your example would resolve to a condition like:

#1900-01-01# = #1900-01-01# and not to NULL = NULL.

I would also say that most modern SQL engines would simple ignore this clause so it might be the full scan is due to another reason. For example, if you don't have an index on your Application Number, the engine will go for a full scan if you pass a value to it. Also, worth notice that an index search might not always be the best path to go, especially if there are a lot of records with the same index match. The engine typically decides what is the best path.


Hope this helps in your investigation.


Best,

Tiago

Rank: #297

HI Ashish 

What do you mean by "How to remove Condition from where clause for the fields where values are NULL?".

I thought you already done this. Because if filed is null the condition not applied.

If I misunderstanding you, I'm sorry.

Please refer to this link, Also please consider always set Max. Record for aggerate and SQL.

Kind regards.

Rank: #619
Solution

Hi,

You can actually inject the where clause as a parameter, enabling the 'Expand Inline' Query Parameter Option.

As an example:

Then passing the result of this expression into the FromClause inside your SQL. Don't forget the Expand Inline.


This is an example for Date values. For Text ones you must take into account SQL injection and you have to protect user inserted inputs with the EncodeSql function.


Now, regarding your full table scan, I'm not sure this is the problem. Your example would resolve to a condition like:

#1900-01-01# = #1900-01-01# and not to NULL = NULL.

I would also say that most modern SQL engines would simple ignore this clause so it might be the full scan is due to another reason. For example, if you don't have an index on your Application Number, the engine will go for a full scan if you pass a value to it. Also, worth notice that an index search might not always be the best path to go, especially if there are a lot of records with the same index match. The engine typically decides what is the best path.


Hope this helps in your investigation.


Best,

Tiago

Rank: #56

Hi Ashish,


As Tiago mentioned, you can use Expand Inline parameters to avoid having the query checking if it is null but take into account that Expand Inline parameters that change often don't allow the database to optimize execution plans (see more here about the side effects of using Expand Inline parameters).


Nevertheless, when querying a table with 8 million records, there are ways to improve performance, but of course you can only improve so much. I would take into account the following improvements based on your screenshot:

  • You're left joining tables you don't use in filters or select, do you really need them? Otherwise, remove them.
  • You have an order defined, do you really need it? Otherwise take it out. The higher the number of records in the table the higher the impact, specially if you're sorting by a non-indexed field.
  • Seriously consider if you need all the records or just 50 or a 100, for example and use TOP to limit this. The higher the number of records, the more impact on performance the TOP has (specially when no order is applied).


As I mentioned with a table of 8 million records (and counting), one starts wondering if a SQL database is the best option to store this data. Usually the priority for audit logging is a table that is super fast to write on (and/or whose write are made asynchronously) and the export / search can take longer. Given the aforementioned, the scenario I come across more often is audit information being sent to another platform (for instant ELK?) more specialized in dealing with these amounts of data, searching and extracting intelligence out of them. 


Kind Regards,
João