What is the best solution between Aggregate (index) and advance SQL (In) ?
Question

When I want to query data with multiple identifiers, So what is the best solution and the best performance between use the index in the Aggregate or use IN in the Advance SQL?


  • Filter with index in the aggregate such as 

StringID = 1,2,3,4,5 

I will add "," to StringId like this 

StringId = ,1,2,3,4,5,


and use this to filter id.

so condition for filter is  index(StringId,","+Entity.id+",") <> -1


  • Where IN in the Advance SQL

use BuildSafe_InClauseIntegerList and query like this

1SELECT{Users}.[Username], {Users}.[Surname], {Users}.[Firstname]
2FROM{Users}
3WHERE{Users}.[IsActive] = 1 AND{Users}.[Id] IN(@idlist)
Solution

The SQL in statement is far more efficient. The Index statement in the aggregate does a string compare on each line individually whereas the SQl query can make use of index's etc.. 

mvp_badge
MVP

Hi Thanaphat,


The rule of thumb is to use aggregates when possible so if you can use those, it will be preferable as they are optimized by the platform and are independent of the SQL engine (MySQL, SQL Server, Oracle) you are using. A good article on the distinction of the two can be found here.

In addition to that, to use the second approach, you would be using the @idlist parameter as an Expanded Inline parameter. These should not be abused according to best practices as inline parameters that change too often don't allow the database to optimize execution plans, since the engine keeps generating different queries. This may have a huge impact on performance.

EDIT: But there are some scenarios in which it is more advantageous to use SQL statement commands. You can always do a comparison of performance and share the results.

Kind Regards,
João

Solution

The SQL in statement is far more efficient. The Index statement in the aggregate does a string compare on each line individually whereas the SQl query can make use of index's etc.. 

agreed with @Jeanene Williams , Even though we should get our things done by using aggregates as they're optimized but in the above case index's plays major roles while scanning the records, also you can see this variation if you're dealing with huge records.

and if you wanted to continue with the second approach you should use BuildSafe_InClauseIntegerList from sanitazation API as you mentioned in your question.


Thanks,

Sandeep.  

There is actually another possible option. Where did the ID's come from in the first place? Often if you are fetching some id's from one aggregate and then passing into another as a filter you can achieve the same result by using a join between the two aggregates and then filtering on that. You get the best of both worlds in that case by using optimised aggregates and no index or in.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.