September 11, 2007 at 12:48 pm
Hi
I have requirement of updating values from views to temp table and display in SRS reports.
The below procedure i am following and it is blocking the process.
CREATE PROCEDURE [dbo].[Test_Sp]
as
SELECT
* INTO #TESTTBL1 FROM Closed_Calls (--view)
select
* from #TESTTBL1
drop
table #TESTTBL1
If any body knows how to avoid blocking issue, please let me know.
Thank you,
September 11, 2007 at 1:33 pm
select into LOCKs the access. You should simply select from the view.
Just make sure you use appropriate WHERE clauses and indexes.
* Noel
September 11, 2007 at 1:41 pm
Hi Noel
Once I insert records from View to temp table, i need to update temp table with different values,
that is the reason i am using Stored Procedure to do this.
Please let me know if any alternative to work with temporary tables updating from views.
Thanks
vijji
September 11, 2007 at 2:43 pm
SELECT ... INTO ... requires more locking as well as procedure recompilation in my experience. In my experience, you should explicitly create the table first and then populate it.
September 12, 2007 at 2:03 am
Select... into puts locks on some of the TempDB system pages until the select is finished. Try using Create Table... Insert into...
Post the entire code if you can. Maybe someone can see a way that avoids the temp table completely.
Do you know what the blocking is on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2007 at 11:54 am
Can you post some sample code ?
* Noel
September 12, 2007 at 12:25 pm
--sample procedure
CREATE
PROCEDURE [dbo].[check10_Proc]
@startdate datetime,
@enddate
datetime
as
SELECT
*, 0 AS PREVSLNO, 'N' as CALLBACK, SPACE(10) as CBTECHID, 'N' AS PARTS, SPACE(2) as INVSTAT
INTO
#Check10 FROM Closed_Calls_Invoice
UPDATE
T4
SET
INVSTAT =
CASE
WHEN
Invoice_Time > 24
THEN
'NG'
WHEN
Invoice_Time IS NULL
THEN
'NG'
ELSE
''
END
from
#Check10 T4
select
* from #Check10
where
Service_Type
like '%P3%'
AND
SRVSTAT
>= '70C'
AND
COMPDTE
>= @startdate
AND
COMPDTE
<= @enddate
order
by SRVSTAT, COMPDTE
drop
table #Check10
September 12, 2007 at 12:53 pm
What is the problem with ?
SELECT
i.*, 0 AS PREVSLNO, 'N' as CALLBACK, SPACE(10) as CBTECHID, 'N' AS PARTS,
( CASE WHEN INVOICE_TIME > 24 OR INVOICE_TIME IS NULL THEN 'NG' ELSE '' END) AS INVSTAT
FROM
Closed_Calls_Invoice i
WHERE
SERVICE_TYPE
like '%P3%'
AND SRVSTAT >= '70C'
AND COMPDTE >= @startdate
AND COMPDTE <= @enddate
ORDER
BY
SRVSTAT, COMPDTE
* Noel
September 14, 2007 at 5:47 am
Hi,
you can do 1 thing when creating a temp table append the random number with ur temporary table which will help u in blocking.
also specify the locking hint(use BOL for locking hints) like NOLOCK on ur view which will help you in unblocking the view while selecting records.
Abhijit More
Database Developer.
September 20, 2007 at 11:30 pm
SQL already automatically adds random numbers to temp table names. Adding your own will just make scripts more complex, nothing else.
Nolock will help with the shared locks, but will not alleviate exclusive locks nor will it reduce latch contention on the system pages in tempdb
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply