October 31, 2007 at 12:47 am
Hi all,
I have a problem with my Stored Procedure. It runs longtime. How can i create Dynamic index for performance issue? or Can i put Transaction for all DML statements in Sotred Procedure?
Any ideas....Thanks in advance..!!
October 31, 2007 at 1:41 am
I dont know dynamic index will only help to resolve the issue. do note down the
statistitics io & statistics time and check out the query plan too.
karthik
November 1, 2007 at 1:47 am
Dynamic index is the last thing I would think about. There is a lot of possible problems. Can you send code example from your procedure? Is there any cursor? Have you tried each statement separately to identify weak point?
November 1, 2007 at 10:09 pm
Thanks. I used 5 tables.I have a problem with my WHILE loop,because the 5 tables have more than 3 lacs records.Each time loop will be excuted,we have got longtime to see the result of the procedure.Apporexmately, it will take 1hr 30 mins.
Any ideas..Thanks...
November 1, 2007 at 10:40 pm
Get rid of loop.
_____________
Code for TallyGenerator
November 1, 2007 at 11:08 pm
Thanks....We were avoided the cursor inour Stored Procedure.We are fetching row-by-row from tables...If you have any ideas about Dynamic index purpose, how can i create and drop the index in Stored Procedure??
Any ideas...Thanks!!
November 2, 2007 at 12:33 am
The only idea I have about dynamic index - it's absolutely useless thing!
_____________
Code for TallyGenerator
November 2, 2007 at 12:51 am
What is Materialized views? Is it replace INDEX?
Thanks,
Kannan
November 2, 2007 at 1:25 am
Overview
SQL Server 2000 Indexed Views are similar to Materialized Views in Oracle - the Result Set is stored in the Database. Query Performance can be dramatically enhanced using Indexed Views. Create an Indexed View by implementing a UNIQUE CLUSTERED index on the view. The results of the view are stored in the leaf-level pages of the clustered index.
An Indexed View automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server 2000 can find the rows in the index that are affected by any data modification.
Guidelines for Creating Indexed Views
The SQL Server 2000 Query Optimizer automatically determines whether a given query will benefit from using an Index View.
Create Indexed Views when:
The performance gain of improved speed in retrieving results outweighs the increased maintenance cost.
The underlying data is infrequently updated.
Queries perform a significant amount of joins and aggregations that either process many rows or are performed frequently by many users.
Restrictions on Creating Indexed Views
Consider the following guidelines:
The first index that you create on the view must be a UNIQUE CLUSTRERD index
You must create the view with the SCHEMABINDING option.
The view can reference base tables, but it cannot reference other views.
You must use two-part names to reference tables.
Example
1). Note the Execution Plan of the following query
SET SHOWPLAN_ALL ON
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev,
OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2,4,25)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
|--Sort(ORDER BY)
|--Stream Aggregate(GROUP BY) ...
|--Sort(ORDER BY) ...
|--Nested Loops(Inner Join) ...
|--Index Seek ...
|--Clustered Index Seek ...
2). Create the Indexed View
USE Northwind
GO
SET SHOWPLAN_ALL OFF
GO
-- Set the options to support Index Views
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON
GO
SET ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
-- Create the Index View
DROP VIEW VW_summary
CREATE VIEW VW_summary
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
-- Create UNIQUE CLUSTERED index on the view
CREATE UNIQUE CLUSTERED INDEX IVW_summary
ON VW_summary (OrderDate, ProductID)
GO
3). Check the Execution Plan again on the same Query as in 1).
|--Sort(ORDER BY:([VW_summary] ...
|--Clustered Index Seek ...
If you prefer a graphical representation of the execution plan, use Query Analyzer.
November 2, 2007 at 2:09 am
As Sergiy already said, first thing to consider is how to get needed result without resorting to row-by-row processing - no matter whether it is a loop or cursor. I've seen row-by-row procedures running for several hours, where the same result was achieved in less than 5 minutes when the procedure was rewritten as set-based.
If you don't know how to do it without loop, post the procedure, DDL (CREATE TABLE for all tables used + a few rows of sample data) and explain in words what you need. If you could also include desired result for the given sample data, it would be great.
November 2, 2007 at 2:26 am
All the tables are having nearly 30+ fileds.But the loop part, we are taking the EXPLANATION ID and CODE. For example:
Sample Data for loop part
CLAIM_ID EXPLIN_ID EXPLA_DESC
100 E1 Explanation 1
200 E2 Explanation 2
200 E1 Explanation 1
300 E1 Explanation 1
300 E2 Explanation 2
300 E3 Explanation 3
we need sort like this form
CLAIM_ID EXPLIN_ID EXPLA_DESC
100 E1 Explanation 1
200 E1,E2 Explanation 1,Explanation 2
300 E1,E2,E3 Explanation 1,Explanation 2,Explanation 3
Can you help me?
Thanks in advance.....
November 2, 2007 at 3:26 am
I'm afraid a lot can depend on the other, not mentioned things you're doing with data in this procedure... but generally, you can concatenate lists using UDF:
/*sample data*/
CREATE TABLE yourtable (CLAIM_ID INT, EXPLIN_ID VARCHAR(10), EXPLA_DESC VARCHAR(50))
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (100, 'E1', 'Explanation 1')
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (200, 'E2', 'Explanation 2')
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (200, 'E1', 'Explanation 1')
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (300, 'E1', 'Explanation 1')
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (300, 'E2', 'Explanation 2')
INSERT INTO yourtable (CLAIM_ID, EXPLIN_ID, EXPLA_DESC) VALUES (300, 'E3', 'Explanation 3')
/*function to concatenate strings*/
CREATE FUNCTION dbo.GetDescrList (@ClaimId INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @List VARCHAR(1000)
SELECT @List = ISNULL(@List+ ', ', '') + t.EXPLA_DESC
FROM yourtable t
WHERE t.CLAIM_ID = @ClaimId
ORDER BY t.EXPLA_DESC
RETURN @List
END
GO
/*Call the function*/
SELECT t.CLAIM_ID, dbo.GetDescrList(t.CLAIM_ID) as descriptions
FROM yourtable t
GROUP BY t.CLAIM_ID
/*cleanup*/
DROP TABLE yourtable
Since you didn't specify datatypes and table name, you may need to alter this function to work in your environment. I'm not sure why you also need to concatenate the explanation ID, but if you really need it, you'd have to write another function for that.
This type of data manipulation will always slow down the processing, but it surely shouldn't be as much as you said. Maybe there is a chance to rewrite the procedure, so that this manipulation happens somewhere later when the dataset you're working with is smaller. It would really help if you post more information about the whole procedure, not just about this part of it.
November 2, 2007 at 3:43 am
First of all, I just wondered about your answer.Thank you very much for your timely help.
Actually, this data are used for Monthly Report display. We are creating Stored Procedure for reports.
Thats why, we are creating comma separated with ascending order of values.That example:
CLAIM_ID EXPLIN_ID EXPLA_DESC
--------- ---------- ------------
100 E1 Explanation 1
200 E1,E2 Explanation 1,Explanation 2
300 E1,E2,E3 Explanation 1,Explanation 2,Explanation 3
Stored Procedure is nearly 1000+ lines of code,the problem is on loop phase and some Subquery part. We have decided to Create Dynamic index and put Transaction for all DML statements. It solve only long running queries.In loop part is not satisfied.
Any ideas...Thanks..
November 2, 2007 at 4:52 am
kannan,
what else your 1000+ lines code does what Vladan's code does not?
_____________
Code for TallyGenerator
November 2, 2007 at 5:48 am
Thanks lot....
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply