Application Development - Searching Millions of Records

Transact-SQL
,
1

I am trying to determine this best design\option for being as efficient as possible.

I have an application that searches a table with several million records for an item and returns 5 items of data

SQL 2005, called from a C#.net app

I am thinking since it is 2005, to institute full-text Indexing, what do you think?

2

Depends on the specific "search" you're doing. If you're checking for an integer or date, full-text index is useless. If you're checking a specific varchar column for a specific value, full-text is not required.

3

Checking for a GUID. so I know there is only 1 unique value in the column.

4

If the data type of the column is UNIQUEIDENTIFER (or even if it is char/varchar), creating an index on that column would be the best solution. Full-text indexing/searching is not required (and is not applicable if the data type is UNIQUEIDENTIFIER).

1 Like
5

Are all the values in that column unique?

SELECT [Rows] = SUM(CASE WHEN YourGUIDColumnName IS NOT NULL THEN 1 ELSE 0 END)
       , [Unique values] = COUNT(DISTINCT YourGUIDColumnName)
       , [NULLs] = SUM(CASE WHEN YourGUIDColumnName IS NULL THEN 1 ELSE 0 END)
FROM YourTable

will tell you - if Rows and Unique are the same, and NULLs is Zero then you can set the create the index as UNIQUE - which will a) be a useful hint to the query optimiser and b) prevent anyone accidentally adding a duplicate value.

If NULLS is not Zero then you can still make a Unique Index (normally not possible because there would be multiple rows for the value "Null") by using a filtered index to exclude the NULL rows from the index BUT you may find you have to change your query to ensure that the index is used. i.e. instead of

SELECT Col1, Col2, ...
FROM YourTable
WHERE YourGUIDColumnName = @SomeGUIDParameter

you may have to also add:

SELECT Col1, Col2, ...
FROM YourTable
WHERE     YourGUIDColumnName = @SomeGUIDParameter
      AND YourGUIDColumnName IS NOT NULL

to EXACTLY match the filter on the index (which can be required by SQL to "realise" that the filter condition on the index is satisfied). The parser seems to be getting smarter, version-by-version, in that regard though.