August 18, 2008 at 9:47 am
Hi, I have couple of stored procedures which gets the data from a view that is formed by a number of unions. each stored procedure takes around 20mins to finish the execution, which is very worst in the performance. What I observed from the rest of the procedures was, the procedures which have only data1 in the where condition finishes executing in 4 mins but if it has both data1 and data2 in where condition it takes 20mins to finish. I gave the code below..Please give me some ideas to tune this procedure...Thank u..
DECLARE @maxtxndate datetime
SELECT @maxtxndate = isnull(max(datetime),'1/1/1900')
FROM tblFTran
WHERE TransactionID = 23221
Select prodid,prodnum,code
From dbo.vwactivity
Where (Code=1054
and Data1 = '23238'
and Data2 = 'PlanViewInquiry'
and eventdtm > @maxtxndate)
August 18, 2008 at 9:49 am
do you have any indexes for data1 and data2?
how many rows are in the table?
August 18, 2008 at 9:52 am
As the query optimizer cannot know the value for declared variables, try re-writting into a single SQL statement such as:
Select prodid,prodnum,code
From dbo.vwactivity
Where (Code=1054
and Data1 = '23238'
and Data2 = 'PlanViewInquiry'
and eventdtm
> COALESCE( select max(datetime)
FROM tblFTran
WHERE TransactionID = 23221) , CAST('1900-01-01' as datetime) )
SQL = Scarcely Qualifies as a Language
August 18, 2008 at 9:52 am
Thanks for the reply...The stored procedure accesses the view but not the table...I could not add any indexes to the views formed by the unions ...The view has around 50 million records...
August 18, 2008 at 9:55 am
Thank you very much Carl Federal...Let me try that...
August 18, 2008 at 9:59 am
We cannot really tell you much in this case, without more information. Please see this article on how to provide this info: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 18, 2008 at 10:11 am
Thank you rbarryyoung ....I m sorry for the inconvenience...next time I will follow that...
August 18, 2008 at 12:21 pm
Execution plans so we can understand which indexes are used, not used, etc., would be very helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2008 at 1:28 pm
ssismaddy (8/18/2008)
Hi, I have couple of stored procedures which gets the data from a view that is formed by a number of unions.
I would recommend reviewing the view that you are using and pulling that code into your procedure. Then, eliminate any additional UNIONS that are not needed for your query. If you do need all of the UNIONS, you could possibly improve performance by eliminating the UNIONS and putting the results into a temporary table.
It all really depends upon what the view is doing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 18, 2008 at 1:56 pm
Thanks for the advise...thats a good idea...I m looking for all the ways I can do...I will try this...
August 19, 2008 at 6:27 am
1.add indexes on columns which are used by where clause....
its better to create indexes on other filegroup the n data filegroup.
2..check union or joins in your query..
if your hand is weak on indexes ..then you can use index tuning wizard...
Dont forget to add clustered index if you dont have
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 19, 2008 at 6:33 am
It would still help to see an execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2008 at 7:06 am
Thanks for the replies.....actually that query access the view which is formed by the unions....so we can't index the view with unions...so we changed our plan and built the new query...now everything is fine....
August 19, 2008 at 10:34 am
Just to confirm - you reviewed the view with the unions and used only what you actually needed in the stored procedure? Just curious what approach you used to fix the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2008 at 11:38 am
we have 12 views for every month...we copied all the data into the single table and made it data for 1 year....so we have only 1 table now...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply