|
|
Hi guys, I need to update the data in local database from other database automatically, by date or suggest if there is other way thanks for the help 
|
|
|
|
|
Depends, on the connection, databases used, and your data.
Running a script on a timer is the obvious way, but you might want to take a look into materialized views, or indexed views depending on your flavour of RDBMS.
I don't necessarily recommend it, it depends.
|
|
|
|
|
Hi, Im using 2 oracle databases how this could be possibly done?
|
|
|
|
|
Start with creating a database link.
Here's[^] the reference. But as the Oracle reference sucks for learning, I suggest that you google it instead. There's a ton of info on the net.
When you have created the link you should be able to SELECT * FROM mytable@the_other_server.
If this works you can either run a script to update the tables on your server, or create a materialized view that does this automatically.
How to create a materialized view is a much to big subject to answer here, I suggest google again.
Just remember that there are a lot of conditions that needs to be met to get a MV to work.
|
|
|
|
|
More info on Advanced Replication[^]
It always helps having the right search term.
You can also find Advanced Replication setup in the Oracle Enterprise Manager under the Data Movement tab.
|
|
|
|
|
One option would be to use the Sync Framework[^].
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi, can I use stored procedure to sync data from other database using DBLink? What are the possible querries that I need to do in this process?
|
|
|
|
|
KingJames06 wrote: Hi, can I use stored procedure to sync data from other database using DBLink?
Dunno, I don't know DBLink. Hence, I can also not say if it is an easier or more efficient approach than the Sync Framework which I did recommend.
KingJames06 wrote: What are the possible querries that I need to do in this process?
Depends on your database and it's structure. Syncing is a delicate process, I suggest you take a few days to read everything on the topic that you can find
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
hi,
I am doing this stored procedure but getting Subquery returns more than 1 row
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_populate_advertisement`(IN `param_volow_banner_size` varchar(255))
BEGIN
SET @random_banner = (SELECT volow_banner_id FROM volow_banners WHERE volow_banner_size = param_volow_banner_size ORDER BY RAND());
INSERT INTO volow_banner_log (volow_banner_id) VALUES (@random_banner);
SELECT volow_banner_file FROM volow_banners WHERE volow_banner_id = @random_banner;
END
can you help please..
|
|
|
|
|
SET @random_banner = (SELECT volow_banner_id FROM volow_banners WHERE volow_banner_size = param_volow_banner_size ORDER BY RAND());
should be
SET @random_banner = (SELECT volow_banner_id FROM volow_banners WHERE volow_banner_size = param_volow_banner_size ORDER BY RAND() LIMIT 1);
I wasn't, now I am, then I won't be anymore.
modified 24-Oct-12 16:49pm.
|
|
|
|
|
|
what db are you using. I wasn't paying attention and assumed MSSQL.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
|
I've updated my original answer to reflect MySql. Sorry for the confusion.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
working now.
thanks Marcus
|
|
|
|
|
You are welcome.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
Hi,
I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result.
The problem is when more users are trying to generate the report, it seems that it takes more time.
My problem was when I tried to update a simple statement such as below, it resulted to a "time out expired" and it affects the entire process of the application becuase users cannot perform their task.
update table
set status = 1
where keyId = 6
I don't have any locking on my select nor update.
What is the best practice to resolve it?
I have 100 users nationwide aside from report users, please need help.
Thanks in advance
Dabuskol
Dabsukol
|
|
|
|
|
dabuskol wrote: I have a SP that calculates data based on criteria, the result were % and duration. It takes 8-10 seconds before it displays the result.
You *could* use the SP to retrieve the bare results, and do the calculations in code (on the client) - that would take some work of the server. Post the SP here, and we could have a look at optimizing it.
dabuskol wrote: The problem is when more users are trying to generate the report, it seems that it takes more time.
That's not a problem, but expected behavior; more requests for the database means simply more work.
dabuskol wrote: I don't have any locking on my select nor update.
What is the best practice to resolve it?
Locking wouldn't help much; you'd still get a time-out if the query does not return within the set timeout-period. Depending on your needs, you could have it timeout after half an hour.
Still, best idea would be to move the calculations, limit the joins and optimize the query.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy is correct, locking is not the issue here, also check to see if you have spit triggers on the table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
He's talking about a SELECT statement, not an UPDATE. No amount of locking is going to speed up reading.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
Any idea what that will do to a database that's not "well designed"? Really think it improves speed? Ever heard of the term "dirty read"??
Stop and THINK for a second - if it were a matter of adding that keyword, wouldn't it be more simple to have the dirty read by default, whether or not the keyword is included?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
