I am searching for information on paging large datasets, and have found
some that involve creating temporary tables in the database.
Before I head off and implement something, I have a number of issues
I'd like to bounce around here.
1. An example I found on MSDN involves creating a temporary table,
copying relevant columns to the row in the temp table. Why do
this, rather add the source tables primary keys into the temp table,
and do a join? Example; browsing Products Catalog which is
categorised into hierarchies. The MSDN version would have a temp
table created with a incrementing field which is used for the paging,
and then a number of fields are also copied from the products table to
the temp table - my question is why not simply copy the product primary
key into the temp table, and then join?
2. In real life, do people allow each user to create their own
temporary tables? If I have 1000 concurrent users, all wishing to
perform a page-based browse, I would be creating 1000 new temporary
tables. Do people consider default temp tables, that is, creating
a default temporary table for browsing each category in the products
table, for example?
3. Do you have any advice/tips for this type of problem?
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too). I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong). So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet: SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ? well thanks for taking the time to read this, any help woulb be appreciated.
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
I am trying to implement custom paging. I want to get a subset from my Threads and Post tables by userID. But I can't make the stored proc work. Could somebody have a look at this and tell me what I am doing wrong or if there is a better way of doing this?
ALTER PROCEDURE [dbo].[syl_ThreadPost_GetSubsetSortedByUserID2]
@UserID uniqueidentifier,
@sortExpression nvarchar(64),
@startRowIndex int,
@maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
BEGIN TRY
IF LEN(@sortExpression) = 0
SET @sortExpression = 'PostID'
-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment
SET @startRowIndex = @startRowIndex + 1
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate]
FROM
(SELECT t.[ThreadName],
p.PostID,
p.[PostTypeID],
p.[LanguageID],
p.[PostAccessID],
p.[UserID],
p.[ThreadID],
p.[PostParentID],
p.[VoteSummaryID],
p.[Subject],
p.[Body],
p.[PostAuthor],
p.[PostDate],
p.[IsApproved],
p.[TotalViews],
p.[FormattedBody],
p.[IPAddress],
p.[PostCount],
p.[ArticleCount],
p.[TrackbackCount],
p.[IsSticky],
p.[StickyDate],
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') AS RowNum
FROM syl_Threads t RIGHT OUTER JOIN syl_Posts p
ON t.[ThreadID] = p.[ThreadID])
WHERE t.[UserID] = ' + CONVERT(nvarchar(16), @UserID) + ' )
AS syl_ThreadPostInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(16), @startRowIndex) + ' AND (' + CONVERT(nvarchar(16), @startRowIndex) + ' + ' + CONVERT(nvarchar(16), @maximumRows) + ') - 1'
-- Execute the SQL query
EXEC sp_executesql @sql
RETURN
END TRY
BEGIN CATCH
--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
Can you create a temporary table using an ad-hoc query?
What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?
I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.
INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited) SELECT * from cr_EquipmentData
SELECT * from @results
When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)
I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'
I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.
I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.
I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.
The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.
Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?
Hi, I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output
Select temp2.Value/temp1.Value as val from ( ( select count(*)as Value from [Master] m inner join [Spares]s on s.SId=m.SID where m.Valid_From between '2007-06-01' and '2007-06-30' )temp1 Union ( select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value from [Order] h inner join [Spares] s on s.Number = s.Number where h.Valid_Date between '2007-06-01' and '2007-06-30' ))temp2 as t
Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows. Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.
Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?
If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP
Hi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,
I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.
My scenario is like this:
I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.
I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.
I have used this stored procedure in the dataset created and getting this error:
Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.
Does anybody encountered this and have a resolution.
The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?
I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I canīt use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2) + LEFT(D.Fecha,8) + D.Valor as Dato FROM ReportesPlantilla RP LEFT JOIN ##Resultados D ON RP.Planta = D.Planta AND RP.Etapa = D.Etapa AND RP.GrupoEquipo = D.GrupoEquipo AND RP.Equipo = D.Equipo AND RP.Concepto = D.Concepto AND D.Fecha BETWEEN @FechaIni AND @FechaFin
Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?
I need to use a temporary table within a stored procedure (which is a transaction), should I use local temporary table or global temporary table?
If I use a temporary table in my transaction, do I destroy the temporary table at the end of the transaction, or just leave it, and let the sytem clean it up? If I destroy the temporary table at the of the transaction, what happens if another user session is accessing the temporary at the very moment?
In Access 97/2000, a local table can be created in the program database, while the data is stored in a linked database. This is useful for me to do a sort-of iterative drill-down on linked tables, e.g. filter table1 into table2, then further filter table2 into table3 and so on.....
If I do this I leave a trail that I can navigate in reverse (back up a step). This is very important in my app.
So now my problem is....I'm trying to duplicate this in an ADP file using only ADO 2.5. I have no local tables, only server tables. If I create a temporary table, it only exists until the procedure ends.
Is there any way to create temporary tables that are specific to a user session and do not automatically delete themselves, or is there a better way to do this in SQL7?
on sql-server-performance.com i read : Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures. For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
I have created a local temporary table (#Temp) and placed data in it.When I do:SELECT * #TempThe result is a set of rows with no columns.The SELECT statement is in the same procedure as the creation of #Temp; so Idon't believe this is a scoping issue. Does that make sense?Thank you in advance,Eric
I'm going through some stored procedures that have been written previously. In every sp, they are checking for existance of temporary table in sysobjects.
So, i made a simple script and checked if #TempTable has a entry in sysobjects. But i did not find it.
On internet many ppl are talking about checking if temp table already exists. So my question is...
1. What are circumstances under which temporary table doesn't get deleted automatically. 2. Does temporary table have a entry in sysobjects?
I have been trying to work with a data flow task that uses temporary tables on the remote DB.
I have seen Jamie Thompson's SSIS Temporary table guide, available here: http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx, and successuflly got his download package working against my AdventureWorksDB.
I cannot get a temporary table implementation working in my SSIS package despite following JT instructions.
Current status:
Marked the OLEDB connection as retainsameconnection = true Created a seperate execute sql task in control flow that creates the temporary table '#BUSessions' A data flow task follows the execute sql task, the ole db source is marked a delayvalidation = true
At this point, I try to enter the query using the interface, validation kicks in and says this object is invalid. If I try to use the properties window to set sqlCommand and manually enter the query then an error icon appears on the Ole Db Source.
Can anyone help me with this?!?! I'd really appreciate it! The query provided to me (for the remote DB) uses temporary tables and I have little say in this...
p.s. - I have also ensured I manually create the global temporary table with same name before starting this process.
I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.
In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."
Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?
Does SSIS (other than, maybe, via the Execute SQL task) support the idea of temporary tables? (I want to make a backup of production data (in temproary tables), truncate the production tables, and populate them with new data. If an error happens in the process, I'd copy the temporary backup tables back into the production tables. When the process has ended the temporary tables should "vanish")
Hello All,I am trying to use a SQL Server 2005 Temporary table with a hash(pound sign) in front e.g. '#OrderTmp'. . ASP.NET is objecting with the message viz "Incorrect syntax near '#OrderTmp' ". I guess it is objecting to the hash.Has anyone any ideas about how to specify this temporary table correctly.Thanks for your help,Peppa
I understand that when you create a Temporary table with '#tablename', the table is only visible for the current session. I have an application which connects to sql server with the same sql account details.The reason for explaining this is that I can have multiple concurrent sessions for the same user. Each of these sessions will run a report by calling a stored procedure which would create a temporary table of the same name. I found that the response times vary when running the stored procedure concurrently with the same user. From viewing the activity from sql server, I noticed some blocking occurs. Is there any way to improve on this set up
