February 6, 2002 at 11:19 pm
Hi,
We're currently testing whether a linked server is the right way to go for accessing .dbf files from within a SQL environment... It all works well however the performance seems poor. When performing a basic select * from <table> we are looking at 7 seconds for a 250 record table and 10 seconds for a 12000 record table. It appears as though it is taking about 5-6 seconds to establish the connection.
The files that the linked server is accessing are on the local machine (ie the SQL Server). THe server is also idle (its a new Compaq 370 with 1GB ram).
Can anybody shed some light on ways to improve performance or alternatives as this will not be acceptable?
We will need to run a number of batches accessing about 20-30 dbfs. One thought that came to mind was to run 4-5 tables in parallel thereby decreasing the overall job time. However when using a linked server we have found the following:
Both of these were run separately:
'select * from table a' - 15 seconds
'select * from table b' - 15 seconds
when running these two in parallel (ie with 2 separate connections) I would expect the two parallel statements to take slightly longer than when they ran independantly. However this is not the case - when running these 2 concurrently they take a combined time of 30 seconds to complete. Any ideas why? If anybody has any links to some real documentation about how linked servers work please would you enlighten me.
thanks
Craig
February 7, 2002 at 10:29 am
Looks like this is not working in parallel. Perhaps there is some single threading behind the connections? Not sure.
Steve Jones
February 7, 2002 at 5:16 pm
Seems like it would be only one connection. Maybe set up a 2nd linked server and test doing two jobs, one on each linked server?
Andy
February 7, 2002 at 5:23 pm
I don't believe the ODBC connector you are using supports multithreading. Many times ODBC connectors are not multithreaded capable and actually have only one instance in memory of the ODBC component and use a process call mutex to prevent other threads from running even if started by a multithreaded controller. Check to make sure you have the latest version of the ODBC connector installed, if not you may want to try a more current version to see if things have changed.
February 7, 2002 at 11:24 pm
Well I tried the suggestion - using two different linked servers but the times didnt improve. It looks as though there is defintely no multi threading going on. I'm using the Jet driver 4.0 - the latest one. I've also had a good look on the web for any other drivers that can be used by linked servers but havent been able to find any. Any ideas where to look?
On another note if the Jet driver is not multi threading for linked servers can I assume that it wont be multi threaded for DTS DBF imports?
thanks
February 8, 2002 at 5:17 am
I wouldnt think so, but I find it pays to test! If you cant get the performance you need via linked server, what about using DTS directly?
Andy
February 8, 2002 at 5:34 am
I would think so either. However if you DTS the data into SQL you can do this regularly without users interference. So they can access the SQL more effeciently while you keep pulling it in.
February 12, 2002 at 1:26 am
Did you try the openrowset command for SELECT. I had the same perfomance problems with an Oracle linked server.
LINKED SERVER:
Select * from Oraclesvr..SPAR3.USERS
OPENROWSET:
SELECT MAT_USR, NOM_USR, PRENOM_USR, SIGNET_USR, CC_DEFLT_USR, STATUS_USR
FROM OPENROWSET(
'MSDAORA',
't30.world';'HRUSER';'HUMRES',
'Select DISTINCT MAT_USR, NOM_USR, PRENOM_USR, SIGNET_USR, CC_DEFLT_USR, STATUS_USR from sapr3.users
Openrowset 20 times faster, at least!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply