April 23, 2007 at 2:16 pm
I'm looking for a fresh perspective on this. I am looking to update a table with basically the typical orders/orderdetails type of relationship. It is however an events table, with an eventdetails table. I'm looking to do mass inserts (up to 1000) and I'm looking for a faster/better way to accomplish this. This happens in a VB application.
The way the app was originally written was individual insert statements for each table. It loops through the clients that need the event added to thier account and it all seems to work ok, but its slow. I'm looking to improve upon this, and would like to find a set based solution, but I just can't get my head wrapped around how I would do the inserts unless I used an updateable view.
Here's the applicable code...
CREATE TABLE Events (
ID INT IDENTITY,
CustId INT NOT NULL,
DateCreated DATETIME ,
CreatedBy VARCHAR(15)
)
CREATE TABLE eventDetails (
ID INT IDENTITY,
eventID INT NOT NULL,
notes VARCHAR(100)
)
DECLARE @maxid INT,
@username VARCHAR(15),
@datetoday datetime
SELECT @username = 'myuser', @datetoday = GETDATE()
--This is what I'm trying to replace
INSERT INTO events (custid, DateCreated, CreatedBy)
VALUES(100, @datetoday, @username)
SELECT @maxid = MAX(ID) FROM Events WHERE CreatedBy = @username
INSERT INTO eventdetails (EventID,Notes)
VALUES (@maxid, 'Notes...')
-- end stuff to replace
SELECT * FROM events INNER JOIN eventdetails ON [events].id = eventdetails.eventid
Yeah I know, no FK's... All of the Referential integrity is handled by the app, because I haven't been able to get that all fixed yet. What can I say I didn't write it, I just have to fix it...
Eventually I'd be looking to do an insert to repeat the same event with all of the same information except each row would be a different event.ID (obviously) and different CustID. And I'm trying to come up with a way of not having that happen in a loop, cursor etc...
I think that should about cover it. Thanks in advance for any insights.
-Luke.
April 24, 2007 at 12:43 am
Luke,
One thing you could consider here is to use SCOPE_IDENTITY() to identify @maxid. This will avoid having to do the
SELECT @maxid = MAX(ID) FROM Events WHERE CreatedBy = @username
for each record.
Jan
April 24, 2007 at 6:28 am
Yeah, I'd planned on changing that, but what I'd really like to make happen is the insert to occur in 1 statement instead of having to make 2 separate database calls (3 if you count the select @maxid bit...)
Thanks.
-Luke.
April 24, 2007 at 6:38 am
I would create a view from these 2 tables and INSTEAD OF INSERT rigger on it.
Trigger will populate the tables using code similar yours.
You may add grouping to eliminate duplicate rows before they get into tables.
_____________
Code for TallyGenerator
April 24, 2007 at 7:30 am
I have the impression that with the updateable view and the instead of trigger, you will just end up with the same code and the same inserts but then implemented in a trigger.
Is this really a one-to-many relationship? Are there more notes to one event?
How does the vb app collect events? Is its data source to find the events set based?
April 24, 2007 at 7:42 am
I was thinking the same thing, but the trigger could be faster than executing it from the client machine. I'll have to do some testing...
Anyhow, it's actually a one to one relation, There should be FK's defined and such, but there are none in the database at all. All Referential integrity is handled (quite poorly I might add) in the application.
The events are collected via a vb form with a bunch of drop downs, event type, notes, custid etc... then you have the option of adding the same event (albeit with a different eventid and such) to a number of different custid's at the same time. This is a somewhat slow process that is basically using a bunch of adhoc SQL statements as posted above, that are executed as the app loops through the selected custid's. It's the multiple custid's process that I'm attempting to speed up at the moment. I just can't really think of anything that would, a) allow me to pass the list of custid's in as a list or array or whatnot, and b) execute the insert in 1 statement. Without the same type logic with a loop or cursor or whatnot that is...
April 24, 2007 at 7:55 am
Luke,
If it is a one-to-one relationship, you could turn this around: create one table where you do the updates and create “Events” and “eventDetails” as views on it for backward compatibility.
A week ago or so there was an interesting article on this site passing a complete resultset at one time to the server in XML. (http://www.sqlservercentral.com/columnists/jSebastian/2908.asp) This might be usefull.
Regards,
Jan
April 24, 2007 at 8:33 am
Yeah I'd read the bit about XML and such, and was thinking it was a possibility for the future, but I was thinking that I'd most likely hit the upper limit of the 8000 characters in a varchar column. Also, it still contains the looping logic... I'll have to play with some different approaches, and see what works best in my environment...
1) a sproc instead of the ad hoc SQL statement in the app
2) view with an trigger
thanks.
-Luke.
April 24, 2007 at 3:45 pm
Consider that insert into view will be single transaction.
You don't need to play with transactions, with locking. SQL Server will do it for you.
And you can perform set inserts, not just line by line.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply