faster query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

faster query

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-04-19 : 05:03:45

Hi every body
Is there a faster solution ?
it takes hours

thanks
-------------------------------------------


SELECT DISTINCT B.TID,B.MDATE,B.PDATE,B.ATJHDAT,B.ATJHNO,
B.ABDOCROW,B.PREBALANCE,B.AMOUNT,
B.BALANCE,B.ABRNCHCOD,B.ATYPCODE,B.AISERIAL,B.ACCOUNTS,B.ABDESC
INTO  center.salavizadeh.bfca_acntbody_4131_1
--** TABLE NAME*****-
FROM  salavizadeh.bfca_acntbody_4131_0 B
WHERE TID IN
(SELECT MAX(TID) 
FROM salavizadeh.bfca_acntbody_4131_0 A
WHERE  A.ACCOUNTS=B.ACCOUNTS AND LEFT(A.PDATE,10)=LEFT(B.PDATE,10)
GROUP BY	A.ACCOUNTS,LEFT(A.PDATE,10)
)
ORDER BY B.TID
CREATE CLUSTERED INDEX IXC_ACCOUNTS_TARIKH
     ON salavizadeh.bfca_acntbody_4131_1 (TID ASC,ACCOUNTS , PDATE ASC,ATJHNO ASC ,ABDOCROW ASC)
----------------------

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-04-19 : 05:54:28

What size data is being inserted into the new table?
Who quickly does the data return without the 'INTO' Clause?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-19 : 07:39:21

Here is a rewritten query that will perform better

SELECT DISTINCT	B.TID,
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
INNER JOIN	(
				SELECT		ACCOUNTS,
						LEFT(PDATE, 10) AS PDATE
						MAX(TID) AS TID
				FROM		salavizadeh.bfca_acntbody_4131_0
				GROUP BY	ACCOUNTS,
						LEFT(PDATE, 10)
		) AS C ON C.ACCOUNTS = B.ACCOUNTS
			AND C.PDATE = LEFT(B.PDATE, 10)
			AND C.TID = b.TID

And here is another version that will perform even better

SELECT		TID,
		MDATE,
		PDATE,
		ATJHDAT,
		ATJHNO,
		ABDOCROW,
		PREBALANCE,
		AMOUNT,
		BALANCE,
		ABRNCHCOD,
		ATYPCODE,
		AISERIAL,
		ACCOUNTS,
		ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		(
			SELECT	TID,
				MDATE,
				PDATE,
				ATJHDAT,
				ATJHNO,
				ABDOCROW,
				PREBALANCE,
				AMOUNT,
				BALANCE,
				ABRNCHCOD,
				ATYPCODE,
				AISERIAL,
				ACCOUNTS,
				ABDESC,
				ROW_NUMBER() OVER (PARTITION BY ACCOUNTS, LEFT(PDATE, 10) ORDER BY TID DESC) AS RecID
			FROM	salavizadeh.bfca_acntbody_4131_0
		) AS x
WHERE		RecID = 1

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-19 : 07:40:47

Here is a rewritten query that will perform better

SELECT DISTINCT	B.TID,
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
INNER JOIN	(
				SELECT		ACCOUNTS,
						LEFT(PDATE, 10) AS PDATE
						MAX(TID) AS TID
				FROM		salavizadeh.bfca_acntbody_4131_0
				GROUP BY	ACCOUNTS,
						LEFT(PDATE, 10)
		) AS C ON C.ACCOUNTS = B.ACCOUNTS
			AND C.PDATE = LEFT(B.PDATE, 10)
			AND C.TID = b.TID

And here is another version that will perform even better

