Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Makkaramestari
Starting Member
8 Posts |
Posted - 2006-02-20 : 05:54:53
|
Hi!I have field1 decimal(11,0) containing number 1234567 andI must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789Makkaramestari |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 06:09:07
|
Here's a few quick options:declare @Value dec(11,0)set @Value = 12345678select @Value, right(cast(@Value as varchar),6) as Method1, cast(@Value as bigint) % 1000000 as Method2*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-20 : 06:14:46
|
Look up RIGHT in Books Online.. |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 06:27:00
|
Lol - or look at RIGHT in my example (Method 1)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-20 : 06:46:54
|
Sorry, my fault for not refreshing the page.. |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 07:21:35
|
hehe..*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-20 : 07:27:45
|
quote: Originally posted by Wanderer hehe..*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!
oh btw, you don't really need to cast as it's implicit.. |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 08:45:48
|
I got errors without the cast, I think because the base data type is dec.declare @Value dec(11,0)set @Value = 12345678select @Value,right(@Value ,6) as Method1,(@Value ) % 1000000 as Method2returns:Server: Msg 206, Level 16, State 2, Line 4Operand type clash: int is incompatible with void typeServer: Msg 8117, Level 16, State 1, Line 4Operand data type numeric is invalid for modulo operator.on checking, those errors occurred when I didn't convert for the modulo issue. You are 100% correct - right 6 works on the dec(11,0)...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-20 : 09:31:29
|
Implicit conversion takes place if the datatype is int or bigintdeclare @Value intset @Value = 12345678select @Value,right(@Value ,6) as Method1,(@Value ) % 1000000 as Method2MadhivananFailing to plan is Planning to fail |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 10:54:48
|
quote: Originally posted by madhivanan Implicit conversion takes place if the datatype is int or bigintdeclare @Value int
Yes - but the original poster data is in a dec(11,0)quote: Originally posted by Makkaramestari Hi!I have field1 decimal(11,0) containing number 1234567 andI must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789Makkaramestari
hence I declared my @Value as dec(11,0) and worked from there.I wonder which is more efficient:right(@Value,6)orcast(@Value as bigint) % 1000000I think the right reads easier, and seems to do less work, but then again, it returns char, whereas the poster wanted int:quote: eg. I want field2 int containing 234567 as result
I think I'll test the 2 using a function and stress it a bit.... I'll let you know what it looks like*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-20 : 11:08:17
|
hmm - looks like the right approach is using RIGHT (excuse the pun).In a loop of 1000 exuctions of a function for RIGHT and a function using MODULO, the results were:************** RIGHT FUNCTION Overall Duration: 123 ms ***************************** MODULO FUNCTION Overall Duration: 313 ms ****************##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-20 : 12:34:50
|
quote: Originally posted by Wanderer I got errors without the cast, I think because the base data type is dec.declare @Value dec(11,0)set @Value = 12345678select @Value,right(@Value ,6) as Method1,(@Value ) % 1000000 as Method2returns:Server: Msg 206, Level 16, State 2, Line 4Operand type clash: int is incompatible with void typeServer: Msg 8117, Level 16, State 1, Line 4Operand data type numeric is invalid for modulo operator.on checking, those errors occurred when I didn't convert for the modulo issue. You are 100% correct - right 6 works on the dec(11,0)...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!
But,declare @Value dec(11,0)set @Value = 12345678select @Value,right(@Value ,6) as Method1 Will work without an explicit conversion..You will need to cast (@Value ) % 1000000 as Method2 as the result from right will return a char.. |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-02-20 : 17:46:00
|
EDIT:You're kidding, right?I'm not paid enough.....quote: Originally posted by Makkaramestari Hi!I have field1 decimal(11,0) containing number 1234567 andI must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789Makkaramestari
Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
jhermiz
3564 Posts |
Posted - 2006-02-20 : 21:24:27
|
quote: Originally posted by Wanderer hmm - looks like the right approach is using RIGHT (excuse the pun).In a loop of 1000 exuctions of a function for RIGHT and a function using MODULO, the results were:************** RIGHT FUNCTION Overall Duration: 123 ms ***************************** MODULO FUNCTION Overall Duration: 313 ms ****************##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here!
Its great and all you tested, but a test like this isn't valid since there are factors that may contribute to a function to perform much slower.Consider network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock, etc etc. Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-21 : 03:39:28
|
@Jhermiz:In a test where the difference in the SQL is:select dbo.@Right6(@Value)orselect dbo.@Modulo6(@Value)How does quote: network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock
affect the DIFFERENCE between those 2 execution times when considered in isolation?I totally agree that differing conditions will cause different executions times per execution, but since the calling code would, at a point in time, either call the @Right6 function, or the @Modulo6 function, those factors can be ignored for the purpose of considering the performance of the 2 different function, since they would be the same or either call, depending on which was used.I did flush buffers before and between executions, to ensure fair comparisons.Please bear in mind - my numbers are not trying to predict the performance on the Poster's systems , as hardware, configurations, and all the factors you mentioned would make that impossible. What I was doing was testing the 2 functions, in isolation, to determine which was superior.Am I missing something? Also, sorry for the long-winded response - hope it makes some kind of sense....*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
jhermiz
3564 Posts |
Posted - 2006-02-21 : 13:09:38
|
You never mentioned anything about isolation. Don't take too much offense just pointing things out. Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
X002548
Not Just a Number
15586 Posts |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-21 : 20:20:36
|
I tested the two methods with a table containg 1 million rows. The Modulo method was faster every time I ran the test.Here's the code I used to load the table and test the methods, and some typical results.Function f_table_number_range is available in the script library./*drop table #tcreate table #t ( num decimal(11,0) not null )*//*truncate table #t-- Load table with one million rowsinsert into #tselect number + 1234567.from dbo.f_table_number_range(1,1000000)*/godeclare @out varchar(20)declare @start datetimedeclare @end datetimeselect @start =getdate()select @out =max(right(num ,6)) from #tselect @end =getdate()select [MS Right] = datediff(ms,@start,@end)godeclare @out varchar(20)declare @start datetimedeclare @end datetimeselect @start =getdate()select @out = max(convert(bigint,num)% 1000000) from #tselect @end =getdate()select [MS Modulo] = datediff(ms,@start,@end) Results:MS Right ----------- 1966(1 row(s) affected)MS Modulo ----------- 843(1 row(s) affected) CODO ERGO SUM |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-02-22 : 04:10:11
|
quote: Originally posted by jhermiz You never mentioned anything about isolation. Don't take too much offense just pointing things out. Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
No offence taken at all - sorry if I came over that way The reason I "tested in isolation" was because in the final code, only 1 method is going to be used, so the environmental factors don't affect the decision between which method to choose.... hmm - I don't seem to be able to find a good way to explain why I tested like this.Basically, I tested "in isolation" because none of the factors mentioned will have an affect on the relative performance of the 2 methods - i.e they are both SQL function calls, using built in SQL function - neither of them are any more, or less, reliant on "network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock".Hope that explains my thinking a bit better... - I am very keen to have flaws in my approach pointed out, and explained, so I can improve.@Michael - really stuggling to find that function - search resources gives no articles. Care to give the code, or a direct link for this poor fool? (fool-Me, btw).I notice you don't clean buffers between your executions - that should have an accfect, imho. What happens when you run modulo6 first, and not right6?I wonder if the difference is because of the fact I created them as functions - I'll try and check that.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-22 : 11:20:11
|
quote: Originally posted by Wanderer...@Michael - really stuggling to find that function - search resources gives no articles. Care to give the code, or a direct link for this poor fool? (fool-Me, btw).I notice you don't clean buffers between your executions - that should have an accfect, imho. What happens when you run modulo6 first, and not right6?...
The F_TABLE_NUMBER_RANGE function is available in this link:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685Edit: I have found it easier to use Google to search SQLTeam. When I put F_TABLE_NUMBER_RANGE into a Google search, the link above was the first item.I did not clean the buffers because it seemed unnecessary and undesirable. I wanted the table data cached so that I was not testing the IO of the system, just the difference in the query run-time. The system I ran the test on was a fast test system that was idle at the time I ran it, so there was no other activity.I ran both queries many times after I loaded the temp table, and none of the runs had any significant difference in run-time. I ran them in different order, and run each one repeatedly by itself. Same result each time.I always try to post my test scripts so you can try it youself. No need to take my word for it. CODO ERGO SUM |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|