8

I am attempting to query between 0 and 65,000 rows from a table.

The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.

Schema

[Id] (PK)        INT      
[varchar1]  VARCHAR(4)     Normal Cardinality
[varchar2]  VARCHAR(250)   Normal Cardinality
[varchar3]  VARCHAR(250)   Normal Cardinality
[varchar4]  VARCHAR(100)   Normal Cardinality
[date1]     DATETIME       High Cardinality
[varchar5]  VARCHAR(100)   Low Cardinality
[varchar6]  VARCHAR(1000)  Normal Cardinality
[varchar7]  VARCHAR(100)   Normal Cardinality
[varchar8]  VARCHAR(20)    Normal Cardinality
[varchar9]  VARCHAR(100)   High Cardinality
[xml1]      XML            Low Cardinality

Query

The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance). The column names have been replaced with the column type and a number:

SELECT   [varchar1]
       , [varchar4]
       , [date1]
       , [varchar5]
       , [varchar6]
       , [varchar7]
       , [varchar8]
       , [varchar9]
       , [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND   ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND   [varchar8] = 'someText'
AND   [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLY

Execution Plan

Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9

But the information it gave so far is:

Query1: Query cost (relative to the batch): 100%
                               Index Seek [NonClustered]
 SELECT            Top          [Table].[i_table_index]
Cost: 0%         Cost: 4%              Cost: 96%

Execution Plan Statement

I am not using a PK in this search. This query has the following durations depending on the columns removed:

All columns in select statement =   18s-27s
Without [xml1] =                     8s-11s
Without [xml1] and [varchar4] =      4s-6s

The total execution from Web to DB and back needs to be within 10s. Preferably this query needs to be within 4s.

Return Size (MB)

I am looking to get anywhere between 290-310 MB worth of data back, with a margin of error being 20 MB, for 65,000 records total.

Index

There is a clustered index on the Primary Key.

There is a non-clustered index defined as:

CREATE NONCLUSTERED INDEX [i_table_index]
ON dbo.[table] ([varchar1], [date1] DESC, [varchar8], [varchar9])
INCLUDE ([varchar4], [varchar7], [xml1]);

Unfortunately I can't really remove [xml1] or [varchar4] or I would. The index didn't seem to help much, to be honest, even though the execution plan showed it was using it.

My SQL Experience is limited to what I do with C#. I appreciate any help or guidance offered (even links), and if you need more information please let me know, I'll do my best to get it.

0
0

Each of the elements in the where clause are using functions. When functions are used sql cannot determine what index to use. It cannot determine what fields are in the where clause causing a table scan.

  1. Converge the date between to date => and date =<
  2. Is the database really case sensitive? Can you get rid of the lower()
  3. Can you turn the contains into a patindex
1
  • 2
    I did as you said the execution plan changed from an Index scan (which it was doing previously to an Index seek (which still did not speed up). I did change the contains into a padindex, but it also didn't affect the duration of the query. – Blacky Wolf Feb 1 '17 at 15:28
0

Bret's overview and item two I would agree with, but must counter item 1 and 3:

  • Between is fine, as it will be optimised internally to exactly what Bret suggested. But be aware that between is inclusive, but only to midnight, i.e. your example query will include records with 2017-1-1 if the column is date only, or 2017-1-1 00:00:00 (but not 2017-1-1 00:00:00.001 and up) if datetime.
  • Generally speaking, Contains against a full-text index will be far faster than patindex. However, for this particular query it is possible that patindex will be faster especially if the column is included (i.e. with the include clause) in the index that the query mainly relies on). But keep in mind that contains is fuzzy, patindex is not (except more narrowly if you use wildcards).

I see you've just done a big update, so I'll perhaps do a separate broader answer. Since I can't yet comment, I'll ask a few things here:
- Are you free to add indexing as your query requires?
- Is the replacement of CONTAINS([varchar3], 'moreText') with [varchar9] LIKE '%a%' correct (i.e. do you definitely no longer need fuzzy search on varchar3?)
- Will OFFSET always be 0?
- Can you give some idea of the proportions of data you expect for columns varchar1, date1 and varchar8?

4
  • As long as inserts aren't heavily impacted, then yes. 1000's of inserts are performed a day into the table. OFFSET will not always be 0 and is actually a variable passed to the stored procedure. [varchar3] represents a string of lowercase letters and numbers (and dashes). I can properly format the text in the application if I need to (plus CONTAINS was giving me problems anyways because of stop words). – Blacky Wolf Feb 1 '17 at 16:43
  • Ok, so low 1000s, or likely 10000s? And Date1 is the insert time, correct? – T.H. Feb 1 '17 at 17:03
  • And for an average day, how many unique varchar1 and varchar8 would you estimate? (I'm guessing both are some sort of code/classification based on the lengths.) – T.H. Feb 1 '17 at 17:05
  • Easily 10,000s (within a timespan of 15 minutes). Unique [varchar1]'s probably range around 120-170 (of 208 unique ID's that have been used) and [varchar8] is most likely 7 on average (of 20 unique ID's that been used) – Blacky Wolf Feb 1 '17 at 17:25
0

Ok, with only a nightly large insert even nolock can only be an issue during that presumably minimal-activity period, so all I would suggest to change in your latest query version is to remove the "[varchar1] ASC," as it's unnecessary (since there will only be one selected) and will actually interfere.

The indexing is going to be key.

I'd try replacing your nonclustered index with the following:
Create Index [i_table_index] ON Table1 (date1, varchar1, varchar8)
INCLUDE (varchar9, varchar4, varchar5, varchar6, varchar7, xml1)

This will satisfy your query without cluster key lookups.

Why date1 first? Because only the first index column can be used for range scans, and clearly if your users are going to need up to 65k rows at a time they will need largish date ranges.
However, it's possible the selectiveness of varchar1 may outweigh the range scan benefit, or that I've not fully understood the first-index-column-only rule (for which I can't find a good source at the moment--and it's possible the rule is actually range-scan-on-any-column-but-only-one). So since you've apparently got real-world data to test on, I would certainly also try this index instead (just the first two columns are swapped):
Create Index [i_table_index] ON Table1 (varchar1, date1, varchar8)
INCLUDE (varchar9, varchar4, varchar5, varchar6, varchar7, xml1)

Also, beware that having date1 first may make the first query page (i.e. OFFSET 0) work nice and fast, but bog down with high OFFSET values. So test both index options with both high and low OFFSET values.

Note I'm assuming that date1 ascending will be usable for the sort and fetch, but that depends how it's implemented internally. You may have to add DESC after date1 (in either index proposed so far), which I believe will make a internally fragmented mess of newly inserted pages in the nonclustered index--but perhaps you can run a reorg after the insert job.

Finally, the long INCLUDE clause will duplicate nearly all the data in your table, which may be an issue especially with large XML. (Disk space may not be such an issue, but the added memory caching may make a difference.)
So you may want to test with just varchar9 in the INCLUDE clause (kept there to efficiently satisfy the filter), as it's possible the non-filtering lookups may perform acceptably.

And you might even want to try keeping the PK as is, having no non-clustered index, and clustering on (date1, varchar1, varchar8) which would satisfy the queries in the same way as the long include, but with no wasted space. However if it turns out you need DESC after date1, clustering in this way would possibly cause too much fragmentation pain.

5
  • I did the different indexes like you suggested. I even removed all of the non-clustered indexes and the duration remained unchanged from previous queries. What's strange to me is the average duration of the query doesn't change whether I removed or reorder the indexes, only when I removed the xml1 and varchar4 columns... – Blacky Wolf Feb 1 '17 at 22:16
  • Is varchar4 really length 100? I can see why the XML column could be bogging you down, but if removing varchar4 also makes a major difference then I suspect the information we have about that column is incomplete. We really do need the execution plans to go any further. Also an important factor is that I've been assuming you're testing your query in a way that is not sensitive to network and app delays. If you're actually doing all your testing in the app, especially if over the network, then the slowness could have many more factors than the SQL performance. – T.H. Feb 2 '17 at 9:02
  • @T.H. varchar4 seems to range between 7-20 characters, though the size allotted is 100. Also uploaded a link to GIST for the execution plan (I think?). Testing is being done through SSMS and it is over a network. – Blacky Wolf Feb 2 '17 at 14:55
  • That execution plan is as efficient as it can be. Try turning on the discard results option in your SSMS window (query - query options, discard results setting in grid or text results section). This will make SQL Server do all the same work (confirm with execution plan and trace) but just not send any result rows. I believe it will be plenty fast and that you'll simply need to lower your timing expectations for processing the required amount of data over the network -- at least in your dev environment. – T.H. Feb 4 '17 at 11:47
  • @BlackyWolf If the discard results test is satisfactory, be sure to still test the query with realistically higher offset values as well, as in such a case SQL Server won't be able to take the same shortcuts as with offset 0. – T.H. Feb 4 '17 at 11:58
0

There is still a lot about your data that is not apparent. I suspect that the biggest drag on your performance is the XML column, since an XML column is often quite large.

Are you actually using every XML column that is in the fetch range? If there are relatively few XML columns that that match your goals, you might consider using:

SQL Server Indexed Views: The Basics

Jes Borland provides a good explanation and quite a bit of guidance on using Indexed Views. Note the following where I reference some of her sample code:

Schema Binding(1) is required to create an indexed view:

CREATE VIEW Sales.vCustomerOrders
WITH SCHEMABINDING
AS
SELECT  CUST.CustomerID ,
        PER.FirstName ,
        PER.LastName ,
        SOH.SalesOrderID ,
        SOH.OrderDate ,
        SOH.[Status] ,
        SOD.ProductID ,
        PROD.Name ,
        SOD.OrderQty
FROM    Sales.SalesOrderHeader SOH
        INNER JOIN Sales.SalesOrderDetail SOD 
               ON SOH.SalesOrderID = SOD.SalesOrderID
        INNER JOIN Production.Product PROD
               ON PROD.ProductID = SOD.ProductID
        INNER JOIN Sales.Customer CUST
               ON SOH.CustomerID = CUST.CustomerID
        INNER JOIN Person.Person PER
               ON PER.BusinessEntityID = CUST.PersonID;

A Unique Clustered Index is needed in order to turn a normal Sales.vCustomerOrders view into an indexed view that physically exists. For example, the indexed view could now look like:

SELECT  CustomerID ,
        FirstName ,
        LastName ,
        SalesOrderID ,
        OrderDate ,
        Status ,
        ProductID ,
        Name ,
        OrderQty
FROM    Sales.vCustomerOrders CO; 

If relatively few of the XML columns are actually needed in your query, then you could join the candidate XML columns to the Unique Clustered Index, perhaps greatly reducing the total amount of data being read. This may well result in a faster execution plan.

Of course, if you actually need every XML column then there would be little benefit. But that depends on the factors described in the linked article.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.