March 3, 2011 at 12:15 pm
Eric M Russell (3/3/2011)
My most recent involvement with querying remote data involves joining huge resultsets originating in a 3rd party Oracle 11g database with reference data in a SQL Server 2008 datamart, and then inserting the result into into the same SQL Server datamart for later use by the reporting team. The SQL Server DTC and Oracle don't share much, and early experiments with 4 part named queries would result in entire table scans being pulled across the wire. When I EXEC a pass-though query to Oracle, I'm only getting a small DTC overhead, and the remaining 1 - 30 minutes of processing time occurs entirely on Oracle, and then however much time it takes to bring the final resultset back across the wire. Joining the temporary resultset with the reference tables is trivial at that point.
Yes, I've been in a similar boat and feel your pain. Distributed query is not perfect by any means, though some of the problems can be traced to the remote end which may not provide the optimizer will good (or any) statistical information about the remote data. No doubt this is especially problematic where the remote server is not a SQL Server (it's been a few years since I had to work with Oracle). Do look into Gus' suggestion re: table variables to avoid a distributed transaction, or if 2008, try the remote transaction promotion option I described: it will prevent the local INSERT implicit transaction from being promoted to a full DTC transaction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 3, 2011 at 8:20 pm
Telarian (3/3/2011)
Jeff Moden (3/3/2011)
Oh, be careful now. "It Depends". 🙂 Scalar and Multi-Line Table Valued Functions (mTVF) can very quickly turn code into some pretty nasty, performance challenged RBAR especially if it refers to a table. Think of it as a hidden cursor.
Inline Table Valued Functions (iTVF), however, can be VERY useful and are calculated in the execution plan as if they were a "programmable view". Oddly enough, they also work very effectively to return single values like a Scalar function would when used with CROSS APPLY (thanks to Paul White for that nice little trick).
Don't get me wrong, I'm not saying there aren't usually better ways to do things. But sometimes you end up in less than ideal places and at those times a sub would be good.
Heh... agreed but I was talking about your recommendation to use UDF's. My suggestion is that Scalar and mTVF's can actually put you in one of those less than ideal places and that, with only a few exceptions, iTVF's are a better way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2011 at 7:33 am
Jeff Moden (3/3/2011)
Telarian (3/3/2011)
Jeff Moden (3/3/2011)
Oh, be careful now. "It Depends". 🙂 Scalar and Multi-Line Table Valued Functions (mTVF) can very quickly turn code into some pretty nasty, performance challenged RBAR especially if it refers to a table. Think of it as a hidden cursor.
Inline Table Valued Functions (iTVF), however, can be VERY useful and are calculated in the execution plan as if they were a "programmable view". Oddly enough, they also work very effectively to return single values like a Scalar function would when used with CROSS APPLY (thanks to Paul White for that nice little trick).
Don't get me wrong, I'm not saying there aren't usually better ways to do things. But sometimes you end up in less than ideal places and at those times a sub would be good.
Heh... agreed but I was talking about your recommendation to use UDF's. My suggestion is that Scalar and mTVF's can actually put you in one of those less than ideal places and that, with only a few exceptions, iTVF's are a better way to go.
I try to avoid scalar functions. One of the worst performance anti-patterns I've seen are when a UDF contains an inline select for a scalar value. This is a common example where joining the customer table, a view, or TVF would be much more appropriate:
select
app.schedule_date,
app.cust_id,
dbo.get_cust_fullname( cust_id ) cust_name, -- ouch!
dbo.get_cust_phone( cust_id ) cust_phone -- ouch!
from appointments app
where app.schedule_date >= '2011-02-01';
Even for for things like calculated indicators, formatting, or concatenating a customer's first name, last name, and title into a full name, I'll implement those as columns in a view based on the table that contains the columns.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 12, 2017 at 7:07 pm
I know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.
The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?
To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.
In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?
It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.
Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.
Dave
August 12, 2017 at 8:16 pm
dmeissner - Saturday, August 12, 2017 7:07 PMI know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?
To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.
In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?
It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.
Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.
Dave
If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".
So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?
To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.
As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?
Now, take the chip off your shoulder and welcome aboard! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2017 at 12:11 pm
Jeff Moden - Saturday, August 12, 2017 8:16 PMdmeissner - Saturday, August 12, 2017 7:07 PMI know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?
To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.
In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?
It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.
Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.
Dave
If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".
So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?
To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.
As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?
Now, take the chip off your shoulder and welcome aboard! 😉
Well said on all counts (as usual) Jeff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 13, 2017 at 1:09 pm
TheSQLGuru - Sunday, August 13, 2017 12:11 PMJeff Moden - Saturday, August 12, 2017 8:16 PMdmeissner - Saturday, August 12, 2017 7:07 PMI know this post is seven years old, but because I was triggered by all the lame responses given that didn't even address the question, I had to sign in and post a reply.The initial question was whether a sub-routine could be called in a T-Sql statement. The number of replies regarding WHY this would be desired is really disturbing. WTF??? Why couldn't someone just answer the question being asked?
To answer why this might be desirable, consider this. A user has 100 different databases that he would like to run a T-Sql statement against. Don't ask me why there are 100 different databases. That is irrelevant.
In the statement that the user is working on, there are a lot of checks and balances to see if various things exist before trying to create, delete, or otherwise alter them (i.e. tables, columns, indexes, etc.). The user wants to create a sub-routine to check if these things exist without having to write custom statements for everything he is trying to check. Those statements may include querying the system tables of these databases with several joins. Why would he want to recreate the same statement for everything he is trying to check? That is a question that should be asked of all of you. Sure, the user could create functions to perform these checks and just call those functions, BUT every function would have to exist in all 100 databases. What if it is 500 databases? That is what the user is trying to avoid. Comprende?
It would be helpful if sub-routines were possible to include right in the statement itself so that external functions would not be required to exist in every database being checked.
Other than that, Sihaab, great topic. It really stirred up the minds. Some of them might be a little weak.
Dave
If the op had phrased the question in such a fashion, perhaps there wouldn't have been what you call a lame dialog of ideas. As it was, the OP asked a general question either open to interpretation with an urge to get at the underlying reason for the open question or open to very short, unhelpful answers like "Submit a CONNECT item".
So, do you have an answer to the OPs question or was it your intent to actually succeed in posting something even more lame than what you've cited (and you DID succeed in grand style at that)?
To answer the OPs questions, although subroutines don't exist in SQL Server, there are a whole lot of alternatives and a good number of them don't require them to exist in any of the databases that you want to affect. But it depends on what you want to do and the OP didn't cite that purpose in the original post and so a discussion ensued. As for compelling MS to do anything, log into the MS CONNECT site, make a suggestion, and try to get people to vote it up. If MS thinks it's a lame suggestion or doesn't see the merit in doing it, they'll close the item regardless of the vote. If they don't know what to do with it, they may close it or keep it open for a decade. If they think it's a good idea, they may work on it now or in a decade and eventually put it in place sometimes with or without trickle down to previous versions.
As for your specific question as to how to check for existence of objects and possibly create or affect objections in 100 or 500 databases (whatever) on the same server, you could use the undocumented sp_MSForEachDB or roll your own little bit of dynamic sql or learn how to use temporary stored procedures with dynamic sql. All you have to do is ask a reasonable question, which includes why you want to do it so we can help you with the best of many possible solutions, or make a clarification without being a freakin' troll about it. Comprende?
Now, take the chip off your shoulder and welcome aboard! 😉
Well said on all counts (as usual) Jeff.
Agreed. BTW, I think procedures can still call other procedures. 😉
August 14, 2017 at 1:16 am
Calm down now Jeff. Don't be triggered yourself. I was only joking will the "Comprende" comment and it wasn't directed at you.
Time and time again, I run across posts where someone asks a specific question and everyone has to question the person's motives rather than answering the question. This was no exception. If there were other ways to perform the same feat, there wasn't much dialog about it.
I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution.
Anyway, isn't it great reviving a thread that's 7 years old? Must be a world record.
Take care...Dave
August 14, 2017 at 5:45 am
dmeissner - Monday, August 14, 2017 1:16 AMCalm down now Jeff. Don't be triggered yourself. I was only joking will the "Comprende" comment and it wasn't directed at you.Time and time again, I run across posts where someone asks a specific question and everyone has to question the person's motives rather than answering the question. This was no exception. If there were other ways to perform the same feat, there wasn't much dialog about it.
I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution.
Anyway, isn't it great reviving a thread that's 7 years old? Must be a world record.
Take care...Dave
Heh... calm down? Ironically, I was going to suggest that you do the same. Your first post on the site and you came out with words like "lame" and suggested that many of the responses came from weak minds.
The reason people here ask questions rather than providing immediate answers is because the OP frequently has no clue of what they're asking and so folks try to get to the root problem with the OP rather than providing an immediate answer which is frequently wrong for either the actual or future usage and teaches the OP little either way.
And, no... resurrecting a 7 year old thread isn't close to a record although resurrecting one in such a thoughtless fashion may be. And speaking of triggers, consider the fact that you did exactly what you accused others of doing... you have an answer and still you didn't post it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2017 at 7:57 am
Wow, triggered again.
August 14, 2017 at 8:14 am
dmeissner - Monday, August 14, 2017 7:57 AMWow, triggered again.
Dude, post your solution.
August 14, 2017 at 12:19 pm
I already did.
"I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution. "
August 14, 2017 at 12:34 pm
dmeissner - Monday, August 14, 2017 12:19 PMI ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db.. "
please could you provide some example code ... I have a similar issue (I think!)
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 14, 2017 at 1:14 pm
dmeissner - Monday, August 14, 2017 12:19 PMI already did."I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution. "
Yep. Got code? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2017 at 1:15 pm
dmeissner - Monday, August 14, 2017 7:57 AMWow, triggered again.
Heh... nah... just responding to that chip you're carrying. 😉 When you drop it, I'll quit. Maybe next time, you won't come into a conversation swinging a bat and calling people names.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 96 total)
You must be logged in to reply to this topic. Login to reply