Combined view of live and archive tables. UNION?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Combined view of live and archive tables. UNION??

Author

Topic

smeadows
Starting Member

4 Posts

Posted - 2008-12-16 : 05:47:16

I have 2 identical table fields, one a live table and one an archive table. I want to be able to present to my users a seamless, combined view for them to be able to search and view results for the 2 tables together. I tried creating a view that used a UNION ALL but the performance was appalling. I've been looking into creating an inline function but cant see how to call this in a view. Any ideas? Any alrernatives?

My original view, which worked but took forever to return results:

CREATE VIEW dbo.Original_Live_Archived_Combined
AS

SELECT *
FROM ARSystem.dbo.BU_60_Order_Processing
UNION ALL
SELECT *
FROM ARSystem_Archive.dbo.BU_60_Order_Processing

My funciton that i want to call in a view:

create FUNCTION fn_BU60Results
(
@BidderID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM LIVE.dbo.Order_Processing
WHERE BA_Bidder_ID = @BidderID
UNION ALL
SELECT *
FROM Archive.dbo.Order_Processing
WHERE BA_Bidder_ID = @BidderID
)

------

CREATE VIEW dbo.Live_Archived_Combined
AS

SELECT * FROM fn_BU60Results(??????? How to call??????)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 06:09:19

SELECT * FROM fn_BU60Results(101245) AS x

And you should REALLY REALLY not use asterisk to get your column names.
Use a column name list to ensure you stay on top of thing when columns are added and deleted in the future.

E 12°55'05.63"
N 56°04'39.26"

smeadows
Starting Member

4 Posts

Posted - 2008-12-16 : 06:48:25

I don't want to pre-define the 'Bidder ID' in the function or the call to the function. I want the user to be able to search from the view using a particular bidder id:

select * from BU_60_Live_Archived_Combined where ba_bidder_id = 1222

So this works when the view is using the UNION, but it takes forever. That's why i'm trying to find an alternative.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 07:00:49

You don't get it, do you?
You can use the function to ACT as a view and return a resultset just as a view.

SELECT * FROM fn_BU60Results(1222) AS x

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 07:01:43

quote:
Originally posted by smeadows

I don't want to pre-define the 'Bidder ID' in the function or the call to the function. I want the user to be able to search from the view using a particular bidder id:

select * from BU_60_Live_Archived_Combined where ba_bidder_id = 1222

So this works when the view is using the UNION, but it takes forever. That's why i'm trying to find an alternative.

can i ask whats the purpose of view if you've created an inline table function? would it be enough?whats the purpose of calling it from view. you can just call it like
SELECT * FROM fn_BU60Results(youridvaluehere)

smeadows
Starting Member

4 Posts

Posted - 2008-12-16 : 07:13:02

Peso... Easy tiger! I do get it thank you very much... I know how to call a function directly... I'm after help here, not condescending replies...

I'm developing in the BMC Remedy product. I'm trying to create a user form that can only reference SQL Views, not functions... A limitation of Remedy...

The users need to be able to query this form, therefore the sql view, using a parameter to return a limited result set from the underlying tables. They need to be able to query and see a combined result set from both a live and archive table. That's why i originally went with a sql view that was using a UNION ALL to combine the results from the 2 tables, but the archive table contains millions of rows and so the union was taking forever.

That's why i'm looking for alternatives to a union in a sql view. From my investigations other people have mentioned calling a function within the view but i can't see how this can work if the user needs to be able to query the view. How do you pass this query into the function within the view.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 07:53:19

Do you have the proper indexes on your tables? have you analysed why query against archive table is taking so long by looking into execution plan?

smeadows
Starting Member

4 Posts

Posted - 2008-12-16 : 07:58:58

Both the tables have the same indexes and the field i am searching on is indexed. Querying the 2 tables separately brings back an almost instantaneous result. Querying the UNION view takes a few minutes. From what i understand the UNION actually returns all the results from the 2 tables before the query is performed on it to limit the results displayed to the user. As i've said there are millions or rows in the 2 tables, hence why it is taking so long.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources