April 26, 2012 at 6:56 am
vinu512 (4/26/2012)
Nothing could be better than having all the data in one server. Both Linked Servers and coding from the front end would be more or less the same in terms of performance.Here the performance in your case takes a hit due to both - the no. of connections and may be the amount of data that is being transported.
So if you can keep all the data in one server it would definitely be better.
so actually i have 20000 records in my table ,so every second i have to execute a search query ,and this query returns 4 result each second ,
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 6:59 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
"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
April 26, 2012 at 7:01 am
Mark Fitzgerald-331224 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Divine Flame (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.I am not sure but I think it is better done from the front end coding (i.e. C# etc.). What I can think of is initializing 3 different connections with the Command to execute as the search query. Later on you can analyze the data set that holds the returned result set.
this way could increase your network traffic
Choices:
1) Create linked servers and union the results sets
+ data kept separate and updated separately so no issues with table structures
- increases network traffic when the query is run
2) Use transactional replication so that all 3 servers have all 3 databases (separately) ie Server1 DB replicates to Server2 and Server3, Server2 DB replicates to Server1 and Server 3 etc in a spiders web network
+ data kept separate and updated separately so no issues with table structures
+ queries will use the local version of all 3 DB at all sites
- increased network traffic during replication
3) Use merge replication (with changed schema to include UNIQUEIDENTIFIER column if you do not have it already).
+ local DB used for queries
- Schema change
- possibility of merge conflicts
- increased network traffic during replication
.... (other choices are available on request)
No magic wand here.
Fitz
i think linked server is the best option
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 7:01 am
Larry Page(Ehsan.Akbar) (4/26/2012)
so actually i have 20000 records in my table ,so every second i have to execute a search query ,and this query returns 4 result each second ,
Its just 20,000 records. I think you should get it to one server and then process it the way you want to. As you say that your query will run every second...so in case of using Linked Servers, regardless of how many records it fetches, your network traffic will increase and your performance will decrease every time the query runs(ie: every second).
April 26, 2012 at 7:09 am
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 7:14 am
when every query is executed ,my result have 4 records and one of the my column is image ,and in every secound i have to update a table
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 7:45 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
"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
April 26, 2012 at 9:31 am
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
so you think i should use linked server?!!
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 9:35 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
so you think i should use linked server?!!
Translation : performance is going to stink = awful performance, so NO Grant does not think that linked servers is the way to go. For my view read the post from earlier. I, personally, cannot advise more without details and testing.
Fitz
April 26, 2012 at 9:44 am
Mark Fitzgerald-331224 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
so you think i should use linked server?!!
Translation : performance is going to stink = awful performance, so NO Grant does not think that linked servers is the way to go. For my view read the post from earlier. I, personally, cannot advise more without details and testing.
Fitz
thanks .so when we can use linked server? what is the advantage of linked server?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 10:53 am
Larry Page(Ehsan.Akbar) (4/26/2012)
Mark Fitzgerald-331224 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
so you think i should use linked server?!!
Translation : performance is going to stink = awful performance, so NO Grant does not think that linked servers is the way to go. For my view read the post from earlier. I, personally, cannot advise more without details and testing.
Fitz
thanks .so when we can use linked server? what is the advantage of linked server?
Linked server is pretty much as advertised, a way to query from one SQL Server instance on another one. It's that simple. But, it's not going to be faster than querying the same data locally on the server. It will be slower. Depending on how you write the queries it will be a lot slower.
I can't advise you on what you need to do because I still don't completely understand. You need to run a query four times a second and it returns four rows, or something like that. Fine, but why is it pulling data from three different instances of SQL Server? Can you consolidate the data in some fashion? How often is this data updated? How big is the data, four rows of bit columns or four rows of VARCHAR(MAX)? It's all so amorphous and vague, that I just don't feel good suggesting what you need.
"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
April 26, 2012 at 11:11 am
I read complete thread but don’t find any good reason for splitting data into 3 servers. Did you assume it will improve performance? In a way yes because DML operations would be possible on separate servers BUT it would be deadly slow for read (Union + Linked Server / Replications) operations.
20K is not a big dataset. Keep it in one server unless you have any justification for not doing so.
April 26, 2012 at 1:04 pm
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.
April 26, 2012 at 1:09 pm
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Mark Fitzgerald-331224 (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
Larry Page(Ehsan.Akbar) (4/26/2012)
Grant Fritchey (4/26/2012)
You could use merge replication to ensure that the same data goes to all three servers, regardless of which one inputs the data. But that requires changes to your table structures. If you just want to copy the data from a single input point, you could use transactional replication, but then all input has to be from one source. If you really want to run the same query against three servers I'd suggest looking at something like BizTalk that will allow you to do an extended transaction.my data in each servers are different ,so i just want to increase my performance !
OK. I'm getting confused. You want to improve performance? What does that have to do with getting data to all three servers?
Are you trying to query from one server to another? That's very different from trying to have all three servers have the same data.
in fact my goal is performance ,
so i have 2 options .
i can insert my data into one server and execute my search query on it ,
or i can spread my data to 3 servers (the data is different on each servers) and use linked server.
my records are 20000 ,and i don't know which option give me the highest performance ,(i have to execute search query every second)
Performance is going to stink trying to UNION data between three different servers.
so you think i should use linked server?!!
Translation : performance is going to stink = awful performance, so NO Grant does not think that linked servers is the way to go. For my view read the post from earlier. I, personally, cannot advise more without details and testing.
Fitz
thanks .so when we can use linked server? what is the advantage of linked server?
Linked server is pretty much as advertised, a way to query from one SQL Server instance on another one. It's that simple. But, it's not going to be faster than querying the same data locally on the server. It will be slower. Depending on how you write the queries it will be a lot slower.
I can't advise you on what you need to do because I still don't completely understand. You need to run a query four times a second and it returns four rows, or something like that. Fine, but why is it pulling data from three different instances of SQL Server? Can you consolidate the data in some fashion? How often is this data updated? How big is the data, four rows of bit columns or four rows of VARCHAR(MAX)? It's all so amorphous and vague, that I just don't feel good suggesting what you need.
i think if i put the data from three instances of SQL SERVER ,it can increase my performance.is it right?
so i have 20000 records ,and every second i should execute a seach query ,and this query returns four row ,and each has these column (id,name family,picture) .when search query executed i should update a table (after each search query ,each second)
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 26, 2012 at 1:21 pm
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?
"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
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply