February 6, 2015 at 8:21 am
Converting a database from Access 2007 to SQL Server 2014.
When I run a query it takes about 25mins in SQL Server while Access takes about couple of mins.
When converted to a OpenQuery, it takes about 10mins which is good but its still taking more time than Access.
Below is the query:
Select a.*
INTO ##Temptble
From OpenQuery (JDDWH,'Select * From CK500.DDTA.K111 WHERE IABCDJ>113001 And IABCT=''AS'' Or ILDCT=''CD''') As a;
This particular Linked Server is AS400 but also have other linked servers which are SQL Server and queries slow there as well.
How can I make SQL queries run faster when pulling data from Linked Servers?
February 8, 2015 at 8:48 am
@ axc_67
Hi there sir,
the question is fairly difficult to answer without knowing more. Hardware specs of the servers, number of rows in these tables etc.
Please, provide more information about your environment so more folks can help,
Petr
February 9, 2015 at 7:45 am
Which provider are you using in the linked server? Probably better not to use the generic ODBC provider, but if you're already using the DB2 specific one it could be worth trying it. http://support.microsoft.com/kb/222937
Why are you inserting into the global temp table? Perhaps defining the table first might give you better performance than hoping SQL server will implicitly work out the appropriate data types.
What columns and data types do you have in that DB2 table? Different database systems often have very different data types, and you might need to explicitly cast them.
Perhaps you are bringing back massive binary/blob columns or other columns you don't need.
Perhaps the OR in the query is slowing it down and you'd be better off with UNION.
Just ideas, without knowing the data types or the number of rows it's hard to say. I know Access doesn't have global temp tables so that query can't possibly be the same.
February 9, 2015 at 9:10 am
This particular table has about 500k rows of records and I am only pulling columns that I need.
Changing it from Temp Table to a make table in the database did not make a difference.
I was comparing the make table in Sql to Access.
Again this is just an example, there are other similar query comparisons where access seem to perform better when pulling data from the linked server.
It does not seem to make a difference what type of Linked server it is.
I have multiple linked servers to both another SQL Server and ODBC Driver and tested each of them.
Each time Access seem to be performing better.
When I compare it pulling tables within the local DB Sql is much faster.
I will keep researching and try different methods...thank you all for providing your input.
If you have more ideas/inputs please do post it.
February 9, 2015 at 9:59 am
Experimenting with a different provider or linked server property configuration may offer marginal performace gains. But when it comes to performance of remote queries, what's most important is that the query be implemented in the form of a pass-through query using EXEC() AT or OPENQUERY() and not a linked query.
Using 4 part naming convention or OpenQuery() to join remote tables with local tables, or just joining remote tables using 4 part naming, is verrrry inefficient, because SQL Server often must perform a full or partial table scans, and then it must also pull multiple resultsets across the network to be joined and filtered locally.
For example, don't do this:
insert into #t ( a, b, c )
select a, b, c
from LinkedServer.RemoteDB.dbo.RemoteTableA
join LinkedServer.RemoteDB.dbo.RemoteTableA
Instead do this:
insert into #t ( a, b, c )
exec ('select a, b, c from RemoteTableA join RemoteTableB') at LinkedServer;
The difference in execution duration is serveral orders of magnitude. I've reduced hour long queries down to seconds simply by reworking the query so that it executes remotely and returns only the result across the network.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2015 at 11:06 am
Eric,
Your solution seem to work faster:
Exec ('Select ...) at linked Server
It took about 14mins...which is still longer than access but atleast it takes half the time of OpenQuery.
Thank you for your input!
February 9, 2015 at 11:11 am
axc_67 (2/9/2015)
Eric,Your solution seem to work faster:
Exec ('Select ...) at linked Server
It took about 14mins...which is still longer than access but atleast it takes half the time of OpenQuery.
Thank you for your input!
Were both approaches, EXEC() AT versus original OpenQuery() method, using a pass-through style query, joining only remote tables?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2015 at 11:17 am
No I am not joining any tables in the remote server.
I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
February 9, 2015 at 11:21 am
axc_67 (2/9/2015)
No I am not joining any tables in the remote server.I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
Also try doing this using an SSIS package.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 9, 2015 at 11:48 am
Eric M Russell (2/9/2015)
axc_67 (2/9/2015)
No I am not joining any tables in the remote server.I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
Also try doing this using an SSIS package.
Not being bullish here. I don't use SSIS but continue to try to gather information so that when I do finally get to it, I'll have some things that I'd like to check and compare.
With that thought in mind, why have you recommended doing this using an SSIS package? Will it be faster, easier to build, easier to maintain, or is it just because that some people have adopted the mantra that ETL should generally be done using SSIS or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2015 at 12:14 pm
I had similar problems with a Teradata linked server. There was no way to use the Teradata linked server that did not perform horribly compared to an Access application connecting with ODBC. I can only guess that it was doing some kind of row-by-row transfer and handshaking rather than sending blocks of data. (To be fair, this was over ten years ago and their current SQL providers may be much improved.) I finally had to face up to the fact that INSERT/SELECT from a four-part name or OPENQUERY is the most natural way for me to write a data transfer, but it is not the only way and sometimes is not the best way.
The quickest way I found to transfer data with Teradata was to use their FASTLOAD/FASTEXPORT utilities, similar to SQL Server's bcp. They were a pain to set up, but they are simple utilities that focus only on moving data without getting the Distributed Transaction service involved. In your case, you might try getting the AS/400 to dump the query results to a flat file, that you then read with BULK INSERT.
You might also get reasonable performance with an SSIS package Data Flow task.
February 9, 2015 at 1:02 pm
Jeff Moden (2/9/2015)
Eric M Russell (2/9/2015)
axc_67 (2/9/2015)
No I am not joining any tables in the remote server.I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
Also try doing this using an SSIS package.
Not being bullish here. I don't use SSIS but continue to try to gather information so that when I do finally get to it, I'll have some things that I'd like to check and compare.
With that thought in mind, why have you recommended doing this using an SSIS package? Will it be faster, easier to build, easier to maintain, or is it just because that some people have adopted the mantra that ETL should generally be done using SSIS or ???
I'll admit that I don't know all the internals regarding T-SQL INSERT versus BULK INSERT or a SSIS DataFlow task. However, when I'm doing something like this, I makeup for my lack for knowledge by experimenting with a variety of different approaches before I settle on a final solution. For example, the thing about EXEC() AT extracting twice as fast as OpenQuery(), even when using the same SQL select, is probably something nobody would have expected.
I wouldn't reccomend SSIS for such a simple data transfer, if this involved only local tables. But this involves loading data from a remote source (IBM AS400). SSIS is optimized for loading data between heterogeneous sources, and there are a lot of knobs to tweak. SSIS supports some "fast load" options for OLEDB destinations, which is equivalent to a bulk insert. There are also options to set properties like network packet transfer size, SSIS buffer size, and the batch commit size. If nothing else, it's just an alternative approach to sucking data through a linked server connection.
http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 26, 2015 at 12:38 pm
Eric M Russell (2/9/2015)
Jeff Moden (2/9/2015)
Eric M Russell (2/9/2015)
axc_67 (2/9/2015)
No I am not joining any tables in the remote server.I am just pulling a simple data extraction from remote server with where conditions....which is why I don't understand why its taking so much time.
Also try doing this using an SSIS package.
Not being bullish here. I don't use SSIS but continue to try to gather information so that when I do finally get to it, I'll have some things that I'd like to check and compare.
With that thought in mind, why have you recommended doing this using an SSIS package? Will it be faster, easier to build, easier to maintain, or is it just because that some people have adopted the mantra that ETL should generally be done using SSIS or ???
I'll admit that I don't know all the internals regarding T-SQL INSERT versus BULK INSERT or a SSIS DataFlow task. However, when I'm doing something like this, I makeup for my lack for knowledge by experimenting with a variety of different approaches before I settle on a final solution. For example, the thing about EXEC() AT extracting twice as fast as OpenQuery(), even when using the same SQL select, is probably something nobody would have expected.
I wouldn't reccomend SSIS for such a simple data transfer, if this involved only local tables. But this involves loading data from a remote source (IBM AS400). SSIS is optimized for loading data between heterogeneous sources, and there are a lot of knobs to tweak. SSIS supports some "fast load" options for OLEDB destinations, which is equivalent to a bulk insert. There are also options to set properties like network packet transfer size, SSIS buffer size, and the batch commit size. If nothing else, it's just an alternative approach to sucking data through a linked server connection.
http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis/
I can testify that SSIS is easily faster than a linked server. In SSIS you can also do all your transformations in memory and not have to create a temp table. The MERGE join can do the equivalent of a inner or any join type all in the transfer pipeline . It also supports transactions and logging. If something goes bad you have messages helping to pinpoint the issue(s). You can annotate all the tasks within the control flow so it provides a handy ability flor documenting the work as well.
----------------------------------------------------
February 27, 2015 at 4:25 am
AtoZ67 (2/6/2015)
Converting a database from Access 2007 to SQL Server 2014.When I run a query it takes about 25mins in SQL Server while Access takes about couple of mins.
When converted to a OpenQuery, it takes about 10mins which is good but its still taking more time than Access.
Below is the query:
Select a.*
INTO ##Temptble
From OpenQuery (JDDWH,'Select * From CK500.DDTA.K111 WHERE IABCDJ>113001 And IABCT=''AS'' Or ILDCT=''CD''') As a;
This particular Linked Server is AS400 but also have other linked servers which are SQL Server and queries slow there as well.
How can I make SQL queries run faster when pulling data from Linked Servers?
Is the same amount of data being returned by the Access and the Linked Server queries?
There might be some operator-precedence gremlins caused by the unbracketed mixed Boolean operators in the WHERE clause.
Just a thought.
February 27, 2015 at 7:07 am
AtoZ67
"This particular Linked Server is AS400 but also have other linked servers which are SQL Server and queries slow there as well."
What kind of internal net do you use? What is the speed?
The performace better then you indicate. I have an application with two linked SQL servers
2005 and 2008 R2 with linked tables in queries there the tables contains more than 20 000 000 rows.
The performace is ok for the purpose of the application.
In old time I was working with AS400. After a lot of experimentation I found out that the best way was to
export textfiles from AS400 and when use BULK INSERT to populate tabels in SQL server.
Is your application "real time" or is it batch oriented? If batch I would create a new database and import the data
from the linked server (scheduled like every night) and then run the questions in the that database.
Gosta M
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply