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
– rene, Commented Dec 23, 2013 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
– Tony Hopkinson, Commented Dec 23, 2013 at 11:24

Adarsh Shah · Accepted Answer · 2013-12-23 11:29:30Z

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, 2013 at 11:29

Adarsh Shah

6,7852 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sohail Over a year ago

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 made me better towards your answer..

Adarsh Shah Over a year ago

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?

Sohail Over a year ago

100 to 150 records. and I dont have index on UserId.

Adarsh Shah Over a year ago

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 blog.sqlauthority.com/2009/03/11/… .Also try adding non-unique index to UserId and see if it helps.

Sohail Over a year ago

Ya, I read that blog regulary.. got the perfect answer. Thanx Adarsh.. :)

Emil Reña Enriquez · Accepted Answer · 2013-12-23 11:15:05Z

-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, 2013 at 11:15

Emil Reña Enriquez

2,9912 gold badges31 silver badges32 bronze badges

4 Comments

Sohail Over a year ago

@Emil Rena: Where can I use the join??

RobH Over a year ago

Presumably there's a users table somewhere, you could join to that. Adarsh's answer is a better way to go.

Martin Smith Over a year ago

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.

Michael Buen Over a year ago

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 ienablemuch.com/2013/08/…

Collectives™ on Stack Overflow

Working SQL Server 2005 Query Optimization

2 Answers 2

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related