Query Question

milo xml

So I'm working on a vendor's database trying to search it. It's not normalized real well and the tables are kind of elephanted up as it was originally an MS Access database that they ported to MSSQL. I cannot modify the database but I can create views. In any case, what I currently have is a recursive string builder assembling a SQL statement that searches across multiple tables and views that I created to try to streamline things (one of my first SQL projects, it's not pretty). As you can imagine, it's sloooow. Can anyone point me towards a tutorial that would help me optimize this search? Current query and code:

            string\[\] strSearch = TextBox1.Text.Split(' ');
            string strSelect = "SELECT INVY.ITEMNUM AS Item, " +
                "INVY.DESCRIPTION AS Description, STOCK.QTYONHAND AS Quantity, " +
                "INVY.UOM AS 'Issue By', STOCK.LOCATION AS Location, INVY.MODEL AS Model, " +
                "INVY.UD2 AS IDGroup " +
                "FROM INVY LEFT OUTER JOIN STOCK ON INVY.ITEMNUM = STOCK.ITEMNUM " +
                "LEFT OUTER JOIN vw\_Koogle\_ISSREC\_Grouping ON INVY.ITEMNUM = vw\_Koogle\_ISSREC\_Grouping.ITEMNUM " +
                "LEFT OUTER JOIN INVVEND ON INVY.ITEMNUM = INVVEND.ITEMNUM " +
                "WHERE ";

            foreach (string strParam in strSearch)
            {
                strSelect = strSelect + "AND ((INVY.DESCRIPTION LIKE '%" + strParam + "%') OR " +
                "(INVY.NOTES LIKE '%" + strParam + "%') OR " +
                "(INVY.MODEL LIKE '%" + strParam + "%') OR " +
                "(INVY.UD2 LIKE '%" + strParam + "%') OR " +
                "(INVY.UD1 LIKE '%" + strParam + "%') OR " +
                "(INVY.ITEMNUM LIKE '%" + strParam + "%') OR " +
                "(vw\_RRD\_Koogle\_ISSREC\_Grouping.EQNUM LIKE '%" + strParam + "%') OR " +
                "(vw\_RRD\_Koogle\_ISSREC\_Grouping.NUMCHARGEDTO LIKE '%" + strParam + "%') OR " +
                "(INVVEND.VENDORITEMNUM LIKE '%" + strParam + "%')) ";
            }
            strSelect = strSelect.Replace("WHERE AND", "WHERE") +
                "GROUP BY INVY.ITEMNUM, INVY.DESCRIPTION, STOCK.QTYONHAND, " +
                "INVY.UOM, STOCK.LOCATION, INVY.MODEL, INVY.UD2 " +
                "ORDER BY INVY.DESCRIPTION";
            sqlMP2Search.SelectCommand = strSelect;
        }

Afzaal Ahmad Zeeshan

Do you want to get so many tutorial URLs that point to nothing, and might not be able to provide a valid result? I am sure, not. Please share a sample query with us, that you are running and the result you are getting. It would be really helpful to share the schema with us, and we would be able to help you out with the normalization process, or at least share how you can use other services on SQL Server like stored procedures to minimize the overheads of the queries. You might also need to incorporate caching services to store the results, instead of having to always rerun the query. [Query Optimization and the SQL Server Cache](https://www.codeproject.com/Articles/543164/QueryplusOptimizationplusandplustheplusSQLplusServ) Please share more details on this, and we will be able to provide you a good solution—or tutorial, as needed.

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

milo xml

Updated the question. Hope that helps. Thanks for the feedback!

Lost User

Full-Text Search - SQL Server | Microsoft Docs[^]

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Afzaal Ahmad Zeeshan

With that update in the question, first thing that anybody is going to say is to remove the concatenation and use parameters in the query—it will protect you from [SQL Injection](https://en.wikipedia.org/wiki/SQL\_injection). Secondly, there are so many LIKE operators, why are you using searches on the table, and trying to match every column with the input. A quick tip would be, use a separate search for each column and then try to aggregate the overall results. This would have a little amount of WHERE clause, and the query would end quickly, yours is having multiple OR clauses, which is not letting SQL Server short-circuit the query either and is making it run on each of the records. I would also not be so sure as to whether any indexing would speed up things, but you can try adding indexes on these columns. Read this for more on that, [tsql - SQL Server: Index columns used in like? - Stack Overflow](https://stackoverflow.com/questions/1388059/sql-server-index-columns-used-in-like) So, you can start by: 1. Add indexers to the columns as needed. You will know where to add them. 2. Create separate stored procedures to find the query results. Then try to aggregate the result of all queries. 3. Use caching to store the results of the most recent queries. 4. Also, try using full text search capabilities of SQL Server. [Full-Text Search - SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-2017) [sql - What is Full Text Search vs LIKE - Stack Overflow](https://stackoverflow.com/questions/224714/what-is-full-text-search-vs-like)

The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~

milo xml

Thanks!

milo xml

It's definitely one of the ugliest things I've ever written but also one of my first. Thanks for the pointers, definitely going to be a big help :)

Lost User

You're welcome. Should be faster for a search than filtering each table, at the cost of some diskspace.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.