Get max count for the group, which is very slow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get max count for the group, which is very slow

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2011-08-18 : 02:57:55

Hi All,

Please rewrite the below query. This is very slow.

We just need Total records for the group UpdFileName, Customer


;WITH CTE (Folder , Flag , UpdFileName , Customer , ISFolderOrFile, [RowNo])AS
                                                    ( SELECT  Folder       ,
                                                             Flag          ,
                                                             UpdFileName   ,
                                                             Customer      ,
                                                             ISFolderOrFile,
                                                             ROW_NUMBER() OVER(partition BY UpdFileName, Customer ORDER BY UpdFileName) AS [RowNo]
                                                    FROM     [DBO].[T_FEED2RTEAM]
                                                    WHERE    Customer IS NOT NULL
                                                    AND      Flag               = 0
                                                    GROUP BY Folder       ,
                                                             Flag         ,
                                                             UpdFileName  ,
                                                             TimesExecuted,
                                                             Customer     ,
                                                             ISFolderOrFile
                                                    )

                                                    SELECT   A.Folder                               ,
                                                             A.Flag                                 ,
                                                             A.UpdFileName                          ,
                                                             A.Customer                             ,
                                                             A.ISFolderOrFile                       ,
                                                             ROW_NUMBER() OVER(partition BY A.UpdFileName, A.Customer ORDER BY A.UpdFileName) AS [RowNo],
                                                             MAX(B.RowNo)                 AS Total
                                                    FROM     CTE         AS A
                                                             INNER JOIN CTE               AS B
                                                             ON       B.UpdFileName = A.UpdFileName
                                                             AND      B.Customer    = A.Customer
                                                    WHERE    A.Customer   IS NOT NULL
                                                    AND      A.Flag                 = 0
                                                    GROUP BY A.Folder       ,
                                                             A.Flag         ,
                                                             A.UpdFileName  ,
                                                             --A.TimesExecuted,
                                                             A.Customer     ,
                                                             A.ISFolderOrFile
                                                    ORDER BY A.Customer   ,
                                                             A.UpdFileName,
                                                             A.[RowNo]    ,
                                                             A.Folder

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:13:32

can you post some sample data and explain what you want as output. thats should make it much easier than going through the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

baburk
Posting Yak Master

108 Posts

Posted - 2011-08-18 : 05:28:35

quote:
Originally posted by visakh16

can you post some sample data and explain what you want as output. thats should make it much easier than going through the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Use the below data

quote:

SELECT '01007293' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01007295' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01007299' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01007337' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01007339' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01007397' AS Folder , 0 AS Flag , 'ABCD_01_18082011110808' AS UpdFileName , 'ABCD' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01057943' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01058204' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01058775' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01059164' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01059230' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01911994' AS Folder , 0 AS Flag , 'UHI_01_18082011110808' AS UpdFileName , 'UHI' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912313' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912365' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912373' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912407' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912426' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912585' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912592' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912722' AS Folder , 0 AS Flag , 'EFGH_01_18082011110808' AS UpdFileName , 'EFGH' AS Customer , NULL , 0 AS TimesExecuted UNION ALL
SELECT '01912757' AS Folder , 0 AS Flag , 'UHI_01_18082011110808' AS UpdFileName , 'UHI' AS Customer , NULL , 0 AS TimesExecuted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 07:27:19

and what should be output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources