Organize Big Solution Tree

Jul 17, 2007

Hello,

I need some advice regarding organizing packages.

I am in starting phase of a data conversion project. As my ETL target, I have to produce around 70 flat files. I am creating one package for each target file. Some target files may be a result of 2-3 packages.
I have some common packages that get called from many other packages.


Before my solution tree grows into unmanageable state, I would like packages to get organized based on their target and purpose of the package.
The ideal solution to this problem would be to organize packages in a folder structure but I couldn€™t see any option to create folders in a solution.
I also noticed that we can€™t easily put package files next to each other even after renaming packages.



I was wondering is there a better or any way to organize packages in solution tree?


Kapil

View 1 Replies


ADVERTISEMENT

SQL 2012 :: Sort Tree Members In Right (tree) Structure?

Apr 6, 2015

I got assignment, how to make it appear in the right order .

/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/

[code]....

View 2 Replies View Related

How Do 'you' Organize Reporting

Jan 9, 2007

Hello,

This is more a informational question. Until now we had over 50 reports ready in our system, we are almost ready to go into production and are starting to link all reports with eachohter. The first problem was that we had them all nicely grouped in different projects so that we could publish in a logical folder-tree. Problem is that we can't jump to reports that are nog in the same folder. Going by url changes the layout and parts of the report manager are gone.

We then started to have a look at Sharepoint but i can't find books on the topic SharePoint & SSRS that describes the possible options. I've seen quite some documents and ways to publish reports but until now we prefer to mail the periodical reports to the users with data-driven subscriptions.

So at this moment we don't know quite wha direction to take. Putting all reports in one folder would solve many linking problems but makes it quite messy for the users.... And the linking issue is a must. As we now will start to create our dashboards with SSRS too.

So, any suggestions on what road to take or what publications to read are welcome.



Kind Regards

Harry

View 1 Replies View Related

Organize Data

Feb 12, 2008



Hi,
I have data like this in Excel table:
ID GameScore1 GameScore2 GameScore3 ClassScore1 ClassScore2 Classscore3

10 10 20 27 5 35 23

I have to arrange like this in SQLServer table:
ID TestRecord GameScore ClassScore

10 1 10 5
10 2 20 35

10 3 27 23


Test Record 1 for score1, 2 for score2, 3 for Score3.
Which way to organize this data in SSIS?

Thanks in advance

View 10 Replies View Related

Organize Into Tables

Feb 6, 2008

Hi,
I have information like this:
Apllicant information: Firstname, Lastname, Address, Phone, SSN, county, workphone..
Each applican can earn 3 certificates:






1st Certificate

2nd certificate

3rd certificate


Status of certification

Status of certification

Status of certification


Status of dues

Status of dues

Status of dues









Certification#

Certification#

Certification#


Validation date

Validation date

Validation date


Renewal date

Renewal date

Renewal date


Effective date

Effective date

Effective date


Date of cert # changed > means earned the next(second or third certificate).

These don€™t have this column information

These don€™t have this column information
Some common information for all 3 certificates like NOTE,€¦

How to organize the above information in SQLServer tables. how to apply the normalization . I can store all the contact information in one table and put the SSN as primary key. How to organize the certificates into tables? I have to made 3 tables for 3 certificates and linked to contact information table?

Thanks in advance

View 13 Replies View Related

Best Way To Organize Multiple Databases?

Jan 21, 2008

I'm very new to SQL, so you'll have to excuse the nubishness of this question. I've got my trusty O'Reilly book here!

We're using Microsoft SQL Server 2005 on our web server. I'm currently working on a project to get a few MS Access Databases available on the web page.

The web part is actually working fine, with the DBs exported into SQL for performance reasons. (From what I've read pulling data from the .mdb file could cause me problems in the future, so I'm tossing it all onto the SQL server from the start.)

Before I get into the project full-tilt, though, I'm curious about how best to organize all the data?



I'm using the Upsizing Wizard in Access for the conversions, and every new DB gets added under Databases. If this gets approved I may be looking at well over a hundred DBs which will eventually be converted and made web accessible.

I can keep everything neat and tidy on the web server by just tossing things in a few directories, but the menu on the SQL Server Management Studio is going to become a mess really quickly if I let it just keep adding to +Databases.

Is there a way to sort everything into a series of folder/groups, like how System Databases has it?

Thanks in advance,

-Lamune

View 2 Replies View Related

Organize DFT Components In Designer

Jul 4, 2007

Hello,

I have DFTs with around 50 components and future ETLs will have 100+ components in DFTs.
I was wondering that is there anyway to group components like we have #region in C#.

Thanks,

Kapil

View 4 Replies View Related

Articles In More Than One Category - How To Organize Tables?

Jun 10, 2005

Greetings,I have one table, named Article, and one table name Category.The problem is, one Article could be in just one or in several categories.What is the best way to connect data between Article and Category according to fast search performance?I have several ideas:1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table 2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.What is recommended for solving problems like this?

View 7 Replies View Related

Organize And View Datasets Using Toad

Jul 23, 2005

Hello,i want to use toad to work with sql-Server for my business. But i cantget rid of it, after extracting the packets i find the toad ss.exe.I dont know how to connect to my database, as i never worked beforewith the odbc-database driver. I think it must be about the manager"start -> controls -> odbc...".All i want is to view the datasets in my db, but i dont want to writethe nesassery querys, like in mysql-admin for mysql databases.Is toad the right applikation or should i use an other manager?plz help me, thanks - xunling

View 1 Replies View Related

SQL Security :: How To Organize And Secure Database Users

Apr 21, 2015

Is there any way in which I could distinguish a group of Database users from the other users. Say for e.g. store the Database users hierarchically, etc. Adding a Prefix/Suffix to the user name as a distinguish-er will not work in my case. I want to restrict the deletion of these Database users. Even the login with sysadmin or serveradmin rights should not be able to DROP those Database users.

View 11 Replies View Related

SQL Server 2012 :: Dynamic Pivot Statement To Calculate And Organize Columns

Mar 11, 2015

How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:

CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME

[Code] ...

Below is Our Sample Table Data.

Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015

How to write Pivot Satement to get Oupt like below:

1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12

1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4

1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17

View 5 Replies View Related

SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!

Nov 30, 2006

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

-----------------------------------

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy








The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

-----------------------------------

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

--------------------------

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.



If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.



This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:mydatamyData.mdf



Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:mydatamyData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.



To Test ----------

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

View 17 Replies View Related

Tree

May 20, 2004

Dear all,

I would like to know how to create a single level hierachy structure in SQL.
Example, I have a single parent record in table A that may later on spawn one or child record in table B that relates back to the parent. It only needs a single level.
that means, one to many.

thanks and rgds.
Loke HC

View 1 Replies View Related

Tree

Jan 2, 2008

I have two tables.

table1 has 3 fields a , b and c. field "a" is a primary key.
table2 has 2 fields x and y. Fields x and y are nothing but the value of "a". Also, y is the child of x.
Therefore, x and y can never have same value. It means value of "a" either be child or parent.
But there is possibility that parent has no child.

Now, i wanted to write Select/Insert query for parent, b , c and child.

This tree is no a binary tree but N-Ary Tree.

Thanks,
sha


View 9 Replies View Related

Tree-walking In T-SQL

May 18, 2007

Hi,
 I'm having a major brain-failure moment here.
 Using T-SQL I want to be able to get all of the leaf nodes (e.g. nodes at the furthest end of a tree from the root) in a hierarchical relationship where the table structure is such NodeID|ParentID|NodeName.
 Basically if I had the following tree structure:
Root
    Child1
        Grandchild1
    Child2
        Grandchild2
 
I want to get all of the Grandchild nodes.  Number of levels will vary and I haven't got any kind of HasChilds column.
 I know this is possible because I remember having done it on a course years ago but I can't for the life of me figure it out on this sunny Friday afternoon.  I know it's going to involve either recursion, a while loop or cursors but my mind is currently jelly.  Can anyone help?

View 8 Replies View Related

How To Get All The Children In The MLM Tree..

Sep 10, 2007

hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I  have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because  in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...

View 1 Replies View Related

Need Help For Tree Functionality

Jun 2, 2008

HI,
 I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi

View 5 Replies View Related

Help Me Please, About Operator Tree

Jan 3, 2006

Consider the following SQL query:
SELECT ENAME,SAL
FROM EMP,ASG,PAY,PROJ
WHERE EMP.ENO=ASG.ENO
AND PAY.TITLE=EMP.TITLE
AND ASG.PNO=PROJ.PNO
AND ASG.DUR=48 AND BUDGET>200000
Give the possible operator trees:right-deep,left-deep and bush
Tank you very much!

View 2 Replies View Related

Tree Structures In SQL

Jun 11, 2006

Hi,

i'm writing a app in c# and have to store Trees in a Database.

I'm working with Datasets for the exchange between the DB and the App.

The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.

But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:

Not Possible:

create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)


Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action

Greetz

View 1 Replies View Related

Tree Structures...

Oct 24, 2006

Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?

I'm desperate, reli need to work out how too do this.....

View 14 Replies View Related

Tree Selection

Jun 13, 2008

SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID


Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.

Thanks for the help!!!!!

View 3 Replies View Related

Tree Selection

Jun 16, 2008

Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:

NetworkId Full Name Sec Class Desc Sec Type Desc SecValue
tte Test Scenario Accounting Budget Center 142- ?



SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type

------------------

inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID

_____________________________________________________________________

What I need is the Description that are located in 4 other different tables that matches the Security Value from my first querie.

The result should look like this...

Network Id full Name Sec Class Desc Sec Type Desc Security Value
tst , Test Example , Accounting ,Budget Center , 142-Accountig dept


Thank you very much,

View 4 Replies View Related

Organization Tree

Oct 14, 2005

Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks

View 2 Replies View Related

Tree Table

Sep 12, 2006

Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy

View 4 Replies View Related

Tree View

Mar 5, 2008

Hi:

Is there a resource or an object in Reporting Services that I could make a tree view in a report file .rdl and see the result on it?

If not, is there a software of MS that a could make a component for it, and connect this component in the report file .rdl ?

View 6 Replies View Related

PLZ HELP ME WITH THE DECISION TREE!!

Apr 16, 2007

I'm having this problem.....



I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?



Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)



my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.



wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?



i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......



Thank you again.........

View 1 Replies View Related

Rescursive Tree

Feb 28, 2008



Alright what im trying to do is build a classic tree, i have id's like this set up in my table referencing itself in sql 2000


cmponent_prt_no parent_part_no
--------------- --------------
112837A2A L115100-1
114379A1A L115100-1
115623A1A L115100-1
203604A L115100-1
203790A L115100-1
203791A L115100-1
115623A 115623A1A
M010137 115623A1A
115623A 115623A1A
20766CR 115623A1A
DRAWINGS 20766CR
M010137 20766CR

i have tried 3 different solutions all coming to a crash at some point, if ANYONE can help me out i would appreciate it, i know the logic will be recursive, i just dont know how to implement

View 8 Replies View Related

Convert A Table Into Tree

Aug 21, 2007

Hi..I have a table  register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.
suppose table is like this.  username   parentid        downline
                                         B                A                left
                                         C               A                 right
                                         D                B               left
                                         E                B               right....
I have to also determine the level in the tree....please help...

View 1 Replies View Related

Does Anyone Know How To Traverse A Simple Tree Using SQL?

Dec 6, 2000

Hello all!

I have a table defined as:

create table OrgTree (parent int, child int)

OrgTree has the following data:

1,2
1,3
2,4
2,5
3,6
4,7
4,8
8,9

Is there a SQL stmt that will return, given a particlar parent, all of the children (and children's children, etc.) of that parent?

So, parent value 1 returns 2,3,4,5,6,7,8,9
parent value 2 returns 4,5,7,8,9
parent value 3 returns 6
etc.

Thanks in advance for your help!!

Palmer F

View 1 Replies View Related

Database Tree Says 'No Items' ???

Oct 30, 2001

I just started having this problem. When I log into SQL2000 enterprise manager and click on my database, the main directory tree shows up. When I click on the Database tree to display all of the different databases on the SQL server, the text 'No Items' shows up. I can not get to any databases, but I am connected to the SQL server. Does anyone have a solution for this problem?????

I have tried re-inatalling and all of the service packs.

Thanks !!

View 3 Replies View Related

Family Tree Branches

Jul 26, 2004

Okie, this one has me a little stumped so bear with me an I will explain as best I can....

I have a family tree database...

The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).

Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.

If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.

I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.

View 8 Replies View Related

Hiearchical List Of Tree

Jul 8, 2007

Hi,

table(id,parent,level, name)
parent ...fk referenced table(id))
level ... depth of tree (root has 0)

How SQL for hiearchical list:

name 1
name 1.1
name 1.2
name 2.
name 2.1.
name 2.1.1
name 2.1.2
etc.

???
thanks for your help

View 12 Replies View Related

Example Code For User Tree

Sep 17, 2007

One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks
GO
DROP TABLE LI_Users
GO
DECLARE @d1DATETIME
, @d2DATETIME
, @d3DATETIME
, @d4DATETIME
, @d5DATETIME
, @d6DATETIME
, @d7DATETIME
, @d8DATETIME

SELECT @d1 = GetDate()

CREATE TABLE LI_Users (
uidINT
PRIMARY KEY (uid)
)

SELECT @d2 = GetDate()

CREATE TABLE LI_UserLinks (
uid_fromINT
CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from)
REFERENCES LI_Users (uid)
, uid_toINT
CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to)
REFERENCES LI_Users (uid)
CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to)
)

ALTER TABLE LI_Userlinks
ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)

SELECT @d3 = GetDate()

INSERT INTO LI_Users (
uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z5

SELECT @d4 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 0 AND 99
AND uid != 100 * uid + n0 + 10 * n1

SELECT @d5 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 100 AND 9999

SELECT @d6 = GetDate()

SELECT u.uid, r1.uid_to, r2.uid_to
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
WHERE 1 = u.uid

SELECT @d7 = GetDate()

SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to)
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)

SELECT @d8 = GetDate()

SELECT
DateDiff(ms, @d1, @d2)
, DateDiff(ms, @d2, @d3)
, DateDiff(ms, @d3, @d4)
, DateDiff(ms, @d4, @d5)
, DateDiff(ms, @d5, @d6)
, DateDiff(ms, @d6, @d7)
, DateDiff(ms, @d7, @d8)
, DateDiff(ms, @d1, @d8)-PatP

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved