paging on complex query with million records

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

Transact-SQL (2005)

paging on complex query with million records

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2010-10-12 : 23:24:15

Am I right that paging was not effective on a million records?

example: I have a procedure that has row_number which is created on runtime. let say the procedure contains 1M records and you run the procedure that has criteria 990000 to 1000000 (or you want to retrieve that range of records) the procedure took 15 minutes before returning the records.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-12 : 23:37:56

though row_number may not always be the most efficient approach it sounds like you have other optimization problems. 1 million rows should not take 15 minutes to query. Post your SP and script the table structures including keys and indexes.

Be One with the Optimizer
TG

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2010-10-12 : 23:52:48

Hello..

PROCEDURE
CREATE PROCEDURE usp_VCReportProcedure(@FromQuery as INT,@ToQuery as INT)
AS
SELECT * FROM (SELECT TOP 100 PERCENT ROW_NUMBER() OVER(ORDER BY (Select 0)) as RowCountID,* FROM (' & SQLQueryString & ') tblVCReport ) tblVCReport2
WHERE RowCountID Between @FromQuery and @ToQuery order by RowCountID

query
SELECT TOP 100 PERCENT
vyu_SMReportPreference.*,
tblGL.dtmDate as dtmDate,
'Account ID: ' + isnull(tblGL.strAccountID,'') + ' Total:' as strTransactionIDFooter,
isnull(tblGL.strTransactionID,'') as strTransactionID,
isnull(tblGL.strBatchID,'') as strBatchID,
isnull(tblGL.strAccountID,'') as strAccountID,
isnull(tblGL.strAccountGroup,'') as strAccountGroup,
isnull(tblGLBeginBalance.dblBeginBalance,0) AS dblBeginBal,
ISNULL(CASE WHEN qsrpGLAccountSegment.strAccountType = 'Asset' OR qsrpGLAccountSegment.strAccountType = 'Expense' THEN ISNULL(tblGL.dblDebit,0) - ISNULL(tblGL.dblCredit,0)
ELSE ISNULL(tblGL.dblCredit,0) - ISNULL(tblGL.dblDebit,0) END,0) AS dblTotal,
isnull([dblDebit],0) - isnull([dblCredit],0) + isnull([dblDebit],0) - isnull([dblCredit],0) as dblSumBalance,
CASE WHEN lower(tblGL.strReference) NOT like '%opening balance%' AND lower(tblGL.strDescription) NOT like '%opening balance%' THEN isnull(tblGL.dblDebit,0) ELSE 0 END as dblDebit,
CASE WHEN lower(tblGL.strReference) NOT like '%opening balance%' AND lower(tblGL.strDescription) NOT like '%opening balance%' THEN isnull(tblGL.dblCredit,0) ELSE 0 END as dblCredit,
dblOpeningBalances = CASE WHEN lower(tblGL.strReference) like '%opening balance%' OR lower(tblGL.strDescription) like '%opening balance%'
THEN ISNULL(CASE WHEN qsrpGLAccountSegment.strAccountType = 'Asset' OR qsrpGLAccountSegment.strAccountType = 'Expense'
THEN ISNULL(tblGL.dblDebit,0) - ISNULL(tblGL.dblCredit,0)
ELSE ISNULL(tblGL.dblCredit,0) - ISNULL(tblGL.dblDebit,0) END,0)
ELSE 0
END,
case when tblGL.strCode = 'GJ' then
case when strJournalLineDescription not LIKE '%opening balance%' then isnull(tblGL.strJournalLineDescription,'')
ELSE isnull(tblGL.strDescription,'') END
else isnull(tblGL.strDescription,'') end as strDescription,
tblGL.strCode as strCode,
tblGL.dtmDateEntered as dtmDateEntered,
isnull(tblGL.strJobID,'') as strJobID,
isnull(tblGL.strReference,'') as strReference,
isnull(tblGL.strCode,'') as strFormDrillDownCode,
'Acct ID: ' + isnull(tblGL.strAccountID,'') + ' - ' + isnull(qsrpGLAccountSegment.strDescription,'') + ' - ' + isnull(qsrpGLAccountSegment.strAccountType,'') + ' Account' AS strCOADescription,
isnull(qsrpGLAccountSegment.strAccountType,'') as strAccountType,
isnull(qsrpGLAccountSegment.strDescription,'') as strAccountDescription,
isnull(tblGL.strCode,'') as strGLCode,
qsrpGLAccountSegment.strSEgmentCode,
vyu_GLDynamicSegment.*
FROM vyu_SMReportPreference,
tblGL LEFT JOIN tblGLAccount as qsrpGLAccountSegment
ON tblGL.strAccountID = qsrpGLAccountSegment.strAccountID
left join vyu_GLDynamicSegment
on vyu_GLDynamicSegment.AccountID = tblGL.strAccountID
Left Join tblGLBeginBalance
on tblGLBeginBalance.strAccountID = tblGL.strAccountID
WHERE tblGL.ysnIsUnposted = 0

INDEX
NONCLUSTERED - strAccountID,strAccountGroup,cntID,dtmDate,strBatchID,strTransactionID,strProductID
CLUSTERED - cntID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources