Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query for search function. Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user. For example, I have apple(with good quality) and i want orange(with poor quality). So the result will display all user that have orange(with poor quality) and want apple(with good quality).

The search query a bit long and i try to simplify as below:

This store procedures will receive user defined table(ItemID & Quality) as parameter

@WantUdt AS HaveItemUdt READONLY,
@HaveUdt AS HaveItemUdt READONLY

Search Query (user can define more than one items and quality, so i use IN):

SELECT * from tbl_Trade WHERE TradeID IN

 (SELECT TradeID from tbl_Want w INNER JOIN

  (SELECT TradeID FROM tbl_Have 
  WHERE HaveID IN (SELECT ItemID FROM @HaveUdt) AND 
  Quality IN (SELECT QualityID FROM @HaveUdt)) as h  --to filter [have], 

 ON w.TradeID = h.TradeID

  WHERE WantID IN (SELECT ItemID FROM @WantUdt) AND 
  Quality =IN (SELECT QualityID FROM @WantUdt)    --to filter [want]
)  

Above query work as expected. However, I am having performance issue. I try do stress test by execute this store proc for multiple times within specific time (few seconds), and my db(sql server 2008 express) seems can't effort and generate a timeout error Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I guess it is because the query above use too many IN CLAUSE. Is there any way to improve this query?

EDIT

Attached with the actual execution plan and statistic

(1 row(s) affected)
Table '#05C6342A'. Scan count 30813, logical reads 39101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemCustomSchema'. Scan count 14025, logical reads 26490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemHave'. Scan count 3182, logical reads 6476, physical reads 72, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchemaExtend'. Scan count 0, logical reads 24134, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchema'. Scan count 0, logical reads 29132, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#06BA5863'. Scan count 20899, logical reads 28266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemWant'. Scan count 4456, logical reads 8974, physical reads 42, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Trade'. Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_UserProfile'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan Image enter image description here

share|improve this question
    
Also post your execution plan –  Mihai Jan 19 at 14:42
    
@Mihai Ok. Edited –  My2ndLovE Jan 19 at 15:52
add comment

2 Answers

The UDT has lot of scans as per the query plan,please try to create UDT with clustered index (primary key)

http://stackoverflow.com/questions/4451052/how-to-add-an-index-or-primary-key-to-a-user-defined-table-type-in-sql-server

share|improve this answer
    
thanks. after i applied clustered idnex, the speed slightly increase –  My2ndLovE Jan 20 at 13:01
    
In my scenario, im not allowed to create primary key(for ItemID) as the udt might consists of duplicate ItemID. Or maybe i should add in a dummy column as primary key? –  My2ndLovE Jan 23 at 1:55
    
Though it sound logical to add a surrogate key(identity columns) to make it primary key,you need to evaluate the performance gain as i am assuming that you would not be joining using the new key in your query key. –  sqlengineer Jan 23 at 6:01
    
Please do look into specfic operators on nonclustered seek tbl_dota2itemschema which is used multiple times and has lot cost associated. –  sqlengineer Jan 23 at 6:07
add comment

I think that you could improve the performance of query in this way:

  1. Instead of SELECT * from tbl_Trade, select only the columns you really want to fetch data from. E.g. : SELECT Id, Timestamp FROM tbl_Trade
  2. SELECT ItemID FROM @HaveUdt and other similar SELECT statements are very expensive. Set a WHERE condition, where possible, to exclude useless records. E.g. : SELECT ItemID FROM @HaveUdt WHERE Condition = Value
  3. Use temporary tables (E.g. : #myTemporaryTable) in which you'll store the data from the tables @HaveUdt and @WantUdt. Use them in your search query to get the ItemID, QualityID data and so on, in order to avoid a next scan for the records, like currently happens with : SELECT ItemID FROM @HaveUdt and SELECT QualityID FROM @HaveUdt. Once your search query is finished, drop the temporary tables.
share|improve this answer
    
Thanks. If i have created UDT with clustered index, do i need to use temporary table? –  My2ndLovE Jan 20 at 13:02
    
@My2ndLovE The clustered index improves the performance and affects the physical order of your data. I think you'll be able to select rows in a faster way. I suggested the using of temporary tables in order to avoid a continuous 'read' loop in your query. Having already a temporary table with all the records you need, I think the performance of the query will improve again. –  Alberto Solano Jan 20 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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