How to import SQL query performance

Using sql server 2012.  I have a single table with about 12 million rows in it.   I have a full text index on the table on a few of the Text fields.  My primary key is DocumentID.

My query to the table is as follows:

      SELECT  COUNT(Document.DocumentID) OVER ( ) AS TotalRecordsReturned ,
        Document.DocumentID ,
        Document.FileNameDisplay ,
        Text1 ,
        Text2 ,
        Text3 ,
        Text4 ,    
FROM    Document
WHERE   CONTAINS ( Document.Text4, '"north*"' )       

I'm using Paging (OFFSET syntax), FULL TEXT Wildcard , and OVER in order to get total records returned.

The above query returns 25 records in about 30 seconds, telling me that there are 821,551 matching records.

If I run a more specific query  (ie.. wildcard text of "154 North Blvd"), I can return 200 rows almost immediation from the 12 million rows.   So, clearly the full text indexing is working well when I have a more specific query but it is all over the board from 15 seconds up to 1 minute 30 seconds when the number of overall matching records is high -- even though I only need to return 25 rows at a time.   I haven't figured out how else to optimize this query when searching for a broader search term.    Am I expecting too much from this query?

Is there anything else you need to help to optimize this query?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Even when you need to return 25 records at a time the less specific search term means to retrieve/count more data from the index and it takes time...

You could optimize the query when you would need just TOP 25 records without the total count. Index on Text1 could help in such case BUT not always.

You may evaluate the query performance by displaying the Execution plan in SSMS. It should show what is the bottle neck and you may focus on it.
Scott PletcherSenior DBACommented:
One big problem is the "ORDER BY Document.Text1 ASC".  SQL must sort the entire list first before it can choose the next 25.

If Text1 is unique and never null, you could use that as the key for the fulltext index, which I think would allow you to read far fewer rows to determine the next 25.
MikeCausiAuthor Commented:
Unfortunately, in this particular example, the Text1 sort, is because the user wanted to sort the list by Text1.   The user could have just as easily chosen to sort by Text 2, or Text 3 etc.   So, I can't really set any particular TextX value as the key (as it's not unique anyway).  Only DocumentID is unique.   I also need to get the total count of records, because in my user interface, I need to return the number of results back from the query (for paging purposes).

It's starting to sounds as if there isn't an easy fix here.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

The text1 field can be non-unique to speed the query up. What is the data type of this field. If we are talking about long varchar field then the index would consume too much resources and its effectiveness would be questionable...
MikeCausiAuthor Commented:
The data type for the TextX Fields are Varchar(1000).
Hmm... too long for effective indexing.

So what you could try is:
1) SELECT TOP 1000 ....  to reduce the output data set size  (you may display the note about the first 1000 rows on output and allow to select again with no restriction)

2) Add computed column LEFT(text1, 25) to the table. If you create an index on this column and use same expression in the ORDER BY clause then it could help  BUT this also depends on SQL engine optimization algorithms. It can decide about full text index processing first... and we are back in the 800000 rows retrieval.

So no simple solution exists when users can do whatever they want...
MikeCausiAuthor Commented:
Let me try to re-think this problem.  Clearly allowing the user to perform a search that could potentially return hundreds of thousands of rows is a meaningless query at the end of the day (the query drives a grid on webpage with paging).  Realistically, any query that would have returned more than say 1000 rows of data is meaningless and I'd be willing to present a message back to the user to be more specific in their search.

My new question, then, is there a way to get an "estimated" number of rows that would have been returned from a query (without a huge performance hit of actually executing the query) and then decide whether or not to proceed with the query?

Any design ideas for that?  

Partly agreed.

I need all records published on the web from time to time. The reason is their automated processing.

Back to the question: You are interested in a count based on the full text index and this will surely take less resources than data retrieval and result sorting because it reads just the index.

You may test it on the following query:
SELECT  COUNT(*) AS TotalRecordsReturned ,
FROM    Document
WHERE   CONTAINS ( Document.Text4, '"north*"' )

and decide if it is worth to split your query into two parts.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeCausiAuthor Commented:
Checking the counts first for a reasonable number of rows seems to be a good solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.