November 30, 2012 at 2:01 am
CREATE TABLE #CLIENTDETAILS(
[CLIENTCODE] int NULL ,
[FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExecCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #CLIENTDETAILS (CLIENTCODE,FullName,ExecCode)
VALUES
(1,'ALAN SMITH','BB1239'),
(2,'BRYAN ADAMS','BB1239'),
(3,'MIKE DDD','BB3343')
CREATE TABLE #CLIENTHOLDINGS(
[ACCOUNTNUMBER] int NULL ,
[STOCK] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STOCKID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExecCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #CLIENTHOLDINGS ([ACCOUNTNUMBER],[STOCK],[STOCKID])
VALUES
(1,'VOD','A222'),
(1,'VOD','A222'),
(1,'ASD','D555'),
(2,'ASF','A111'),
(2,'DKR','D559'),
(2,'CPR','L556'),
(2,'VOD','A222'),
(3,'DKR','D559'),
(3,'LMM','D958'),
(3,'TFW','D214')
SELECT
A.CLIENTCODE,
A.FullName,
A.ExecCode
FROM #CLIENTDETAILS A
WHERE A.CLIENTCODE NOT IN (
SELECT HoldingsInclNT.AccountNumber
FROM (
SELECT ACCOUNTNUMBER,STOCK,STOCKID FROM #CLIENTHOLDINGS
) HoldingsInclNT
WHERE (STOCK = @Stock OR STOCKID = @STOCKID)
)
AND A.ExecCode = (CASE WHEN @EXECCODE = 'All' THEN A.ExecCode ELSE @EXECCODE END)
this query work fine for small data but when the @execcode parameter is 'ALL' its take like more than one minute as the data is huge. Is there any other way I can do this. There are 151676 rows in #CLIENTHOLDINGS
November 30, 2012 at 2:03 am
Can you attach the execution plan in a .sqlplan format?
Also can you complete the problem you are facing as it seems the original post isn't complete
November 30, 2012 at 2:08 am
Hello I have completed the post. How do I attach the execution plan in here?
November 30, 2012 at 2:15 am
Please follow the link in my signature on posting performance problems, it details in their the steps and information, to help us troubleshoot.
November 30, 2012 at 2:19 am
ok I have attatched the execution plan for actual stored proc.
November 30, 2012 at 2:24 am
Well the major thing that stands out is all the index scans, especially the one on Client_oe_info and All_Holding.
Can you post the table definitions along with indexes of all objects which are used within the query.
November 30, 2012 at 2:31 am
CREATE TABLE [dbo].[Client_oe_info](
[Acct_no] [varchar](7) NOT NULL,
[Client_name] [varchar](70) NOT NULL,
[ClientShortName] [varchar](60) NULL,
[Remisier_code] [char](7) NOT NULL,
[ADDRESSCODE] [decimal](18, 0) NULL,
[Branch] [tinyint] NULL,
[Acct_type] [char](1) NULL,
CONSTRAINT [PK_Client_oe_info_1] PRIMARY KEY CLUSTERED
(
[Acct_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[ALL_HOLDINGS](
[HOLDID] [int] NULL,
[STOCK_REC_NO] [varchar](50) NULL,
[ACC_NUMBER] [varchar](6) NULL,
[CODE_ASCII] [tinyint] NULL,
[FUND] [char](2) NULL,
[STOCK_NAME] [varchar](100) NULL,
[SEDOL] [varchar](7) NULL,
[TIDM] [varchar](12) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
what do u mean by indexes of all the objects used in the query?
November 30, 2012 at 2:37 am
I take it that you've looked at the missing indexes suggested by the query optimiser?
/*
Missing Index Details from
The Query Processor estimates that implementing the following index could improve the query cost by 30.4222%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Orderex]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ALL_HOLDINGS] ([CODE_ASCII])
INCLUDE ([ACC_NUMBER],[SEDOL],[TIDM])
GO
----------------
/*
Missing Index Details from
The Query Processor estimates that implementing the following index could improve the query cost by 61.2731%.
WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
USE [Orderex]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Client_oe_info] ([SFAClientType])
INCLUDE ([Acct_no],[Client_name],[ClientShortName],[Remisier_code],[ACCOUNTACTIVE])
GO
You'd be better off splitting this query into a few that are accessed via IF statement control flow, changing it to being dynamic SQL or adding OPTION(RECOMPILE). What you're attempting to do with the @SFAClientType = 'ALL' and the @AccountActive = 'A' parameters is called a "catch all" query. Luckily, Gail Shaw wrote a very interesting blog post about it back in 2009, you can view it here --> http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D.
November 30, 2012 at 2:43 am
Only the primary key as an index? Or do you have other indexes?
IIRC, the CONVERT(INT,A.Acct_no) wont help either as that will cause the query to be Non-Sargable causing a whole heap of issues, can you not change Client_oe_info from varchar(7) to int at the table level?
November 30, 2012 at 2:48 am
I cannot change the table def. Let me try crazyss answer.
November 30, 2012 at 2:55 am
Please post the updated execution plan after creating the two indexes.
November 30, 2012 at 2:56 am
deleted
November 30, 2012 at 3:30 am
You already specify the WITH RECOMPILE option so doing it in dynamic SQL isn't going to change much.
Please create the indexes and repost an updated execution plan.
Please also include the definition of the All_holdings, All_NT_Holdings and RR_Codes tables along with any other indexes which may be on all the tables.
November 30, 2012 at 3:35 am
There are few changes you may try, please note my comments:
create PROCEDURE [dbo].[ABC]
(
@user-id as varchar(7),
@LoginType as varchar(5),
@AccType varchar(3),
@AccountActive varchar(1),
@SFAClientType varchar(20),
@TIDM varchar(7),
@SEDOL varchar(7),
@responsibilityCode varchar(7),
@pageNumber int,
@pageSize int,
@sortExpression varchar(32),
@sortOrder varchar(4),
@virtualCount int OUTPUT
)
WITH RECOMPILE
AS
BEGIN
SET @SFAClientType = CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END
SELECT CONVERT(INT,A.Acct_no) AS CLIENTCODE
,A.Client_name AS FullName
,A.ClientShortName AS ShortName
,A.Remisier_code AS ExecCode
,B.Client_name AS ExecName
,CASE WHEN SFAClientType = 'E' THEN '(E) Exec'
WHEN SFAClientType = 'M' THEN '(M) Adv Mgd'
WHEN SFAClientType = 'A' THEN '(A) Adv'
WHEN SFAClientType = 'D' THEN '(D) Disc'
WHEN SFAClientType = 'B' THEN '(B) Bus'
WHEN SFAClientType = 'N' THEN '(N) Disc - N/F'
WHEN SFAClientType = 'F' THEN '(F) F.Cli.'
WHEN SFAClientType = 'X' THEN '(X) Exp'
WHEN SFAClientType = 'I' THEN '(I) Ind Cust'
WHEN SFAClientType = 'O' THEN '(O) Ord'
WHEN SFAClientType = 'S' THEN '(S) STC'
ELSE '(--) Udf'
END AS SFAClientType
INTO #CLIENTHOLDINGSSTOCKTIDMDETAILED
FROM [Orderex].[dbo].[Client_oe_info] AS A
INNER JOIN Awol.dbo.RR_CODES AS B
ON A.Remisier_code = B.Acct_no
WHERE -- changed NOT IN to NOT EXISTS
NOT EXISTS (
SELECT 1 HoldingsInclNT.AccountNumber
FROM (SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL, CODE_ASCII FROM ALL_HOLDINGS
UNION
SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL, CODE_ASCII FROM ALL_NT_HOLDINGS
) HoldingsInclNT
WHERE (TIDM = @TIDM OR SEDOL = @SEDOL)
AND code_ascii = Cast(Ascii(@AccType) as VarChar(3))
AND HoldingsInclNT.AccountNumber = CONVERT(INT,A.Acct_no)
)
AND (
-- something missing here, as the next line starts with AND in your posted code...
A.Remisier_code = (CASE WHEN @responsibilityCode = 'All' THEN A.Remisier_code ELSE @responsibilityCode END)
AND A.ACCOUNTACTIVE = (CASE WHEN @AccountActive = 'A' THEN A.ACCOUNTACTIVE ELSE @AccountActive END)
-- the next one is I think is a bug, your input param is varchar(20), so do you intend to supply the desciption here?
-- if yes, then it's not going to work, as it will not refer to the result of you CASE WHEN in select, but
-- to single character code...
AND SFAClientType LIKE @SFAClientType
)
And the main one:
You have complained that the query takes a minute when you run it with "ALL" parameters. Let say you need only one option - return ALL, test the query without
filtering in WHERE. How long does it take? Do you have any real margin to fight for?
November 30, 2012 at 3:58 am
thanks mate..I am just going in a meeting at 11. Will get back to you. Actually I can removed the 'ALL' parameter from the query. But some on the remiser codes have a lot of rows. Let me get back to you on this once I check it on my system. thanks
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply