Select from a Union Query - Can this be improved?

[Dimm]

I have a function GetBestPrice that returns a table of values that are pulled from a union select query on 15+ Tables

I then call the function to get

SELECT * FROM
GetBestPrice()
WHERE Price IN
(
SELECT Min(Price)
FROM GetBestPrice()
WHERE STOCK > 0
)

This does work, but I have a strong feeling that it is not the most efficient way of getting a whole row from the union query.

The union is
Select * from Table 1
UNION
Select * from Table 2
etc

 

[AlexCuse]

For starters, it's usually more efficient if you specify only the columns you need in any query.

I would also pass in some kind of variable so that the union only returns rows applicable to what you are looking for in your union query. I imagine you'd only want a certain product that you are looking for best price for, correct?

HTH,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Dimm]

Alex.

The union only returns a single row from each table, maybe 15-20 results. However these results come from tables with up to 100k records and normally returns in less than 1 second.

I can reduce the amount of fields in the union, currently it returns around 20 and I only require 2 of them.

The query still takes a good 3 seconds.

Al

 

[gmmastros]

Will there ever be duplicates?

The reason I ask is that Union will return distinct records. If the nature of your data prevents this, then you should consider using [!]Union All[/!] which will return duplicate records if they exist.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Dimm]

George

The union returns one row from each table, this was by design thru unique keys but has been changed to a select top 1 which also speeded the query up.

Al

 

[gmmastros]

So... Is everything OK now, or do you need additional assistance?

To be honest, I wouldn't write the query the way you did because you are essentially calling the function twice and linking on it. Instead, I would have created a table variable and then inserted the data from the function in to it. I suspect that this would cause the query to take 1/2 the time (especially since you are returning such a small number of records).

For example...

Declare @Temp Table(Price Decimal(10,2), Stock Int)

Insert Into @Temp(Price, Stock)
Select Price, Stock
From   dbo.GetBestPrice()

SELECT * 
FROM   @Temp
WHERE Price IN 
(
SELECT Min(Price) 
FROM   @Temp
WHERE  STOCK > 0
)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Dimm]

George

Great that does the trick.

Thanks

Al