Search query optimization

Query optimization helps maintain high performance and a smooth user experience. An optimized search responds in less than one second, even on large data volumes.

Performance objectives

Target response times

Search type	Target time	Acceptable time	Critical time
Simple search	< 500ms	< 1s	> 3s
Advanced search	< 1s	< 2s	> 5s
Search with filters	< 1s	< 2s	> 5s
Full-text search (files)	< 2s	< 3s	> 10s

Indicators to monitor

Key metrics:

• Average response time
• 95th percentile response time
• Number of queries per second
• Error rate
• Average result size

Index optimization

Regular optimization

Recommended frequency:

• Small databases (< 10,000 documents): Monthly
• Medium databases (10,000 - 100,000): Weekly
• Large databases (> 100,000): Daily (automated)

Impact:

• 20 to 50% improvement in search time
• Reduced fragmentation
• Reduced index size

Procedure:

1. Go to AdminTools > AdminFulltextIndex
2. Click "Optimize index"
3. Wait for processing to complete
4. Measure the improvement

Automation:

• Configure a scheduled task (AdminTools > ProcessTimers)
• Schedule during nighttime (2-3 AM)
• Monitor execution (logs)

Targeting indexed fields

Principle: Only index what is actually searched

Fields to index:

• ✅ Title
• ✅ Reference
• ✅ Description
• ✅ Comments
• ✅ Important business fields

Fields to exclude:

• ❌ Technical ID
• ❌ GUID
• ❌ Calculated fields
• ❌ Non-searched system fields

Impact:

• Index size reduction (-20% to -40%)
• Improved indexing time
• Improved search time

Attachment limitations

Recommendations:

File size:

• < 10 MB: Index
• 10-50 MB: Index with caution

• 50 MB: Consider exclusion

File types:

• ✅ Text PDFs
• ✅ Office (Word, Excel)
• ⚠️ Scanned images (requires OCR, slow)
• ⚠️ CAD (depends on available IFilter)
• ❌ Videos, audio

Configuration:

• Filter by file size (if possible)
• Selectively index by document type
• Monitor indexing time

Index size monitoring

Alert thresholds:

Index size	Action
< 5 GB	Normal, standard monitoring
5-10 GB	Attention, optimization recommended
10-20 GB	Optimization required
> 20 GB	Urgent configuration review

Corrective actions:

1. Optimize the index
2. Review indexed fields (too many?)
3. Check attachments (very large files?)
4. Purge obsolete documents
5. Review indexing filters

Query optimization

Efficient search techniques

Simple vs advanced search:

Simple (fast):

• Simple keywords: contract supplier
• Search all indexed fields

Advanced (targeted):

• Operators: contract AND supplier
• Specific fields: title:contract AND service:purchasing
• Filters: Date, Status, Type

Recommendation: Train users on advanced search for more relevant and faster results.

Search operators

Available operators:

Operator	Usage	Example
AND	All terms	quality AND audit
OR	At least one term	NC OR non-conformity
NOT	Exclusion	procedure NOT draft
"..."	Exact phrase	"action plan"
field:term	Search in a field	author:Smith
*	Wildcard	proc* (procedure, process)

Optimization:

• AND is faster than OR
• Exact phrases are faster than wildcards
• Limit wildcard usage at the beginning (*term is very slow)

Filters and sorting

Filters:

• Apply filters before textual search
• Reduce scope, therefore faster
• Examples: Status, Service, Date, Type

Sorting:

• Relevance sorting is fastest (default)
• Date sorting: fast
• Text field sorting: slower

Database optimization

SQL Server statistics

Importance:

• SQL Server uses statistics to optimize queries
• Outdated statistics = slow queries
• Recommended update: Weekly

Procedure (requires SQL administrator):

UPDATE STATISTICS [DatabaseName].[dbo].[Documents]

Automation:

• SQL Server maintenance plan
• Weekly scheduled task
• Performance monitoring

SQL Server indexes

Critical tables to index:

• Documents table (id, reference, title, status, date)
• Workflow table (document_id, step_id, user_id, status)
• EmailLog table (date, recipient, status)
• Search tables (depending on configuration)

Verification (SQL administrator):

• Analyze execution plans
• Identify table scans (slow)
• Add indexes if necessary

Fragmentation

Issue:

• Over time, tables become fragmented
• Degraded performance
• Defragmentation required

Solution:

• SQL Server maintenance plan
• Index reorganization (< 30% fragmentation)
• Index rebuilding (> 30% fragmentation)
• Frequency: Monthly

Business case: Performance improvement

Initial situation:

• 50,000 indexed documents
• Search = 8-10 seconds (too slow)
• Dissatisfied users

Diagnosis:

1. Index size: 15 GB (high)
2. Last optimization: Never
3. Indexed fields: 25 per form (too many)
4. Attachments: All files, including large scanned PDFs

Corrective actions:

1. Reduce indexed fields:

   • Went from 25 to 12 fields
   • Excluded technical fields
   • Impact: -30% index size

2. Limit attachments:

   • Excluded files > 20 MB
   • Excluded scanned images (except specific cases)
   • Impact: -40% index size, 3x faster indexing

3. Index optimization:

   • Manual optimization launch
   • Automatic task configuration (nighttime)
   • Impact: -20% additional size

4. SQL optimization:

   • Update statistics
   • Defragment main tables
   • Impact: +30% query speed

5. Complete reindexing:

   • With new configuration
   • During weekend

Result:

• Index size: 7 GB (-53%)
• Search time: 0.8 seconds (-92%)
• User satisfaction: ⬆️⬆️⬆️

Best practices

Initial configuration

Thoughtful design:

• Identify real search needs
• Only index what's necessary
• Plan for volume evolution
• Document choices

Regular maintenance

Daily:

• Monitor response times
• Verify indexing tasks

Weekly:

• Index optimization
• Update SQL statistics
• Performance testing

Monthly:

• Analyze index size
• Review configuration
• SQL defragmentation
• Export statistics

Quarterly:

• Complete configuration audit
• Adjustments based on usage evolution
• User training

Measurement and monitoring

KPIs to track:

• Average search time (target: < 1s)
• Index size (target: < 10 GB)
• Search success rate (do users find what they need?)
• User satisfaction

Tools:

• Usage logs
• User surveys
• Application monitoring

Documentation

To document:

• Current configuration (indexed fields)
• Optimization history
• Measured performance
• Incidents and resolutions

User training

Efficient searching:

• Using operators (AND, OR, NOT)
• Searching by specific fields
• Using filters
• Exact phrases vs wildcards

Best practices:

• Precise keywords
• Term combinations
• Using filters before search
• Interpreting results (relevance)

Support:

• Advanced search guide
• Efficient query examples
• Training sessions
• Helpdesk for assistance

Advanced monitoring

Additional tools:

• Application Insights (Microsoft)
• ELK Stack (Elasticsearch, Logstash, Kibana)
• APM tools (Application Performance Monitoring)
• Custom dashboard

Advanced metrics:

• Response time distribution
• Slowest queries
• Most frequent queries
• Cache hit rate

Support

For advanced optimization (SQL Server tuning, advanced Lucene configuration, distributed architecture), contact Avanteam support.