Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Split Tables and Keep ID

    I have a large table that I'm planning on splitting out into 5 smaller ones. What I need to do is maintain some central repository for auto-numbering new records to make sure that no 2 records in different tables have the same unique ID. Thanks in advance!
    BillS

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Can you explain the nature of the table, and the reason for splitting into five other tables? I ask because normally it's a bad practice to have a surrogate key value that depends on the the value of a key in another table.

    Bill

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Sure. My table has ~190,000 rows and most of my users access the data via some ASP pages I've created. The rows are basically properties that I'm going to export and then import into new smaller tables organized by county. For accounting purposes we've been referring to a given property by its ID number and therefore they need to stay unique.

    I started down this whole path because the table has gotten very large and perfomance is really starting to suffer. We're running SQL 2K on and SBS 2K3 box and it's really starting to choke. Thanks.
    BillS

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    You could have 190 million rows and not have performance issues. Without seeing the DDL of your table, I suspect an index or two would is all that you need.

    In your select statement, look at the WHERE clause and see what columns you use to filter your resultset. Those column(s) are a good candidate for an index, and will make a HUGE difference in performance. If you want to filter by county, and a county column, put an index on it, and include it in your WHERE clause.

    I'm making some assumptions. If you post your DDL and the SQL statement you are using to extract your data, I can help you more.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Please read the sticky at the top of the forum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks, I saw that before. How do I post the DDL? I'm not sure how to run a query that show that. Thanks.
    BillS

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Go to Enterprise Manager.

    Open the database folder to display all of the tables.

    Right Click on the table you want.

    Choose Menu options All Tasks>Generate SQL Scripts

    Look at the dialog, there are thre tabs. Make sure you pick all the correct options (indexes, keys, ect)

    Click Preview.

    Copy and paste the code.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Here's my DDL. Should I just create a simple index on the handful of fields I need to index?
    Code:
    CREATE TABLE [dbo].[Tax Roll Property List] (
    	[ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[OwnerID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Owner] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Owner Address 1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Owner Address 2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Owner Address 3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[State] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Zip] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Zip+4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Owner 2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Lease ID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[RRC] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Lease] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[LEASEXREF] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Operator] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OPERXREF] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Operator Address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Operator Telephone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[County] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Field] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Abstract] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Block] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Section] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Legal Description 1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Legal Description 2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Legal Description 3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Year] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ACCT1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ACCT2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[TPF] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[DIP] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ORR] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Division Order Number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Gross Acres] [numeric](18, 0) NULL ,
    	[Net Acres] [numeric](18, 0) NULL ,
    	[Plat] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Value] [money] NULL ,
    	[Flag] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Offer] [money] NULL ,
    	[Accepted] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Purchased] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Purchase Price] [money] NULL ,
    	[Comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Title Comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Mineral Deed Recorded] [smalldatetime] NULL ,
    	[Division Order Received] [smalldatetime] NULL ,
    	[Tax Notice Received] [smalldatetime] NULL 
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Tax Roll Property List] WITH NOCHECK ADD 
    	CONSTRAINT [PK_Tax Roll Property List] PRIMARY KEY  CLUSTERED 
    	(
    		[ID]
    	)  ON [PRIMARY] 
    GO
    
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    BillS

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Yup

    I also ask how do you generate your DML inserts - I use longhand or excel - is there a faster way ?

    Back on Topic Bill - Is your proposed new model normalized / denormalized in any way ?:-)

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by BillSinc
    I started down this whole path because the table has gotten very large and perfomance is really starting to suffer. We're running SQL 2K on and SBS 2K3 box and it's really starting to choke. Thanks.

    OK, let's back off of the track of why you want to split the data in to 5 tables.


    You are saying that when you generated all of the indexes on the third tab, that there are no indexes?

    Show us the data access from the asp where it is slow...ya know, the SELECT Statement....I can assume that the identity column is not how you access the data. This is a poor choice for a primary key.

    EDIT: But it will make a very good unique index when you want to peform your Updates and Deletes (if you do them), since you will have it (or should have) from the original SELECT.

    EDIT2: OK, first of all, everything is nvarchar(255). Did this whole thing start in access? What a mess. Run this in Query Analyzer. It check a table in Northwind called Employee.

    Code:
    USE Northwind
    GO
    
    DECLARE @sql varchar(8000)
    SELECT @sql = CASE WHEN DATA_TYPE IN ('varchar', 'varbinary', 'text', 'image', 'nvarchar', 'ntext' ) 
    		   THEN COALESCE(@sql + ', ','') + 'MAX(DATALENGTH('+COLUMN_NAME+')) AS MAX_LEN_' + COLUMN_NAME 
    		   ELSE COALESCE(@sql + ', ','') + 'MAX(LEN('+COLUMN_NAME+')) AS MAX_LEN_' + COLUMN_NAME
    	      END 
    FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'Employees'
    ORDER BY ORDINAL_POSITION
    
    SELECT @sql = 'SELECT ' + @sql + ' FROM Employees' 
    
    EXEC(@sql)
    GO

    Just change it to your table name and make sure your in the right database.

    You should really resize this table.
    Last edited by Brett Kaiser; 09-27-05 at 14:04.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    My connection string
    Code:
    <% 
    xDb_Conn_Str = "Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=****;Initial Catalog=CBDatabaseSQL;Data Source=SERVERNAME"
    %>
    BillS

  12. #12
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    This is how I'm connecting. I using an app called ASPMaker to generate my pages.
    Code:
    '-------------------------------------------------------------------------------
    ' Function LoadData
    ' - Load Data based on Key Value
    ' - Variables setup: field variables
    
    Function LoadData()
    	Dim sSql, rs, sWhere, sGroupBy, sHaving, sOrderBy
    	sSql = "SELECT * FROM [Tax Roll Property List]"
    	sWhere = ""
    	sGroupBy = ""
    	sHaving = ""
    	sOrderBy = ""
    	If sWhere <> "" Then sWhere = sWhere & " AND "
    	sWhere = sWhere & "([ID] = " & AdjustSql(x_ID) & ")"
    	sSql = sSql & " WHERE " & sWhere
    	If sGroupBy <> "" Then
    		sSql = sSql & " GROUP BY " & sGroupBy
    	End If	
    	If sHaving <> "" Then
    		sSql = sSql & " HAVING " & sHaving
    	End If	
    	If sOrderBy <> "" Then
    		sSql = sSql & " ORDER BY " & sOrderBy
    	End If	
    	Set rs = Server.CreateObject("ADODB.Recordset")
    	rs.Open sSql, conn
    	If rs.Eof Then
    		LoadData = False
    	Else
    		LoadData = True
    		rs.MoveFirst
    
    		' Get the field contents
    		x_ID = rs("ID")
    		x_OwnerID = rs("OwnerID")
    		x_RRC = rs("RRC")
    		x_LEASEXREF = rs("LEASEXREF")
    		x_OPERXREF = rs("OPERXREF")
    		x_TPF = rs("TPF")
    		x_DIP = rs("DIP")
    		x_ORR = rs("ORR")
    		x_Owner = rs("Owner")
    		x_Owner_Address_1 = rs("Owner Address 1")
    		x_Owner_Address_2 = rs("Owner Address 2")
    		x_Owner_Address_3 = rs("Owner Address 3")
    		x_City = rs("City")
    		x_State = rs("State")
    		x_Zip = rs("Zip")
    		x_Zip2B4 = rs("Zip+4")
    		x_Owner_2 = rs("Owner 2")
    		x_Lease_ID = rs("Lease ID")
    		x_Lease = rs("Lease")
    		x_Operator = rs("Operator")
    		x_Operator_Address = rs("Operator Address")
    		x_Operator_Telephone = rs("Operator Telephone")
    		x_County = rs("County")
    		x_Field = rs("Field")
    		x_Abstract = rs("Abstract")
    		x_Block = rs("Block")
    		x_Section = rs("Section")
    		x_Legal_Description_1 = rs("Legal Description 1")
    		x_Legal_Description_2 = rs("Legal Description 2")
    		x_Legal_Description_3 = rs("Legal Description 3")
    		x_Year = rs("Year")
    		x_ACCT1 = rs("ACCT1")
    		x_Type = rs("Type")
    		x_ACCT2 = rs("ACCT2")
    		x_Division_Order_Number = rs("Division Order Number")
    		x_Gross_Acres = rs("Gross Acres")
    		x_Net_Acres = rs("Net Acres")
    		x_Plat = rs("Plat")
    		x_Value = rs("Value")
    		x_Flag = rs("Flag")
    		x_Offer = rs("Offer")
    		x_Accepted = rs("Accepted")
    		x_Purchased = rs("Purchased")
    		x_Purchase_Price = rs("Purchase Price")
    		x_Comments = rs("Comments")
    		x_Title_Comments = rs("Title Comments")
    		x_Mineral_Deed_Recorded = rs("Mineral Deed Recorded")
    		x_Division_Order_Received = rs("Division Order Received")
    		x_Tax_Notice_Received = rs("Tax Notice Received")
    	End If
    	rs.Close
    	Set rs = Nothing
    End Function
    %>
    BillS

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An identity column is a poor choice for a primary key? Huh?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Sep 2005
    Posts
    161
    Can you post a sample string from your sSQL variable?

    Your're likely going to need an index in your WHERE column, your GROUP BY column, and your ORDER BY column. Without any further optimizations, this should get you a sub-second response time.

    Bill

  15. #15
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    An identity column is a poor choice for a primary key? Huh?
    Since the primary key is usually your clustered index, this could create "hot spots" in your database if the new records are the ones most likely to be updated. For example, if you have a table for orders, the new orders are going to have the most activity. This can be solved by creating an clustered index on a different column. Generally speaking, identity columns are fine for a primary key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •