Row_Number Record Count - 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 2005 Forums

Transact-SQL (2005)

Row_Number Record Count

Author

Topic

JsonTerre1
Starting Member

9 Posts

Posted - 2007-02-12 : 01:06:07

I have a very large query with many joins and a huge where clause that i have converted and am now using the Row_Number function. This improved performace by 600% from a temp table method that was used prior.

Now i am using this for paging on a UI and am putting that into my where clause. while wrapping the first query in another select statement.

SELECT * FROM (
[[ The query with Row_Number ]]
) x
WHERE ....

I need to get the total number of rows before the outer where clause so that i can create my UI paging. I don;t want to create a variable and set the value of that variable because that means i am running the complex query twice and i would assume that would be less productive then what i have currently.

Can anyone make any suggestions to help. I tried to get the count this way although it causes an error:

With tempTable (
SELECT ..... Row_Number
)

SELECT @RowCount = COUNT(*) FROM tempTable

SELECT *, RowCount = @RowCount
FROM tempTable
WHERE .....

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 02:10:30

"that means i am running the complex query twice"
Is

SELECT [red]COUNT(*)/red] FROM (
[[ The query with Row_Number ]]
) x
WHERE ....

too slow to be useful?

Probably better would be to change the "query with Row_Number" to be a SELECT COUNT(*) instead of whatever the Select column list is - as this may change what indexes can be used to "cover" the query

Also worth trying the SELECT COUNT(*) before and after the main query. It may benefit from the main query running first and SQL Server having data in-memory to assist with the subsequent COUNT(*) (at least in circumstances where the majority of data was required for the main data query)

Kristen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-12 : 04:10:38

in .net for most scenerios you should return the entire row set to your application, then have the application do the paging, vs's returning sections of the data.

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 04:17:48

"you should return the entire row set to your application"

Sounds like a very slow solution to me, using truck loads of bandwidth for data that is then not needed.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 06:02:31

Why not INSERT the query data into a temporary table with IDENTITY?
Is is very easy later to "reseed" rownumber within a group.

Peter Larsson
Helsingborg, Sweden

JsonTerre1
Starting Member

9 Posts

Posted - 2007-02-12 : 11:03:55

quote:
Originally posted by Vinnie881

in .net for most scenerios you should return the entire row set to your application, then have the application do the paging, vs's returning sections of the data.

I would never do this. It is very slow when returning over 300k records. You simply can not do that. You have to only return the ones being displayed. Custom pagin is a must.

Thanks

JsonTerre1
Starting Member

9 Posts

Posted - 2007-02-12 : 11:09:42

quote:
Originally posted by Kristen

"that means i am running the complex query twice"
Is

SELECT COUNT(*) FROM (
[[ The query with Row_Number ]]
) x
WHERE ....

too slow to be useful?

Probably better would be to change the "query with Row_Number" to be a SELECT COUNT(*) instead of whatever the Select column list is - as this may change what indexes can be used to "cover" the query

Also worth trying the SELECT COUNT(*) before and after the main query. It may benefit from the main query running first and SQL Server having data in-memory to assist with the subsequent COUNT(*) (at least in circumstances where the majority of data was required for the main data query)

Kristen

This is not to slow:
SELECT COUNT(*) FROM (
[[ The query with Row_Number ]]
) x
WHERE ....

I will try the solution you sugested running the count() before and after to see which is faster. I wish there was a better way then having to run the entire where clause and joins in another query to get the count(). Seems like this method would be better if it didn't error.

With tempTable (
SELECT ..... Row_Number
)

SELECT @RowCount = COUNT(*) FROM tempTable

SELECT *, RowCount = @RowCount
FROM tempTable
WHERE .....

But it doesn't.

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 11:28:19

One other option might be to SELECT [only] the PKs of the records you are interested in to a Temp Table. Make sure that the query is covered by the indexes, so you should only, in effect, access the indexes and not the data record.

Now it will be fairly easy to COUNT(*) the rows in the temp table, and JOIN the Temp Table to whichever associated tables you need to get the actual columns to be displayed for the relevant page.

Of course you may have only been getting the PK columns in the first place ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources