Arthur Fuller
fuller.artful at gmail.com
Wed Jun 24 07:35:48 CDT 2009
The execution time varies significantly with the nature of a given function. I love UDFs but experience has taught me that they can be expensive. A while ago I inherited a database that used lots and lots of UDFs for simple lookups, the developer's theory being that the PKs of said lookups might possibly change and therefore to insulate against this, they ought to be UDFs that sought the text string and returned the PK. These were all called in a process that ran overnight. After first investigating the implications, I rewrote the procedure, replacing all the numerous UDF calls with hard-coded references. It took a while (the procedure was about 2000 lines of code), and I was careful (e.g. copy the line, paste it in, change it, and comment out the previous line). At the end of the day, the process, which formerly took six hours to run, came down to 45 minutes. Since I was the new kid on the block and since DBA tasks tend to be invisible to the users, I was especially pleased that my boss chose to make an official announcement of the success of the little project. This is one case where the use of UDFs was bad in terms of performance. There are many other cases where UDFs are a godsend, but for simple lookups, as in the example cited above, they are pointless. And one other thing before I finish this message: UDFs that return a table rather than a simple scalar variable are truly wonderful. A. On Wed, Jun 24, 2009 at 7:50 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > Mark, > > I know that they exist, and I have used them very occasionally. I really > need to go become > acquainted with all of them, print out a list and study what is available. > I am doing rather a lot > of stored procedures now, but they mostly just build tables, add fields, > indexes, bcp in/out etc. > > I am still at the advanced beginner level in SQL Server. > > John W. Colby > www.ColbyConsulting.com > > > Mark Breen wrote: > > Hello John, > > you thought me how to call vba functions in MS Access 97 queries, I was > > shocked by how powerful that feature was. > > > > Have you used functions in SQL Server? If so, please ignore my email and > > accept my apologies for even asking the question;) > > > > I have created a few functions in SQL Server and for low volumes of data, > > they work great, of course in your case, you are seeking max efficiency > and > > I have no idea about performance of a function versus inline code. I > guess > > no major difference. > > > > If you have not used them, give them a try. > > > > Thanks > > > > Mark > > > > > > > > > > > > 2009/6/23 jwcolby <jwcolby at colbyconsulting.com> > > > >> This certainly looks like the answer. Now to turn the computer loose > for a > >> week, updating the > >> address field... > >> > >> There are about 6 or 8 of these address parts, each will now be inside > of > >> an IsNull(). ;) > >> > >> Is it possible to do this X records at a time? IOW I have a fifty > million > >> record table. I want to > >> cause the computer to go off and start doing this but I also want the > table > >> that is being updated to > >> be available for use while this is happening (for selection queries). > How > >> do I tell SQL Server to > >> do updates in blocks of 1000 records or something like that? Can I > >> dynamically change that "number > >> of records updated at a time" inside of a stored procedure? > >> > >> John W. Colby > >> www.ColbyConsulting.com > >> > >> > >> Francisco Tapia wrote: > >>> John, you can do it right out of a select, something like this > >>> > >>> IsNull(StreetNumber + ' ', '') + IsNull(Direction + ' ', '') + > >>> IsNull(StreetName + ' ', '') etc... > >>> > >>> I added the space padding right inside of the IsNull formula because > this > >>> way you get a padding, for each field, unless there is nothing then the > >>> result is no padding. In Sql Server Null + a value = Null :) > >>> > >>> If you don't want to accidentally end up with a space padding on the > >> right > >>> simply trim your statement with RTRIM(your isnull series) > >>> > >>> > >>> > >>> -Francisco > >>> http://sqlthis.blogspot.com | Tsql and More... > >>> > >>> > >>> On Tue, Jun 23, 2009 at 6:53 AM, jwcolby <jwcolby at colbyconsulting.com > >>> wrote: > >>> > >>>> I am trying to append all of the pieces of an address into a single > >> address > >>>> string. As an example, > >>>> if I have the address: > >>>> > >>>> 121 S. Main Street NE Apt #201 > >>>> > >>>> this address will be stored with standardized spellings of all the > parts > >>>> into a set of fields: > >>>> > >>>> 121 > >>>> S > >>>> Main > >>>> St > >>>> NE > >>>> Apt > >>>> 201 > >>>> > >>>> I need to get this all back into a single field. Understand however > >> that > >>>> lots of those pieces might > >>>> be missing in any given address. For example the next field might be: > >>>> > >>>> 121 > >>>> > >>>> Main > >>>> St > >>>> > >>>> Ste > >>>> 1342 > >>>> > >>>> So... I need to get all the pieces back into a single address field. > If > >> a > >>>> piece exists then it > >>>> needs padding around it so I don't end up with: > >>>> > >>>> 121MainStSte1342 > >>>> > >>>> But if a piece does not exist then I do not want the padding for that > >>>> missing field. > >>>> > >>>> I am assuming that I will be using a stored procedure. Pass in the > >>>> database, table, > >>>> UnifiedAddressField, then all of the field parts. > >>>> > >>>> It seems clumsy to do it this way, not to mention record by record, > for > >> 50 > >>>> million records... well... > >>>> > >>>> There must be a better way. Has anyone on the list done this before? > >>>> > >>>> -- > >>>> John W. Colby > >>>> www.ColbyConsulting.com > >>>> _______________________________________________ > >>>> dba-SQLServer mailing list > >>>> dba-SQLServer at databaseadvisors.com > >>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >>>> http://www.databaseadvisors.com > >>>> > >>>> > >>> _______________________________________________ > >>> dba-SQLServer mailing list > >>> dba-SQLServer at databaseadvisors.com > >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >>> http://www.databaseadvisors.com > >>> > >>> > >> _______________________________________________ > >> dba-SQLServer mailing list > >> dba-SQLServer at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >> http://www.databaseadvisors.com > >> > >> > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >