Link to home
Start Free TrialLog in
Avatar of John Kininjjew
John KininjjewFlag for Australia

asked on

MSSQL dynamic queries and indexing

I want to write a query that is dynamic, sometimes the query will search 1 column, 2 columns or even more, depending on how much the user performing the query wants to refine the results. The front end will build the query based on a series of checkboxes. One check box for each column of the table. To keep the query as efficient as possible as this table will have millions of records Indexing is needed. I just don't quite know what approach to take when doing indexing for this kind of thing. I am conflicted as some suggestions are to do indexing but don't over to it. Where does one draw the line? There are potentially many combinations of query that can be dynamically generated. It's not practical to build an index for each and every possible combination. Any feedback, suggestions would be greatly appreciated.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> I am conflicted as some suggestions are to do indexing but don't over to it

Yes, that is pretty much a correct suggestion..
Before I comment on whether it would benefit or degrade performance on your table having Millions of records and you haven't shared any sample queries, would like to know more about
1. the columns involved and their datatypes
2. how much NULL or blank values available in those columns or in other words Cardinality
3. how much of records would be fetched as results from the table having Millions of records
4. Any Aggregation operation with GROUP by clauses in your query or not
5. Any ORDER by clauses in your query..
6. How many records in the SELECT clause from that particular table.

If the no. of columns in SELECT clause is less and uses the Primary key in your JOIN condition then you can try creating a covering index for all possible scenarios with the Key columns used in WHERE clause and other columns in INCLUDE clause..
Again, this is a generic suggestion without knowing your actual query so sharing your Query structure with scrambled column and table names would help to suggest better.
Avatar of John Kininjjew

ASKER

Hello, Thank you for your response. Its is only 1 table and does not include any JOINS or GROUPS. The table holds customer information. Basics are Given Names, Surnames address info and contact info. Most columns allow for Null as only A Given Name & Surname are required. Columns are VARCHAR & DATETIME there are some others but they are not required for the search. We limit search results to the TOP 100. If the user cannot find the result they need in the 100 results they need to provide more search criteria, By adding more detail to the search string or by selecting additional columns to search in. The data is always ORDERED by the Surname column. 
Hi,
Table index is exactly on specific columns only. If you need dynamic search on table, you have to create proper query with "dynamic criteria" inside to search the tables.
Is it really an SQL Server 2008?
Máté, Indeed it is. We will look at migrating to a newer version at some point in the near future.
If you cannot index table because of so many combination of criteria then make sure that server has enough memory to keep the whole table in memory to make table scans as fast as possible. Additionally you can use DBCC PINTABLE.
There should be only several specific indexes on one table. There is limit to create too many indexes. There is way to apply dynamic check through the codes.
>> Additionally you can use DBCC PINTABLE.                                  

No, DBCC PINTABLE is a deprecated command and no longer effective in SQL Server starting SQL Server 2005 itself..
Ideally DBCC PINTABLE is supposed to be used on smaller tables and not on big tables.

https://www.sqlskills.com/blogs/paul/dbcc-pintable/

>> Basics are Given Names, Surnames address info and contact info.

If at any point in time, you will be passing values for the above fields, then try creating a covering index with above fields as key columns and the remaining columns in INCLUDE clause which can help out..
Surname and GivenName seems to be the primary candidates for the clustered index.
Create one filtered index for each nullable column.  

For the search process you should use prepared statements. Create a new statement, if the columns number in the filter condition changes. As long as the user narrows down the search reuse the prepared statement.

Most columns allow for Null as only A Given Name & Surname are required.
Having a correct data model would also help ;) Cluster the nullable columns to entities and offload them into their own tables.

p.s. using a stored procedure with a catch-all clause and recompile hint can also work in some scenarios.
Is it Standard Edition or Enterprise?  I believe page compression is only available for Enterprise, iirc.

In general you'd want to cluster the table for the most common WHERE condition(s).  That will reduce the I/O the most up front.

Need the table DDL to give further detailed advice.  It's likely you need to encode 1 or more varchar columns in the table.  This will shorten the main table and allow some "side table" lookups.  More details on that later if you want to pursue it.
I think columnstore is too difficult to use in SQL 2008 to consider it as a viable option here.  Once you get to, say, SQL 2016, then columnstore would definitely be worth looking into for this table.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.