CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Sohail
Sohail

Reputation: 574

Working SQL Server 2005 Query Optimization

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

Upvotes: 3

Views: 75

Answers (2)

Adarsh Shah
Adarsh Shah

Reputation: 6775

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

Upvotes: 3

Emil Reña Enriquez
Emil Reña Enriquez

Reputation: 2971

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.

Upvotes: -1

Related Questions