question

Ian Roke avatar image
Ian Roke asked

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-2005t-sql
2 comments
10 |1200

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

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 Like 1 ·
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 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
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 comments
10 |1200

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

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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
Cyborg avatar image
Cyborg answered
If this is for a report why cant you try using cross tab?
1 comment
10 |1200

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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