Huge Records...

Question

Huge Records...

hosseini.mehran

SSC Enthusiast

Points: 116
More actions
October 23, 2010 at 12:31 am

#228232

My table has about 5.5 milion records,I was forced to Union this table with itself and now i have about 11 milion records in my result set:
SELECT FKRecordID, FKPathID, RecordID
FROM ST.RecordFK
WHERE EntityID = @EntityID
UNION
SELECT RecordID AS FKRecordID, FKPathID, FKRecordID AS RecordID
FROM ST.RecordFK
WHERE FKEntityID = @EntityID
and it goes worse when the result set join with two tables ...
my performance is done . what can i do ?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

hossein what indexes are on the table? the key to performance is having good indexes in place, along with SARG-able WHERE statements(which you seem to have)

The EntityID column name sort of implies that might be the clustered PK; is there an index on the FKEntityID? an index on FKEntityID with some INCLUDE columns on (RecordID, FKPathID, FKRecordID) might be worthy of testing to see what sort of performance lift you get.

can you show us the actual execution plan(attach the xml .slqplan here so we can help)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hosseini.mehran SSC Enthusiast Points: 116 More actions · Answer 2

I have all indexes that you said Lowell !

I can explain more : there are an SP include INSERT INTO statement , In the SELECT FROM of INSERT INTO i used the JOIN with Table-Valued Function, In the Table-Valued Function I have the UNION statement that i sent it in the first post.

so the Execution Plan can not show the process in the Table-Valued Function or maybe i don't know how i can use EP to this job!

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Absolutely do not use a table valued function for this. Those things are notorious. Since you're saying you can't see it in the execution plan, it's a multi-statement function, right? Those things have no statistics and will lead to trouble.

Try running just the SELECT query, away from the INSERT and absolutely outside the function. See what kind of performance you're getting, index use, operations, etc. Post the actual execution plan (not the estimated) if you're having trouble interpreting it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

tonydp Valued Member Points: 70 More actions · Answer 4

The UNION statement will impose a removal of duplicate records. With such large record volumes this can impose a reasonable load on the processor.

If no duplicates are expected you could use UNION ALL.

hosseini.mehran SSC Enthusiast Points: 116 More actions · Answer 5

Thanks my friends , all your guidance help me, I could decrease the time of sp from 184 seconds to 42 seconds by using covering index.

thanks a lot....

Lowell SSC Guru Points: 323504 More actions · Answer 6

hosseini.mehran (10/26/2010)
Thanks my friends , all your guidance help me, I could decrease the time of sp from 184 seconds to 42 seconds by using covering index.
thanks a lot....

how many records are you returning? 42 seconds is a lot of time; I've got a couple of tables with 30 million rows, and when looking for a specific ID, it's usually sub-second response times; unless your downloading a hundred thousand plus rows, I think you could get some better response time.s

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hosseini.mehran SSC Enthusiast Points: 116 More actions · Answer 7

Actually this is the time of my sp ! In my table "RecordFK" i have about 17million records , as i told you this table UNION with itself ! also the result set of this UNION is Right JOIN with 2 other table and... .

I now that 42 second is not good and i have problem yet but for the first step 182 to 45 is not bad . So what is your suggestion?

Regards

Lowell SSC Guru Points: 323504 More actions · Answer 8

my suggestions are still the same; show us your execution plan, and tell us how many records you are returning.

for example, if you are returning less than 10 rows, 42 seconds needs to be improved.

the only way for us to really help you is if you post the actual execution plan, so we can help you anaylyze the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 9

Try this query and see if it isn't faster:

SELECT case when FKEntityID = @EntityID then RecordID else FKRecordID as FKRecordID,

FKPathID,

case when EntityID = @EntityID then RecordID else FKRecordID as RecordID

FROM ST.RecordFK

WHERE EntityID = @EntityID or FKEntityID = @EntityID

The probability of survival is inversely proportional to the angle of arrival.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

hosseini.mehran (10/26/2010)
So what is your suggestion?

Post the whole query. Could a doctor diagnose a sore throat from looking at a single toe?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 11

ChrisM@home (10/26/2010)
hosseini.mehran (10/26/2010)
So what is your suggestion?
Post the whole query. Could a doctor diagnose a sore throat from looking at a single toe?

And the Actual Execution plan (not an estimated).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hosseini.mehran SSC Enthusiast Points: 116 More actions · Answer 12

Lowell,I put execution plan in the file, how can i attach it in this forum? I can't find any link for attaching here !?

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 13

hosseini.mehran (10/27/2010)
Lowell,I put execution plan in the file, how can i attach it in this forum? I can't find any link for attaching here !?

Scroll down a little ways when you replay. Below the box is a button labeled "Edit Attachments" You can just use the .sqlplan file, no need to zip it or anything (unless it's ginormous or something).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hosseini.mehran SSC Enthusiast Points: 116 More actions · Answer 14

hosseini.mehran

SSC Enthusiast

Points: 116

October 27, 2010 at 7:07 am

#1242852

Thanks

This is my execution plan.

regards,

M.H