Working SQL Server 2005 Query Optimization

Question

I have two tables Sell and Purchase. My query is giving me the desired result, I am carefull about it performance so plz guide me if it can be better. My tables are:
Table Sell

UserId  | ProductId    |  ProductName   | ProductPrice
1       | p_101        |  Cycle         |   500
1       | p_121        |  Car           |   500000
2       | p_111        |  Cycle         |   5000

Table Purchase

UserId  | ProductId    |  ProductName   | ProductPrice
1       | p_109        |  CellPhone     |   150
2       | p_121        |  Car           |   500000
3       | p_111        |  Book          |   15

Desired OutPut Table

Type    | ProductId    |  ProductName   | ProductPrice
Sell    | p_101        |  Cycle         |   500
Sell    | p_121        |  Car           |   500000
Purchase| p_109        |  CellPhone     |   150

Working Query:

SELECT type, P1.ProductId, P1.ProductName, P1.ProductPrice
     FROM 
        (
         SELECT s.UserId, 'Sell' as type, s.ProductId, s.ProductName, s.ProductPrice FROM [Sell] s
         UNION
         SELECT p.userid, 'Purchase' as type, p.ProductId, p.ProductName, p.ProductPrice FROM [Purchase] p
        ) as P1
     WHERE userid=1

Do you have the query execution plan? Any indexes? Is this ms-sql? This might be better suited for dba.stackexchange.com... Number of rows on those tables? — rene, Dec 23 '13 at 11:15
To see the plan, prefix the query with Explain and execute it. Saying that the only useful indexes are UserId in products and sales seeing as it's the only factor in the where clause. — Tony Hopkinson, Dec 23 '13 at 11:24

score 3 · Accepted Answer · answered Dec 23 '13 at 11:29

3

Better design is to combine both tables and have a transaction_type column which will either have "Purchase" or "Sell" as values. If you do that you won't have to do UNION or UNION ALL.

With current design here is a simple and faster way to get records. Note that I have used UNION ALL which is faster than UNION as UNION uses DISTINCT to unique records which I think in your case doesn't apply. If you provide details about the index and execution plan I can see if there is a better way.

SELECT s.userid,
    'Sell' as type,
    s.ProductId, 
    s.ProductName, 
    s.ProductPrice 
FROM Sell s
WHERE UserId = 1
UNION ALL
SELECT p.userid,
    'Purchase' as type,
    p.ProductId, 
    p.ProductName, 
    p.ProductPrice
FROM Purchase P
WHERE UserId = 1

answered Dec 23 '13 at 11:29

Adarsh Shah

6,755
2
25
39

if I run both queries on sql server 2005 and click on "Display Estimated Execution plan", It shows that both query are using 50%, 50% of cpu... with UNION and UNION ALL. but [this link](http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) made me better towards your answer.. – Sohail Dec 23 '13 at 11:40
How many records do you have in your table? Do you have a non-unique index on UserId? Also, as I said in the answer above, is it possible to use only 1 table with a transaction_type column? – Adarsh Shah Dec 23 '13 at 11:44
100 to 150 records. and I dont have index on UserId. – Sohail Dec 23 '13 at 11:46
1

100 to 150 records might not show the difference but when the table grows UNION ALL will help. You can read more about it at http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ .Also try adding non-unique index to UserId and see if it helps. – Adarsh Shah Dec 23 '13 at 11:50
Ya, I read that blog regulary.. got the perfect answer. Thanx Adarsh.. :) – Sohail Dec 23 '13 at 11:53

score -1 · Answer 2 · answered Dec 23 '13 at 11:15

-1

Its better to use joins rather than subqueries. This way, there will be no overhead on your queries specially on dealing with large volumes of data.

answered Dec 23 '13 at 11:15

Emil Reña Enriquez

2,929
1
29
32

@Emil Rena: Where can I use the join?? – Sohail Dec 23 '13 at 11:30
1

Presumably there's a users table somewhere, you could join to that. [Adarsh's](http://stackoverflow.com/a/20742625/1402923) answer is a better way to go. – RobH Dec 23 '13 at 11:31
2

`Its better to use joins rather than subqueries` isn't always true as a general statement. And difficult to see how it applies to the code in the question anyway. – Martin Smith Dec 23 '13 at 11:33
*JOIN is faster than subquery* is really a myth on real RDBMS(e.g. Oracle, Sql Server, PostgreSQL). MySQL subquery's is utterly slow, hence its join looks fast http://www.ienablemuch.com/2013/08/not-in-is-faster-than-left-join-is-null.html – Michael Buen Dec 23 '13 at 11:39

Working SQL Server 2005 Query Optimization

2 Answers2