Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Sequence-numbering groups Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 10:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
LutzM (10/21/2010)How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...


This table currently has almost 414,000 rows. Here is the DDL for the table:

USE [MyDatabase]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 10/21/2010 08:48:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[AcctFundCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctFundId] DEFAULT (''),
[ACHTrans] [bit] NOT NULL CONSTRAINT [DF_MyTable_ACHTrans] DEFAULT (0),
[AddrCity] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCity] DEFAULT (''),
[AddrForeignStateCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCountry] DEFAULT (''),
[AddrName] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorName] DEFAULT (''),
[AddrStateCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorState] DEFAULT (''),
[AddrStreet] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorStreet] DEFAULT (''),
[AddrZip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorZip] DEFAULT (''),
[AmtCheck] [money] NOT NULL CONSTRAINT [DF_MyTable_AmtTotal] DEFAULT (0),
[BankAcctId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BankAcctId] DEFAULT (''),
[BankCheckId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_CheckId] DEFAULT (''),
[BankCheckSort] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrSortValue] DEFAULT (''),
[DateCancelled] [datetime] NULL,
[DateCheck] [datetime] NULL,
[DateTimeCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTable_DateTimeCreated] DEFAULT (getdate()),
[DateTimeEdited] [datetime] NULL,
[DateTimePrinted] [datetime] NULL,
[EmpId] [int] NOT NULL CONSTRAINT [DF_MyTable_PersonId] DEFAULT (0),
[OrgId] [smallint] NOT NULL CONSTRAINT [DF_MyTable_OrgId] DEFAULT (0),
[PayeeId] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_StudentId] DEFAULT (''),
[RegisterId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterId] DEFAULT (''),
[RegisterIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterIdCancel] DEFAULT (''),
[Status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_ReqStatusCode] DEFAULT (''),
[UserIdCreated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdCreated] DEFAULT (''),
[UserIdEdited] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdEdited] DEFAULT (''),
[UserIdPrinted] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdPrinted] DEFAULT (''),
[VendorAddrId] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorAddrId] DEFAULT (0),
[VendorId] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorId_1] DEFAULT (''),
[VendorPayrollCheckType] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorPayrollCheckType] DEFAULT (0),
[PrintedCheckNum] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BatchId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchId] DEFAULT (''),
[DateCleared] [datetime] NULL,
[AcctResourceCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctResourceCode] DEFAULT (''),
[AcctProjectYearCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctProjectYearCode] DEFAULT (''),
[BatchIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchIdCancel] DEFAULT (''),
CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED
(
[BankCheckId] ASC,
[OrgId] ASC,
[BankAcctId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [MyDatabaseData4]
) ON [MyDatabaseData4]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Fund Id when only single funded checks are allowed' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctFundCode'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True if this was part of an ACH Bank Transaction' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'ACHTrans'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee City Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrCity'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Country Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrForeignStateCode'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee State Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStateCode'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Street Address' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStreet'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Zip Code' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrZip'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total Check Amount' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AmtCheck'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Bank Account Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankAcctId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Sort value used when printing checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckSort'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date this check was cancelled' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCancelled'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Date' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCheck'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeCreated'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeEdited'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Print date and time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimePrinted'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id value when this check is associated with an Employee' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'EmpId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Organization''s Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'OrgId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Id value when this check is associated with a one time vendor' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PayeeId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Register this appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cancel Register Id this check appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterIdCancel'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Status Code value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'Status'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdCreated'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed by User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdEdited'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Id of the user who printed the check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdPrinted'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Address Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorAddrId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Payroll Check Type (1 - Deduction, 2 - Contribution, 3 - Both Deduction & Contribution, 4 - ACH Check to cover direct deposits)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorPayrollCheckType'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a check # that some foreign system printed for our check record' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PrintedCheckNum'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Batch Id value associated with the printing of checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datetime the check cleared the bank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCleared'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Resource Code that paid the most for this check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctResourceCode'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Project Year component value that paid the most in combination with the AcctFundCode, AcctResourceCode' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctProjectYearCode'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'BatchId that this check was cancelled on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchIdCancel'



Here are the indexes:

USE [MyDatabase]
GO
CREATE CLUSTERED INDEX [IX_MyTable_BankAcctId] ON [dbo].[MyTable]
(
[BankAcctId] ASC,
[OrgId] ASC,
[DateCheck] DESC,
[BankCheckId] DESC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_BatchId] ON [dbo].[MyTable]
(
[BatchId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_BatchIdCancel] ON [dbo].[MyTable]
(
[BatchIdCancel] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_CheckNum] ON [dbo].[MyTable]
(
[PrintedCheckNum] ASC,
[DateTimeCreated] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_Status] ON [dbo].[MyTable]
(
[BankAcctId] ASC,
[Status] ASC,
[OrgId] ASC,
[DateCancelled] ASC,
[DateCheck] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_VendorId] ON [dbo].[MyTable]
(
[OrgId] ASC,
[VendorId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED
(
[BankCheckId] ASC,
[OrgId] ASC,
[BankAcctId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]




Here is the current execution plan:

DECLARE @tmpChecks TABLE
(
OrgCheck char(15),
OrgId smallint,
CheckNum int,
Status char(1),
AmtCheck money,
CheckDate char(10),
RegisterId char(12)
)

INSERT INTO @tmpChecks
SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,
OrgId, PrintedCheckNum as CheckNum, Status,
AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,
CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId
from MyTable WITH (NOLOCK)
WHERE
ISNUMERIC(PrintedCheckNum) = 1
order by 1

;WITH cte1 AS -- row number per group
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY OrgCheck) AS CkRow,
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS StatRow,
ROW_NUMBER() OVER(ORDER BY OrgCheck)-
ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS DiffRow
FROM @TmpChecks
),cte2 AS -- row number per group range, ordered by OrgCheck
(
SELECT MIN(CkRow) as MinRow,MAX(CkRow) as MaxRow, Status, DiffRow,
ROW_NUMBER() OVER(ORDER BY MIN(CkRow)) AS CheckGroup
FROM cte1
GROUP BY Status, DiffRow
)
SELECT cte1.OrgCheck, cte1.OrgId, cte1.CheckNum, cte1.Status, cte1.AmtCheck,
cte1.CheckDate, cte1.RegisterId, cte2.CheckGroup
FROM cte1
INNER JOIN cte2 ON cte1.CkRow>=cte2.MinRow AND cte1.CkRow<=cte2.MaxRow
ORDER BY cte1.OrgCheck



The general idea is that the accounting application that uses this table currently allows users to run reports that were written using Crystal Reports. I have written one custom report for internal control of the checks which lists series of open checks and series of void checks. The report relies on a stored procedure which uses values provided by the user to select the checks for the report. I have written this stored procedure which is similar to the execution plan shown above but which also includes fairly complex WHERE clauses to handle the user-provided values. When I connect the stored procedure to the Crystal Report, I need it to run through all rows in MyTable once so that when it is run later by the users, none of their user-provided values will have been excluded by the initial connection of the stored procedure to the Crystal Report.

I don't mean for this to turn into a large task for anyone on the forum. Thank you again for any suggestions.

gmrose
Post #1008646
Posted Thursday, October 21, 2010 12:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 7,155, Visits: 13,216
That explains the bad performance pretty much...

You're using a table variable just like I did for my demo version. My fault. I'm sorry.

A table variable is a bad option to use since it's usually populated without proper indexing causing a table scan instead of a index seek. Furthermore, there are no statistics available for the query optimizer to come up with the best query plan. Instead QO will assume that the table varialbe will have only one row.

I thought you would replace the table variable with your original table, but I didn't mention it.

Now that I see you're using a computed column as a replacement of ckid, I'd recommend you make the following changes (+ change the table name in the CTE).

CREATE TABLE #tmpChecks
(
OrgCheck char(15) PRIMARY KEY CLUSTERED,
OrgId smallint,
CheckNum int,
Status char(1),
AmtCheck money,
CheckDate char(10),
RegisterId char(12)
)

INSERT INTO #tmpChecks
SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,
OrgId, PrintedCheckNum as CheckNum, Status,
AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,
CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId
from MyTable WITH (NOLOCK)
WHERE
ISNUMERIC(PrintedCheckNum) = 1
order by OrgCheck

I'd be curious to know the timing of this code compared to the previous solution...
Again, I'm sorry...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1008740
Posted Thursday, October 21, 2010 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:07 AM
Points: 6,594, Visits: 8,881
ColdCoffee (10/20/2010)
Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )
What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?

P.S: i am still learning the QU, so please pardon my ignorance..


Not a problem - it would only be a problem when you stop learning - and even then, it's YOUR problem!

In the original post, the OP shows the expected results in ckid order, and the grp number changes whenever the stat changes. That is why there is a clustered index on the ckid column. The ROW_NUMBER() ... ORDER BY clause MUST identical to the clustered index columns (including the sort direction).

The ROW_NUMBER() ORDER BY is a neat trick that Paul White came up with, and it was enhanced by Tom Thompson. It essentially guarantees that the update occurs in the proper order, or not at all. Tom's enhancement covers the (theoretical) possibility that the sequence number is properly generated in a linear fashion, while the variable assignment (that's being carried row-to-row) isn't.

In the solution I posted, I alluded to the possibility of needing an additional field to do this properly. I'm assuming that there would be an account number. This would require:
1. changing the clustered index (and the ROW_NUMBER()) to include this column.
2. changing the logic of the variable assignment to handle when the account number changes.

I've found that the best way of learning is by practicing. You don't get rid of c.u.r.s.o.r.s. by writing more of them; you learn the QU (and other set-based practices) by doing things with them. So, let's make a couple of minor modifications to the specification. Let's include the account number, and change the specs so that whenever the account number changes, the grp restarts at one. All other specs remain the same. The following code has the sample data and expected results.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #temp (acct_nbr, ckid, stat)
SELECT 1, 101 ,'Open' UNION ALL
SELECT 1, 102 ,'Open' UNION ALL
SELECT 1, 103 ,'Open' UNION ALL
SELECT 1, 104 ,'Void' UNION ALL
SELECT 1, 105 ,'Void' UNION ALL
SELECT 1, 106 ,'Open' UNION ALL
SELECT 1, 107 ,'Open' UNION ALL
SELECT 1, 108 ,'Open' UNION ALL
SELECT 1, 109 ,'Void' UNION ALL
SELECT 1, 110 ,'Open' UNION ALL
SELECT 2, 101 ,'Open' UNION ALL
SELECT 2, 102 ,'Open' UNION ALL
SELECT 2, 103 ,'Open' UNION ALL
SELECT 2, 104 ,'Void' UNION ALL
SELECT 3, 105 ,'Void' UNION ALL
SELECT 3, 106 ,'Open' UNION ALL
SELECT 3, 107 ,'Open' UNION ALL
SELECT 4, 108 ,'Open' UNION ALL
SELECT 4, 109 ,'Void' UNION ALL
SELECT 4, 110 ,'Open';

-- expected output:
SELECT acct_nbr = 1, ckid = 101 , stat = 'Open', grp = 1 UNION ALL
SELECT 1, 102 ,'Open',1 UNION ALL
SELECT 1, 103 ,'Open',1 UNION ALL
SELECT 1, 104 ,'Void',2 UNION ALL
SELECT 1, 105 ,'Void',2 UNION ALL
SELECT 1, 106 ,'Open',3 UNION ALL
SELECT 1, 107 ,'Open',3 UNION ALL
SELECT 1, 108 ,'Open',3 UNION ALL
SELECT 1, 109 ,'Void',4 UNION ALL
SELECT 1, 110 ,'Open',5 UNION ALL
SELECT 2, 101 ,'Open',1 UNION ALL
SELECT 2, 102 ,'Open',1 UNION ALL
SELECT 2, 103 ,'Open',1 UNION ALL
SELECT 2, 104 ,'Void',2 UNION ALL
SELECT 3, 105 ,'Void',1 UNION ALL
SELECT 3, 106 ,'Open',2 UNION ALL
SELECT 3, 107 ,'Open',2 UNION ALL
SELECT 4, 108 ,'Open',1 UNION ALL
SELECT 4, 109 ,'Void',2 UNION ALL
SELECT 4, 110 ,'Open',3;



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1008771
Posted Thursday, October 21, 2010 5:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 26, Visits: 223
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.

For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.

gmrose
Post #1008892
Posted Thursday, October 21, 2010 6:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:07 AM
Points: 6,594, Visits: 8,881
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.

For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.

gmrose


Yes, this is indeed a real improvement. Out of curiosity, how many records are you processing? And did you try out the script that I posted?


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1008906
Posted Thursday, October 21, 2010 11:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.

For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.

gmrose


You are processing information for a bank. You might want to post your final code so we can double check for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1008963
Posted Friday, October 22, 2010 8:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 7,155, Visits: 13,216
gmrose (10/21/2010)
Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor adjustments to exclude about 100,000 unneeded rows. Now the script runs as desired.

For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.

gmrose


Not sure if I'd be happy with that performance yet... (even without using the quirky update)

How long does each part of the two actually take? (timing for populating the temp table and for the final select)

Another question:
Are you sure that your WHERE condition "ISNUMERIC(PrintedCheckNum) = 1" will actually return the results as needed? What would you do with a row holding '100E12'??

At this point I'd really like to see the actual execution plans before making any suggestions...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1009255
Posted Friday, October 22, 2010 9:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 7,224, Visits: 13,696
gmrose (10/21/2010)
For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.

gmrose


Heh Jeff's gonna hate me for this but I couldn't resist it.
400,000 rows in 15 seconds:

IF OBJECT_ID('tempdb..#tempstore') IS NOT NULL DROP TABLE #tempstore;
CREATE TABLE #tempstore (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #tempstore (acct_nbr, ckid, stat)
SELECT 1, 101 ,'Open' UNION ALL
SELECT 1, 102 ,'Open' UNION ALL
SELECT 1, 103 ,'Open' UNION ALL
SELECT 1, 104 ,'Void' UNION ALL
SELECT 1, 105 ,'Void' UNION ALL
SELECT 1, 106 ,'Open' UNION ALL
SELECT 1, 107 ,'Open' UNION ALL
SELECT 1, 108 ,'Open' UNION ALL
SELECT 1, 109 ,'Void' UNION ALL
SELECT 1, 110 ,'Open' UNION ALL
SELECT 2, 101 ,'Open' UNION ALL
SELECT 2, 102 ,'Open' UNION ALL
SELECT 2, 103 ,'Open' UNION ALL
SELECT 2, 104 ,'Void' UNION ALL
SELECT 3, 105 ,'Void' UNION ALL
SELECT 3, 106 ,'Open' UNION ALL
SELECT 3, 107 ,'Open' UNION ALL
SELECT 4, 108 ,'Open' UNION ALL
SELECT 4, 109 ,'Void' UNION ALL
SELECT 4, 110 ,'Open';

-- 400,000 rows of data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (acct_nbr INT,
ckid INT,
stat varchar(10),
Grp int);
INSERT INTO #temp (acct_nbr, ckid, stat)
SELECT acct_nbr+(4*n), ckid+(20*n), stat
FROM #tempstore
CROSS JOIN (SELECT TOP 20000 [n] = ROW_NUMBER() OVER(ORDER BY a.[name])-1 FROM master.dbo.syscolumns a, master.dbo.syscolumns b) n

-- copy our data to a working table
SELECT rn = ROW_NUMBER() OVER (ORDER BY acct_nbr, ckid),
acct_nbr, ckid, stat
INTO #Workings
FROM #temp
ORDER BY acct_nbr, ckid
CREATE UNIQUE CLUSTERED INDEX CIrn ON #Workings ([rn] ASC)

-- get the results
;WITH Calculator AS (
SELECT rn, acct_nbr, ckid, stat, NewGroup = CAST(1 AS INT)
FROM #Workings WHERE rn = 1
UNION ALL
SELECT w.rn, w.acct_nbr, w.ckid, w.stat,
NewGroup = CASE
WHEN w.acct_nbr <> c.acct_nbr THEN 1
WHEN w.stat <> c.stat THEN c.NewGroup + 1
ELSE c.NewGroup END
FROM #Workings w
INNER JOIN Calculator c ON c.rn+1 = w.rn)
SELECT acct_nbr, ckid, stat, NewGroup
FROM Calculator
OPTION( MAXRECURSION 0)
-- 400,000 rows: 15 seconds



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1009302
Posted Friday, October 22, 2010 10:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
Chris Morris-439714 (10/22/2010)
Heh Jeff's gonna hate me for this but I couldn't resist it.
400,000 rows in 15 seconds:


Actually, I don't hate you. That's some good, solid, creative, fast code, Chris. There's just one thing... you code only displays the data. It doesn't update either table. Make it so you update one of the two tables and we'll have a drag race.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1009560
Posted Saturday, October 23, 2010 12:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 11,194, Visits: 11,142
Jeff Moden (10/22/2010)
Chris Morris-439714 (10/22/2010)
Heh Jeff's gonna hate me for this but I couldn't resist it.
400,000 rows in 15 seconds:

Actually, I don't hate you. That's some good, solid, creative, fast code, Chris. There's just one thing... you code only displays the data. It doesn't update either table. Make it so you update one of the two tables and we'll have a drag race.

I'm not sure I see where the requirement to update the original table comes from?

If the results need to be persisted, simply change the SELECT into a SELECT INTO.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1009568
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse