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)
 Big Data query speeded up with RowNumber Why?

Author  Topic 

pvera
Starting Member

4 Posts
Posted - 2011-10-27 : 16:56:01
I´ve got a query from a table with aprox 400.000 rows called PricingErrors

My query makes a subquery from PricingErrors taking aprox 250.000 rows and the join with other several tables. If I try to run the following query it takes hours without results:


select pe.ErrorDescription, cd.*
from
(select * from itx.PricingErrors where ErrorDescription = 'Not rated CDR') pe
INNER JOIN ...


If I put in the subselect a Top 7000 on the table pe the query last 3 seconds if I put more than 7000 it takes hours. It seems a memory problem so I decided to make it in groups of 7000 rows using ROW_NUMBER and making several queries changing the ranges of the row numbers to join.

then y Do this:

select pe.rownum, pe.ErrorDescription, cd.*
from
(
SELECT * FROM
(select ROW_NUMBER() OVER (ORDER BY PricingErrorID) AS ROWNUM, * from itx.PricingErrors where ErrorDescription = 'Not rated CDR') A
WHERE ROWNUM >=0 AND ROWNUM <300000
) pe
inner join ... (several tables)

And this query takes only take only 8 seconds to give me the whole table results. The question Is Why row number speeds up the query??

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-10-27 : 17:29:31
post the whole query.


inner join.... (several tables)

could conceal a lot of stuff!

My guess is that the presence of the row number changes the execution plan (probably because the engine doesn't try to *optimise* your query in a inefficient way).

Post the whole query please

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

pvera
Starting Member

4 Posts
Posted - 2011-10-27 : 17:32:44


select pe.rownum, pe.ErrorDescription, cd.*
from
(
SELECT * FROM
(select ROW_NUMBER() OVER (ORDER BY PricingErrorID) AS ROWNUM, * from itx.PricingErrors where ErrorDescription = 'Not rated CDR') A
WHERE ROWNUM >=0 AND ROWNUM <300000
) pe
inner join
(
select
cdrs.CDR_ID
,cdrs.CALL_DATETIME
,PMANI.DESCRIPTION AS AniMode
,pmbni.DESCRIPTION as BniMode
, CARANI.DESCRIPTION AS CarrierANI
, CARBNI.DESCRIPTION AS CarrierBNI
, POIRIN.DESCRIPTION AS PoiRouteIN
, POIROUT.DESCRIPTION AS PoiRouteOUT
, RINST.DESCRIPTION AS SubTypeRouteIN
, ROUTST.DESCRIPTION AS SubTypeRouteOUT
, cdrs.CALL_DURATION as Duration
, DESTANI.DESCRIPTION as DestinationANI
, DESTBNI.DESCRIPTION as DestinationBNI

from (
select * from CDRS

) CDRS

INNER JOIN ROUTE RIN on RIN.ROUTE_ID = CDRS.ROUTE_IN_ID
INNER JOIN ROUTE ROUT on ROUT.ROUTE_ID = CDRS.ROUTE_OUT_ID
INNER JOIN PREFIX PANI ON PANI.PREFIX_ID = CDRS.ANI_PREFIX_ID
INNER JOIN PREFIX PBNI ON PBNI.PREFIX_ID = CDRS.BNI_PREFIX_ID
INNER JOIN PREFIX_MODES PMANI ON PMANI.PREFIX_MODE_ID = PANI.PREFIX_MODE_ID
INNER JOIN PREFIX_MODES PMBNI ON PMBNI.PREFIX_MODE_ID = PBNI.PREFIX_MODE_ID
INNER JOIN CARRIER CARANI ON CARANI.CARRIER_ID = PANI.CARRIER_ID
INNER JOIN CARRIER CARBNI ON CARBNI.CARRIER_ID = PBNI.CARRIER_ID
INNER JOIN DESTINATION DESTANI ON DESTANI.DESTINATION_ID = PANI.DESTINATION_ID
INNER JOIN DESTINATION DESTBNI ON DESTBNI.DESTINATION_ID = PBNI.DESTINATION_ID
INNER JOIN POIS POIRIN ON POIRIN.POI_ID = RIN.POI_ID
INNER JOIN POIS POIROUT ON POIROUT.POI_ID = ROUT.POI_ID
INNER JOIN ROUTE_SUBTYPES RINST ON RINST.ROUTE_SUBTYPE_ID = RIN.ROUTE_SUBTYPE_ID
INNER JOIN ROUTE_SUBTYPES ROUTST ON ROUTST.ROUTE_SUBTYPE_ID = ROUT.ROUTE_SUBTYPE_ID

) cd

on cd.CDR_ID = pe.CDRID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-28 : 05:40:20
what all indexes itx.PricingErrors has?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-10-28 : 05:46:07
Are you only using [CDRID] and [ErrorDescription] from PE (alias for itx.PricingErrors) ? If so does it help to explicitly just list them, rather than SELECT *

(select CDRID, ErrorDescription from itx.PricingErrors where ErrorDescription = 'Not rated CDR') pe

I'd want an index on ErrorDescription with either the ClusteredIndex=CDRID or an include field

Edit: Changed "PK" to "Clustered Index"
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2011-10-28 : 06:19:40
My guess here would be that PricingErrors table is heap and does not have a clustered index.

PBUH
Go to Top of Page

pvera
Starting Member

4 Posts
Posted - 2011-10-28 : 09:05:30
The index on PricingErrors are:

Non Clustered index over ErrorDescription WITH INCLUDE COLUMN CDRID (i do not know what it means to include a column) (ASC)
Non Clustered index over CDRID (ASC)
Non Clustered index over ErrorDescription (ASC)
PK Clustered index over PricingErrorID
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-10-28 : 10:48:50
"Non Clustered index over ErrorDescription WITH INCLUDE COLUMN CDRID "

Inner query should be "instant" then, but you may have to change SELECT * to just those two columns for the index to be used.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-10-28 : 13:03:42
quote:
Originally posted by pvera

The index on PricingErrors are:

Non Clustered index over ErrorDescription WITH INCLUDE COLUMN CDRID (i do not know what it means to include a column) (ASC)
Non Clustered index over CDRID (ASC)
Non Clustered index over ErrorDescription (ASC)
PK Clustered index over PricingErrorID


Including a column in the nonclustered index means that you can retrieve values from that column by using the index instead of having to look up the row in the clustered index to get the value

So if you have an index over errorDescription that Includes CDRID then you can use the index to:
1) Search on the errorDescription column
2) retrieve the value from CDRID from the index.

Note that the index doesn't help you search on the CDRID column -- only retrieve it.

if CDRID wasn't included in the index then you would have to do a secondary lookup on the clustered index to find the values you want.

Because you are SELECTing * then you probably can't use this index fully. Don't select *

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-10-28 : 13:17:03
all identity key columns eh....

mother function...Dear lord WHY

HOW many rows Would that retunr?

300,000??? or more???

What do you think your buffer is gonna do?

Get a chum bucket

Do you have ANY predicates you want to use?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2011-10-28 : 13:21:27
What does this give you???




SELECT COUNT(*) FROM (
			  cdrs.CDR_ID 
			, cdrs.CALL_DATETIME 
			, PMANI.DESCRIPTION			AS AniMode
			, pmbni.DESCRIPTION			AS BniMode
			, CARANI.DESCRIPTION		AS CarrierANI
			, CARBNI.DESCRIPTION		AS CarrierBNI
			, POIRIN.DESCRIPTION		AS PoiRouteIN
			, POIROUT.DESCRIPTION		AS PoiRouteOUT
			, RINST.DESCRIPTION			AS SubTypeRouteIN
			, ROUTST.DESCRIPTION		AS SubTypeRouteOUT
			, cdrs.CALL_DURATION		AS Duration
			, DESTANI.DESCRIPTION		AS DestinationANI
			, DESTBNI.DESCRIPTION		AS DestinationBNI
      FROM    CDRS 
INNER JOIN	  ROUTE RIN					ON RIN.ROUTE_ID				= CDRS.ROUTE_IN_ID 
INNER JOIN	  ROUTE ROUT				ON ROUT.ROUTE_ID			= CDRS.ROUTE_OUT_ID 
INNER JOIN	  PREFIX PANI				ON PANI.PREFIX_ID			= CDRS.ANI_PREFIX_ID 
INNER JOIN	  PREFIX PBNI				ON PBNI.PREFIX_ID			= CDRS.BNI_PREFIX_ID 
INNER JOIN	  PREFIX_MODES PMANI		ON PMANI.PREFIX_MODE_ID		= PANI.PREFIX_MODE_ID 
INNER JOIN	  PREFIX_MODES PMBNI		ON PMBNI.PREFIX_MODE_ID		= PBNI.PREFIX_MODE_ID 
INNER JOIN	  CARRIER CARANI			ON CARANI.CARRIER_ID		= PANI.CARRIER_ID 
INNER JOIN	  CARRIER CARBNI			ON CARBNI.CARRIER_ID		= PBNI.CARRIER_ID 
INNER JOIN	  DESTINATION DESTANI		ON DESTANI.DESTINATION_ID	= PANI.DESTINATION_ID 
INNER JOIN	  DESTINATION DESTBNI		ON DESTBNI.DESTINATION_ID	= PBNI.DESTINATION_ID 
INNER JOIN	  POIS POIRIN				ON POIRIN.POI_ID			= RIN.POI_ID 
INNER JOIN	  POIS POIROUT				ON POIROUT.POI_ID			= ROUT.POI_ID 
INNER JOIN	  ROUTE_SUBTYPES RINST		ON RINST.ROUTE_SUBTYPE_ID	= RIN.ROUTE_SUBTYPE_ID 
INNER JOIN	  ROUTE_SUBTYPES ROUTST		ON ROUTST.ROUTE_SUBTYPE_ID	= ROUT.ROUTE_SUBTYPE_ID
) AS XXX





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-10-28 : 14:59:49
Have you recently deleted the large number of rows from the pricingErrors table?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2011-10-29 : 00:16:59
What does this return ?

select avg_fragment_size_in_pages,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats
(DB_ID('YourDBName'),OBJECT_id('PricingErrors '),OBJECT_id('PrimaryKeyNameOnPricingErrors'),null,null)


PBUH
Go to Top of Page

pvera
Starting Member

4 Posts
Posted - 2011-10-31 : 13:37:42
It´s a really big database. CDRs table has 40 millions rows.

Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site