SELECT		TID,
		MDATE,
		PDATE,
		ATJHDAT,
		ATJHNO,
		ABDOCROW,
		PREBALANCE,
		AMOUNT,
		BALANCE,
		ABRNCHCOD,
		ATYPCODE,
		AISERIAL,
		ACCOUNTS,
		ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		(
			SELECT	TID,
				MDATE,
				PDATE,
				ATJHDAT,
				ATJHNO,
				ABDOCROW,
				PREBALANCE,
				AMOUNT,
				BALANCE,
				ABRNCHCOD,
				ATYPCODE,
				AISERIAL,
				ACCOUNTS,
				ABDESC,
				ROW_NUMBER() OVER (PARTITION BY ACCOUNTS, LEFT(PDATE, 10) ORDER BY TID DESC) AS RecID
			FROM	salavizadeh.bfca_acntbody_4131_0
		) AS x
WHERE		RecID = 1

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 07:50:56

hopefully OP is using SQL 2005 and later and posted in the wrong forum

KH
[spoiler]Time is always against us[/spoiler]

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-04-19 : 09:00:34

thanks but I use sql server 2000

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-19 : 17:22:31

Your query would be much faster with non-clustered composite index on accounts and pdate (in that order) in addition to existing clustered index. Why that clustered index is so wide?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-04-20 : 00:27:38

quote:
Originally posted by mmarovic

Your query would be much faster with non-clustered composite index on accounts and pdate (in that order) in addition to existing clustered index. Why that clustered index is so wide?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

-----------------------
Items in index make the corect order
I have records eith same accounts ,same pdate ,same ATJHNO
and different ABDOCROW

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-04-20 : 01:08:24

Hi,
That one in 2008 was perfect .
I also need faster query for these two query.Even in 2008
thanks in advance
-----------------------


SELECT 
TID = IDENTITY (BIGINT ,1,1),B.MDATE,S.MAXDATE PDATE,B.ATJHDAT,B.ATJHNO,
B.ABDOCROW,B.PREBALANCE,B.AMOUNT,
B.BALANCE,B.ABRNCHCOD,B.ATYPCODE,B.AISERIAL,B.ACCOUNTS,B.ABDESC,B.DEPOSITSID,
B.DEPOSITS,B.TCD_ABRNCHCOD,B.TBDPTYPE,B.CFCIFNO	,B.TDSERIAL,
B.TRELCOD
INTO SALAVIZADEH.[BFCA_ACNTBODY_ALL1]  FROM
(SELECT MAX(PDATE) MAXDATE ,ACCOUNTS
FROM SALAVIZADEH.[BFCA_ACNTBODY_ALL] 
WHERE LEFT( PDATE,10) <='1389/12/29' 
GROUP BY ACCOUNTS )S
INNER JOIN SALAVIZADEH.[BFCA_ACNTBODY_ALL]  B
ON B.ACCOUNTS=S.ACCOUNTS AND S.MAXDATE=B.PDATE
 --B.TRELCOD=S.TRELCOD
 WHERE LEFT( S.MAXDATE,10) <='1389/12/29' 
ORDER BY B.ACCOUNTS , S.MAXDATE ASC ,B.ATJHNO ASC ,B.ABDOCROW ASC ,TRELCOD
CREATE CLUSTERED INDEX IXC_ACCOUNTS_TARIKH
     ON SALAVIZADEH.[BFCA_ACNTBODY_ALL1]  (TID ASC,DEPOSITS , PDATE ASC,ATJHNO ASC ,ABDOCROW ASC)
