SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to handle very large dataset


How to handle very large dataset

Author
Message
NineIron
NineIron
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 868
I need to find the most recent post date for all the invoices in my table. There are millions of records. Any thoughts?

[code language

select INV_NUM,

POST_DT

from (

select INV_NUM,

POST_DT,

row_number() over(partition by INV_NUM order by POST_DT desc) as RowNum

from IDX_INCOME

where GRP__2='7'

) b

where b.RowNum=1

and INV_NUM='0'




Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7132 Visits: 1347
Without you providing a create table statement and sample data in the form of inserts very generically:

select non_agregated_column, max(date_column) MaxDate
from tbl
group by non_agregated_column;


NineIron
NineIron
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 868
I know how to find the most recent records, see my query, but it's wicked slow because of the number of records. I would have to send a few million rows of sample data in order to get the same impact.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211183 Visits: 24522
NineIron - Monday, February 26, 2018 10:30 AM
I know how to find the most recent records, see my query, but it's wicked slow because of the number of records. I would have to send a few million rows of sample data in order to get the same impact.

Provide an actual execution plan, please.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
NineIron
NineIron
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 868

Pardon my ignorance but, how do I copy then paste the execution plan?


Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211183 Visits: 24522
NineIron - Monday, February 26, 2018 11:24 AM

Pardon my ignorance but, how do I copy then paste the execution plan?


Right click / Save Execution Plan As ... pick your filename & then attach.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21795 Visits: 10962
The optimiser will do this anyway but simplify your query and you clarify the index requirements:

SELECT MAX(POST_DT)
FROM
IDX_INCOME
WHERE
GRP__2 = '7'
AND INV_NUM = '0'

If you don't already have an index on GRP__2 and INV_NUM which also includes POST_DT in the KEY or INCLUDE part, then you might need one.




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
NineIron
NineIron
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 868
See attached.
Attachments
ExPlanForSSC.sqlplan (23 views, 15.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859989 Visits: 47116
NineIron - Monday, February 26, 2018 12:00 PM
See attached.


The query in that execution plan is quite a bit different than what you posted.

In the execution plan, you're doing a convert on the invoice date. What is the datatype of that date column?

You're also searching for an invoice balance of '0', which is a string rather than a numeric so please identify the datatype of the invoice column, as well.

The only thing that may make this faster is an index on the WHERE criteria and, even then, it may result in an index scan simply because it needs to a scan to enumerate the rows.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21795 Visits: 10962
NineIron - Monday, February 26, 2018 12:00 PM
See attached.


"idx_income" is an odd name for a heap! Why don't you have a clustered index? What's the purpose of this table? What's its daily/weekly cycle of changes?
And what Jeff said too - this is wildly different from your trivial original query.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search