December 11, 2008 at 7:04 pm
Hi,
I have a stored procedure which is for a report, so based on the filter criteria i am getting everything in a table variable and for this filtered i need to get some more information from the linked server as some of the information is saved on another server which is managed by one more application. Till now in the main stored procedure there is cursor which is picking record and for every record we are going on linked server where there is another procedure which returns some values which we are updating in table variable of main procedure.
We can change both stored procedure but can i send i table as parameter in stored procedure in sql server 2005 .. no right?
So what is another option please help me ...because report is taking hours or sometimes it never completes.
December 11, 2008 at 7:08 pm
Please see the links in my signature to see how to post to get better answers. Without code and DDL there is almost no way anyone can give you a better way.
Obviously if you can find a way to only make 1 trip across the linked server you will see a great increase in performance. Is there any reason why you can't replicate the needed data from the linked server to the report server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2008 at 8:24 pm
We are creating an application for our client and client is already using another product which maintains some information and we have to generate integrated report.
Code is not complicated, we are just selecting records based upon filteration required and need to get some data from linked server for each one of the record. So, is there any way so that i can send complete table or anything like that in stored procedure on the linked server and get the same at one go?
December 11, 2008 at 8:51 pm
You'd be surprised how many options there may be to do something when DDL and code is provided. Someone may have dealt with a similar issue and seeing code and DDL helps refresh memories and often times a new set of eyes and experiences can come up with another way to do it.
There is not an efficient way to do it. Passing a table data type as a parameter is not supported until SQL Server 2008. You could pass a large XML parameter to the linked server I suppose.
Is replication an option? Using a job to copy data from one server to another so you stay on 2 box?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 12, 2008 at 4:19 am
As Jack explained to you, without some specifics, there's almost no way to address your issue.
Speaking in general terms, using a cursor to select records is almost always a mistake. There should be set based methods for performing what you want. When dealing with large sets of data, table variables are a poor choice because they have no statistics. You are correct, there is no way to pass a table as a parameter in 2005, but there is in 2008. From your description, which is pretty vague & general, so this answer will be too, I'd suggest you can either answer what you need with a SELECT statement, or, if there really is so much data that the transfer across the wire adds overhead to blocking, locking, etc. then you should explore using sqlcmd to export to a file, copy the file and then import on the new server using bulk processes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 8:51 pm
Thanxs to every one, i really apprecite your views.
I asked other people how are managing other application/Dbase (linked server) i asked them to create a view for me let us see whatever i am thinking will help me to increase the performance
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply