Retrieving records and inserting into table

Last Post 18 Apr 2008 04:08 PM by SQLUSSR. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqldba20
New Member
New Member

--
19 Mar 2008 07:11 AM
Folks:

I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?


SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbl1010WebServiceRequestResults WITH(NOLOCK) WHERE ImportId = @ImportId)


SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = ''
THEN NULL
ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = ''
THEN NULL
ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)


====================================================================

Same Query when trying to insert the records in a temp table it takes hours.

====================================================================

SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbl1010WebServiceRequestResults WITH(NOLOCK) WHERE ImportId = @ImportId)

create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)


INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName,
resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP,
CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = ''
THEN NULL
ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage,
resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType,
CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = ''
THEN NULL
ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff
FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode)
CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)

SELECT * FROM #TResults


============================================


Here is the Client Statistics. I ran this for only 100 rowcount as a test and for select it took less than 15 seconds and with INSERT it took 16 mins. The Trial 2 (13:10:31) is WITH INSERT and Trail 1 (12:53:58) is WITHOUT INSERT.

Trial 2 Trail 1
(With INSERT) (Without INSERT)

Client Execution Time 13:10:31 12:53:58
Query Profile Statistics
Number of INSERT, DELETE
and UPDATE statements 1 0
Rows affected by INSERT,
DELETE, or UPDATE statements 0 0
Number of SELECT statements 3 3
Rows returned by SELECT statements 0 0
Number of transactions 1 0
Network Statistics
Number of server roundtrips 1 1
TDS packets sent from client 1 1
TDS packets received from server 2 2
Bytes sent from client 2356 2392
Bytes received from server 4406 7031
Time Statistics
Client processing time 0 0
Total execution time 965871 1296
Wait time on server replies 965871 1296




Thanks !
SQLUSA
New Member
New Member

--
19 Mar 2008 10:46 PM
Have you tried SSIS?

Use the XML task.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/
scotchp
New Member
New Member

--
31 Mar 2008 09:01 PM
Some Hints to try/consider
=================

TempDb holds temp tables.... if tempdb has to grow to accommodate your insertion of large amounts of data then things will slow down, other factors include page fragmentation, disk fragmentation etc etc etc.

If you're doing this on a regular basis then perhaps temp tables as permanent working tables in your db.

If you're using SQL20905 then you can optimize/tune TEMPDB much more than the limitations with
SQL2000 tempdb.

Also check indexation on the FROM tables (check query plan) , also review your CROSS join, perhaps LEFT join may work better, sorry Ive not had time to tinker with the query.

Try SELECT .. INTO #xxxx rather than create temp table first.
SQLUSA
New Member
New Member

--
18 Apr 2008 01:13 PM
quote:

Originally posted by: scotchp

If you're using SQL20905 then you can optimize/tune TEMPDB much more than the limitations with
SQL2000 tempdb.
.



What is your favorite way of tuning tempdb?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSSR
New Member
New Member

--
18 Apr 2008 04:08 PM
I like using a tuning fork. Before I had experience, I had to use an electronic tuner. But THEN I DISCOVERED SQLUSA training and found that all I had to do was drop constraints and foreign keys to be able to do whatever I want and haven't had any problems since. Of course the data is all bad, but the insert performance is crazy delicious!!!!
You are not authorized to post a reply.

Acceptable Use Policy