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


Best practices are really the best (fastest) solution?


Best practices are really the best (fastest) solution?

Author
Message
ricardo_chicas
ricardo_chicas
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 694
Hello all,
I was "optimizing" a procedure that was taking a long time to run, and I am really confused about the results,
That procedures did theses things
part a)
select into #temptable from table 1 join table 2 join table 3 ( table 2 and 3 weren't really needed)
( about 50 million records)
create indexes
part b)
select into #temptable2 from ...
create indexes
part c)
select into #temptable3 from ...
create indexes

part d)
select into #temptable4 from #temptable1 join #temptable2 join #temptable3
(about 5 million records)

Resultant time 50 minutes


I changed it to something like this
Part a)
create table #temptable1
create clustered index
insert into #temptable1 select from table1 ( all needed data was here)
create non clustered indexes

Part b)
create table #temptable2
create clustered index
insert into #temptable2 select from ...
create non clustered indexes

Part c)
create table #temptable3
create clustered index
insert into #temptable3 select from ...
create non clustered indexes

Partd d)
select into #temptable4 from #temptable1 join #temptable2 join #temptable3

Resultant time 4 hours


Somebody can explain to me why such a thing is possible? where can I find the very best practices for tuning?
I believed that I was following the rules and look at what happened!

Thanks in advance
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39140 Visits: 9291
Your question isn't entirely easy to answer. You're missing a few pieces. Like, were you creating clustered indexes in the first sequence, or non-clustered indexes? Secondly, did you change any of the other Select queries like you did the first one?

Things like that will change performance. None of us can give you a decent answer to your problem with the pseudo-code you've listed. However... I can tell you this. Clustered indexes suck for data insertion. They're great for reads, not so much for writes. And 50 million rows? That's a LOT of data. I'm betting your second attempt spent most of that 4 hours just on part a.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ricardo_chicas
ricardo_chicas
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 694
Thanks for the quick response
I didn't change the indexes, only the order
I dind't change the other querys since they already use the correspondant indexes, I only changed the first one since it used 3 tables and all the data was on the fist one and reacheble with a clustered index
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39140 Visits: 9291
Okay, the best way to figure this out is to try Part A of both methods and time them. I'm betting this is where the problem is. Because you're creating the clustered index prior to data load, it's forcing the data to get reordered as it's inserting.

But that's a guess. As I said, the best way to know for sure is to ignore the other parts, and just do Part A of both methods in two separate SSMS windows. Time it, have it Display Execution Plan, and look at the plan for both bits when it's done running.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
skrilla99
skrilla99
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1321
I think that best practice is to create indexes after the load.
Otherwise you are doing index maintenance for each insert operation which I would think would be substantially slower.

-Dan B



skrilla99
skrilla99
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1321
Oops! Ditto what Brandie wrote...



ricardo_chicas
ricardo_chicas
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 694
Already did that test
Part A, original sproc 14 minutes/New sproc 15 minutes
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39140 Visits: 9291
Then keep doing the tests one at a time until you find out which part of your method changed so much that it's taking 4 hours to do.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46085 Visits: 14925
My first question is, what are you doing with the data that you need all the temp tables? That's probably a lot of spill to disk that might be avoided by avoiding temp tables all together.

Why can't you just do your final join on the base tables that are filling the temp tables? That "might" be faster than the loading temp tables with 50million rows to return 5 million.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221414 Visits: 42002
The second most important part of optimizing is to know how long each part took using the old way and how long it takes using the new way. Otherwise, you're tuning blindly and are likely going to make things very much worse. :-)

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