June 14, 2004 at 2:21 pm
I have a query like the following
SELECT Test1, Test2, Test3 From OPENQUERY(AS400SRV,'SELECT Test1, Test2, Test3 FROM TestLib.TestTab Where Test1 = 1')
which returns some info from an AS400 works fine but takes very long to execute! (10min)
this is executed in an sp_executesql since I have to build the where dynamicaly I could not figure out a way to add a parameter inside a openquery statement but this is not the issue b/c I can copy an paste the resulting sql into QA and it takes just a long
I can run the same query "SELECT Test1, Test2, Test3 FROM TestLib.TestTab'" in a oledb recordset in C# and get the results back very fast! (20sec)
Anyone have any ideas why openquery would be so much slower?
June 14, 2004 at 3:07 pm
I wish I had an answer, but I don't. The client access linked server from SQL has been flaky for me in the past as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 14, 2004 at 5:09 pm
You will only be able to use dynamic sql to use that method (which for your needs will be the best). I suggest looking at the data on the other end to see if you access the AS400 directly on it's own client that ikt doesn't take roughly the same time.
June 14, 2004 at 11:07 pm
10 minutes vs. 20 seconds is quite a difference! Are they both executing from the same machine? If not, check for a network problem on the slower one. We once replaced a network switch and somehow the OS setting for network speed changed from "auto-detect" to "10 MB/sec", resulting in an awful slowdown. Also, is the linked server that openquery is using done with OLE or ODBC? If OLE is an available option, it should be faster than ODBC, but not 30 times faster - something else is going on...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply