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#.

Its gonna be hard without knowing how is you table, indexes and so on. Please consider showing us the execution plan also. — Rafael Colucci, Jul 29 '11 at 16:39
i make store procedure. and i use data set to retrieve to store procedure. — chetan singhal, Jul 29 '11 at 16:41
And you have a number of relevant indicies? Don't know about the join optimization difference between using the comma-separated `from` clause and a standard `join` (I'd change that anyways, and put the join criteria in the `on` clause - future generations will thank you). Also, what is your expected time v. the real time? — Clockwork-Muse, Jul 29 '11 at 16:43
Can you generate a Query plan and share it here? How long is a long time? How many records are being returned? — Bob Probst, Jul 29 '11 at 16:45
@TrickyNixon: given the first line of the query I'm going to say somewhere around 10... ;) — NotMe, Jul 29 '11 at 16:49
max 30 sec my expected time but real time more than 2 min some time request time out — chetan singhal, Jul 29 '11 at 16:50
2 minutes against 100k rows sounds like a hardware I/O issue to me. — Bob Probst, Jul 29 '11 at 17:07
Try it without the order by and without set rowcount. Faster? How many records? — Bob Probst, Jul 29 '11 at 17:09

Al W · Answer 1 · 2011-07-29T17:45:14.343

4

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)

edited Jul 29 '11 at 17:45

answered Jul 29 '11 at 16:43

Al W

7,539
1
19
40

score 3 · Answer 2 · answered Jul 29 '11 at 16:41

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

score 3 · Answer 3 · answered Jul 29 '11 at 16:44

For one, use SELECT TOP () instead of SET ROWCOUNT - the optimizer will have a much better chance that way. Another suggestion is to use a proper inner join instead of potentially ending up with a cartesian product using the old style table,table join syntax (this is not the case here but it can happen much easier with the old syntax). Should be:

...
FROM Stxn INNER JOIN Sprovider
  ON Stxn.SproviderID = SProvider.Sproviderid
...

And if you think 100K rows is a lot, or that this volume is a reason for slowness, you're sorely mistaken. Most likely you have really poor indexing strategies in place, possibly some parameter sniffing, possibly some implicit conversions... hard to tell without understanding the data types, indexes and seeing the plan.

score 3 · Answer 4 · answered Jul 29 '11 at 16:48

There are a lot of things that could impact the performance of query. Although 100k records really isn't all that many.

Items to consider (in no particular order)

Hardware:

Is SQL Server memory constrained? In other words, does it have enough RAM to do its job? If it is swapping memory to disk, then this is a sure sign that you need an upgrade.
Is the machine disk constrained. In other words, are the drives fast enough to keep up with the queries you need to run? If it's memory constrained, then disk speed becomes a larger factor.
Is the machine processor constrained? For example, when you execute the query does the processor spike for long periods of time? Or, are there already lots of other queries running that are taking resources away from yours...

Database Structure:

Do you have indexes on the columns used in your where clause? If the tables do not have indexes then it will have to do a full scan of both tables to determine which records match.
Eliminate the ISNULL function calls. If this is a direct query, have the calling code validate the parameters and set default values before executing. If it is in a stored procedure, do the checks at the top of the s'proc. Unless you are executing this with RECOMPILE that does parameter sniffing, those functions will have to be evaluated for each row..

Network:

Is the network slow between you and the server? Depending on the amount of data pulled you could be pulling GB's of data across the wire. I'm not sure what is stored in the "raw" column. The first question you need to ask here is "how much data is going back to the client?" For example, if each record is 1MB+ in size, then you'll probably have disk and network constraints at play.

General:

I'm not sure what "slow" means in your question. Does it mean that the query is taking around 1 second to process or does it mean it's taking 5 minutes? Everything is relative here.

Basically, it is going to be impossible to give a hard answer without a lot of questions asked by you. All of these will bear out if you profile the queries, understand what and how much is going back to the client and watch the interactions amongst the various parts.

Finally depending on the amount of data going back to the client there might not be a way to improve performance short of hardware changes.

score 0 · Answer 5 · answered Jul 29 '11 at 16:41

0

Make sure Stxn.SproviderID, Stxn.status, Stxn.CreatedOn, Stxn.CreatedBy, Stxn.StxnID and SProvider.Sproviderid all have indexes defined.

(NB -- you might not need all, but it can't hurt.)

answered Jul 29 '11 at 16:41

Hogan

69,564
10
76
117

score 0 · Answer 6 · answered Jul 29 '11 at 16:41

0

I don't see much that can be done on the query itself, but I can see things being done on the schema :

Create an index / PK on Stxn.SproviderID
Create an index / PK on SProvider.Sproviderid
Create indexes on status, CreatedOn, CreatedBy, StxnID

answered Jul 29 '11 at 16:41

Wadih M.

12,810
7
47
57

score 0 · Answer 7 · answered Jul 29 '11 at 17:15

0

Something to consider: When ROWCOUNT or TOP are used with an ORDER BY clause, the entire result set is created and sorted first and then the top 10 results are returned.

How does this run without the Order By clause?

answered Jul 29 '11 at 17:15

Bob Probst

9,533
8
32
41

How to improve query performance

7 Answers7