I also found the function to be slow in testing. SELECT N, N * N AS N_SQUARED, dbo.fnIntegerInWords(N * N) AS WORDSĬPU time = 4992 ms, elapsed time = 5213 ms. IF '1' AND RIGHT(S.STRING,1) '0' THEN '-' + (SELECT Word FROM WHERE Num = RIGHT(S.STRING,1)) */ - PUT asterisk slash at the beginning of this line, but for testing remove it * - PUT slash asterisk at the beginning of this line, but for testing remove it Here's the code and the results:ĬREATE FUNCTION dbo.fnIntegerInWords ( int ) I then re-wrote this function to make use of dbo.Tally, where I expected to improve execution time significantly because the code would then be set-based. The dbo.Tally table is just a table of the numbers from 1 to 11000. SELECT N, N * N AS N_SQUARED, dbo.fnSpellInteger(N * N) AS WORDSĬPU time = 13681 ms, elapsed time = 14052 ms. I created the function as is on a SQL Server 2005 Developer Edition 64-bit, w/SP2 (no CU's), running on Vista Ultimate 64-bit with SP1, on an Intel Q9550 Quad-Core cpu at 2.83GHz and 8 GB of RAM, with SQL Server limited to 4096MB. I haven't messed with the AND, but I am concerned about function performance over a large number of rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |