Retrieving records and inserting into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Retrieving records and inserting into table

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-19 : 10:05:26

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 tbRequests 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 tbRequests 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

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

Thanks !

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-03-19 : 12:32:05

Are you sure it is the INSERT that takes hours, or the SELECT (your select * from #tresults)?

Run the query with Client statistics. Query --> Include Client stats, or press Shift+Alt+S. Run the query in both configurations.

You should also compare indexes to the physical table versus temp (some versus absolutely none).

Changing your temp table structure very simply from what you have to:

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

will create a clustered index on the identity column, and the Select * will perform substantially faster.

Also worth considering is where the tempdb resides, where the log file for it resides, and the physical differences in hds.

Just some thoughts to consider...

----- added
THe reason you run with client statistics is to see the impact on run-time in a more substantiated manner. The over-simplified view of "Time statistics" should provide enough of a basis for comparison.

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-19 : 13:20:23

I am pretty sure it takes hours for INSERT and not SELECT. Here is the Client Statistics. I ran this time for only 100 rowcount 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.

Also, I tried it on various hardware (RAID 10) it runs slow for INSERT. I even tried it using Permanent table (thinking problem with tempdb) but the same behaviour.

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

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-03-19 : 13:33:13

Check out this discussion on table variables versus temp tables:
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Curious, if you run it as a variable, how is the performance impacted? In theory, the variable will run mostly in ram, rather than use as heavily the tempdb.

Also, did you try running with that slight change I suggested? (I just added PRIMARY KEY to the identity column).

You do need to check the configuration of your tempdb. If your tempdb is hosted on your system drive, or on a different array, it might be a physical issue. You can go here for a discussion on tuning the tempdb for performance.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1180698_tax301334,00.html?bucket=ETA

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-19 : 13:43:21

Tempdb is on it own physical disk (RAID 1). I also tried using table variables but the same output with the same time (no change). Regarding the small change you suggesed about primary key, I ran the client statistics using the same but no change. Has anything to do with the CROSS Apply or the Query seems wrong?

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-03-19 : 14:33:47

If the query is exactly the same with the exception of the temp table versus physical, then it would seem to me the issue is not the syntax at all. You can verify this by executing both with the Execution plans turned on. Query --> Include Actual Plan. They shouldn't really differ, aside from the creation of the temp table. You will also see exactly where in the cycle the process is getting held up. You are saying it is inserts, so it should be after the table is created you see N% of your query.

If you're saying that the insert is taking a long time, it could be that the raid 1 array is slow, the controller card is overwhelmed, or maybe the drives (the ones hosting the physical table) are actually different speeds.

Are your tempdb data and log files on the same array? Same logical partition? Physical partition?

------- edit
For testing purposes, create a physical table and run the insert to it. Compare THAT, not the pure select versus insert/select.

That should have been the first thing done...

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-19 : 14:46:55

I tried all of this jordanam. One thing which I noticed is this happens only where the XML scripts are longer.

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-03-19 : 15:05:11

Maybe this will prove more fruitful:
http://msdn2.microsoft.com/en-us/library/ms345118.aspx

I'll leave you be. Good luck.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources