How to improve query performance

Question

I have a lot of records in table. When I execute the following query it takes a lot of time. How can I improve the performance?

SET ROWCOUNT 10
SELECT StxnID
      ,Sprovider.description as SProvider
      ,txnID
      ,Request
      ,Raw
      ,Status
      ,txnBal
      ,Stxn.CreatedBy
      ,Stxn.CreatedOn
      ,Stxn.ModifiedBy
      ,Stxn.ModifiedOn
      ,Stxn.isDeleted
  FROM Stxn,Sprovider
  WHERE Stxn.SproviderID = SProvider.Sproviderid
  AND Stxn.SProviderid = ISNULL(@pSProviderID,Stxn.SProviderid)
  AND Stxn.status = ISNULL(@pStatus,Stxn.status)
  AND Stxn.CreatedOn BETWEEN ISNULL(@pStartDate,getdate()-1) and  ISNULL(@pEndDate,getdate())
  AND Stxn.CreatedBy = ISNULL(@pSellerId,Stxn.CreatedBy)  
  ORDER BY StxnID DESC

The stxn table has more than 100,000 records.

The query is run from a report viewer in asp.net c#.

Answer 1

This is my go-to article when I'm trying to do a search query that has several search conditions which might be optional.

http://www.sommarskog.se/dyn-search-2008.html

The biggest problem with your query is the column=ISNULL(@column, column) syntax. MSSQL won't use an index for that. Consider changing it to (column = @column AND @column IS NOT NULL)

Answer 2

You should consider using the execution plan and look for missing indexes. Also, how long it takes to execute? What is slow for you?

Maybe you could also not return so many rows, but that is just a guess. Actually we need to see your table and indexes plus the execution plan.

Check sql-tuning-tutorial