asked on 1 query/SP multiple databases
We have some database that like span different states.
We have code for a stored procedure and that stored procedure resides in a Stage database on the same server so a linked server is not necessary.
As you see in this store procedure updates dbo.TableA. Yet, there could be let's say 14 states that have this. So basically there is 1 aspect of code which is in a stage database that needs to run for each. So I basically could not even use a dbo.TableA being that the SP is on a stage database.
What I would need is something like:
Or basically leave it as dbo.TableA but yet still reside on the stage DB. Or even like a @DBName.dbo.TableA where the @DBName would equate to like the StateFL or StateCO.
I know there is the sp_MSforeachdb but that would basically be like a dynamic SQL as well.
What other possible ways are there to achieve this without a dynamic sql as a dynamic sql does not take the best query path with the sql optimizer unless that has changed which I do not think it did.
We have code for a stored procedure and that stored procedure resides in a Stage database on the same server so a linked server is not necessary.
CREATE PROCEDURE dbo.TestA
(@StateID CHAR(2))
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.TableA
SET MemTypeInd='Y'
WHERE State=@State
END
As you see in this store procedure updates dbo.TableA. Yet, there could be let's say 14 states that have this. So basically there is 1 aspect of code which is in a stage database that needs to run for each. So I basically could not even use a dbo.TableA being that the SP is on a stage database.
What I would need is something like:
CREATE PROCEDURE dbo.TestA
(@StateID CHAR(2))
AS
BEGIN
SET NOCOUNT ON
UPDATE StateFL.dbo.TableA
SET MemTypeInd='Y'
WHERE State=@State
END
CREATE PROCEDURE dbo.TestA
(@StateID CHAR(2))
AS
BEGIN
SET NOCOUNT ON
UPDATE StateCO.dbo.TableA
SET MemTypeInd='Y'
WHERE State=@State
END
Or basically leave it as dbo.TableA but yet still reside on the stage DB. Or even like a @DBName.dbo.TableA where the @DBName would equate to like the StateFL or StateCO.
I know there is the sp_MSforeachdb but that would basically be like a dynamic SQL as well.
What other possible ways are there to achieve this without a dynamic sql as a dynamic sql does not take the best query path with the sql optimizer unless that has changed which I do not think it did.
DatabasesMicrosoft SQL ServerSQL
Last Comment
Marc Davis
Hi,
I think, you have only two possibilities: Adding the same code for each database into the same SP or using dynamic SQL.
The execution plan should not be very different in comparison to a normal SQL code. The real difference is that in case of dynamic SQL the execution plan would be built again with each execution (but there is also some cache mechanism which saves the execution plan also for dynamic SQL as long as the code does not change and if it is executed often). I don't think that the performance difference is so high that you should avoid it in all cases. In my last project many of the SQL code was built using dynamic SQL and the database was very performant (and that was only SQL Server 2008 R2).
If you only want to execute the same code on different databases and this is not application dependent then you could also use SSMS with registered servers. Then you can write your query and execute it on the registered servers list and SSMS will run the same code on each database without any dynamic SQL.
Cheers,
Christian
I think, you have only two possibilities: Adding the same code for each database into the same SP or using dynamic SQL.
The execution plan should not be very different in comparison to a normal SQL code. The real difference is that in case of dynamic SQL the execution plan would be built again with each execution (but there is also some cache mechanism which saves the execution plan also for dynamic SQL as long as the code does not change and if it is executed often). I don't think that the performance difference is so high that you should avoid it in all cases. In my last project many of the SQL code was built using dynamic SQL and the database was very performant (and that was only SQL Server 2008 R2).
If you only want to execute the same code on different databases and this is not application dependent then you could also use SSMS with registered servers. Then you can write your query and execute it on the registered servers list and SSMS will run the same code on each database without any dynamic SQL.
Cheers,
Christian
ASKER
Christian,
Obviously, regenerating the query plan would take some time away. SQL Server is going to utilize the keys and indexes as the first thing anyway, I believe. The fact that we're using the keys on already is an added plus. I don't see how it would relate an existing query plan stored in cache to a dynamic sql which it might save. I don't see how it can associate one dynamic sql cache plan with another.
The second option is pretty much a no-go with the different servers especially being with the implementation that is already in play.
The options of the dynamic SQL nature really would be different with the exception of the database like (StateFL or StateCO). Outside of that they would wouldn't be do different for most stored procedures for the most part. I would be basically constructing a dynamic SQL in a stored procedure. If nothing else that might help with the "cached" or saved query plan albeit the primary difference would be the database in the dynamic SQL (I.e. again "StateFL" or "StateCO")
Believe that sounds like a reasonable amicable solution?
Obviously, regenerating the query plan would take some time away. SQL Server is going to utilize the keys and indexes as the first thing anyway, I believe. The fact that we're using the keys on already is an added plus. I don't see how it would relate an existing query plan stored in cache to a dynamic sql which it might save. I don't see how it can associate one dynamic sql cache plan with another.
The second option is pretty much a no-go with the different servers especially being with the implementation that is already in play.
The options of the dynamic SQL nature really would be different with the exception of the database like (StateFL or StateCO). Outside of that they would wouldn't be do different for most stored procedures for the most part. I would be basically constructing a dynamic SQL in a stored procedure. If nothing else that might help with the "cached" or saved query plan albeit the primary difference would be the database in the dynamic SQL (I.e. again "StateFL" or "StateCO")
Believe that sounds like a reasonable amicable solution?
Hi,
as long as you are executing one SQL statement on the same database with the same SQL text it is cached to be reused if it is executed again with the same text. Regarding different databases it makes no difference.
At the beginning you said that you are working with all databases on the same server. So I also, like Icohan, do not understand why you have this setup and what you want to achieve with such an UPDATE statement (of course it will be more than what's in your demo above).
Cheers,
Christian
as long as you are executing one SQL statement on the same database with the same SQL text it is cached to be reused if it is executed again with the same text. Regarding different databases it makes no difference.
At the beginning you said that you are working with all databases on the same server. So I also, like Icohan, do not understand why you have this setup and what you want to achieve with such an UPDATE statement (of course it will be more than what's in your demo above).
Cheers,
Christian
ASKER
It's quite similar but the proper database is third party we want to leave that alone as much as possible. Hence, the reason for the stage database on the same server. The proper databases all have a different database name.
For instance:
StateFL
StateCO
StateMI
StateCA
StateOH
etc
The data in each of those databases although the structure is the same the data is different - related to the state. Remember it's from a third-party app which is constructed in this manner. Now, could the DB's have been put on different servers - absolutely - but they weren't
Each has a StageDB as well (that is custom (not third-party)
StageFL
StageCO
StageMI
StageCA
StageOH
etc
Now, I do not want to maintain 14 (one for each state) different versions of like the SP I mentioned. That Stage is a different DB on the same server so I can access the "StateXX" from the same server and not have a linked server.
I have one version of that SP. I was thinking/hoping to have like CREATE SP code with the dbo.TableA created on each StageXX DB. However, in that SP and in the process I described that would update the MemTypeInd on the StateXX DB table A IF I did not have to change that dbo.TableA.
So, take CO for instance:
I would create that SP in CO and it has the dbo.TableA.
When that runs it fails because it couldn't find the dbo.TableA because I am on the StageCO DB. and not on the StateCO DB.
However, on the SP that is CREATED in the StageCO if I have it update StateCO.dbo.TableA then it's good.
The problem with that is I have a different version of the original SP (the one where it started with dbo.TableA.)
So, no, it's not executing on the same database. The text that it will execute is basically the same but will obviously have the StateXX.dbo.Table (where XX is the state)
Does that make any better sense?
For instance:
StateFL
StateCO
StateMI
StateCA
StateOH
etc
The data in each of those databases although the structure is the same the data is different - related to the state. Remember it's from a third-party app which is constructed in this manner. Now, could the DB's have been put on different servers - absolutely - but they weren't
Each has a StageDB as well (that is custom (not third-party)
StageFL
StageCO
StageMI
StageCA
StageOH
etc
Now, I do not want to maintain 14 (one for each state) different versions of like the SP I mentioned. That Stage is a different DB on the same server so I can access the "StateXX" from the same server and not have a linked server.
I have one version of that SP. I was thinking/hoping to have like CREATE SP code with the dbo.TableA created on each StageXX DB. However, in that SP and in the process I described that would update the MemTypeInd on the StateXX DB table A IF I did not have to change that dbo.TableA.
So, take CO for instance:
I would create that SP in CO and it has the dbo.TableA.
When that runs it fails because it couldn't find the dbo.TableA because I am on the StageCO DB. and not on the StateCO DB.
However, on the SP that is CREATED in the StageCO if I have it update StateCO.dbo.TableA then it's good.
The problem with that is I have a different version of the original SP (the one where it started with dbo.TableA.)
So, no, it's not executing on the same database. The text that it will execute is basically the same but will obviously have the StateXX.dbo.Table (where XX is the state)
Does that make any better sense?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Oh lord, I *completely* forgot about SYNONYM!! After some testing with it things started coming back.
That should suffice for what I need and not use dynamic queries which I have also dreaded the thought unless absolutely no other option after everything has been exhausted.
I will implement that more and let you know on the results.
That should suffice for what I need and not use dynamic queries which I have also dreaded the thought unless absolutely no other option after everything has been exhausted.
I will implement that more and let you know on the results.
I mean what do you really need to do? Do you need to separate/segregate/identif