| Author |
Topic |
|
chulheekim
Starting Member
46 Posts |
 Posted - 2010-09-20 : 17:35:34
|
| I have a query to access to a linked server table.select *FROM linkedServer.DBName.dbo.TableA WITH(NOLOCK)where iTableId = 1 and iSystemId = 12626Its excution plan shows something like Remote Query Cost: 100%. Does that mean it's not using any index on the table? To make it use the index, what should I do? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-20 : 18:07:05
|
| I think the way linked server queries work is, it brings the entire table here and does the filtering.. search for common linked server perf issues..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-21 : 02:49:44
|
| "WITH(NOLOCK)"I hate it whenever I see that. Can I presume you are skilled enough to know what you are doing?I find OPENQUERY more performant, and gives me more control, than query direct from linkedserver, but it probably depends a bit what you are conencting to. |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-21 : 13:06:57
|
| There's index for those fields. I know that with(Nolock) will bring dirty rows. I'm really desperate to make the query fast. Is that really true that it brings the entire table and does the filtering? I don't know whether it matters. The query is running on sql 2000 and the linked server is sql 2005. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-21 : 13:14:08
|
"I know that with(Nolock) will bring dirty rows."Well that's a good start  Beware that there will also be rows missing, and some rows appearing twice in the results - but such occurrences will be as rare as hen's-teeth, so neigh-on impossible to reproduce / debug, but you should not use it for anything on which business decisions may be taken, or where people will be spooked when they press REFRESH and get a different resultset.READ_COMMITTED_SNAPSHOT is a better solution (but it was not available in SQL2000, but it will work for the query part that is on the remote SQL2005 server)"Is that really true that it brings the entire table and does the filtering?"If you also have a JOIN to a local table then "perhaps". If you don't then it should run same speed as if you ran the query on the remote machine itself (but if you are pulling a lot of data you have the time it takes to transfer the data between machines, which is dependent on the speed of the link between them)Try using OPENQUERY and see if that is any faster; if so then its a problem with bringing the data locally first, if not then its as good as it gets I reckon.(Run the query remotely too, and then you can see what query plan the remote server is using) |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-21 : 23:20:58
|
| I do have a join with a loca table. How about if I create a stored procedure in the linked server and run it remotely? I still have to move the output data over to the local server.Will that work better? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-22 : 02:26:09
|
| Why not try OPENQUERY ... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-22 : 06:32:56
|
| This data in the remote server...how big is it and how often does it get updated? Could it be a viable option to copy it over to the local server every X minutes? Then your "fast" query would be local, hence faster...but it really depends on the remote table.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-09-23 : 20:14:34
|
| I don't know much about OPENQUERY. I will defintely try OPENQEURY option. The table in the remote server has multi million records. It is also one of the busiest tables (A lot of Read, Update, and Insert). The query runs every 30 min and brings about at least 100,000 records over to the local server every time. I'll have a meeting about the issue and all your suggestions will be on the table. Thank you so much |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-23 : 20:17:59
|
| you should probably look into an ETL process rather than running a linked server query...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Remote Query Cost: 100%