---------------------------------------------------------
SELECT 
TID = IDENTITY (BIGINT ,1,1),B.MDATE,B.PDATE,B.ATJHDAT,B.ATJHNO,
B.ABDOCROW,B.PREBALANCE,B.AMOUNT,
S.BALANCE,B.ABRNCHCOD,B.ATYPCODE,B.AISERIAL,B.ACCOUNTS,B.ABDESC,B.DEPOSITSID,
B.DEPOSITS,B.TCD_ABRNCHCOD,B.TBDPTYPE,B.CFCIFNO	,B.TDSERIAL,
B.TRELCOD--,B.OPENDATE,B.CLOSEDATE,B.STATUS1
INTO SALAVIZADEH.[BFCA_ACNTBODY_ALL2] FROM
(SELECT SUM(BALANCE) BALANCE,DEPOSITS 
FROM  SALAVIZADEH.[BFCA_ACNTBODY_ALL1]
GROUP BY DEPOSITS --,TRELCOD
)S 
INNER JOIN SALAVIZADEH.[BFCA_ACNTBODY_ALL1] B
ON B.DEPOSITS=S.DEPOSITS
ORDER BY B.DEPOSITS , PDATE ASC ,B.ATJHNO ASC ,B.ABDOCROW ASC ,TRELCOD
CREATE CLUSTERED INDEX IXC_ACCOUNTS_TARIKH
     ON SALAVIZADEH.[BFCA_ACNTBODY_ALL2] (TID ASC,DEPOSITS , PDATE ASC,ATJHNO ASC ,ABDOCROW ASC)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-20 : 01:31:33

Did you try my first suggestion?

SELECT DISTINCT	B.TID,
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
INNER JOIN	(
				SELECT		ACCOUNTS,
						LEFT(PDATE, 10) AS PDATE
						MAX(TID) AS TID
				FROM		salavizadeh.bfca_acntbody_4131_0
				GROUP BY	ACCOUNTS,
						LEFT(PDATE, 10)
		) AS C ON C.ACCOUNTS = B.ACCOUNTS
			AND C.PDATE = LEFT(B.PDATE, 10)
			AND C.TID = b.TID

N 56°04'39.26"
E 12°55'05.63"

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-20 : 03:20:55

quote:
Items in index make the corect order
I have records eith same accounts ,same pdate ,same ATJHNO
and different ABDOCROW

Is TID identity column? If so I would make it primary key with clustered index and make a unique key with accounts, pdate, ATJHNO and ABDOCROW in that order. Then I would test all important queries against that configuration. Depending on other important queries it may be a great design or it could suck.

Either way, I would use latest suggestion from Peso with two small possible improvements:


SELECT   	B.TID, --should be no need for distinct
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
INNER JOIN	(
				SELECT		ACCOUNTS,
						LEFT(PDATE, 10) AS PDATE
						MAX(TID) AS TID
				FROM		salavizadeh.bfca_acntbody_4131_0
				GROUP BY	ACCOUNTS,
						LEFT(PDATE, 10)
		) AS C ON C.ACCOUNTS = B.ACCOUNTS
			AND B.PDATE like C.PDATE + '%'
			AND C.TID = b.TID

another possibility is:

SELECT   	B.TID,
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
where not exists(select *
                   from salavizadeh.bfca_acntbody_4131_0 AS c
                   where 
                         c.tid > b.id and
                         c.accounts = b.accounts and
                         c.pdate like left(b.pdate, 10) + '%'
                 )

This one would be slow with current index design bug worth testing with index design I suggested.

On top of that, can you change pdate data type to be datetime or smalldatetime?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-20 : 03:36:19

If TID is really primary key the solution by PESO can be even simpler:

SELECT   	B.TID, 
		B.MDATE,
		B.PDATE,
		B.ATJHDAT,
		B.ATJHNO,
		B.ABDOCROW,
		B.PREBALANCE,
		B.AMOUNT,
		B.BALANCE,
		B.ABRNCHCOD,
		B.ATYPCODE,
		B.AISERIAL,
		B.ACCOUNTS,
		B.ABDESC
INTO		center.salavizadeh.bfca_acntbody_4131_1
FROM		salavizadeh.bfca_acntbody_4131_0 AS B
INNER JOIN	(
				SELECT		ACCOUNTS,
						LEFT(PDATE, 10) AS PDATE
						MAX(TID) AS TID
				FROM		salavizadeh.bfca_acntbody_4131_0
				GROUP BY	ACCOUNTS,
						LEFT(PDATE, 10)
		) AS C ON C.TID = b.TID

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources