Question
amine benchaou · Feb 20, 2020

Sql request

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ?    Thank you

KR,

Amine

0
0 220
Discussion (5)1
Log in or sign up to continue

That depends on your data model.

If you require many related tables, it may be more useful to have the invocation with the data you need by appending the tables by primary keys.


SELECT table1.code, table2.description
FROM table1
INNER JOIN table2 ON table2.tab_id = table1.id

Also, it's important create the proper index to the fields that you need to search.

There is not a simple answer :)

Best Regards,
Francisco Lopez

1 sql request per property, or only one "big" sql request ?

How are you planning on merging distinct requests? One case I've seen where you need something like this is when you want to get a valid subset of filter values (which would return 1+ rows) for each next filter, but in the end all filters are still combined into one query.

You need to decide on:

  • what predicates are allowed (=, %STARTSWITH, <, >, LIKE, IN, ISNULL, BETWEEN)
  • what logic operations are allowed (OR, AND, NOT)
  • are nesting and brackets allowed
  • is it free form (auto constructed from metadata) or fixed (from a pre-set list of properties).

After that on a client you construct filter object, validate and parse it on a server and execute the query.

If you're not satisfied with performance, server-side part can be tweaked without modifying the client.

what's the disadvantage of using lot of indexes ?

INSERTS/UPDATES are slower, more space is needed.

Hi Eduard, 

thank you for your reply.

my query is mostly a mix of "AND" and "BETWEEN", and my filters is a list of properties

Best regards,

Amine

FWIW, you can just put as many conditions as you want in the same SQL query. InterSystems IRIS includes an excellent SQL optimizer that will consider all the conditions (and other clauses) in your query and decide on the most efficient path to access the data.

Thank you for your reply Benjamin. 

it's inedeed what you have explain in the last symposium about sql requests. We're not yet on the new IRIS (on the road).

BR,

Amine