March 20, 2012 at 7:35 am
Procedure is taking much time when we execute in Qurey Editior where is was giving best result (working fast\ able to fetch data) from Application...
1. we are testing same procedure from SQLCMD
--- SQLCMD also working fine, i can able to get the result immdeatly
March 20, 2012 at 7:43 am
without seeing the procedure itself, all i can offer is basic suggestions.
you might be suffering from parameter sniffing.
you might want to update your statistics.
the SET options (ansi_nulls, etc) between SSMS and SQL command might be different, and cause the issue .
you definitely need to look at the actual execution plan in SSMS and examine why it is slow.
Lowell
March 20, 2012 at 7:48 am
Backing what Lowell said. Sounds like parameter sniffing is possible.
An execution plan would be helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 20, 2012 at 9:34 am
we have desined this procedure to get data from another db where the data in able to huge.
tableA will having :ID and row_number
TableB is having :Document_version_id and row_number
TableC is having :Document_version_id,name,value,enty_path
---
How can i optimize this procedue
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[Getdata] As
Begin
ECLARE @Flag INT,@counter INT
SELECT @Flag = COUNT(*) FROM LocalTempTableA
set @counter=1
Declare @document_id INT
WHILE(@counter <= @Flag)
BEGIN
set @document_id=(select document_id from LocalTempTableA where ROW_NUMBER =@counter)
DECLARE @document_version_id INT
SET @document_version_id=(SELECT TOP 1
dv.document_version_id
FROM
Dot_Net_Installer_REP.dbo.document d WITH (READCOMMITTED)
INNER JOIN
mydb.dbo.document_version dv WITH (READCOMMITTED) on dv.document_id=d.document_id
WHERE
(@document_id IS NOT NULL AND d.document_id=@document_id AND d.current_document_version_id=dv.document_version_id))
insert into LocalTempTableB(Document_version_id,Row_number) values
(@document_version_id,@counter)
set @counter=@counter+1
END
--select * from #LocalTempTable2
DECLARE @Flag1 INT,@counter1 INT
SELECT @Flag1 = COUNT(*) FROM LocalTempTableB
SELECT @Flag1
set @counter1=1
DECLARE @document_version_id1 INT
WHILE(@counter1 <= @Flag1)
BEGIN
set @document_version_id1=(select Document_version_id from LocalTempTableB where ROW_NUMBER =@counter1)
insert into LocalTempTableC(Document_version_id ,name ,value ,entry_path)
SELECT document_version_id, [name], [value],entry_path
FROM mydb.dbo.entry WITH (READCOMMITTED)
WHERE document_version_id = @document_version_id1
and entry_path in('/document/asset/content_provider/document','/document/asset/content_policy/document','/document/asset/id')
set @counter1=@counter1+1
END
END
----truncate table Delete1
--select distinct document_version_id from LocalTempTableC
March 20, 2012 at 9:35 am
Above procedure execution is taking more time to complte
arround 2hour
March 20, 2012 at 11:46 am
Well it certainly isn't parameter sniffing...it is all the RBAR going on. There is no need to have nested loops for these inserts. If you want some help making this a set based solution read the first link in my signature about how to best post questions and you will gets lots of help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 12:26 pm
first guess:
does this return the same results as the two hour procedure?
SELECT
ent.document_version_id,
ent.[name],
ent.[value],
ent.entry_path
FROM mydb.dbo.entry ent WITH (READCOMMITTED)
LEFT OUTER JOIN
(SELECT
d.document_id,
MAX(dv.document_version_id ) AS document_version_id
FROM
Dot_Net_Installer_REP.dbo.document d WITH (READCOMMITTED)
INNER JOIN
mydb.dbo.document_version dv WITH (READCOMMITTED)
on dv.document_id=d.document_id
WHERE d.current_document_version_id=dv.document_version_id
GROUP BY d.document_id
)MAXDocVersions
ON ent.document_version_id = MAXDocVersions.document_version_id
WHERE ent.entry_path in('/document/asset/content_provider/document','/document/asset/content_policy/document','/document/asset/id')
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply