Thoughts on how to improve string search queries

[Bhanu1]

Please see sample code I am running below. What options can I explore to improve speed of query execution in such a scenario? Current full code takes about 4 hrs to run on 1.5 billion rows. Thanks!

SELECT
 fullVisitorId
 ,VisitId
 ,EventDate
 ,PagePath
 ,deviceCategory 
FROM ga360.ga_sessions a
WHERE
(
contains(lower(PagePath) , 'step-one')
OR contains(lower(PagePath) , 'step-two')
OR contains(lower(PagePath) , 'step-three')
)
AND not contains(utm_source,'ads')
AND hitstype = 'PAGE'
and a.EventDateKey >= 20200101

[Kaniz_Fatma]

Hi @Bhanu1, When dealing with large datasets and slow query execution, there are several strategies you can explore to improve performance. Let’s dive into some options:

Indexing:

• Indexing is a critical technique for enhancing SQL query performance on large tables. Ensure that you have appropriate indexes on the columns frequently queried. Since you mentioned that adding indexes is not an option, consider reevaluating the existing indexes. Sometimes, removing or modifying existing indexes can lead to better performance.
• If possible, create a single-column index on each column used in your query. This provides SQL Server with something to work with for most queries.
• Additionally, create specific indexes for the most common queries to optimize them further.

Partitioning:

• Partitioning large tables can significantly improve query performance. It allows the database engine to scan only the partitions containing the data needed for the query.
• Consider partitioning based on the EventDate column, as it appears in your query. Partitioning by date ranges can help speed up queries that involve date-based filtering.

Limit Returned Data:

• To optimize queries with large datasets, limit the data returned. Use techniques like window functions or pagination to retrieve only the necessary rows.
• For instance, if you’re displaying results in a user interface, consider showing a limited number of....

Parallel Execution:

• SQL Server can execute queries in parallel, utilizing multiple cores. Ensure that parallelism is enabled and configured appropriately.
• Adjust the degree of parallelism (DOP) to balance resource utilization and query performance. Experiment with different settings to find the optimal configuration for your workload.

Remember that query optimization is often a combination of these techniques. Analyze your specific use case, monitor query execution plans, and test different approaches to find the best solution for your scenario. 🚀