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?
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
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'
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?
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?
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
Is there a way to put a variable name into the name of a temporary table. For example, I would like to create a table called 'test@passedvariable', or test2 assuming that @passedvariable is 2. How would I go about doing this. Any help would be greatly appreciated.
I've created one stored procedure which uses #temp, temporary table. When I schedule it to generated output in CSV file using DTS, it flags me an error as , 'Context: Error Calling GetColumnInfo. Your Provider dows not support all the interfaces/methods required by DTS'
Can anyone tell me why this error occurs? Or is it true that one cannot use Temporary tables in generating output in CSV or text file using DTS ? I am using MS-SQL Server 2000
I've created one stored procedure which uses #temp, temporary table. When I schedule it to generated output in CSV file using DTS, it flags me an error as , 'Context: Error Calling GetColumnInfo. Your Provider dows not support all the interfaces/methods required by DTS'
Can anyone tell me why this error occurs? Or is it true that one cannot use Temporary tables in generating output in CSV or text file using DTS ? I am using MS-SQL Server 2000
In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.
My SQL statement is using 2 databases as follows: 1.PerformanceDeficiencyNotice 2.HRDataWarehouse
Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.
The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.
And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.
And now when I am trying to save the view I am getting the error as below “Incorrect syntax near the keyword ‘INTO’�. Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.
Please suggest how to solve this problem. Any help is greatly appreciated.
Thank You
MY SQL Statement is as follows:
SELECT pdn.transactionid, pdn.employeenbr, pdn.lastname, pdn.firstname, pdn.processlevel, pl.facilityname as processlevelname, pdn.department, pdn.jobcode, pdn.title, pdn.supemployeenbr, pdn.managername, pdn.timeframe as pdn_timeframe, pdn.actualeffectivedate as pdn_startdate, /*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/ /*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/ case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then case pdn.timeframe when '30' then dateadd(month,1,pdn.actualeffectivedate) when '60' then dateadd(month,2,pdn.actualeffectivedate) when '90' then dateadd(month,3,pdn.actualeffectivedate) else null end end as pdn_enddate, pdn.status as pdn_status, status.description as pdn_statusdesc, pdn.managersignoff as pdn_managersignoff, pdn.managersignoffdate as pdn_managersignoffdate, pdn.associatesignoff as pdn_associatesignoff, pdn.associatesignoffdate as pdn_associatesignoffdate, pdn.witnessname as pdn_witnessname, /*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/ /*where the evaluationtype = 'X' (Extension Final).*/ /*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/ case when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate) else null end as ext_startdate, eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/ eval.evaluationtype as eval_evaluationtype, evaltype.description as eval_evaltypedesc, eval.status as eval_status, status2.description as eval_statusdesc, eval.effectivedate as eval_effectivedate, eval.managersignoff as eval_managersignoff, eval.managersignoffdate as eval_managersignoffdate, eval.associatesignoff as eval_associatesignoff, eval.associatesignoffdate as eval_associatesignoffdate, eval.witnessname as eval_witnessname into #pdninfo FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval on pdn.transactionid = eval.transactionid left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl on pdn.processlevel = pl.processlevel left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status on pdn.status = status.status and status.type = 'PDN' left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2 on eval.status = status2.status and status2.type = 'EVAL' left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype on eval.evaluationtype = evaltype.type /*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/ WHERE pdn.status in ('A','S') /*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/ /*'D' - In Progress, or 'N' - Not started)*/ OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))
/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/ /*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/ /* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/ /* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/ SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate into #perfreview FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview WHERE #pdninfo.employeenbr = perfreview.employeenbr AND perfreview.updatedate = (SELECT max(updatedate) FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2 WHERE perfreview2.employeenbr = perfreview.employeenbr AND updatedate IS NOT NULL)
/*select active pdns ('orig' = original)*/ SELECT 'orig' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.pdn_status in ('A','S')
union
/*select extensions ('ext' = extension)*/ SELECT 'ext' as orig_or_ext, #pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate, /*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/ emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi, (SELECT emp2.lastname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname, (SELECT emp2.firstname FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname, (SELECT emp2.mi FROM [HRDataWarehouse].[dbo].[Employee] emp2 WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi FROM #pdninfo left outer join #perfreview on #pdninfo.employeenbr = #perfreview.employeenbr left outer join [HRDataWarehouse].[dbo].[Employee] emp on #pdninfo.employeenbr = emp.employeenbr WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')
