Creating tables with dynamic columns in SP

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating tables with dynamic columns in SP

Author

Topic

Jofi
Starting Member

1 Post

Posted - 2009-07-14 : 11:06:09

Hi!

I have three tables related to each other as follows:

tblObject > Stores general information about the object
tblAttributes > Stores different types of attributes that can be appended to the object
tblValues > Stores the values of the attributes being append to the different objects

The design for this (simple example):

tblObject
- ID
- Name
- Owner

tblAttributes
- ID
- Name

tblValues
- ObjectID
- AttributeID
- Value

What I also done is to create a stored procedure which will return a table of the objects with the attributes appended as columns containing the specific value for the object for that attribute. Depending on which objects you filter out this table can contain different columns.

So far so good, I’ve created the sp and it works quite well. Roughly, the SP works like this:

CREATE TABLE #Temp (ID, Name, Owner)

INSERT INTO #Temp (ID, Name, Owner) SELECT ID, Name, Owner FROM tblObject WHERE …

Creates a cursor for “SELECT ID FROM #Temp” and loops over all the inserted objects and creates a @ObjectID variable with the ID of the object

Inside this object cursor I have created another cursor for “SELECT ObjectID, AttributeID, Value FROM tblValues WHERE ObjectID = @ObjectID” which loops over all the extra values for the current object

Inside the value cursor I ALTER TABLE #Temp with new columns based on the attribute name and UPDATE #Temp with the value for the matching combination of AttributeID and ObjectID

As mentioned, this is a very rough overview but perhaps you can get a clue about what I’m achieving here.

The problem with this SP is that it becomes really slow when the object table starts to grow to many thousands of objects. I’ve localized the big bottle-neck to be in the UPDATES being executed at the last step in the list above. If I run both cursors without doing any UPDATES it takes about 2 sec. With UPDATES being made it takes about 2 minutes if I have around 5000 objects.

Any tips or tricks in speeding this SP up would be greatly appreciated!

Thanks!

Best regards,
Johan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-14 : 11:32:25

Ditch the cursors entirely.

do a seach for DYNAMIC CROSS TAB.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-14 : 11:34:08

Start here:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

sounds like you are wanting to PIVOT based on an unknown number of key value pairs?

If so give some example data and you'll get a bunch of solutions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources