Implications of dynamically created table with large number of users

Question

question

Ian Roke asked Oct 19, '10

Implications of dynamically created table with large number of users

I am creating the data for a report with 19 sets of entities that require pivotting by day to produce a result that I can sent to an ASP page. A start date and an end date is given so as you can imagine the columns need generating dynamically. So far I have this: DECLARE @ColumnSchema varchar(max) DECLARE @StartDate datetime DECLARE @EndDate datetime SET @StartDate = '2010-10-01' SET @EndDate = '2010-10-16' SELECT @ColumnSchema = COALESCE(@ColumnSchema + ', ' + QUOTENAME(CONVERT(varchar(10), ReportDate, 120)) + ' varchar(max)', '' + QUOTENAME(CONVERT(varchar(10), ReportDate, 120)) + ' varchar(max)') FROM AgentDetailETLTest WHERE ReportDate >= @StartDate AND ReportDate < @EndDate + 1 ORDER BY ReportDate SET @QueryText = 'CREATE TABLE AgentDetailTempResult ([EntityName] varchar(max), ' + @ColumnSchema + ')' EXEC(@QueryText) This then generates a table using the same parameters that I am going to pivot my data with however I would prefer it to be a table variable or a temporary table so that it is handled in memory otherwise I am going to have problems when a large number of people start using the script with the same table being dropped and recreated all the time. I then use the following code 19 times to create the different entity lines: SET @QueryText = 'SELECT ''Percentage Free'' , ' + @ColumnNames + ' FROM ( SELECT ReportDate , CONVERT(decimal(3, 2), PercentageFree) [PercentageFree] FROM AgentDetailETLTest WHERE ReportDate >= ''' + CONVERT(varchar(10), @StartDate, 120) + ''' AND ReportDate sql-server-2005 t-sql

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ · Oct 19, 2010 at 01:39 AM

Ian, if this is a lot of data then it will be written to disk whether you select a table variable or a temp table. The in-memory thing is a bit of a myth...

1 ·

Ian Roke · Oct 19, 2010 at 01:48 AM

Yes that is true however SQL Server manages the name. If I have a thousand users all creating a table with that DDL and the same table name I will run into problems.

0 ·

Answer 1 · 2010-10-19T01:54:28Z

Matt Whitfield answered Oct 19, '10

I can see why you are using a dynamic pivot - but I can't see why you are putting this information into a table... Is there anything preventing you from using the meta-data available with SqlDataReader to return your ASP page report without using an intermediate table (i.e. instead of `INSERT AgentDetailTempResult EXEC(@QueryText)` just `EXEC(@QueryText)` and interpret the result set, rather than reading from a table)?

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke · Oct 19, 2010 at 01:58 AM

I am having to do this 19 times for each different piece of information. That pivot script is just an example of one line in my outputted table.

0 ·

Matt Whitfield ♦♦ · Oct 19, 2010 at 02:01 AM

Right, so what's wrong with having 19 result sets? No, it's not hugely efficient - but it sounds like you're working with a data dictionary that ties your hands anyway?

0 ·

Matt Whitfield ♦♦ · Oct 19, 2010 at 02:03 AM

Another possibility would be to convert this to a CLR table function or stored proc - and build the results you want in memory and then return a single result set to the ASP page

0 ·

Ian Roke · Oct 19, 2010 at 02:27 AM

I intend it to be a stored procedure that I can pass dates and agent details too and it returns a table with completed data in. Entity name then a column for each date with the values under it.

0 ·

Matt Whitfield ♦♦ · Oct 19, 2010 at 03:29 AM

Right, well go for the CLR procedure then. It's a bit difficult to say, because you haven't said what the schema of AgentDetailETLTest is, and therefore why querying from it 19 times returns different data... Unless the 'AgentDetailETLTest' bit changes each of the 19 times, and you're querying from 19 different tables? In which case you could just do the same query with a UNION ALL ?

0 ·

Answer 2 · 2010-10-19T01:56:16Z

Cyborg answered Oct 19, '10

If this is for a report why cant you try using cross tab?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke · Oct 19, 2010 at 02:20 AM

I need the data pivotted but I am not using it in the one report in one place it is more like a dashboard report I am creating where I send all the data at once.

0 ·

question