November 5, 2007 at 10:09 am
I'm currently getting a xml stream from a vendor provided API, after which I truncate my table and do a bulk insert. The problem I'm having is during that 60 /90 sec(maybe less) time-span that the data is being truncated and inserted if a user hits the web page it will throw an error. The truncate is done 1st in the DTS package, then the bulk insert is called. Does anyone have suggestions on how I can do this more efficiently? I could do a truncate table in the stored procedure.
TIA
November 5, 2007 at 10:39 am
Isnt the loading process being done during off hours?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
November 5, 2007 at 12:16 pm
no, it's done 3 times a day.
i know it's a bad idea, but not my decision.
November 5, 2007 at 12:27 pm
This might not be palatable, but here's a thought. Add a step at the beginning of your import process that updates a "site status" table from "online" to "offline". flip it back to online once the data insert is done.
Have your ASP check that table first. If it's online, then go query the data. If offline - just ask them to hold on and try again in a few minutes, and/or have the ASP page try again in a few minutes (there's an AJAX function for that, if your environment would support that).
A second way is to build the secondary table "in parallel" from scratch from the "CREATE TABLE" statement on up, and then to perform a last minute "rename" of the "old table" while you swap in the "new table". That's actually MORE work for the server to do (since you'd have to script all of the table attributes, indexes, etc...), but it should make the time to switch much smaller.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 12:56 pm
Matt Miller (11/5/2007)
This might not be palatable, but here's a thought. Add a step at the beginning of your import process that updates a "site status" table from "online" to "offline". flip it back to online once the data insert is done.Have your ASP check that table first. If it's online, then go query the data. If offline - just ask them to hold on and try again in a few minutes, and/or have the ASP page try again in a few minutes (there's an AJAX function for that, if your environment would support that).
Matt,
Thanks this is the solution that i was looking for, just not sure how to implement.
Can you give me an example.. not in AJAX, i'm doing it in asp.net 1.1 or give me a hint on how to take the table offline/online.
TIA
November 5, 2007 at 1:28 pm
I didn't explain that very well. I was thinking of an entirely separate table, probably holding just one row, called "ImportStatus" (or anything descriptive to you).
For the purposes of this - let's call the table ImportStatus.
CREATE TABLE ImportStatus (StatusField varchar(10))
so, in front of the existing process (that runs 3x day), add a step to update the status
UPDATE ImportStatus set StatusField='Offline'
...
Do the rest of the process, then add another line to put the status back
...
UPDATE ImportStatus set StatusField='Online'
In the OnLoad property of your ASPX page - just open up a scalar data call to go pull the value out of ImportStatus (meaning - call a SQL function or SQL Command, and check the value of the field you get back). If you get a value of 'Offline' then skip databinding anything else (since it will fail); you might even kick them to a flat HTML error page.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 1:52 pm
John, how much of you data is actually changed during those updates?
From my experience it's typically 5-10% of the total amount of data.
Then you need to upload into some staging table and do DELETE/UPDATE/INSERT to main table where it's needed.
This will cut your outage time to seconds and properly lock your table, so users will see sand-glass pointer, not an error.
_____________
Code for TallyGenerator
November 5, 2007 at 2:23 pm
Matt,
thanks.. that is what i'll do.
Sergiy,
the data is not really that much but the problem is it's coming from a vendor api with a standard data for all their users. Unfortunately they don't have anything that will distinguish updates, or new data rows.
November 5, 2007 at 6:34 pm
John N (11/5/2007)the data is not really that much but the problem is it's coming from a vendor api with a standard data for all their users. Unfortunately they don't have anything that will distinguish updates, or new data rows.
You can figure it out after you have uploaded the data.
Compare data within 2 tables and synchronize it.
It replaces single big EMPTY/POPULATE thing with 3 small DELETE/UPDATE/INSERT statements.
And you never can cause errors on UI.
_____________
Code for TallyGenerator
November 5, 2007 at 6:50 pm
can you give me a hint on how to do this?
tia
November 5, 2007 at 7:57 pm
Let's assume that:
TheTable is your table you update;
StTable is Staging Table you just inserted;
KeyCol - one or more columns uniquely identifying rows in TheTable.
[Code]
DELETE FROM TheTable
WHERE NOT EXISTS (select 1 from StTable where TheTable.KeyCol = StTable.KeyCol)
UPDATE T
SET {all columns not in KeyCol}
FROM TheTable T
INNER JOIN StTable S ON T.KeyCol = S.KeyCol
WHERE {check here that values in columns are actually changed}
INSERT INTO TheTable
({all columns}) -- corrected after Matt's note
SELECT {all columns}
FROM StTable S
WHERE NOT EXISTS (select 1 from TheTable T where T.KeyCol = S.KeyCol)
[/Code]
_____________
Code for TallyGenerator
November 5, 2007 at 8:19 pm
Sergiy -
On the INSERT part... you SURE you want to NOT include the keycol?
I'm assuming that's a cut/paste error?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 9:04 pm
Matt Miller (11/5/2007)
Sergiy -On the INSERT part... you SURE you want to NOT include the keycol?
I'm assuming that's a cut/paste error?
How did you know?
😀
_____________
Code for TallyGenerator
November 5, 2007 at 9:17 pm
:Whistling:oh.. I don't know....never done THAT before....:blush:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2007 at 9:51 pm
Wouldn't it just be easier to create two identical tables... have a surrogate passthrough view pointed at 1 for access while the other is being loaded. When all done loading, just alter the view... total down time less than 65 milli-seconds and I guarantee no blocking on read only tables.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply