December 6, 2011 at 10:23 am
I have a stored procedure that is triggered from an Access application. It runs fine most of the time but hangs at times and causes problems. I'm learning as I go here so bare with me. Below is a copy of my OpenQuery that gets the data from the read only Progress database and then inserts into my SQL Server database. Can anyone give me some advice on making this a more efficient and faster method? I've been told to use "Read Uncommitted" to avoid locks but I must admit to having never used this before. Thanks for any advice.
Code:
DECLARE
@strQuery NVARCHAR(4000)
Begin
SET @strQuery = 'INSERT INTO Estimate ([Created-by], [Created-Date], [Update-By], [Update-Date], [Update-Time], [CSR-ID], [Estimate-ID], [Description], [Cust-ID], [Cust-Name], [Sales-Agent-ID])
SELECT * FROM OPENQUERY([HAGEN], ''SELECT "Created-by", "Created-Date", "Update-by", "Update-Date", "Update-Time", "CSR-ID",
LTRIM("Estimate-ID"), "Description", "Cust-ID", "Cust-Name", "Sales-Agent-ID" FROM PUB.Estimate
WHERE LTRIM("Estimate-ID") = '''''+ @HQN +''')'
EXEC sp_executesql @strQuery
END
Mike
December 6, 2011 at 10:31 am
Read uncommitted would be useless here.
It's also a a bad practice most of the time.
If you can't say with 100% certainty that you can have the incorrect results returned then you can't use that trick (same as nolock).
Never used a linkserver to progress so I can't help in that matter.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
December 6, 2011 at 11:35 am
100% Agree with Ninja, the locking hint would be useless (and is generally frowned upon)
At our company we have a lot of reporting running off data from MSSQL and about 40% still running off Progress...Unforuntately, unless you want to explore more detailed methods like SSIS (utilizing ETL's and flat files from progress) you're going to be limited to what you already have...
In my experience working with Progress...what you have below is pretty much the best way to get the data back. You need to get your hands on information regarding which indexes are on the tables you're querying from Progress to make this go faster...it will help, but may not do much else for you
To get around these extremely slow-running queries, I implemented a full blown SSIS solution that loads the flat-files into our system every few hours, performs UPSERTS as needed, and then the data is "relatively" new around the clock...This is what i would reccomend to you.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply