Union query question

[jadams0173]

Using SQL 2000

I have two tables. At the begging of the day both tables are identical. However, at mid day the dynamic table updates and it takes about 40 minutes to load the updated data (about 2 million rows).

During this reloading time, my application is in a blackout time since this huge table is reloading and that's the table my procedure uses so I get current data after the update.

My question is, if I used a UNION query to join the tables would this eliminate the blackout period?

example

Select Col1, Col2, Col 3
From StaticTable

Union 

Select Col1, Col2, Col3
From DynamicTable

 

[gmmastros]

I would suggest that you investigate why it takes 40 minutes to load 2 million rows. This seems like an extremely long time for such a relatively small amount of data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey gmmastros. Unfortuantly I can not change how the data is loaded or even investigate. I know the data is read from a DBF file using a DTS package.

That's the reason I'm trying to eliminate the blackout time.

 

[SQLBill]

The data will still be loading and thus in a 'blackout' state. You have two options:

1. if you care about the 'state' of the data - that means you only want to select the data after it has changed. You have to wait until the load is finished.

2. if you don't care about the 'state' of the data - you don't mind getting out of date data - try using the NOLOCK hint. That will permit a dirty read of the data.

For example:

Select Col1, Col2, Col 3
From StaticTable (NOLOCK)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jadams0173]

I guess, as usual, I didn't give enough detailed information. I am actually using views in my sql since that's the way it was setup.

The views are written like

Create View dbo.V_StaticTable
as
Select *
from StaticTable NOLock

So I then query the view so the nolock hint is already in use.

Select Col1, Col2, Col 3
From V_StaticTable

Union 

Select Col1, Col2, Col3
From V_DynamicTable

 

[gmmastros]

Are there parenthesis around the NoLock ? If not, SQL is probably interpreting NOLOCK as a table alias.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Yes there are parenthesis around NoLock. That was my error.

Short of redisgning how the imports work, which won't happen, is there anything I can do to eliminate this?