November 14, 2014 at 10:48 am
Hi
Have an old app that imports data into a MSSQL server DB using an ODBC connection.
Using Profiler I can see it does this per record
one call to DB
DELETE FROM tblCustomer WHERE Cid =1
2nd call
Insert into tblCustomer (Cid, fname, lname) VALUES (1, 'bob', 'smith')
I would say 98% of the time it is inserting new records. So the delete affects no rows.
We are limited in what we can do, but playing with its config files
I can get it to do this
UPdate tblCustomer
SET fname ='Bob', lname ='Smith' WHERE CID=1
IF @@ROWCOUNT = 0
Insert into tblCustomer (Cid, fname, lname) VALUES (1, 'bob', 'smith')
Would it make it much quicker as an Stored Proc?
Advice appreciated.
November 14, 2014 at 11:51 am
I like your second method better because it does stand a chance of short circuiting one hit on the database if the row already exists and won't hit the log file twice in such cases either. I wouldn't expect a miracle here, though, because it's still going to require SQL Server to create or reuse 2 execution plans. It is RBAR, after all.
What might make it a fair bit faster is to have a "staging" database that's set to the SIMPLE recovery model. If a minute of delay won't hurt anything, have the app insert into that table and then create a scheduled job to sweep the table once a minute to add (upsert) the rows by batch.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2014 at 6:25 am
Thanks Jeff
The DB containing tblcustomer is already SIMPLE. Quite a few indices as well.
I was just wondering if an SP would be quicker.
It probably needs turning into an IIS job (I've no experience of that) or a set of Bulk inserts into staging tables. Either way alot of work.
Before I changed Delete + Insert for Update/Insert in one statement I thought would an SP make sense?
create proc mysp_cust_write
@ID int,
@fname varchar(32),
@lname varchar(32)
AS
UPdate tblCustomer
SET fname =fname, lname =@fname WHERE CID=@ID
IF @@ROWCOUNT = 0
Insert into tblCustomer (Cid, fname, lname) VALUES (@ID, @fname, @lname)
November 15, 2014 at 7:26 am
In either approach, the code will generate two plans - one for each statement. I think what Jeff was referring to it the staging database not having to log every single row being written one at a time.
If you can code what you posted above in a configuration file, then you can probably also code an EXECUTE statement in the same manner and pass your values into the procedure you call. Will it be faster? Well, if the procedure contains the same statements, then the same amount of work will be performed. Unless you change the code, which is pretty straightforward, the work (reads, CPU, result) is going to be the same. One benefit I see is that you would have the data handling in the database, where it's probably easier to maintain if you need to.
The only way to truly test performance would be to set it up and benchmark the approaches against each other. Granted, if an application is making the call, you might be limited in how fast you can get it to go, which inhibits high-volume testing.
November 16, 2014 at 3:18 am
Thanks
What about making use of the fact that it will always be inserting new records and very rarely having to Update exisiting
Would this Pseudo Code be better (if I could turn it into TSQL)
Try
INSERT INTO TBLCustomer (f1...) VALUES(v1...)
Catch
IF Error = 'Primary Key Validation'
UPDATE TBLCustomer SET F1=V1, ... WHERE CiD =1
ELSE
RAISEERROR -- Pass on non PK Errors
I thought SP were slighter faster because they were compiled, but from what I've read, it would seem in later editions of SQL Server this performance difference has gone?
I do agree with control/admin much easier with SPs/Views etc.
January 23, 2015 at 6:39 pm
Since the majority of the activity is inserts you may also want to investigate something like this:
INSERT INTO tblCustomer (cid, fName, lName)
SELECT @cid, @fName, @lName
WHERE NOT EXISTS (
SELECT *
FROM tblCustomer
WHERE cid=@cid)
IF @@ROWCOUNT=0 UPDATE tblCustomer SET fName=@fName, lName=@lName WHERE cid=@cid
I know subqueries has a bad name, but NOT EXISTS is actually an exception, I think. The only thing it does is look up the index, which doesn't incur a lot of overhead IMHO.
It might just work for you. 😉
And yes, I would put it into an sp.
January 23, 2015 at 7:11 pm
But the try-catch approach is probably the better one. Something like this:
BEGIN TRY
INSERT INTO tblCustomer (cid, lName, fName)
SELECT @cid, @lName, @fName
END TRY
BEGIN CATCH
IF error_number() = 2627 UPDATE tblCustomer SET lName=@lName, fName=@fName WHERE cid=@cid
END CATCH
It seems to work...
February 2, 2015 at 5:47 pm
And here I was thinking the standard way to do an Upsert in SQL 2005 was like this:
IF EXISTS
(
SELECT 1
FROM tblCustomer
WHERE Cid = 1
)
UPDATE tblCustomer
SET fname = 'Bob', lname = 'Smith'
WHERE Cid = 1;
ELSE
INSERT INTO tblCustomer (Cid, fname, lname) VALUES(1, 'bob', 'smith');
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 2, 2015 at 6:22 pm
Have an old app that imports data into a MSSQL server DB using an ODBC connection.
To be honest, because of the CID=1 thing, all of the solutions so far look like RBAR to me. Also, because a lot of "developers" to a cursory insert and then an update just to insert one row, I usually do inserts first and then updates.
You say that you have an old app that imports the data but you don't say from where or how. It think it's time to spill the beans on that subject because none of this is looking set-based so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply