March 28, 2008 at 1:18 pm
Matt Miller (3/28/2008)
Or....you could just use the NTILE function to achieve the same result...:)
You guys and your fancy SQL 2005 functions...:hehe:
That's a nice one. How's the performance vs. something like I wrote (which I'd think has less overhead because there's no reordering involved)?
March 28, 2008 at 1:31 pm
dfalso (3/28/2008)
Matt Miller (3/28/2008)
Or....you could just use the NTILE function to achieve the same result...:)You guys and your fancy SQL 2005 functions...:hehe:
That's a nice one. How's the performance vs. something like I wrote (which I'd think has less overhead because there's no reordering involved)?
Not 100% sure to be honest. One thing it does do is to make SURE that the "piles are even", which a modulo against the identity can't guarantee (since there may be gaps due to inserts/deletes, etc...). It also keeps stuff mostly grouped together (meaning, rows 1-100 would be together, 101-200 together, etc...) which may work better in some cases. So you'd end up handing out all of the "A's" together, etc..., which sometimes ends up being easier to "wrap your head around".
But still - it would probably be worth testing to know. It hasn't seemed awful yet, but the tests were small, so I don't know that it has the giddy up and go.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 28, 2008 at 2:17 pm
dfalso (3/28/2008)
Jeff Moden (3/28/2008)
Any code to go along with this "FWIW"? 😉What, you want answers too? 🙂
Well, assuming you have an integer-ish rowid field on the table, MOD it by the number of buckets you need. This is if the phone numbers are allowed to be randomly assigned, of course:
select *,
rowid_field % @BucketCount as bucket
from
where @BucketCount is a parameter to the proc.
Strange, my cat's name is "Bucket" and thats all i know about "Buckets". Neither do i know anything about ntile functions... I'l have to see what xml can do for me, I read the links and found it very interesting. Maybe in few months i can cope with more then that but for now, XML suits me fine.
Thanks a lot !
Now, just loop through your array of people, with successive SELECT statements
where bucket = @ArrayIndex
Assuming your array is 0-based, you don't have to do anything. If it's 1-based (VB), either change your MOD calc:
(rowid_field % @BucketCount) + 1 as bucket
or decrement your array index in your where clause
where bucket = @ArrayIndex - 1
From what I read, I think this satisfies the problem. Right?
Unless I'm missing something...
March 28, 2008 at 3:23 pm
Basically, stats people do this sort of problem alot. You might hear about deciles or quartiles; that's when you take your range of data, and assign it to different groups (in this case 10 or 4), with an attempt to have each group have about the same number of data points. You're doing the same thing, only assigning to N groups. Deciles implies 10 and quartiles 4, so in a general case people either refer to this process as "binning" or "bucketing", or making Ntiles (quartiles, quintiles,...Ntiles).
The MOD method takes advantage of the fact that integer remainder of integer division by N is anywhere from 0 to N-1 (in other words, N different values), which when applied against a continuous range of values (your identity field) gives even "bucketing". It looks like the NTILE function works by taking the total number of records divided by N, giving the number of records in each "bucket" NB. It then takes the first 1 to NB records and assigns them value 1, then next (NB+1) to (NB+NB), value 2, and so on.
March 28, 2008 at 4:54 pm
dfalso (3/28/2008)
Jeff Moden (3/28/2008)
Any code to go along with this "FWIW"? 😉What, you want answers too? 🙂
Heh... no... just wanted to know if I was gonna have to write some... lots of folks don't "get it" unless they can see some code...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2008 at 5:00 pm
blahknow (3/26/2008)
I've been told i must send a string and create a function to change the string into a table and i guess thats what i'll do unless i hear a better idea.Thanks
Lot's of folks have coughed up some suggestions and even some code. But, when I see things like what you told Gail above, I've gotta ask, are you all set or do you still need some help with code?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2008 at 3:11 am
Hi Jeff
Using XML is a satisfactory resolution for me.
There could of course be better solutions which are above my present comprehension and i admit i didnt understand all the suggestions i was consulted. Those of whice two i understood and adopted are Making a function to convert a string into table ans using XML. I preferred using XML because i like to discover things i had not known formerly and second thing is i think that XML code can be used in HTML pages so using it, to my opinion is a future foresight.
Thank you all fellows !
March 29, 2008 at 4:20 am
🙂 Don't try to do the things you like to do in project. Try those which project like. (I am talking in performance context, as Jeff has mentioned using tally table concept over XML, so that you can get better performance)
A well formed UDF or open code that uses a Tally table to do the split will blow the doors off of any form of XML split. Side benefit is that the numbering that results from the Tally table method can actually be used to create an array-like coordinate system for the individual elements.
The Tally table (numbers table) method of splitting such inputs will be faster (and easier) than any other method including XML. The Tally table method is very similar to an "array" in that, if required, you can develop an (index) coordinate system for the elements much as you would have in VB or C for one and two dimensional "arrays" quite easily.
-- By Jeff
So try to post the real life example here, so that Jeff can show us, how to use it.
Thanks,
Mahesh
MH-09-AM-8694
March 29, 2008 at 7:47 am
Mahesh Bote (3/29/2008)
🙂 Don't try to do the things you like to do in project. Try those which project like. (I am talking in performance context, as Jeff has mentioned using tally table concept over XML, so that you can get better performance)A well formed UDF or open code that uses a Tally table to do the split will blow the doors off of any form of XML split. Side benefit is that the numbering that results from the Tally table method can actually be used to create an array-like coordinate system for the individual elements.
The Tally table (numbers table) method of splitting such inputs will be faster (and easier) than any other method including XML. The Tally table method is very similar to an "array" in that, if required, you can develop an (index) coordinate system for the elements much as you would have in VB or C for one and two dimensional "arrays" quite easily.
-- By Jeff
So try to post the real life example here, so that Jeff can show us, how to use it.
Thanks,
Mahesh
I'll think of doing things in accordance to "what my project likes" once i build it.
By now, i dont know what "Tally table" is and as for XML or function to convert a string into table i was supplied with great links wherby i could instantly perform the job.
As i said before, once things start working, i'll think of better performanc and in future time maybe i'll prefer Tally tables, give that i know what it is.
March 29, 2008 at 10:58 am
blahknow (3/29/2008)
Hi JeffUsing XML is a satisfactory resolution for me.
There could of course be better solutions which are above my present comprehension and i admit i didnt understand all the suggestions i was consulted. Those of whice two i understood and adopted are Making a function to convert a string into table ans using XML. I preferred using XML because i like to discover things i had not known formerly and second thing is i think that XML code can be used in HTML pages so using it, to my opinion is a future foresight.
Thank you all fellows !
Sure... no problem. Thanks for the feedback. One of the problems I see, though, is that, like you said... "i admit i didnt understand all the suggestions i was consulted"... you don't understand how bad the XML split can be compared to the Tally table.
If you'd like, post your split function and I'll model a Tally table function to do the same thing. Who knows, you might learn another new thing or two 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2008 at 11:24 am
I'll do, when i'm finished in a day or 2.
Thanks
March 29, 2008 at 12:35 pm
I'm still curious what you're doing with these created tables. Are the sales people selecting from them (and if so, can you change the queries they use)? Are you exporting them?
I have a suspicion that maybe this can be done without creating multiple new tables, but I do need some more info.
If you don't mind, that is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2008 at 1:12 am
Hi,
If the so called "array" contains 3 names for example, those 3 names (salsemen) will get in his/her mail box an excel sheet contains a list of phone numbers. All 3 will get exactly the same number of phones where each phone appears in one of those 3 tables only.
All 3 tables will have the same criteria: The phones will belong to clients/candidates/refusals that joined the oraganisation at the same range of dates although each salesmen may get a different dates' range (not necessarily) but still under one date's range condition that characterizes all 3 dates' ranges.
March 30, 2008 at 12:33 pm
Ok. Almost clear. From the looks of things, there's no need to create separate tables at all. Just one more thing....
Where does the email address come from that you're sending to? Do you have them in a table somewhere? Can you put them into a temp table before you call the proc to generate the lists?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2008 at 2:12 am
GilaMonster (3/29/2008)
I'm still curious what you're doing with these created tables. Are the sales people selecting from them (and if so, can you change the queries they use)? Are you exporting them?I have a suspicion that maybe this can be done without creating multiple new tables, but I do need some more info.
If you don't mind, that is.
Hi Gail,
This is the main procedure, not yet finished because i've got stuck wuth an Error message:
/*exec sp_financial_consult
'name1, name2,name3',
'name1@mail.com,name2@mail.com,name1@mail.com',
'20080201',
'20080331',
'vip_cancel_statistics.dbo.vips'*/
ALTER PROCEDURE dbo.sp_financial_consult
@list_of_consultants VARCHAR(MAX),
@list_of_emails VARCHAR(MAX),
@mindate DATETIME,
@maxdate DATETIME,
@source_file VARCHAR(50)
AS
DECLARE @array_of_parameters TABLE
(
ordinal INT,
name VARCHAR(50),
email VARCHAR(250)
)
DECLARE @array_of_names TABLE
(
ordinal INT,
name VARCHAR(50)
)
INSERT INTO @array_of_names
SELECT occurenceid, splitvalue
FROM dbo.make_table_from_string(@list_of_consultants,',')
DECLARE @array_of_mails TABLE
(
ordinal INT,
mail VARCHAR(250)
)
INSERT INTO @array_of_mails
SELECT occurenceid, splitvalue
FROM dbo.make_table_from_string(@list_of_emails,',')
INSERT INTO @array_of_parameters
SELECT
a.ordinal,
a.name,
b.mail
FROM
@array_of_names AS a LEFT JOIN @array_of_mails AS b
ON
a.ordinal=b.ordinal
CREATE TABLE #financial_consult
(
ordinal INT IDENTITY(1,1),
accountno VARCHAR(20),
lastname VARCHAR(50),
name VARCHAR(50),
spouse_name VARCHAR(50),
city varchar(50),
address1 varchar(50),
address3 varchar(10),
zip varchar(10),
phone1 VARCHAR(50),
phone2 VARCHAR(50),
phone3 VARCHAR(25),
date_of_join datetime,
date_of_birth datetime,
email VARCHAR(80)
)
DECLARE @myDynamic VARCHAR(MAX)
SET @myDynamic=
'
INSERT INTO #financial_consult
SELECT
accountno,
lastname1,
name1,
name2,
city,
street,
number,
zip,
phone1,
phone2,
phone3,
date,
date_of_birth1,
FROM ' +
@source_file +
'
WHERE
date>=' + @mindate + ' AND
date<=' + @maxdate
PRINT(@myDynamic)
The code to convert string into table goes that way (no errors !!!)
ALTER FUNCTION dbo.make_table_from_string
(
@list_of_names VARCHAR(200),
@delimiter VARCHAR(5)
)
RETURNS @consultants TABLE
(
occurenceid SMALLINT IDENTITY(1,1),
splitvalue VARCHAR(200)
)
AS
BEGIN
DECLARE @name_length INT
WHILE LEN(@list_of_names)>0
BEGIN
SELECT @name_length=
(CASE CHARINDEX(@delimiter, @list_of_names)
WHEN 0
THEN LEN(@list_of_names)
ELSE
CHARINDEX(@delimiter, @list_of_names) -1
END)
INSERT INTO @consultants
SELECT SUBSTRING(@list_of_names,1,@name_length)
SELECT @list_of_names=
(CASE (LEN(@list_of_names) - @name_length)
WHEN 0
THEN ''
ELSE
RIGHT(@list_of_names, LEN(@list_of_names) - @name_length -1)
END)
END
RETURN
END
Running the procedure eventually it turns out that i cannot mix datetime variable inside dynamic code. Error shows up
Msg 241, Level 16, State 1, Procedure sp_financial_consult, Line 73
Conversion failed when converting datetime from character string. Your help will be very welcome !
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply