数据获取问题，更快的数据获取工具

Question

在我的查询中，我使用了索引，但是data以5分钟为单位获取结果，有时是以7分钟为单位。我在一个表中有4284399770条记录。是否有任何工具可以使用SQL服务器更快地获取数据？请推荐任何快速获取数据的工具或服务。

  Declare @FromDate Datetime  ='2019-06-27 16:57:01.000'        
Declare @Todate Datetime =    '2019-06-28 16:29:19.000'        
Declare @register_ID int =-9999    
Declare @location_number int =-9999    
Declare @receipt_number int =-9999    
Declare @VenueID int =-9999  
Declare @MediaType int =-9999
select        
 EJTable.register_ID'Terminal ID'        
,EJTable.location_number         
,EJTable.receipt_number        
,EJTable.VenueID'clerk NO'        
,EJTable.Sale_Total        
,EJTable.Receipt_date_time        
,EJTable.SQLPostDate        
from  EJTable WITH (NOLOCK,INDEX( IX_Receipt_Date_Time))  
INNER JOIN EJMediaTable WITH (NOLOCK ,INDEX (IX_MediaType) ) ON EJMediaTable.Transaction_Number = EJTable.Transaction_Number 
where          
(EJTable.Receipt_date_time between @FromDate and @Todate)      
and    
(isnull (@register_ID ,-9999)=-9999 or  EJTable.register_ID=@register_ID)    
and     
(isnull (@location_number , -9999)=-9999 or  EJTable.location_number=@location_number)    
and    
(isnull (@receipt_number ,-9999)=-9999 or  EJTable.receipt_number=@receipt_number)    
and    
(isnull (@VenueID ,-9999)=-9999 or  EJTable.VenueID=@VenueID) 
and
(isnull (@MediaType ,-9999)=-9999 or  EJMediaTable.MediaType=@MediaType)

Answer 1

我个人会在这里创建一个动态查询。然而，你可以使用OPTION (RECOMPILE)，因为你有各种不同的查询可以在这里运行(由于有5个NULL参数)，我认为只使用WHERE中需要的子句的动态方法给了关系数据库管理系统最好的机会。

正如我和Guzman都提到的，我也删除了这两个提示；出于不同的原因，这些提示更有可能弊大于利。索引作为查询可能返回错误的结果，而INDEX提示是因为索引不太可能对将要运行的大约15个不同的查询有帮助。

我不能测试这个查询，因为没有示例数据，但是您可以使用您最好的朋友来调试任何错误：

DECLARE @FromDate datetime = '2019-06-27T16:57:01.000',
        @Todate datetime = '2019-06-28T16:29:19.000',
        @register_ID int, --Use NULL, not a random non-NULL value
        @location_number int, --Use NULL, not a random non-NULL value
        @receipt_number int, --Use NULL, not a random non-NULL value
        @VenueID int, --Use NULL, not a random non-NULL value
        @MediaType int; --Use NULL, not a random non-NULL value

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT EJT.register_ID AS [Terminal ID],' + @CRLF +
           N'       EJT.location_number,' + @CRLF +
           N'       EJT.receipt_number,' + @CRLF +
           N'       EJT.VenueID AS [clerk NO],' + @CRLF +
           N'       EJT.Sale_Total,' + @CRLF +
           N'       EJT.Receipt_date_time,' + @CRLF +
           N'       EJT.SQLPostDate' + @CRLF +
           N'FROM dbo.EJTable EJT' + @CRLF +                                                                --Removed hints, they are very uinlikely to help here
           N'     JOIN dbo.EJMediaTable EJMT ON EJT.Transaction_Number = EJTM.Transaction_Number' + @CRLF + --And are actually more likely to cause worse performance
           N'WHERE EJT.Receipt_date_time >= @FromDate AND EJT.Receipt_date_time <= @ToDate' +
           CASE WHEN @register_ID IS NOT NULL THEN  @CRLF + N'  AND EJT.Register_ID = @Register_ID' ELSE '' END +
           CASE WHEN @location_number IS NOT NULL THEN  @CRLF + N'  AND EJT.location_number = @location_number' ELSE '' END +
           CASE WHEN @receipt_number IS NOT NULL THEN  @CRLF + N'  AND EJT.receipt_number = @receipt_number' ELSE '' END +
           CASE WHEN @VenueID IS NOT NULL THEN  @CRLF + N'  AND EJT.VenueID = @VenueID' ELSE '' END +
           CASE WHEN @MediaType IS NOT NULL THEN  @CRLF + N'  AND EJTM.MediaType = @MediaType' ELSE '' END + N';';


--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL, N'@FromDate datetime, @Todate datetime, @register_ID int, @location_number int, @receipt_number int, @VenueID int, @MediaType int',
                       @FromDate, @ToDate, @register_ID, @location_number, @receipt_number, @VenueID, @MediaType;