Jeff Moden

Forum Replies Created

Viewing 15 posts - 38,446 through 38,460 (of 59,072 total)

RE: Code generation

Lowell (5/11/2010)
Jeff Moden (5/11/2010)
1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 2:41 pm

#1165841
RE: problem importing data from a huge xml file 1g

sohairzaki (5/11/2010)
I used ssis xml task xpath the original file was of the following form
______________
My input file has the following format
<enterprise>
<person>
<sourcedid>
<source>111111</source>
<id>22222</id>
</sourcedid>
<name>
<fn>xxxxxxx</fn>
<n>
<family>yyyyy</family>
<given>zzzzzz</given>
</n>
</name>
<demographics>
<gender>2</gender>
</demographics>
<email>xxxxxx@fffff.edu</email>
<adr>
<street>cccccc</street>
<locality>ccccc</locality>
<region>cccc</region>
<pcode>ccccccc</pcode>
</adr>
<academics>
<academicmajor>gggggg</academicmajor>
<customrole>hhhhhh</customrole>
<customrole>dddddd</customrole>
<customrole>cccccc</customrole>
</academics>
</person>
<person>
…
</person>
</enterprise>
______________________
out put was in the form of...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 2:35 pm

#1165836
RE: CTE performance

kazim.raza (5/11/2010)
Yo nailed it.. here's what I did
I have joined EntityType with FullyQualifiedName. The script would treat it as another extension to levels. My last select remains intact and I...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 2:26 pm

#1165831
RE: Code generation

1.6 million random 8 character codes that no one can guess and they're guaranteed to be unique within the set... takes about 25 seconds on my 8 year old desktop...
WITH
cteFirstGen...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 7:41 am

#1165534
RE: Code generation

Atif Sheikh (5/11/2010)
This code gave out only 253 duplicates on 16 Mln records.
I don't know if this is exactly what you're after, but you could give it a go.
SELECT TOP...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 7:19 am

#1165522
RE: There Must Be 15 Ways To Lose Your Cursors… Part 2: Just Put It in a S

john.moreno (5/10/2010)
How does the idea that you can always replace a cursor with something else, fit in with your Connect suggestion, https://connect.microsoft.com/SQLServer/feedback/details/440375?
Maybe it's just me but it seems obvious... it's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 6:53 am

#1165510
RE: When Should a Table be Partitioned?

... and aggregate the older data if it's static.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 6:30 am

#1165494
RE: problem importing data from a huge xml file 1g

sohairzaki (5/10/2010)
Any solution for that
It depends... where does the data come from and why can't it be split up a bit at the source?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 6:28 am

#1165493
RE: Tally Table to split string

You bet... thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 6:25 am

#1165489
RE: Concatenating with conditional delimiter

Cory E. (5/11/2010)
Jeff Moden (5/10/2010)
STUFF works fine but let's demonstrate a little trick that a lot of people don't know about with SUBSTRING... you don't actually have to calculate a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 11, 2010 at 6:20 am

#1165488
RE: CTE performance

kazim.raza (5/10/2010)
Those unused columns were just for the purpose of dev check; I am only keeping FullyQualifiedName, Rownum and EntityID.
Duplicates records need to be there as they'll be going...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 10, 2010 at 9:22 pm

#1165321
RE: Using " IN " inside " WHERE " clause

You bet, MC... thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 10, 2010 at 9:04 pm

#1165316
RE: How to obtain week by week plus YTD totals

First... http://www.sqlservercentral.com/articles/Best+Practices/61537/
Second... http://www.sqlservercentral.com/articles/Crosstab/65048/
I have the patience if you have the data and the willingness to help yourself by learning something new. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 10, 2010 at 8:57 pm

#1165312
RE: CTE performance

First, it would probably run a bit faster if you didn't select so many unused columns from #Hie.
Second, since your latest query isn't actually using OrgEntityType, my suggestion would be...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 10, 2010 at 8:47 pm

#1165310
RE: How to execute SQL in query reults?

irobertson (5/10/2010)
Hi,
You need to pass the results of your query to a variable, then call this using either exec or preferably sp_executesql.
I'm assuming that you'll have more than one...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 10, 2010 at 8:37 pm

#1165308

Viewing 15 posts - 38,446 through 38,460 (of 59,072 total)