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.
[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
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 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%
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.
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
[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.