One of the things which I love about writing technical blogs is getting constant feedback from the community and most of the times, it helps me to improve and there is always something new to learn and a different perspective to look at things.
Few days back, I wrote an article on ‘Searching Data in SQL Tables in a Single Click’ by leveraging a free 3rd party tool called ‘ApexSQL Search’ —
This tool makes searching data inside SQL tables very convenient – it abstracts the entire complexity of running complicated TSQL queries manually and provides the search results in a breeze.
I posted this article in the SQL Server groups in LinkedIn and got some great feedback from the group members —
That basically prompted me to come up with this blog. The biggest concern which was brought up was the negative impact of using this tool/functionality in Production database.
Searching for a value in the SQL tables is for sure a resource intensive operation. In Production databases, we should be careful while searching for a particular value – especially if we have lot of tables/columns to search. Generally to implement this search functionality we use a LIKE predicate with wildcards. And when we do so, there is a problem –
A LIKE predicate with a leading ‘%’ wildcard can lead to non-usage of suitable indexes, resulting in performance degradation. Even if you have an indexed column, you are most likely going to end up with a table scan if you use a WHERE clause that contains a LIKE predicate such as ‘%Search String’. That’s one of the reasons why we should always try to be careful when using this operation.
By using SQL Express Profiler, I captured the trace containing all the SQL queries fired against the database, when I search for a string(“Alabama”) using ‘ApexSQL Search’ tool. Looking at the SQL queries, we see that it uses LIKE predicate with both leading and trailing wildcard – probably the most easiest way to implement this search functionality.
One thing which I did not like about the above SQL query was that my search criteria was a string(“Alabama”), but it did scan all the columns for the search string – including columns with numeric and datetime datatypes.
However I noticed that the tool, out of the box, provides multiple filters to allow reducing the search scope —
If you are searching for a string value, you can uncheck the checkboxes to exclude ‘Numeric columns’ and ‘Date columns’. Since I was searching for the text – ‘Alabama’, I chose to just search the ‘Text type columns’ —
Capturing the trace again using SQL Express Profiler, we see that now it just searches the ‘Text type columns’ – thus reducing the search scope for an optimized performance —
Irrespective of whether you are writing a T-SQL query to search for data in SQL tables or are leveraging some optimized tool set which provides the same functionality, be aware that it will be a resource intensive operation. From a performance standpoint, ideally we should be avoiding usage of LIKE predicate with a leading wildcard.
If you are not an experienced T-SQL developer or do not have complete knowledge about database objects, then you should avoid writing/using a T-SQL query for this operation – because a wrongly written query might cause more harm on the database performance. Using an add-in like ApexSQL Search makes your job easier – since it is already tested for optimal performance. But I would highly recommend to use the ‘filters’ available in the toolset to narrow your search scope.
Do you use any other free SSMS add-in to boost your productivity in the IDE?
Please do share the same in the comments section below. #sqlfamily
Related Posts –