Author Topic: Best Practice - Index vs Filter  (Read 304 times)

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 515
    • View Profile
Best Practice - Index vs Filter
« on: August 12, 2022, 08:48:36 AM »
It's been a long time since I've done report development, but Im trying to come up with some standards. Also, I am not a DBA by any stretch of the imagination

Wondering about AG fields - Here are my below assumptions I'd assume are correct.

1) Is there any reason why I would not always use an index as field type?
2) Referring to question 1 - Would I only use an index on fields that are searched commonly, AKA - Account, SSN, Name, ETC.
3) Dates- Should they be filters?
4) "Dropdown Boxes" - should they be filters?

Wondering how everyone does it and if i can get some tips. In the mean time, I'll be hitting up mr google. Thanks
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2138
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Best Practice - Index vs Filter
« Reply #1 on: August 15, 2022, 11:27:43 AM »
1)  Storage space.  Indexes take up a lot of space.
2)  Only the MOST UNIQUE ("high cardinality") fields like customer number or policy number.
3)  Usually.
4)  Probably.

Indexes in the CMOD database work the same was as the index in the back of a book.  Indexing the words 'the' 'and' & 'a' are pointless, because it's not particularly meaningful or useful - you only put words in the index that help you find what you're looking for.

I've had a few questions about this lately.  I'll write something up.

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 515
    • View Profile
Re: Best Practice - Index vs Filter
« Reply #2 on: August 16, 2022, 02:31:41 PM »

Space isn't so much a concern I dont think but who knows.

thank you for this!
« Last Edit: August 17, 2022, 10:44:55 AM by Justin Derrick »
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2138
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Best Practice - Index vs Filter
« Reply #3 on: August 17, 2022, 10:44:37 AM »
Space is always a concern - if not now, in the not-too-distant future.  :)

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR