Updating Temporary Tables Causes Slow Performance
Jul 8, 1999
After upgrading to SQL 7 (SP1), we have several SP's that have gone from taking 2-3 min to take 15-20. Each of these SP's creates at least one temp table, inserts into that table, then updates the records in that table. From our research, we can tell that the creation and inserts into the temp tables are fine. It is the updating of these tables that causes the problem. We can observe that the problem is happening by watching the processors go to and stay above 90%. If it were just a few SP's, we could easily fix it and go on, but because of 6.5's limit of 16 tables referenced in a SP, we had to use this method many times. Is there a fix out there for this or a configuration change I can make?
View 2 Replies
ADVERTISEMENT
Nov 20, 2007
Will you recommend the usage of temporary tables in a SQL server database ? AFAIK, it boosts the performance. But recently I read one article in SQL Server performance.com[^] which confused me. Any insights on this would be helpful ?
Thanks
View 3 Replies
View Related
Oct 5, 2007
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
View 2 Replies
View Related
Apr 11, 2007
Hi,
I have a series of queries which have doubled in the amount of time they take to execute since moving to SQL Server 2005. The queries being performance tested are utilising hardware that is very similar to that of the comparison SQL Server 2000 server. They have 6 CPUs exactly the same and we have swapped RAM around to eliminate that difference.
There are 4 parts to the query suffering performance degredation.
1. Create temporary results table
2. Create (using SELECT INTO) and populate temporary working table 1 - 212,263 rows
3. Create (using SELECT INTO) and populate temporary working table 2 - 5,102 rows
4. Insert into temp results table matches found in temp work table 1 and temp work table 2 - 382 rows
On 2000, the queries take a total of 15 secs. On 2005, they take 30 seconds. Part four of the query takes approx 17 secs on its initial run. However, if i truncate the temp results table and re-run just the last query it only takes 1 sec. Query Plan caching?
I have reviewed the forum for a solution to the problem but with no luck. Many of the solutions presented appear to relate to permanant user tables. Solutions such as UPDATE STATISTICS and recompiling stored procedures have no positive effect. When reviewing the query plans, there are very little differences. Some expected between versions right?
The following code snippet is the query from part 4.
Code Snippet
INSERT #MatchingResults
(Table1IDNo, Table2IDNo, MatchRunNo)
SELECT DISTINCT #Table2.IDNo AS Table2IDNo,
#Table1.IDNo AS Table1IDNo,
1 AS MatchRunNo
FROM #Table1
INNER JOIN #Table2
ON ( #Table2.LastName = #Table1.LastName )
AND ( #Table2.AddressDetails = #Table1.AddressDetails )
AND ( #Table2.Country = #Table1.Country )
AND ( ( #Table2.FirstName = #Table1.FirstName) OR ( #Table1.FirstName = '' ) )
AND ( ( #Table2.Title = #Table1.Title ) OR ( #Table1.Title = '' ) )
The query plan shows a hash join on both servers. I have tried removing the distinct statement and forcing a Loop Join (query hint).
I have also run SQL Profiler. The only differences there appear to be with the "SELECT StatMan" statements.
On 2000, part of the query duration is 1719 and is as follows:
Code SnippetSELECT statman([AddressDetails],[LastName],[FirstName],[Title],[Country],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [AddressDetails],[LastName],[FirstName],[Title],[Country] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________000000000096] WITH(READUNCOMMITTED,SAMPLE 3.675520e+001 PERCENT) ORDER BY [AddressDetails],[LastName],[FirstName],[Title],[Country]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)
On 2005, part of the query duration is 5188 and is as follows:
Code Snippet
SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [AddressDetails] AS [SC0] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________00000000000E] WITH (READUNCOMMITTED,SAMPLE 7.946877e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Its clear that the sampling rate is higher. I assume this could have something to do with it. Can this be modified?
Thank-you for your help in advance..
Cheers
Tim
View 9 Replies
View Related
Feb 5, 2001
Hi all,
I got a problem. I am working on DTS package. The last step is updating a table field. I wrote a stored procedure as below:
CREATE PROCEDURE [Update_product_manufacturer] AS
Declare @product_id int
Declare @supplier_name VarChar (255)
Declare ValueCursor Cursor For
select product.product_id, [P21_SUPPLIER_id_name_ke].[supplier_name]
from [VARIANT],[P21_INV_MAST_uid_itenID_weight_ke],[product],
[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e],[P21_SUPPLIER_id_name_ke]
where
[product].product_id = [VARIANT].[product_id]
and
[P21_INV_MAST_uid_itenID_weight_ke].[item_id]=[VARIANT].[SKU]
AND
[P21_INV_MAST_uid_itenID_weight_ke].[inv_mast_uid]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[inv_mast_uid]
AND
[P21_SUPPLIER_id_name_ke].[supplier_id]=[P21_INVENTORY_SUPPLIER_uid_supplierID_price_cost_k e].[supplier_id]
order by [product].[product_id]
for read only
Open ValueCursor
while (0 = 0)
begin
fetch next
from ValueCursor
Into @product_id, @supplier_name
update product
set manufacturer = @supplier_name
where product_id = @product_id
end
close ValueCursor
Deallocate ValueCursor
Notes: Table: Product has 28,000 rows, other tables with 28,000 - 56,000 rows
it's been 2 hours, the job is still working.
Who has this kind of experience? How can I make updating quickly?
Thanks,
Kevin Zhang
View 1 Replies
View Related
Mar 24, 2007
Hi all...
I need urgent help, about someting:
i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
please help me what should i do......
View 3 Replies
View Related
Mar 30, 2000
I need to transfer a database from one server to another, I'm using the DTS utility because the servers have different sort orders. our database size is about 5GB which include about 2500 tables. Using DTS is taking many hours to transfer all objects and data. is there a better/faster way to do this?
Any help would be appreciated, Thank you
View 3 Replies
View Related
Mar 31, 1999
Help! I am new to SQL (6.5) and am designing a small database on a small LAN.
Everything seems to work fine until more than one user gets in the database.
It was originally an Access database and we upsized some of the information to SQL.
I have not upsized all the tables yet, and am not sure if that is causing a problem.
Since we are new to SQL we are trying to make all the changes in Access then upsize
everything. If this is the problem though, I will not be hesitant in upsizing.
Any ideas?
View 1 Replies
View Related
Dec 27, 2001
Since sp2 a number of applications, specifically web ado access has slowed very badly and I seem to be seeing a high cpu usage too. Internal jobs such as backups and table defrags are not affected. I'm on a 4 way box with ample hardware .. stats indexes etc. are fine .. client access is just slow. I suspect mdac but I can't pin anything down and it's driving me crazy !!!!!
I've even built a new box with a fresh install but to no avail.
help ???
View 3 Replies
View Related
Aug 24, 2004
I'm running the following statements. They have been running for 48 hours and counting... In performance monitor, I can see the system is steadily 100% disc bound. Any idea how to get status or ETA on these? Any idea how I can speed up such actions? This seems unusually slow. There are no errors in the Error Log. Does other DBMS systems (such as Oracle) handle such scenarios more quickly or with better status information?
INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)
UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)
Stages is 173 million records
Domains is 2.4 million records
This is running on pretty decent hardware:
Windows 2000 SP5
SQL Server 2000 Enterprise Edition SP3a
Dual 3.06 GHz Xeons with HyperThreading enabled (4 virtual CPUs)
4GB RAM (OS 3GB switch is enabled to give SQL Server 3GB of RAM)
70GB SCSI boot drive
Data/Transaction Log is on a RAID 5, 30 spindle, fibrechannel SAN
View 14 Replies
View Related
Aug 30, 2006
Hi - I have MS SBA2007beta and Office Outlook 2007 BCM Beta. Both use SQL2005 and they are both as slow to point of being frustrating. Outlook2007 without BCM is fine and every other application runs fine if no SQL server apps are present.
I have this running on a 2.8ghz desktop with 1G memory and a Laptop of 1.8ghz and 1.5g memory. The laptop is far faster.
When SQL apps are running everything is slowed.
View 1 Replies
View Related
Mar 15, 2004
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?
Thanks,
Trey
View 8 Replies
View Related
Sep 12, 2004
Hi could anyone give me a hint what does term "temporary table" mean regarding sql server?
View 1 Replies
View Related
Nov 7, 2005
Hi all,
how can i execute this query without errors??
create table #luca(c int)
drop table #luca
select * into #luca
from anagrafica_clienti
The error lanched is:
Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#luca' in the database.
Why if i drop the table?How can i do?Thanks guy
View 3 Replies
View Related
Jan 15, 2007
Afternoon.
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.
So:
Code:
DECLARE @RESULTS TABLE (
ItemID int,
ItemDescription char(50),
ItemType int,
ItemRequestedBy char(50),
ItemStatus int,
ItemQuantity int,
ItemCostPer money,
ItemOrderNumber int,
DateAdded smalldatetime,
DateLastEdited smallDateTime
)
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'
Has anyone got any ideas?
Thanks...
View 3 Replies
View Related
Apr 14, 2004
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?
View 14 Replies
View Related
May 25, 2004
How can I view logs of local (to a session) temporary that are created/dropped?
View 6 Replies
View Related
Aug 21, 2007
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
I could not find the output..
Plz help me..
Thanks..
View 3 Replies
View Related
Nov 2, 2007
Hello,
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?
View 2 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Jul 20, 2005
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,
View 4 Replies
View Related
May 8, 2007
Hello,
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.
Thanks in advance.
View 11 Replies
View Related
Dec 12, 2007
Hi All
declare @temp table
([EVENT_TYPE_ID] [int],
[Desc] [nchar](50) NOT NULL,
[Lead_Time] [smallint] NULL)
INSERT @temp
SELECT *
FROM TBL_EVENT_DEFINiTION
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?
Many thanks in advance
Alex
View 4 Replies
View Related
May 15, 2006
Hello!
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?
Thank you!
View 5 Replies
View Related
Apr 26, 2007
Hello,
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.
My Store is likely to his. Please help me.
INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,
Valor,idConcepto)
EXEC CalculosDiarios @Area,@Reporte,@FechaIni,@FechaFin,0
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
View 6 Replies
View Related
Jul 23, 2007
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?
View 1 Replies
View Related
Aug 9, 2007
Hi,
I have some questions regarding Temporary tables.
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?
Thanks.
Cathie
View 8 Replies
View Related
Jan 31, 2000
Hello,
I have the same database on two different servers. One for production and one for testing. A view that I use runs in less than 2 seconds on the test system, but takes almost 2 minutes on the production server.
What I have noticed is on the test server the view will use an index. The production server ends up scanning a whole table. All indices are the same on both machines for the tables involved and I have updated the statistics. I even went through the process of creating a new table with its indices for the table that is being scanned. Both machines have had service pack 1 installed on them.
Any ideas?
View 1 Replies
View Related
Jan 28, 2003
An Application Times out with tons of SQL Server Locks but there is no log of any errors in SQL Servers. I checked all the Indexes and I reindex it but still same issues. CPU is 100% full at that time and users unable to do anything. Any ideas on how to fix it
View 1 Replies
View Related
Jun 5, 2004
Hi
We are facing performance related problem using Sql server 2000.
We have one stand alone P4 Pc (128 ram) and around 30 users access the sql server through network.
We have written our aplication in VB 6 and backend as Sql Server 2000. We have used Stored Procedure where ever necessary. We have used cursor location as Server side.
When we start with 5 users it is not slow, when all the users say 30 comes in it is slow down.
Can some one help to find out what is the problem.
Thanks
View 1 Replies
View Related
Nov 29, 2005
I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.
The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.
Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.
The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.
Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.
However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.
So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.
But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.
Sorry I wrote a book.
Ideas? This place has always helped me before, so thanks in advance!
View 14 Replies
View Related
Feb 11, 2007
Hi,
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
Best Regards,
Manolis Perrakis
View 1 Replies
View Related
