April 26, 2012 at 1:30 pm
Grant Fritchey (4/26/2012)
20,000 rows is not that much. Even tripling it to 60,000 rows is still not much. Returning four columns doesn't mean much because I don't know the data types of the columns. Plus, the real question here is what does your structure look like, what do the queries look like and what does the current execution plan look like? With that information it's easier to tell you if the query is running in an optimal fashion. Not knowing anything, I don't know how well the query will perform. Adding in the UPDATE for each SELECT statement just confuses the issue. What are we updating? Are the updates a part of the SELECT or a seperate transaction?
no actually i should update a separate transaction.
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 1:32 pm
Divine Flame (4/26/2012)
Seeing the OPs affinity towards linked servers I would like to say that being a DBA you should always justify the use of linked servers. They are there to query the data from other servers but they are notorious when it comes to performance.When talking about network traffic, obviously it is going to be the same no matter what you use linked server/front end coding as the same number of rows will be returned to the application any way. In fact doing it from the front end may benefit you in following ways:
1. You will actually distribute the load among the three database servers for their query processing & returning the results.
2. You will utilize the hardware capacities of your web server also (which are usually underutilized because many developers tend to put all the logic in SQL Server procedures only & using the front end to just bind the data grid)
3. You will not have to maintain an extra replication topology
As stated by Dev earlier, if it is only about 20,000 rows then there is really no need of spreading three databases on three different servers. However, I believe you are constrained by some means & you can't do anything about that.
what about update?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 10:06 pm
Larry Page(Ehsan.Akbar) (4/26/2012)
what about update?
You need performance...right??
The best performance you can get is by getting all the data on 1 server and then perform whatever actions you want to perform on that. This will give you the best(est) performance.
If you use any other techniques....it will definitely cost you in terms of performance. I am sure there must be a lot of things going on in your server at the time when this query is executed. So, I am even sure that you must want all your processes to work at a "Cumulative optimum".
That optimum can only be attained if all your processes work at the optimum. In this case the optimum solution is getting all the data onto one server and then query it.
If you go for any other solution, then your Search procedure is going to take a performance hit(small or big depending on various factors) when compared to the optimal solution. This hit will in turn affect all the processes that run on your Server. Thus, making you deviate from the "Cumulative Optimum" that you are looking for.
April 27, 2012 at 4:49 am
vinu512 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
what about update?
You need performance...right??
The best performance you can get is by getting all the data on 1 server and then perform whatever actions you want to perform on that. This will give you the best(est) performance.
If you use any other techniques....it will definitely cost you in terms of performance. I am sure there must be a lot of things going on in your server at the time when this query is executed. So, I am even sure that you must want all your processes to work at a "Cumulative optimum".
That optimum can only be attained if all your processes work at the optimum. In this case the optimum solution is getting all the data onto one server and then query it.
If you go for any other solution, then your Search procedure is going to take a performance hit(small or big depending on various factors) when compared to the optimal solution. This hit will in turn affect all the processes that run on your Server. Thus, making you deviate from the "Cumulative Optimum" that you are looking for.
when we should use linked server ?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 27, 2012 at 5:04 am
Larry Page(Ehsan.Akbar) (4/27/2012)
vinu512 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
what about update?
You need performance...right??
The best performance you can get is by getting all the data on 1 server and then perform whatever actions you want to perform on that. This will give you the best(est) performance.
If you use any other techniques....it will definitely cost you in terms of performance. I am sure there must be a lot of things going on in your server at the time when this query is executed. So, I am even sure that you must want all your processes to work at a "Cumulative optimum".
That optimum can only be attained if all your processes work at the optimum. In this case the optimum solution is getting all the data onto one server and then query it.
If you go for any other solution, then your Search procedure is going to take a performance hit(small or big depending on various factors) when compared to the optimal solution. This hit will in turn affect all the processes that run on your Server. Thus, making you deviate from the "Cumulative Optimum" that you are looking for.
when we should use linked server ?
1.
Suppose you have an application which connects to database DB1 on Server S1.
Now, your application needs some data occasionally (no huge number of transactions) from another database DB2 that is existing on server S2. Also, you do need the data in real time. So, if you know that the data you need is less in volume & the query that fetches out that data is optimized well, you can use linked server here.
2. You need to schedule a job that over night transfers some data from one server to another server, you can use linked server there also.
April 27, 2012 at 5:09 am
Divine Flame (4/27/2012)
Larry Page(Ehsan.Akbar) (4/27/2012)
vinu512 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
what about update?
You need performance...right??
The best performance you can get is by getting all the data on 1 server and then perform whatever actions you want to perform on that. This will give you the best(est) performance.
If you use any other techniques....it will definitely cost you in terms of performance. I am sure there must be a lot of things going on in your server at the time when this query is executed. So, I am even sure that you must want all your processes to work at a "Cumulative optimum".
That optimum can only be attained if all your processes work at the optimum. In this case the optimum solution is getting all the data onto one server and then query it.
If you go for any other solution, then your Search procedure is going to take a performance hit(small or big depending on various factors) when compared to the optimal solution. This hit will in turn affect all the processes that run on your Server. Thus, making you deviate from the "Cumulative Optimum" that you are looking for.
when we should use linked server ?
1.
Suppose you have an application which connects to database DB1 on Server S1.
Now, your application needs some data occasionally (no huge number of transactions) from another database DB2 that is existing on server S2. Also, you do need the data in real time. So, if you know that the data you need is less in volume & the query that fetches out that data is optimized well, you can use linked server here.
2. You need to schedule a job that over night transfers some data from one server to another server, you can use linked server there also.
thank you .
before i thought we can use linked server for increasing performance.
so suppose you have a huge data like google ,how you can get best performance?
what is its method?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 27, 2012 at 5:16 am
1.
Suppose you have an application which connects to database DB1 on Server S1.
Now, your application needs some data occasionally (no huge number of transactions) from another database DB2 that is existing on server S2. Also, you do need the data in real time. So, if you know that the data you need is less in volume & the query that fetches out that data is optimized well, you can use linked server here.
2. You need to schedule a job that over night transfers some data from one server to another server, you can use linked server there also.
- Divine Flame
Apart from quoted scenarios use Linked Servers when there isn't a better performing alternative. Its as simple as that. If you think that Linked Server can be your best bet at doing something then use it.
In your case you have the choice of a better alternative. Now, you are the DBA and have to decide for yourself what would be good for you.
April 27, 2012 at 5:18 am
Larry Page(Ehsan.Akbar) (4/27/2012)
thank you .before i thought we can use linked server for increasing performance.
so suppose you have a huge data like google ,how you can get best performance?
what is its method?
When talking about Google, Google uses Big Table as their database system. Big Table is totaly different from the conventional RDBMSs like SQL or Oracle. Big Table is the DBMS developed by Google & it works on GFS (Google File System). You can search more about Big Table on Google itself ;-).
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply